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 modeling tools and techniques
Related Q&A from Pete Stiglich
Learn about the roles of data architects when it comes to making data management project decisions. Continue Reading
Find out why it’s important and how to organize an enterprise conceptual data model development. Learn how a data governance organization can help ... Continue Reading
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