Logical vs. Physical Dimensional Data Models

• Logical Data Model: suits business requirements, creates and maintains the lookup data, compares the versions of data model, maintains change log, and generate reports from data model.
• Logical Data Model:
• Represents business information and defines business rules
• Entity
• Attribute
• Primary Key
• Alternate Key
• Inversion Key Entry
• Rule
• Relationship
• Definition

• Physical Data Model: defines the source and target database properties. Should know the technical-know-how to create data models from existing databases and to tune the data models with referential integrity, alternate keys, indexes, and how to match indexes to SQL code. It would be good if the physical data modeler knows about replication, clustering, and so on.
• Physical Data Model:
• Represents the physical implementation of the model in a database.
• Table
• Column
• Primary Key Constraint
• Unique Constraint or Unique Index
• Non Unique Index
• Check Constraint, Default Value
• Foreign Key
• Comment

Feature	Conceptual	Logical	Physical
Entity Names	✓	✓
Entity Relationships	✓	✓
Attributes		✓
Primary Keys		✓	✓
Foreign Keys		✓	✓
Table Names			✓
Column Names			✓
Column Data Types			✓