Data Modeling

The previous lesson on schema design covered how to define tables well — types, keys, indexes. This one is about the harder question that comes first: what tables should exist at all?

Data modelling is the step where you read a requirements document and decide which nouns become entities, how they relate, and what the system will therefore find easy or impossible to answer later.

Get this wrong and no amount of indexing rescues you. The model decides which questions are cheap.

Finding the entities

Start with the requirements and underline the nouns. For a course platform:

"Students enrol in courses. Each course has several lessons and is taught by an instructor. Students leave reviews and earn a certificate on completion."

Six candidate entities. Not every noun survives — some are attributes, not entities. The test is whether the thing has its own identity and lifecycle.

• A student has identity: it exists independently, changes over time, is referenced by other things. Entity.
• A student's email address has no independent existence. Attribute.
• A certificate is issued at a moment, has a number, may be revoked. Entity.

The three relationships

Every pair of entities relates in one of three ways, and each has a standard representation.

One-to-one

One user has one profile. Rare in practice, because you can usually merge the two tables.

It earns its place when you are separating sensitive or rarely-read columns — keeping identity documents in a separate table with tighter access, or moving a large biography field out of a row you read constantly.

One-to-many

The most common by far. One instructor teaches many courses; one course has many lessons.

Represent it with a foreign key on the "many" side:

CREATE TABLE courses (
  id            BIGSERIAL PRIMARY KEY,
  instructor_id BIGINT NOT NULL REFERENCES instructors(id),
  title         TEXT   NOT NULL
);

The foreign key always lives on the child. A beginner instinct is to store a list of course ids on the instructor — that is the wrong side, and it fights the database rather than using it.

Many-to-many

Students enrol in many courses; courses have many students. Neither side can hold the key, so you need a join table:

CREATE TABLE enrolments (
  student_id  BIGINT NOT NULL REFERENCES students(id),
  course_id   BIGINT NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id)
);

erDiagram
    STUDENT ||--o{ ENROLMENT : has
    COURSE  ||--o{ ENROLMENT : has
    COURSE  ||--o{ LESSON : contains
    INSTRUCTOR ||--o{ COURSE : teaches

Here is the insight beginners miss: the join table is usually a real entity in disguise. It started as plumbing to connect two things, but almost immediately it wants attributes of its own — when they enrolled, their progress, whether they completed, what they paid.

The moment a join table has attributes, stop thinking of it as a link and give it a proper name. enrolments is a concept the business talks about; student_courses is not.

Model events, not just current state

This is the most valuable idea in the lesson, and the one that most often separates a model that ages well from one that has to be rebuilt.

The naive model stores the present:

accounts (id, balance)

It answers "what is the balance?" instantly, and nothing else. It cannot answer:

• How did this balance come about?
• What was it last Tuesday?
• Who changed it, and why?
• A customer disputes a charge — what happened?

Store the events that produced the state instead:

transactions (id, account_id, amount_cents, kind, created_at, reference)

Now the balance is derivable, and history is intact. Every question above becomes answerable. If computing the balance from history gets slow, you cache it — but the cache is derived from the truth rather than replacing it.

The rule generalises: when something changes for a reason people will later ask about, model the change, not just the result. Balances, inventory levels, subscription status and order state all belong to this family.

Modelling time properly

Two mistakes are so common they are worth naming.

Overwriting values that need history. If a product's price changes, updating products.price destroys the past — and now every historical order shows the current price rather than what the customer actually paid. Store the price on the order line at the moment of purchase.

Booleans where timestamps belong. is_published BOOLEAN records state; published_at TIMESTAMPTZ records state and when, in the same storage. Null means not published. You gain the ability to answer "what was published last month?" for free.

Denormalisation is a decision, not a shortcut

The schema lesson covered the trade-off. In modelling, the key discipline is to be explicit about who owns the copy.

If you store author_name on posts for faster listing, you have created a second copy of a fact. Someone must update it when the author renames themselves. If nothing does, your data quietly disagrees with itself and no error is ever raised.

Duplicate deliberately, in one place, with a stated owner. Duplicating by accident is how systems end up with three different values for the same customer's name.

A worked example

Requirement: "Customers place orders containing several products. Each order has a delivery address and a status. We need to report monthly revenue by product."

The entities and relationships:

customers      (id, name, email, created_at)
addresses      (id, customer_id, line1, city, postcode)
products       (id, name, price_cents, active)
orders         (id, customer_id, address_id, status, placed_at)
order_items    (id, order_id, product_id, quantity, unit_price_cents)

Two decisions carry most of the weight:

order_items stores unit_price_cents. It duplicates the product price — deliberately. Prices change, and an order must record what was actually charged. Reading the price from products at report time would silently rewrite history.

orders references an address rather than storing customer address inline. A customer has several addresses and may change them; the order must remember where it actually went. For maximum safety many systems snapshot the address onto the order, for the same reason as the price.

Notice both decisions come from the same principle: an order is a historical fact, so it must not change when the present changes.

Check your understanding

Common mistakes

Modelling the screen instead of the domain. Tables shaped like the current UI break the moment the UI changes. Model what things are, not how they are currently displayed.

One giant table with many nullable columns. When half the columns only apply to some rows, you probably have two entities crammed together.

No join table for many-to-many. Comma-separated ids in a column cannot be indexed, joined or constrained, and will be regretted.

Ignoring cardinality edge cases. Can an order have zero items? Can a course have no instructor? Decide deliberately and enforce with NOT NULL and constraints, rather than discovering the answer from corrupt data.

Premature denormalisation. Copying fields for speed before measuring gives you the synchronisation burden without the benefit.

Practice

Key takeaways

• The model decides which questions are cheap later; indexes cannot rescue a wrong one.
• Something is an entity if it has its own identity and lifecycle; otherwise it is an attribute.
• Foreign keys live on the "many" side; many-to-many needs a join table — which is usually a real entity in disguise once it gains attributes.
• Model events, not just current state. Storing transactions rather than a balance keeps history and answers questions the state alone cannot.
• Prefer timestamps over booleans — same storage, strictly more information.
• Historical records must not change when the present changes: snapshot prices and addresses onto orders deliberately.
• Denormalise explicitly, with a named owner for keeping the copy in sync.

Further reading

• Eric Evans, Domain-Driven Design (Addison-Wesley, 2003) — entities versus value objects, which is the distinction in the second question.
• Martin Fowler, "Event Sourcing" (2005) — storing changes rather than current state.
• Edgar F. Codd, "Further Normalization of the Data Base Relational Model" (1971).
• Richard Snodgrass, Developing Time-Oriented Database Applications in SQL (Morgan Kaufmann, 1999) — effective time versus record time.
• Pat Helland, "Immutability Changes Everything" (CIDR 2015) — why append-only models simplify so much.