Conceptual, Logical and Physical Data Models Explained

A data model describes what data a system holds and how the pieces relate. It is usually built in three steps, each more detailed than the last:

• The conceptual model names the main business things and how they relate, in words anyone in the business can check.
• The logical model adds every attribute, the keys and the exact relationships, without choosing a database product.
• The physical model turns that design into real tables for one database: data types, constraints, indexes and partitions.

Think of building a house. The conceptual model is the sketch you agree with the owner (“three bedrooms, kitchen next to the garden”). The logical model is the architect’s plan with every room and door measured. The physical model is the construction drawing for a specific site, with the materials chosen.

The three levels side by side

	Conceptual	Logical	Physical
Question it answers	What does the business care about?	What exactly do we store, and how is it related?	How is it built in this database?
Contains	Entities and relationships	Entities, attributes, primary and foreign keys, cardinality, normalisation	Tables, columns, data types, constraints, indexes, partitions
Depends on a database product?	No	No	Yes
Main audience	Business owners, product managers	Data modellers, analysts, engineers	Engineers and database administrators
Typical form	A simple diagram or a list of sentences	An entity-relationship diagram (ERD)	DDL scripts or a migration

Step 1: the conceptual model

Kestrel Market, the fictional shop used in this course, is building a new ordering system. After talking to the team, you write down the business rules in plain sentences:

• A customer places orders. A customer can have many orders; each order belongs to one customer.
• An order contains one or more order lines.
• Each order line is for one product. A product can appear on many order lines.

That is the conceptual model: four entities (customer, order, order line, product) and three relationships. It has no columns and no data types. Its value is that a shop manager can read it and say “no, one order can be split between two customers for gift orders” before anyone writes code. Fixing a wrong rule here costs minutes; fixing it after launch can take weeks.

Step 2: the logical model

The logical model adds the detail an engineer needs, still without choosing a database:

Entity	Attributes	Primary key	Relationships
Customer	customer id, email (unique), full name, created at	customer id	places 0..many orders
Product	product id, SKU (unique), name, list price	product id	appears on 0..many order lines
Order	order id, customer id, status, ordered at	order id	belongs to exactly 1 customer; has 1..many lines
Order line	order id, line number, product id, quantity, unit price	order id + line number	belongs to 1 order; refers to 1 product

Decisions made at this level:

• Keys. Each entity gets a primary key. The order line uses a composite key (order id and line number).
• Cardinality. “Exactly one”, “one or more” and “zero or more” are written down, because they become NOT NULL and foreign keys later.
• Normalisation. The customer’s name is stored on the customer, not copied onto every order, so the model reaches third normal form. See normalisation and its types (1NF to BCNF).
• Business rules as data rules. An order status can only be placed, paid, shipped or cancelled. The quantity must be positive.
• Why unit_price is on the line. The product’s list price changes over time, but an old order must keep the price the customer actually paid.

Step 3: the physical model

Now you choose a database, here PostgreSQL, and write the model as DDL. Every logical decision becomes something the database enforces:

-- Physical model for PostgreSQL: exact types, keys, constraints and an index.
CREATE TABLE customer (
    customer_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        varchar(254) 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,
    sku          varchar(32)  NOT NULL UNIQUE,
    name         varchar(200) NOT NULL,
    list_price   numeric(10,2) NOT NULL CHECK (list_price >= 0)
);

CREATE TABLE sales_order (
    order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customer (customer_id),
    status       varchar(20) NOT NULL DEFAULT 'placed'
                 CHECK (status IN ('placed', 'paid', 'shipped', 'cancelled')),
    ordered_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX sales_order_customer_idx ON sales_order (customer_id, ordered_at);

CREATE TABLE order_line (
    order_id     bigint  NOT NULL REFERENCES sales_order (order_id),
    line_no      smallint NOT NULL,
    product_id   bigint  NOT NULL REFERENCES product (product_id),
    quantity     integer NOT NULL CHECK (quantity > 0),
    unit_price   numeric(10,2) NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

What changed from the logical model:

• Names. “Order” became sales_order, because ORDER is a reserved word in SQL.
• Types. Money uses numeric(10,2), never a floating-point type, so totals do not drift by fractions of a cent. Times use timestamptz so time zones are stored correctly.
• Generated keys. GENERATED ALWAYS AS IDENTITY lets the database create ids.
• Constraints. “Exactly one customer” became NOT NULL REFERENCES customer. The status list became a CHECK.
• Indexes. The app shows “my recent orders”, so an index on (customer_id, ordered_at) makes that query fast. Indexes are a physical decision: they do not change what the data means. See SQL indexes.

The model in action

Valid data goes in, and queries follow the relationships:

-- The constraints are part of the model: the database now refuses data the business rules forbid.
INSERT INTO customer (email, full_name) VALUES ('[email protected]', 'Asha Rao');
INSERT INTO product (sku, name, list_price) VALUES ('MUG-01', 'Trail mug', 12.00);
INSERT INTO sales_order (customer_id) VALUES (1);
INSERT INTO order_line VALUES (1, 1, 1, 2, 12.00);

SELECT o.order_id, c.full_name, o.status, l.quantity * l.unit_price AS line_total
FROM sales_order o
JOIN customer c   ON c.customer_id = o.customer_id
JOIN order_line l ON l.order_id = o.order_id;

 order_id | full_name | status | line_total
----------+-----------+--------+------------
        1 | Asha Rao  | placed |      24.00

An order with a status the business never agreed to is rejected:

INSERT INTO sales_order (customer_id, status) VALUES (1, 'lost');

ERROR:  new row for relation "sales_order" violates check constraint "sales_order_status_check"
DETAIL:  Failing row contains (2, 1, lost, 2026-11-01 01:35:23.085152+00).

This is the point of carrying rules down through all three levels: the rule written in plain words in step 1 is now enforced on every insert.

The same idea in a data warehouse

The three levels apply to analytical models too, but the logical design is different. For reporting on Kestrel Market’s sales:

• Conceptual: “We measure sales by date, customer and product.”
• Logical: a star schema with a fact table at the grain of one order line, and customer, product and date dimensions. Dimensions may keep history with slowly changing dimensions.
• Physical: for example, a Snowflake or BigQuery table clustered or partitioned by order date, with column types chosen for compression. Warehouses often do not enforce foreign keys, so tests in the pipeline check them instead.

The source system’s model is shaped for safe transactions, and the warehouse model is shaped for questions. The OLTP vs OLAP lesson explains why they differ.

Common mistakes

1. Skipping the conceptual step and jumping to tables. Misunderstood business rules then turn up as data bugs in production.
2. Putting physical details in the logical model, such as varchar(254) or index names. It ties the design to one product too early.
3. Treating indexes as part of the meaning. They are a performance choice and can change without changing the model.
4. Leaving rules undocumented. If “an order has at least one line” is not in the model, nobody enforces or tests it.
5. Using floating-point types for money.

What interviewers look for

When asked “explain conceptual, logical and physical data models”:

• One clear sentence for each level and who it is for.
• One example carried through all three levels, as in this lesson.
• The difference between a product-independent logical model and a database-specific physical model.
• Bonus: how this changes for a warehouse (star schema, grain, unenforced constraints) and how the model is kept current with migrations.

Practise with the data engineering interview questions.

Key takeaway

Conceptual models agree on the business things and how they relate. Logical models add attributes, keys, cardinality and normalisation, still without a database product. Physical models build that design in one database, with types, constraints and indexes. Carry each business rule down through all three levels so the database or the pipeline enforces it. Next: ER diagrams.