Conceptual vs Logical vs Physical Data Model

Conceptual vs logical vs physical data model: what each layer decides, leaves out, and who reads it.

Syntax

design-and-specify

CONCEPTUAL   What exists?        things + named relationships
LOGICAL      What does it mean?  attributes, identifiers, cardinality, 3NF
PHYSICAL     How is it stored?   tables, types, keys, indexes, policies

Order in SDD:  stories -> conceptual -> logical (APPROVED) -> migration -> live check

Example

design-and-specify

LAYER       CONTAINS                                 LEAVES OUT             READ BY
Conceptual  MEMBER borrows TOOL                      attributes, keys       the business
Logical     LOAN: loan number (id), borrowed on,     data types, indexes    business + builders
            due on; status = derived
Physical    loans(id uuid PK, loan_number text UQ,   nothing                the database
            member_id uuid FK, due_on date, ...)

REFUSES
Conceptual  a noun nobody can define
Logical     a fact stored twice
Physical    a table no entity explains

TEST
If the diagram was generated from the database,
it cannot tell you the database is wrong.