TopolearnA clear conceptual model is what keeps a schema small, correct and easy to change later.
Data modelling is deciding what your system needs to remember and how those facts relate. Start away from SQL, in the language of the domain: an online shop has customers, products and orders. These are entities — things with identity that you store many of. Each has attributes (a customer has an email and a name) and they are connected by relationships (a customer places orders; an order contains products).
A useful way to work is in three passes:
- Conceptual: entities and relationships, often drawn as an entity-relationship (ER) diagram. No types, no tables.
- Logical: tables, columns, keys and constraints for a relational model, still independent of a particular product.
- Physical: types, indexes, partitioning and settings for the actual database you run.
Good questions while modelling: what uniquely identifies this thing? Can this attribute have several values (then it is probably its own entity)? Does this fact belong to the entity or to the relationship? The price a customer actually paid belongs to the order line, not the product, because the product's price changes later.
Model the facts, not the screens. A UI shows a "customer dashboard", but the database stores customers, orders and payments; the dashboard is a query. Schemas built around screens end up duplicating data and break when the UI changes.
Example
-- Conceptual: Customer places Order; Order contains Product (many-to-many)
CREATE TABLE customer (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL
);
CREATE TABLE product (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10,2) NOT NULL -- current list price
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customer(id),
placed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_line (
order_id bigint NOT NULL REFERENCES orders(id),
product_id bigint NOT NULL REFERENCES product(id),
quantity int NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL, -- price actually paid: a fact of the sale
PRIMARY KEY (order_id, product_id)
);Remember
- Entities have identity, attributes describe them, relationships connect them
- Work conceptual, then logical, then physical
- Store facts where they belong: the paid price lives on the order line
Watch out for
- Designing tables to mirror UI screens instead of the domain's facts.