Skip to content
Software Architecture Guide

Physical Data Model

Explanation

Section titled “Explanation”

A physical data model is the most concrete level of data modeling. The logical data model is more abstract. The physical model is tied to one database management system (DBMS), the product that stores and serves the data. See the articles Logical Data Model and Conceptual, Logical and Physical Data Model for the other levels.

What it contains

  • Primary keys and foreign keys. A primary key identifies a row. A foreign key points to a row in another table.
  • Indexes. These help the database find data faster.
  • Storage details such as filegroups, tablespaces, and partitioning. Partitioning splits stored data into parts.

How it is produced

Converting the logical model into a physical one includes assigning storage, for example partitioning and tablespace assignment. A data modeler drafts a first-cut physical model. The database administrator (DBA) then refines it.

Deviating from the logical model

The physical model may denormalize the design for performance. Denormalizing means deliberately departing from the clean logical structure. Choices about indexes, clustering, and denormalization can force the physical model to differ from the logical one.

An alternative style: Data Vault

Data Vault is one way to shape a physical design. It uses three kinds of tables:

  • Hubs hold unique business keys that rarely change.
  • Links are essentially many-to-many join tables with some metadata.
  • Satellites hold descriptive attributes, usually grouped by source system.

Example

Section titled “Example”

A team has a logical model with Customer and Order. The modeler drafts a first physical version for the chosen database. The DBA then adds an index on the customer key to speed up lookups. The DBA also partitions the large order table and assigns it to its own tablespace. If a slow report needs it, the team may denormalize a table and accept a gap from the logical model.

Trade-offs

Section titled “Trade-offs”

Tuning for performance can pull the physical model away from the logical model. Denormalization, index choices, and clustering are typical causes. Because the physical model is tied to one DBMS, it is also specific to that product.

From logical model to a refined physical model

Diagram source 
flowchart LR
  A[Logical model] --> B[Modeler drafts physical model]
  B --> C[DBA refines: keys, indexes, storage]
  C --> D[Physical model for one DBMS]

Checks

Section titled “Checks”
  • The physical model names the target DBMS it is designed for.
  • Primary keys, foreign keys, and indexes are defined for data access.
  • Storage choices such as partitioning and tablespaces are documented.
  • Deviations from the logical model, such as denormalization, are stated and justified.
Section titled “Related”

Sources

Section titled “Sources”