Conceptual, logical and physical model or ERD are three different ways of modeling data in a domain. While they all contain entities and relationships, they differ in the purposes they are created for and audiences they are meant to target. A general understanding to the three models is that, business analyst uses conceptual and logical model for modeling the data required and produced by system from a business angle, while database designer refines the early design to produce the physical model for presenting physical database structure ready for database construction.
In Visual Paradigm, you can draw and keep track of the traceability between conceptual, logical and physical model.
Conceptual Model
Conceptual ERD models information gathered from business requirements. Entities and relationships modeled in such ERD are defined around the business’s need. The need of satisfying the database design is not considered yet. Conceptual ERD is the simplest model among all.
Note: Conceptual ERD supports the use of generalization in modeling the ‘a kind of’ relationship between two entities, for instance, Triangle, is a kind of Shape. The usage is like generalization in UML. Notice that only conceptual ERD supports generalization.
Logical Model
Logical ERD also models information gathered from business requirements. It is more complex than conceptual model in that column types are set. Note that the setting of column types is optional and if you do that, you should be doing that to aid business analysis. It has nothing to do with database creation yet.
Physical Model
Physical ERD represents the actual design blueprint of a relational database. It represents how data should be structured and related in a specific DBMS so it is important to consider the convention and restriction of the DBMS you use when you are designing a physical ERD. This means that an accurate use of data type is needed for entity columns and the use of reserved words has to be avoided in naming entities and columns. Besides, database designers may also add primary keys, foreign keys and constraints to the design.
Synchronization between Conceptual, Logical and Physical ERD
The model synchronization feature helps you maintain the traceability between conceptual, logical and physical model. Instead of drawing a logical or physical model from scratch, use the synchronize feature to produce an initial ERD from its previous form (i.e. conceptual / logical model), and then make the changes needed to create a logical or physical model. This not only save you time but also help you maintain the traceability among the models. Further changes made to a model can be synchronized to its subsequent form (e.g. from conceptual to logical). You also can navigate between models through the context menu of entities.
How to create logical/physical model from conceptual/logical model?
If you have created a conceptual ERD and now you want move on to developing the logical ERD, perform the steps below to produce an initial logical ERD:
- Right click on the background of the conceptual ERD and select Utilities > Synchronize to Logical ERD… from the popup menu.
- In the Select Parent Model of New Logical ERD window, select the model to store the ERD and the entities to be generated. You can create a new model by clicking New Model at top right. Click OK.
- An initial logical ERD is formed. Modify it to create the logical ERD.
The same technique can be applied in creating a physical ERD from logical ERD.
Abbreviation set
When you synchronize from logical to physical ERD, you will be prompted to apply abbreviation to create/update the physical names of schema, entities and columns in physical ERD. As you may need different names for physical entities to avoid the use of reserved words or to follow certain database management guideline, abbreviation is a mechanism that helps you produce legit name when creating / updating objects in physical ERD during the synchronization from logical to physical ERD. For instance, you can define an abbreviation “PUR_ORDER” for entity name “Order” to have physical entity “PUR_ORDER” produced from logical entity “Order” upon synchronization.
How to synchronize changes between model?
When changes are made to an ERD, you can synchronize the changes to the subsequent form. To do this, right click on the background of the source ERD and select Utilities > Synchronize to Logical/Physical ERD… from the popup menu.
How to navigate between models?
To open an entity under another model, move your mouse pointer over that entity and click on the tiny Model Transitor icon at bottom right. Select Transit From/To > <model> to instantly jump to the previous/next form of that entity.
