Could you please let me know the key mandatory steps that have to be followed while converting a logical data model...
into a physical data model?
For a community in which semantics is of great importance, the data modeling community is itself in need of semantic disambiguation. While we all wait for that day, the way that I understand the logical model is that it resides between the conceptual and physical model levels and is comprised of entities, relationships, attributes and keys. Names are not abbreviated and acronyms are avoided in order to provide better understandability to the business.
When converting the logical data model to a physical data model, some common key steps include:
- Assignment of DBMS data types
- Name abbreviation (if necessary)
- Identifying non-key indexes
- Assignment of storage (e.g., partitioning and tablespace assignment)
- Generation of the data definition language (DDL) to create/update the database
How you convert the logical data model to a physical data model also will depend upon your application and your data modeling practices. For a data warehouse application, for example, foreign-key constraints in the database might be disabled due to the performance impact they can have on bulk loads, although you certainly would want foreign-key constraints to be enabled for OLTP applications.
A common modeling practice is to have a data modeler develop the conceptual and logical data models as well as a first-cut physical data model. After that is completed, the physical data model is then further developed and refined by the database administrator (DBA). For example, assignment of storage is usually the domain of the DBA.
Dig Deeper on Data integration
Related Q&A from Pete Stiglich
Get best-practices advice and tips for choosing and evaluating data modeling tools. Also, learn how to define data modeling requirements and use your... Continue Reading
Learn the differences between enterprise level and project level conceptual data modeling, and find out how both approaches are compatible. Continue Reading
Learn two common approaches to converting a data model from a conceptual design to a logical design. Continue Reading