Conceptual, Logical and Physical Data Model

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.

With Visual Paradigm, you can draw the three types of model, plus to progress through models through the use of Model Transitor.

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.

Conceptual ERD example


NOTE: Conceptual ERD supports the use of generalization in modeling the 'a kind of' relationship between two entities, for intance, 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.

Logical ERD example

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.

Physical ERD example

Transiting from Conceptual/Logical to Physical ERD

Model Transitor enables you to transit a logical ERD to a physical ERD and with the transition relationship maintained. To transit, right click on the background of your conceptual/logical ERD, and then select Utilities > Transit to Logical/Physical ERD... from the popup menu. This will form a new ERD with new entities there. You can make changes like to rename the entities and columns, or to add extra entities in the new ERD.


NOTE: Alternatively, select Transit to Logical ERD or Transit to Physical ERD from the action bar on the right of an ERD to transit the opened, conceptual ERD to logical/physical, or to transit a logical ERD to physical ERD.


Related Resources

The following resources may help you to learn more about the topic discussed in this page.

1. Drawing Entity Relationship Diagram Table of Contents 3. Using Entity Domain