Data models are built at three levels of detail: conceptual, logical and physical. This page explains what each level shows and who it is for, then takes a single online-shop example through all three, ending in runnable SQL.
Data modelling is usually done at three levels of detail, a framework that goes back to the ANSI/SPARC architecture of the 1970s:
Each level serves a different conversation. The conceptual model lets business stakeholders confirm that the system understands their world ("can an order belong to two customers?") without being distracted by data types. The logical model is where precision about meaning lives: what uniquely identifies a product, whether a customer can exist without an order, which attributes depend on which keys. The physical model is where performance and platform constraints enter.
Keeping them separate means a change of technology (say, moving from an on-premise database to a cloud warehouse) changes only the physical model, and a change in business rules is discussed at the level where it is cheapest to fix. Errors found at the conceptual level cost a conversation; the same error found after the physical schema is in production costs a migration.
Take a simple online shop. The conceptual step starts from interviews: customers place orders, orders contain products. Three entities and two relationships, with the order-to-product relationship being many-to-many (an order has many products; a product appears in many orders). That is enough to validate scope with the business.
The logical step adds precision:
The physical step then picks a target platform and makes concrete choices.
-- Physical model for PostgreSQL, derived from the logical model above.
CREATE TABLE customer (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(320) NOT NULL UNIQUE,
full_name VARCHAR(200) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE product (
product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(200) NOT NULL,
unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0)
);
CREATE TABLE customer_order ( -- "order" is a reserved word
order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customer(customer_id),
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'placed'
);
CREATE INDEX ix_order_customer ON customer_order (customer_id, order_date);
CREATE TABLE order_line (
order_id BIGINT NOT NULL REFERENCES customer_order(order_id) ON DELETE CASCADE,
line_no INT NOT NULL,
product_id BIGINT NOT NULL REFERENCES product(product_id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL, -- price at time of sale, not current price
PRIMARY KEY (order_id, line_no)
);Notice the decisions that appear only at this level: order renamed to customer_order because it is a reserved word; surrogate identity keys; NUMERIC(10,2) for money rather than floating point; TIMESTAMPTZ for time zones; a composite index chosen because the application's most common query is "a customer's recent orders"; and a cascading delete policy. None of these change what the data means, which is exactly the point.
The same logical model can have several physical models. For the transactional application, normalised tables with enforced foreign keys protect integrity under concurrent updates. For analytics, the warehouse version might flatten order and order line into one wide fact table with denormalised customer and product attributes, partitioned or clustered by date, because analytical scans favour fewer joins and pruning by date. Both are faithful to the logical model; they optimise for different workloads.
Analytical platforms add one more twist. Many teams keep a normalised, source-aligned logical model for the integrated "core" layer, and a separate dimensional logical model (facts, dimensions, grain) for the marts that serve reporting. Both are logical models in the sense used here: product-independent statements of structure and meaning. Each then has its own physical design. Keeping the dimensional model explicit, including the declared grain of every fact table ("one row per order line"), prevents the most common analytics error, which is joining tables at different grains and silently double-counting. Tools such as dbt make the physical layer visible as code, but they do not replace the logical decisions; write those down in the model documentation, next to the SQL, so that a reviewer can check the implementation against the intent.
| Aspect | Conceptual | Logical | Physical |
|---|---|---|---|
| Purpose | Agree on scope and meaning | Define structure and rules precisely | Implement efficiently on a platform |
| Contains | Entities, relationships | Attributes, keys, cardinality, normal form | Tables, types, indexes, partitions, constraints |
| Technology-specific? | No | No | Yes |
| Typical notation | Simple ER or boxes and lines | ER with crow's foot, IDEF1X | DDL, database diagrams |
| Main audience | Business stakeholders | Analysts, data architects | Engineers, DBAs |
| Changes when | Business scope changes | Business rules change | Platform or workload changes |