Comparing Logical and Physical ERD

Logical 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.

Physical ERD represents the actual design of database. It deals with conversion from logical design into a schema level design that will be transformed into relational database. When modeling a physical ERD, Logical ERD is treated as base, refinement occurs by defining primary keys, foreign keys and constraints. Sometimes, relationships need to be resolved by introducing additional tables, like a Linked table for a many to many relationship.

Since physical ERD and logical ERD represent the business requirement and database schema respectively, comparing physical and logical ERD helps to find out the differences between them, thus confirming the database is exactly following the initial business requirements regardless of the changes.

Visual Paradigm's Model Transitor enables you to transit a logical ERD to a physical ERD with the transition relationship well maintained. In this tutorial, we'll see how to make use of Model Transitor to transcribe a logical ERD into physical ERD. After that, we will make use of Visual Diff, a tool for comparing changes between diagrams, to trace the differences between them.

In order to complete this tutorial you must have Visual Paradigm installed. You also need to have basic knowledge in database design and data modeling with Visual Paradigm.

Create a Project for This Tutorial

In order not to mess up your production data, we will create a new project for this tutorial. In this section, you are going to create such a project.

1. Select Project > New from the toolbar.
2. Enter ERD Sample as project name.
   
3. Click Create Blank Project to confirm the creation.

Drawing a simple logical ERD

In this section, we are going to draw a simple logical ERD that contains just two entities.

1. Select Diagram > New from the toolbar.
2. In the New Diagram window, select Entity Relationship Diagram and click Next.
   
3. Enter Logical ERD as diagram name.
   
4. Let's put the ERD to be created in a model for better grouping. Click ... next to Location.
5. In the Select Parent Model window, click New Model.
6. In the Model Specification window, enter Logical Model as name and click OK.
   
7. Click OK in the Select Parent Model window.
8. Click OK to confirm the creation of Entity Relationship Diagram. On the right hand side of the diagram you should see a floating panel Data Model, with the option Logical Model selected. If you find Physical Model selected, it's possible that you have entered a wrong name in step 6. If that's the case, select Logical Model manually now.
   
9. Click on the background of diagram. The Data Model panel will then disappear.
10. Draw two entities Customer and Order in the ERD.
    
11. Add the following columns into the Customer entity:

    Column
    name : varchar(255)
    address : varchar(255)
    order count : integer (10)

    
    
12. Add the following columns into the Order entity:

    Column
    amount : integer (10)
    date : date

    
    

    That's all for our logical ERD. Since it aims at representing business requirements, it's quite simple and, as you can see, we haven't add any keys yet.

From Logical ERD to Physical ERD

In this section, we are going to form a physical ERD from the logical ERD drew in the previous section.

1. Right-click on the background of the logical ERD and select Utilities > Transit to Physical ERD... from the popup menu.
2. This time, let's group the diagram to be created in a model named Physical Model. In the Select Parent Model, click on the project root node first.
   
3. Click New Model.
4. In the Model Specification window, enter Physical Model as name and click OK.
   
5. Keep Physical Model selected in the Select Parent Model window and click OK.
6. You should see a new ERD formed, which looks quite similar to the logical ERD, except that the entities are in orange and with a primary key column automatically created.
   

   Now, let's modify it to make the diagram a real physical ERD.

7. First, one Customer can produce multiple Order. Let's connect them with a one to many relationship. Click OK when you are prompted for the creation of foreign key column.
   
8. The column order_count is redundant because the number of order can be obtained by counting the number of Order record associated to a particular customer. Let's delete this column. Select it and press Delete.
   
9. Order is a reserved word that cannot be used as table name. Let's rename the Order entity to Purchase_Order.
   

Comparing Logical and Physical ERD with Visual Diff

Let's compare the two ERDs. Doing so allows us to know the differences between the logical model and physical model.

1. Let's open the logical ERD. To open it, click on Switch Diagram on the navigation bar and then double click on the logical ERD to open it.
2. Select Modeling > Visual Diff from the toolbar.
3. Select the physical ERD on the right hand side of the Visual Diff window.
   
   
   • Top: Settings for controlling how and what to compare
   • Left hand side: A list of diagrams in the two projects selected on left and right hand side
   • Middle: A pane that has two sides. Each side represents a project and one of its diagram. Comparison is made for the two sides.
   • Bottom: Difference of the two diagrams are shown here.
4. At the top left corner of the Visual Diff window, select Transitor to be the comparison strategy.
   
   
   There are three types of strategies. They are:
   
   • ID: Shapes will be matched base on their internal model element ID. This way of comparison is useful when visualizing differences for different stages of design.
   • Name: Shapes will be matched base on their names. This way of comparison is useful when visualizing differences for external works. Typical examples are to compare databases and class models.
   • Transitor: Shapes will be matches base on their transition established by Model Transitor. This way of comparison is useful when visualizing differences for different Models.
5. Next to the Strategy setting, there is a drop down menu for selecting the things to compare. For View, differences like the coordinate of shape will be reported. For Model Element, differences such as the name of model element or other specification-level changes will be reported. As we are interested in knowing only the differences at schema level, select Model Element.
   
6. Check the results. Differences between logical and physical ERD can then be found easily.



1. ID column has been added in physical ERD.
2. The one-to-many relationship has been added.
3. Column order count has been deleted.
4. Entity Order has been renamed to Purchase_Order.