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.
Physical Data Model
Comparing the logical data model
shown above with the logical data model diagram, we see the main differences between the two:
- Entity names are now table names.
- Attributes are now column names.
- Data type for each column is specified. Data types can
be different depending on the actual database being used.
