Database Data Modeling

The three levels of data modeling.

Conceptual data model

A conceptual data model identifies the highest-level relationships between the different entities. Features of conceptual data model include: Includes the important entities and the relationships among them. No attribute is specified. No primary key is specified.

Logical data model

A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database. Features of a logical data model include: Includes all entities and relationships among them. All attributes for each entity are specified. The primary key for each entity is specified. Foreign keys (keys identifying the relationship between different entities) are specified. Normalization occurs at this level. The steps for designing the logical data model are as follows: Specify primary keys for all entities. Find the relationships between different entities. Find all attributes for each entity. Resolve many-to-many relationships. Normalization.

Physical data model

Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Features of a physical data model include: Specification all tables and columns. Foreign keys are used to identify relationships between tables. Denormalization may occur based on user requirements. Physical considerations may cause the physical data model to be quite different from the logical data model. Physical data model will be different for different RDBMS. For example, data type for a column may be different between MySQL and SQL Server. The steps for physical data model design are as follows: Convert entities into tables. Convert relationships into foreign keys. Convert attributes into columns. Modify the physical data model based on physical constraints / requirements. The figure below is an example of a physical data model.

Here we compare these three types of data models. The table below compares the different features:

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

Below we show the conceptual, logical, and physical versions of a single data model.