Turning requirements into tables — relationships, join tables, and the decisions to get right early.

Designing a Schema

Normalisation tells you when a table is wrong. It does not tell you which tables to create. That starts with the relationships.

The three relationships

One-to-many — the common case. One department, many employees. The foreign key goes on the many side: employees.dept_id, never a list of employees on the department.

Many-to-many — needs a third table. Students and courses: neither side can hold the reference, so a join table holds pairs.

CREATE TABLE student_courses (
  student_id INTEGER NOT NULL REFERENCES students(student_id),
  course_id  INTEGER NOT NULL REFERENCES courses(course_id),
  enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
  PRIMARY KEY (student_id, course_id)
);

Two details worth copying. The composite primary key both identifies the row and prevents duplicate enrolment. And the join table can carry its own attributes — enrolled_on belongs to the relationship, not to either side.

One-to-one — rarer than people think, and usually a sign two tables should be one. Legitimate uses: splitting off rarely-read bulk columns, or separating data with different access rules.

Working from requirements

A practical order:

Find the nouns. Customer, order, product, payment. These are candidate tables.
Find the relationships between them, and their cardinality. "A customer places many orders" — one-to-many.
Find the attributes of each, and give each its narrowest correct type.
Choose keys. A surrogate primary key for each entity; unique constraints on the natural keys.
Add the constraints — NOT NULL, foreign keys, CHECK on every constrained domain.
Check it against 3NF. Is any fact stored twice?
Write the queries the application needs, on paper. This is the step that catches design errors, because a query that is awkward to write is usually pointing at a missing relationship.
Index what those queries filter, join and sort on.

Step 7 is the one people skip. A schema that models the domain beautifully and makes the three most common queries painful is the wrong schema.

Decisions worth getting right early

Some choices are cheap now and expensive later.

Key type. BIGINT identity by default. INTEGER runs out; UUIDv4 fragments indexes; UUIDv7 is fine if you need client-generated ids.

Timestamps on everything. created_at and updated_at on every table, always. You will want them the first time you debug anything, and they cannot be reconstructed retrospectively.

Soft versus hard delete. A deleted_at column preserves history and satisfies "undo", at the cost of every query needing WHERE deleted_at IS NULL — and the one that forgets it is a bug that shows deleted data. Decide once, per table, and be consistent.

Time zones. Store instants as TIMESTAMP WITH TIME ZONE, in UTC. Convert at the edges. Retrofitting this is genuinely painful.

Money. DECIMAL, and store the currency alongside it. An amount column with no currency is a bug waiting for the second market.

Enumerations. A CHECK constraint for a small fixed set; a lookup table when the values carry extra attributes or change without a deploy.

Naming

Consistency matters more than the specific convention, but some choices are better:

snake_case — survives every database's identifier folding without quoting.
Consistent table names — plural or singular, pick one. customers and orders, or customer and order; not both.
Primary key id, or <table>_id — again, pick one. <table>_id makes USING (dept_id) work and reads better in joins.
Avoid reserved words — a column called order, user or group needs quoting forever.
Say what it is, not what it holds — email, not email_string.

An audit of this course's dataset

The schema you have been querying has deliberate flaws. Reading a schema critically is the skill this module is for, so:

employees.dept_id is nullable. Sometimes right — a contractor in no department — but it caused the inner join to drop a person in lesson 19, and NOT IN to return nothing in lesson 24. Nullable foreign keys have consequences.
orders.customer_id has no foreign key. Hence the orphaned order lessons 20 and 22 kept finding. The constraint would have refused that row at insert time.
orders.status has no CHECK. Nothing prevents 'Shipped' appearing next to 'shipped'.
orders.amount is REAL. It is money. It should be DECIMAL, for the reason lesson 40 demonstrated, and refunds stored as negative amounts should probably be their own rows with a type.
customers.email is not unique. Two customers share one, which is why lesson 18's data-quality query finds a duplicate.
No created_at anywhere. There is no way to ask when a row appeared.

A corrected orders table:

CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
  placed_at   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  shipped_at  TIMESTAMP WITH TIME ZONE,
  status      TEXT NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending','shipped','cancelled','refunded')),
  amount      DECIMAL(10,2) NOT NULL,
  currency    CHAR(3) NOT NULL DEFAULT 'GBP',
  created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

shipped_at stays nullable, deliberately: an unshipped order has no shipping date, and that is a real "not applicable", exactly the case lesson 14 said NULL is for.

Common mistakes

Designing tables without writing the queries — the schema models the domain and fights the application.
No created_at — unrecoverable once the rows exist.
Money without a currency.
Reserved words as identifiers — quoted forever, or subtly broken.
A "misc" or "attributes" text column — the place unmodelled requirements go to become unqueryable.

Module complete

You can now choose types, declare the constraints that keep data honest, normalise to remove duplication, and design a schema from requirements — including reading an existing one critically, which is most of the job.

Where the course stands

Forty-three lessons, ten modules, every runnable example executed before it ships. Still being written: the graded interview problem set, the practice projects, and the assessments. The playground on the course page runs everything taught here.