Steps to create Data Model

Steps to create and maintain a new data model from business requirements:

These are the general guidelines to create a standard data model and in real time, a data model may not be created in the same sequential manner as shown below. Based on the enterprise’s requirements, some of the steps may be excluded or included in addition to these.

Sometimes, data modeler may be asked to develop a data model based on the existing database. In that situation, the data modeler has to reverse engineer the database and create a data model.

Note:

As a data modeler, the intention is to create tables, columns, datatype, not null, primary keys, and foreign keys. If there is a need for sequence, views, triggers and other database parameters, the data modelers should not take a hasty decision by embedding all objects in the data model. Before creating the data model, it is a good practice to understand the database object creation process by discussing with DBAs or top-notch technical executives and take it forward.

Steps to create a Logical Data Model:

1. Get Business requirements.
2. Analyze Business requirements.
3. Create High Level Conceptual Data Model. Get it approved.
4. Create a new Logical Data Model. Add the following to the logical data model.
5. Select target database where data modeling tool creates the scripts for physical schema.
6. Create standard abbreviation document for naming logical and physical objects according to business/data modeling standard.
7. Create domain.
8. Create rule.
9. Create default.
10. Create Entity and add definitions.
11. Create attribute and add definitions.
12. Assign datatype to attribute. If a domain is already present then the domain should be attached to attribute.
13. Add check constraint/rule or default to the columns (wherever it is required).
14. Create primary or unique keys to attribute.
15. Create unique index or bitmap index to attribute.
16. Based on the analysis, create surrogate key columns.
17. If required, create super types and sub types.
18. Analyze the relationship between entities and Create foreign key relationship (one to many or many to many) between those entities.
19. Create subject areas and add relevant entities to those subject areas.
20. Align the objects in the main subject area and other subject areas.
21. Validate the data model.
22. Generate reports from the data model.
23. Take a print out of the data model.
24. Get it approved.

Steps to create a Physical Data Model:

1. Create Physical Data Model from the existing logical data model.
2. Add database properties to physical data model.
3. Generate SQL Scripts from Physical Data Model. Tick or check the necessary parameters in the tool, create scripts and then forward that to DBA. (DBA will execute those scripts in database).
4. Compare database and data model. Make sure everything is okay.
5. Create a change log document for differences between the current version and previous version of the data model.

Maintenance of Data Models:

• Maintain Logical & Physical Data Model.
• For each release (version of the data model), try to compare the present version with the previous version of the data model. Similarly, try to compare the data model with the database to find out the differences.