System Design · Module 3 · Lesson 1 of 4 · 40 min

Tables Keys Relationships and Normalization

Turn booking operations into a schema with identities, relationships and constraints, then distinguish historical facts from accidental duplicates.

Model the identity of the business fact and enforce the invariants that every writing path must obey.

01 The idea

The problem this lesson solves

Read a show’s seat map, claim one seat and list a customer’s bookings.

Prerequisite: earlier lessons introduce the vocabulary and shared examples used here. Follow the module order.

Primary keyThe columns identifying one row uniquely.

Foreign keyA constraint requiring a referenced identity to exist, under its configured rules.

NormalizationOrganizing facts to reduce accidental duplication and update anomalies.

02 Worked example

Follow one worked example

Shows: show_id PK • Show seats: show_id + seat PK • Bookings: booking_id PK • Customers: customer_id PK.

Trace the example in order. Follow the same identities, units and state transitions through the diagram and the events below.

List access patternsRead a show’s seat map, claim one seat and list a customer’s bookings.

Choose identityUse (show_id,seat_no) for one sellable seat.

Add relationshipsReference shows and customers by stable keys rather than copied display names.

Add rulesReject invalid state values and duplicate identities at the database boundary.

Model the identity of the business fact and enforce the invariants that every writing path must obey.

03 The method

Mechanisms decisions and a guided tutorial

Work through the explanation in order, then apply it in the tutorial. Each comparison names a cost as well as a benefit.

Choice or concept	Purpose	Boundary or cost
Primary key	Stable row identity	Choose the complete business identity
Foreign key	Valid reference	Does not replace authorization
Normalized current fact	One update location	Requires joins for combined views
Historical snapshot	Value at event time	Must not follow future edits

Build the model from identity and relationships

A movie has many shows; a show has many seats; a customer can have many bookings. The sellable object is a seat at a particular show, so (show_id, seat_no) is a natural composite primary key for show_seats. A seat number alone is not unique across shows. Foreign keys link show_seats to shows and bookings to customers. A CHECK constraint can restrict state to free, held or booked. Constraints reject bad writes regardless of which application path issued them.

Normalize facts before copying them

Store a customer's current email once in customers, not on every booking merely for convenience. Otherwise a change must update several copies and can leave contradictions. Some copied values are intentional historical facts: a price charged at booking time must not change when a future price changes. Denormalization is a deliberate duplicate used to accelerate a measured access pattern. State who maintains it and how stale it may become.

Begin with operations and facts

A seat map needs all seats for one show. A booking needs a customer, a show and the seats claimed. The physical label F7 appears at many showtimes, so the sellable identity includes show_id. Start with shows, customers, bookings and show_seats rather than a single table containing every repeated movie and customer attribute.

A one-to-many relationship usually places the parent key on the child. A many-to-many relationship needs an association with both identities and any facts belonging to that association. Choose keys that remain stable when display names change; a person’s current name is usually a poor durable identifier.

Constraints are executable rules

A primary key prevents two rows with the same identity. A foreign key protects references. NOT NULL distinguishes required values from optional ones. A CHECK can restrict a state or require a positive quantity. These guards run for every client that writes to the database, including a maintenance script.

Constraints have scope. UNIQUE(seat_no) prevents the same seat label across all shows, which is too strong. UNIQUE(show_id,seat_no) matches the actual identity. A basic foreign key says a parent exists; it does not prove that the authenticated caller is allowed to act on that parent. Authorization and relational integrity solve different problems.

Keep historical snapshots intentionally

Suppose a movie title changes or a customer updates an email address. Normalization lets current facts be maintained once. A booking’s charged price, however, is a historical fact at a particular time. Replacing it with a lookup of today’s price would rewrite history.

When duplicating a derived value for performance, name its maintenance path. A rating_count on movies must be updated consistently with ratings or treated as a deliberately asynchronous projection. The decision is measurable: identify the hot query, the saved work and the cost of keeping copies correct.

Guided tutorial

1. Sketch four tables

Write primary and foreign keys before adding descriptive fields.

2. Test an invalid row

Describe which constraint rejects a duplicate show/seat pair.

3. Change a current fact

Change customer email and explain which rows should change.

4. Preserve history

Model charged price separately from the current advertised price.

CREATE TABLE sd_schema_seats (
  show_id integer NOT NULL,
  seat_no text NOT NULL,
  state text NOT NULL CHECK (state IN ('free','held','booked')),
  PRIMARY KEY (show_id, seat_no)
);

04 Cheat sheet

Review the decisions

Use this table to explain the mechanism and its limit without memorizing a product name.

Choice or concept	Remember	Check
Primary key	Stable row identity	Choose the complete business identity
Foreign key	Valid reference	Does not replace authorization
Normalized current fact	One update location	Requires joins for combined views
Historical snapshot	Value at event time	Must not follow future edits

Primary keyThe columns identifying one row uniquely.

Foreign keyA constraint requiring a referenced identity to exist, under its configured rules.

NormalizationOrganizing facts to reduce accidental duplication and update anomalies.

05 Where & why

Check the primary references

Use official references for protocol and implementation details. The questions here are teaching prompts.

Primary reference

PostgreSQL 16 transaction isolation

Read PostgreSQL 16 transaction isolation

Primary reference

PostgreSQL explicit locking

Read PostgreSQL explicit locking

Primary reference

Multicolumn index behavior

Read Multicolumn index behavior

State the implementation version and the workload assumptions when defending your own design.

06 Interview questions

Explain the reasoning out loud

Answer before opening the explanation. The questions progress from vocabulary to decisions and failure behavior.

What does Primary key mean?

The columns identifying one row uniquely.

What does Foreign key mean?

A constraint requiring a referenced identity to exist, under its configured rules.

What does Normalization mean?

Organizing facts to reduce accidental duplication and update anomalies.

Why not use seat_no as the primary key?

The label repeats across shows. The identity must include the show or another key identifying that sellable occurrence.

What is an update anomaly?

Copies of the same current fact can disagree after only some rows are updated. Storing that fact once avoids the inconsistent update paths.

Is every duplicate value denormalization?

No. Two fields can represent different facts, such as current price and charged price at booking time. Their meanings and update rules differ.

What does a foreign key not prove?

1. Pick the identity

A pair such as (session_id,student_id) identifies one attendance fact. A student id alone permits only one record for all sessions.

2. Name the relation

Use enrollments with student_id and course_id, plus fields such as enrolled_at and status. Apply a suitable uniqueness rule for one active or historical enrollment contract.

3. Correct the key

It wrongly makes F7 globally unique. Scope uniqueness to (show_id,seat_no) or the complete occurrence identity.

4. Keep a snapshot

Store the actual charged price on the line item. A future catalogue change must not alter the receipt’s historical amount.

5. Find an anomaly

The same current email now has conflicting values. Keep current email on the customer row or explicitly label immutable contact snapshots if those are the intended facts.

6. Defend a schema

Model book metadata, physical copies and loans separately. A copy has a stable copy id; loans reference a copy and borrower. Enforce the chosen active-loan uniqueness and retain returned-loan history without making titles globally unique.