Tables Keys Relationships and Normalization

01 The idea

The problem this lesson solves

A cinema screen has a seat named F7, but F7 can be sold again for tomorrow’s show. If your table makes seat_no globally unique, legitimate bookings fail. If it has no appropriate uniqueness rule, two rows may claim to represent the same sellable seat. Schema design begins by deciding what one row means, which facts identify it, and which impossible states the database must reject.

You should recognize API resources and failure responses. SQL examples introduce their own tables and constraints.

02 Worked example

Give a show seat a complete identity

03 The method

Understand the mechanism

Start with the operations and the meaning of a row

List the operations before drawing tables: display a show’s seat map, place a temporary hold, confirm a booking and list a customer’s bookings. These operations reveal that a physical seat, a seat at a particular show and a completed booking are related but different concepts.

A physical seat belongs to an auditorium. A show assigns a performance to a time and auditorium. A show-seat row represents one sellable occurrence of a physical seat. A booking records a customer’s accepted purchase. Mixing all of these into one row makes identity and updates ambiguous.

For the teaching model, use (show_id,seat_no) to identify a show seat. F7 at show 1 and F7 at show 2 are different rows. Seat labels must also be interpreted within their auditorium; the show’s identity provides that context in this simplified model.

Write a sentence above every proposed table: “one row represents …”. If the sentence contains two independent events or entities, reconsider the model. This small habit catches many accidental many-to-many relationships and repeated-history problems before SQL is written.

Choose keys for stability and business uniqueness

A primary key uniquely identifies a row and cannot contain null values. A natural key uses meaningful business attributes, such as the complete show-seat pair. A surrogate key supplies an independent identifier, such as booking_id. Both can be useful in the same schema.

A surrogate id does not remove the need for business uniqueness. Giving every show-seat row a random id while allowing repeated (show_id,seat_no) pairs still permits duplicate representations of one sellable seat. Keep a unique constraint on the business identity when that is the invariant.

Choose identifiers that remain stable when descriptive facts change. A customer’s name or email can change, may not be unique under the product’s rules, and can be unsuitable as a relationship key. A stable customer_id lets bookings refer to the customer without copying a mutable display field into every reference.

Key scope matters in multi-tenant systems too. If external code values are only unique within a tenant, the relevant identity includes tenant_id. Leaving it out of a lookup or constraint can create cross-tenant collisions or access defects. Identity and authorization remain separate concerns, but both must use the intended scope.

Represent relationships rather than repeating lists

A one-to-many relationship typically puts the parent key on the child: many shows reference one movie, and many bookings reference one customer. A foreign key checks that the referenced parent exists under its configured rules.

A many-to-many relationship needs an association. A booking can contain several show seats, and seats participate in booking history over time under a carefully defined state model. A booking_items table can store the booking reference and complete seat identity, plus facts that belong to that association.

Do not put a comma-separated list of seat ids into a column and expect the database to enforce each relationship. The list is difficult to join, validate, update concurrently and constrain. Rows in an association table expose each relationship to ordinary keys and constraints.

Decide deletion behavior explicitly. Deleting a customer should not accidentally erase legally or operationally required booking history through an unexamined cascade. You might restrict deletion, preserve a historical record, or anonymize selected data according to the product’s policy. The schema should express the chosen lifecycle rather than inherit an accidental default.

Normalize facts that must change together

Suppose every booking repeats customer_name and current_email. Changing one customer’s email now requires updating many rows. If only some updates succeed, the database contains conflicting claims about the same current fact. This is an update anomaly.

Normalization organizes facts according to the identities they depend on. Customer attributes belong with the customer; show timing belongs with the show; a line item’s charged price belongs with that purchase event. The goal is not to eliminate every repeated string. It is to avoid storing one current fact in several places without a maintenance rule.

Historical snapshots are different facts. A receipt’s charged price must not change when the current advertised price changes. An order may preserve a delivery address as it was accepted at purchase time. Calling these snapshots “bad duplication” would erase useful history.

Explain the dependency in plain language: this value describes the customer now, or this value records what was charged then. That explanation is more important for a designer than mechanically reciting normal-form names without connecting them to update behavior.

Put local invariants into executable constraints

Use NOT NULL for required values, CHECK for permitted local states or ranges, UNIQUE for the relevant identity, and foreign keys for references. A CHECK restricting state to free, held or booked prevents an application typo from creating a fourth undocumented state.

Constraints protect writes from every client, including maintenance scripts and future services. Relying only on an API’s validation leaves other write paths able to corrupt data. Application validation remains useful for clear errors, but the database is the final guard for invariants it can express.

Understand the boundary. A foreign key can show that customer_id exists; it does not prove the authenticated caller may book for that customer. A row-local CHECK cannot by itself enforce every rule involving several rows, time or external payment state. Those require appropriate transactions, locking, uniqueness patterns or workflow design.

Handle constraint violations as expected outcomes where concurrency can trigger them. Two app instances may both generate the same short code. A unique insert can select the winner; the loser can allocate another code within a bounded policy. A preliminary “is this free?” query is not equivalent to the constraint.

Denormalize only with a maintenance and repair path

After measuring a hot read, you may choose to store a derived count or duplicate a field to avoid repeated work. For example, an owner dashboard might display a link count maintained alongside its underlying links.

Ask how that count changes on create, delete, rollback and retry. If it is updated in the same transaction as the source rows, the transaction adds work and possible contention. If it is updated asynchronously, the product must tolerate lag and the consumer must handle duplicate events. Either way, include a rebuild procedure from authoritative data.

A denormalized value without a repair path becomes difficult to trust after an incident. Reconciliation can compare the projection with source data and correct drift. The design should identify which copy wins when values disagree.

The first schema should make ownership and invariants clear. Optimize specific access patterns after observing their cost. This produces a model you can reason about under concurrent changes rather than a collection of convenient fields that happen to work for the first screen.

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.

05 Where & why

Check the primary references

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

06 Interview questions

Explain the reasoning out loud

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

07 Practice problems

Practice a new case

Solve the new cases before revealing hints. Complete worked solutions follow the set.

Worked solutions