You can easily document your database with Data Modeler: just reverse engineer the database with File-> Import -> Data Dictionary. But what if the database has no foreign keys? What’s the point of documenting then? Just tables that has nothing to do with each other….
Well, Data Modeler can help with that too: you can use “Discover Foreign Keys” functionality to find the foreign keys that should be in the database and even generate the DDLs for them if needed…
There are two ways Data Modeler can guess the foreign keys for you: the column has the same name as a primary key column in parent table or using the naming templates. And combinations of those.
Select the relational model from the Browser. Right-click and select “Discover Foreign Keys”. I have two tables: CUSTOMER and ORDER.
I will ask Data Modeler to find me the foreign keys.
If I select “Referred Column” as the “FK Column name property” and press “Scan Again”, Data Modeler suggests that column CustNo might be a potential foreign key column.
If I agree on that I press OK and the foreign key has been created automatically into my relational model.
If I select “FK Column Template” as the “FK Column name property” and press “Scan Again”, Data Modeler suggests that column Customer_ID might be a potential foreign key column.
The reason for that is that I have defined in my Design Properties that a foreign key column will be named as PARENTTABLE_ID.
If you want Customer_ID to be the foreign key just press OK.
You can also select either “Referred and Template” or “Template and Referred” as the “FK Column name property”. The first one tries to find first columns with fitting name and secondary columns that fit the template, the latter does the opposite. In my example if I select “Referred and Template” the result would be the same as with “Referred” and if I select “Template and Referred” it would be the same as with “FK Column Template”. But if you have both naming standards in the database the results might be very different.
When you have created the discovered foreign keys into the logical model remember to press “Engineer to Logical Model” to get the relationships to entities and to the logical model as well.
All discovered foreign keys have a Dynamic Property (createdByFKDiscoverer) attached to them and can be easily found using that. If you want to remove the discovered foreign keys, select “Remove Discovered Foreign Keys “ from “Edit” menu. The foreign keys are removed without notice. Note that the Dynamic Property is not engineered to the relationship in logical model. If you decide to remove the discovered foreign keys for some reason remember to press “Engineer to Logical Model” again so that the relationships will also be removed.
I am sure you already guessed that there are some restrictions:
– the parent table must have either a Primary Key or a Unique Key that will be used while comparing
– the columns compared must be of same datatype, so not only the naming is enough
I think this is a great functionality and I am sure you will save a lot of time and money with it. Have fun!
HeliFromFinland 