Lesson 5 — Conceptual, Logical, Physical, and Canonical Models
The easiest way to remember model levels is to ask how much implementation specificity has been introduced.
Requirements → CDM → LDM → PDM
Each move to the right adds precision and implementation detail. Modeling remains iterative, so new discoveries may send the team backward to correct an earlier assumption.
Conceptual Data Model (CDM)
The CDM is the high-level business view.
Typical content: - major business concepts/entities; - major relationships; - scope boundaries; - shared vocabulary and terminology.
It is intentionally not overloaded with DBMS-specific details.
If a “conceptual” model contains indexes, column lengths, DBMS datatypes, physical partitions, or vendor-specific storage choices, the level is being mixed.
Logical Data Model (LDM)
The LDM is detailed but technology-independent.
Typical content: - detailed entities; - atomic attributes; - detailed relationships/cardinality; - domains; - candidate/primary/alternate keys; - normalized structures; - detailed business/data requirements.
Think: enough precision to define what the solution must represent, but not yet tied to a specific DBMS implementation.
Physical Data Model (PDM)
The PDM is technology-specific implementation design.
Typical content: - tables and columns; - physical datatypes/lengths; - nullability/constraints; - indexes; - partitions; - views/materialized views; - physical key choices; - physical reference-data implementation; - justified denormalization; - vendor/platform naming constraints.
Canonical data model — data in motion
A canonical model represents a generic/shared structure for messages or payloads exchanged between systems.
It is easy to confuse with a logical or physical database model because the same business names can appear in all of them. The deciding clue is interface/message structure for data in motion, not stored database structure.
Example: ten services exchange Customer events. Instead of ten unrelated point-to-point payload definitions, the enterprise uses a canonical Customer message structure and maps senders/receivers to it.
Views and materialized views
A view is a virtual/on-demand presentation derived when queried.
A materialized view stores an instantiated result refreshed at a predetermined time.
Deciding clue: compute on request vs pre-instantiated/stored result.
A morning executive report that can be refreshed overnight and must respond instantly is a classic materialized-view clue. If it must reflect the latest committed values at query time and performance is acceptable, a standard view may fit better.
Partitioning
- Vertical partitioning: split by columns.
- Horizontal partitioning: split by rows, often based on values/ranges.
A 180-column table where only 15 columns are frequently used → vertical clue. Billions of rows split by year/region → horizontal clue.
Level-recognition checklist
| Clue | Classification |
|---|---|
| High-level business concepts and vocabulary | CDM |
| Attributes, domains, keys, normalization but no DBMS choice | LDM |
| Vendor datatypes, indexes, partitions, storage | PDM |
| Shared message/payload between systems | Canonical |
Source anchors: current Mastery Lab model-level and physical-design sections, pp. 143–149.