How to Keep ERD and Data Dictionary Synchronized?

What is Data Modeling?

Data modeling is the process of representing data objects and their relationships to other objects. Data modeling is performed to define the data requirements of an information system in supporting business processes.

What is a Data Dictionary?

A data dictionary is a textual description of data objects and their inter-relationships. It is commonly used to confirm data requirements and for database developers to create and maintain a database system. A data dictionary describes the physical attributes of a data element. What is the data type (string, boolean, integer, etc.)? What is the maximum length? What is the format?

What is this Tutorial About?

This is a data modeling tutorial written to outline the steps you need to take to perform data modeling and produce a data dictionary in Visual Paradigm.

Preparation

To complete this tutorial, make sure you have Visual Paradigm downloaded and installed. Click here to download Visual Paradigm if you do not have it installed.

Part I - Data Modeling

In this section, we will use the ERD tools to create a simple database design.

1. Create a new project in Visual Paradigm. You can create a project by selecting File > New Project from the main menu.
2. To create an Entity Relationship Diagram, select Diagram > New from the toolbar.
3. In the New Diagram window, select Entity Relationship Diagram and click Next. Give the diagram a name and click OK to confirm.
   
4. Draw a Company entity. To draw an entity, select Entity from the diagram toolbar (on the left-hand side of the diagram) and click on the blank area of the ERD to create one.
   
5. Right-click on the Company entity and select New Column from the popup menu.
   
6. Enter + ID : integer. The "+" stands for a primary key, while ID is the name of the column and integer is its type.
   
7. Press Enter and create another column: name : varchar(255). This column is not a primary key, so you don't need to add a "+" before it. It's a varchar with a length of 255.
   
8. Press Enter to confirm editing.
9. Press Esc to complete column creation.
10. Let's create one more entity. Move your mouse pointer over the Company entity. Click on the Resource Catalog icon at the top right and drag it out.
    
11. Release the mouse button in the empty space on the right-hand side. Select One-to-Many Relationship -> Entity from the Resource Catalog.
    
12. Enter Employee as the entity name.
    
13. Right-click on the Employee entity and select New Column from the popup menu.
14. Again, enter +ID : integer to create a primary key for this entity.
15. Press Enter and create another column: name : varchar(255).
16. Press Enter and create another column: jobtitle : varchar(50).
17. Press Enter to confirm the edit.
18. Press Esc to complete column creation.
    
19. Let's describe the entities and columns in detail. Open the Description Pane by clicking the Show Description button at the bottom right of the application window.
    
20. Click on the Company entity and enter its description: "A company is a business unit that provides goods or services."
    
21. Similarly, click on Employee and enter its description: "An employee is someone who works in a company."
22. Repeat the steps for the columns in the Company entity:
    ID - For the unique identification of company records.
    name - The name of the company.
    
23. Repeat the steps for the columns in the Employee entity:
    ID - For the unique identification of employee records.
    name - The name of the employee.
    jobtitle - The position of the employee in a company.

Part II - Producing a Data Dictionary

Time to produce a data dictionary! There are several reporting tools you can select to produce a data dictionary. In this tutorial, let's try Doc. Composer.

1. Select Tools > Doc. Composer from the toolbar.
2. Select Build Doc from Scratch in Doc. Composer.
   
3. This creates an empty report. You need to fill it with content. In this tutorial, the data dictionary. Now, click on the ERD listed under the Diagram Navigator.
   
4. The Template Pane (at the bottom left of the application window) lists the available templates for an ERD. Select Data Dictionary and drag it to the document.
   
5. Release your mouse button to produce the content of the data dictionary.
   
6. Are you happy with the content? Let's try something different. Create a new document. Again, select Tools > Doc. Composer from the toolbar and then select Build Doc from Scratch in Doc. Composer.
7. Click on the ERD listed under the Diagram Navigator.
8. This time, select the Basic template and drag it onto the document.
   
9. Select the Data Dictionary 2 template and drag it onto the document, under the ERD image.
   
10. Let's change the pages to show in landscape. Click on Document Properties... in the toolbar.
    
11. In the Document Properties window, open the Page Setup tab.
12. Select Landscape for Page Orientation.
    
13. Click OK to return to Doc. Composer. Looks pretty nice now, doesn't it?
14. Now, let's produce a Word document from Doc. Composer. Click the Export button at the top right corner of Doc. Composer, and then select Word Document... from the popup menu.
    
15. In the Export Word Doc. window, specify the output path of the Word file and click Export.
    
    You now have a Word document of your data dictionary. In practice, you can send this to your client, the database administrator, or anyone who needs to refer to it.

Part III - Keeping the Data Dictionary Up-to-Date

One of the most important, yet challenging, parts of documenting a design is keeping the document up-to-date, showing the latest changes made in the design. Imagine if you have added more entities to the ERD, or have updated the definition of some entities. You will probably need to update the data dictionary to reflect the latest changes. The Doc. Composer provides a 'refresh' function to accomplish this. Let's try it.

1. Open your ERD.
2. Add an entity named Project from the Employee entity, with a one-to-many relationship in between.
   
3. Now, go back to your document with the data dictionary.
   On top of the document, there is a toolbar. Click the Refresh button.
   
4. You should see the document content updated to show the new entity in the image.
   
   So now, you can export an updated data dictionary and send it to anyone who needs it. :-)