Model Type Selection and Why FKs Don’t Migrate to Child Entities in Logical Model
In Toad Data Modeler you can create a logical model (database independent) and physical models specific for every target database platform, for example for Oracle 10g, MS SQL 2008, MySQL 5 etc.
Of course, there are differences between logical and physical models. Logical model is a general model where you can design just a basic structure without regard to database specific items. On the contrary, physical models contain specific items and detailed settings for the selected target database.
Key differences also include the following:
• Inheritance can be modeled only in logical model
• FK attributes don’t migrate to child entities in logical model
Before you start your database development, it is critical to make the right decision and consider if you do want to start with logical model or physical model.
Recommendations:
1. If you do not plan to use inheritance, start with physical model.
2. If you need to define database specific items, like Autoincrement property etc., start with physical model.
3. If you need to create a model for multiple target database platforms, for example one for Oracle and one for MS SQL – you can consider both of the options 1 and 2. You can convert a logical model to two models, one for Oracle and second for MS SQL Server (and others, of course. LER to PER conversion). However, if you start with physical model for Oracle, you can also easily convert the model to MS SQL Server model (PER to PER conversion).
Note: If you are used to defining descriptive names for some objects - physical and logical names for tables, views and other objects, you can also choose physical model. Example: physical name of table is CST. More descriptive name for the same table CST is Customer. Both names can be defined in a physical model in Toad Data Modeler (while in some other database modeling tools it is possible to specify only certain logical information in logical model).
Inheritance in Logical Model
In logical models you can model inheritances. Toad Data Modeler allows you to define three methods of the inheritance conversion. Each of the three options significantly affects the converted physical model. See the examples below
Logical Model:
Inheritance Settings:
Possible Outputs:
A] Single Table
B] N-1 Tables
C] N Tables
As you can see in the example, converted physical models can have one table, two tables or three tables and one ID attribute, two ID attributes or three ID attributes. Inheritance settings affect the final physical model and that’s why FK attributes don’t migrate to child entities in logical model. It doesn’t seem to have a sense to display three ID attributes in logical model when there finally will be only one or two attributes as shown in output B] and C].
Conversion from logical to physical model is not only about conversion of data types and adding logical names to tables and columns. It is a more complicated task. Some other products show FK attributes in child tables in logical models, but such products probably don’t support three methods of conversion from logical to physical models.
New TDM Versions – Logical or Physical?
In the latest BETA version and in future versions of Toad Data Modeler, the following dialog will be shown every time you create a new logical model. We believe it will help you to decide which model to select to start your modeling work.
If you have any questions, please do not hesitate to ask us via the Modeling community forum.
Thank you.
Vaclav + Vladka