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
Find out how semantic modeling is changing data modeling and what the future holds for the use of semantic technologies in data modeling. Continue Reading
Learn the functions and potential benefits of a conceptual data model, from data modeling expert Pete Stiglich. Continue Reading
Learn how to convert a logical data model into a physical data model via data modeling best practices. Find out how DDL and your data modeling ... Continue Reading