Compare Logical and Physical ERD

Entity relationship diagram (ERD) represents a detailed picture of the entities needed for a business. In forward engineering, ERD will be transformed into a relational database eventually. There are at least two types of ERD — Logical and Physical. They are used in different stages of development, and are inter-related.

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.

From Logical Model to Physcial Model...

Here we have created a logical ERD.
To transit this logical ERD into a physical ERD:
1.	Right click on the ERD background and select Transit to New Diagram... from the popup menu. This opens the Select Parent Model of New Diagram dialog box.
2.	We need to create a model for storing the physical ERD. First, select the project root node. Then, click New Model at the top right of the dialog box. After that, enter Physical Model as model name. Once we have confirmed the creation of model, we can see a new model node appear in the dialog box.
3.	Keep Physical Model selected and click OK to confirm the selection of model.
4.	This forms the physical ERD. We need to refine it to make it fulfill the database design.
5.	Here we have a completed physical ERD which reflects the database design.

Show Differences of Logical and Physical Model by Comparing the Two ERDs

1.	From the logical ERD, right click on the diagram background and select Utilities > Visual Diff... to open it.
2.	The Visual Diff tool is started.
	Here is a description of different parts of the Visual Diff tool. 1) Settings for controlling how and what to compare 2) A list of diagrams in the two projects 3) A pane that has two sides. Each side represents a project and one of its diagram. Comparison is made for the two sides. 4) Difference of the two projects are shown here.
3.	The left hand side is filled in with the currently opening diagram. Let's keep it unchanged. Now, select Use Working Project for the right hand side.
4.	Select the physical ERD to compare with.
5.	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.
6.	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.
7.	Check the results. Differences between logical and physical ERD can then be found easily.
	1. Entity ORDER_LINE is newly added in physical ERD. 2. Entities are renamed. 3. Primary keys are added. 4. Foreign keys are added.

Resources