Conceptual, Logical, and Physical Models

It's tempting to jump straight to writing CREATE TABLE statements. Experienced modelers instead move through three progressively more concrete layers, each answering a different question.

Conceptual model

The conceptual model captures business entities and relationships in plain language, with no reference to any database technology. 'A Customer places many Orders. Each Order contains one or more Line Items. Each Line Item refers to one Product.' This is often a simple diagram reviewed with business stakeholders who have never heard of SQL.

Logical model

The logical model adds structure: entities become tables (in the relational sense), relationships become keys, and attributes get defined — still independent of any specific database engine. This is where you'd decide 'Customer has customer_id, email, signup_date' and 'Order has order_id, customer_id (foreign key), order_date, status' without yet worrying whether customer_id is a UUID or an integer.

Physical model

The physical model is the actual implementation: specific data types, indexes, partitioning strategy, and any denormalization done purely for performance on a specific engine. Two teams with an identical logical model might have very different physical models — one partitioned by date for a warehouse, another optimized for low-latency lookups in a transactional database.

-- Physical model: concrete types, keys, and a partitioning decision
create table orders (
  order_id      bigint primary key,
  customer_id   bigint not null references customers(customer_id),
  order_date    date not null,
  status        varchar(20) not null
)
partition by range (order_date);

In day-to-day work, especially on small teams, these layers blur — but keeping the distinction in mind helps you know which question you're actually trying to answer when a modeling discussion stalls.