Designing a Schema: Keys, Normalisation and Constraints

In lessons 0 to 3 the shop tables were handed to you, and you learned to read them. Now you design them. The choices here (key type, what happens on delete, how to store money and time) are the ones that are hard to change later, because they sit under every query and every row. They are also the favourite "design a table" interview topic. For the Prisma side of migrations see Full-Stack Q&A, Lesson 08; for how a changing schema is rolled out safely see lesson 7.

Words used in this lesson

• A primary key (PK) identifies one row. It is unique and never NULL. A surrogate key is an artificial id with no business meaning. A natural key is real data used as the key, such as an email or a country code.
• A foreign key (FK) is a column that must match a key in another table. ON DELETE says what happens to the child rows when the parent row is deleted.
• A constraint is a rule the database enforces: NOT NULL, UNIQUE, CHECK, primary key, foreign key.
• Normalisation means splitting data into tables so each fact is stored once. Denormalisation means copying data on purpose, for speed.
• A functional dependency means one column decides another: the email decides the name.
• A junction table (link table) connects two tables in a many-to-many relationship.
• An enum is a column type with a fixed list of allowed text values. A lookup table is a small table that holds the allowed values.
• Soft delete means marking a row as deleted (with a timestamp) instead of removing it.
• Anomaly means a bug caused by bad table design: an update that fixes one copy of a fact but not the others.

Start with the bad design

Many first schemas look like a spreadsheet. Here is the shop as one flat table: each order is one row, and the products, quantities and prices are comma lists. I loaded four of our orders:

CREATE TABLE orders_flat (  order_id int, customer_name text, customer_email text, customer_city text,  products text, quantities text, prices text, ordered_at text);

 order_id | customer_name |  customer_email  | customer_city |         products          | quantities |       prices       | ordered_at ----------+---------------+------------------+---------------+---------------------------+------------+--------------------+------------        1 | Asha          | asha@example.com | Pune          | Notebook, Pen             | 2, 5       | 3.50, 1.20         | 2025-01-05        2 | Asha          | asha@example.com | Pune          | Headphones                | 1          | 59.00              | 2025-02-11        3 | Ben           | ben@example.com  | London        | Backpack, Notebook        | 1, 1       | 45.00, 3.50        | 2025-02-14        6 | Asha          | asha@example.com | Pune          | Backpack, Headphones, Pen | 1, 1, 2    | 45.00, 59.00, 1.20 | 2025-03-15(4 rows)

It looks fine until you use it. Four problems:

1. Update anomaly. Asha's city is stored on every order. Change it on one row only:

UPDATE orders_flat SET customer_city = 'Delhi' WHERE order_id = 1;SELECT DISTINCT customer_name, customer_city FROM orders_flat ORDER BY 1, 2;

 customer_name | customer_city ---------------+--------------- Asha          | Delhi Asha          | Pune Ben           | London(3 rows)

Where does Asha live? The data now disagrees with itself, and the database cannot tell which is right.

2. Hard questions. "How many orders include a Pen?" needs products LIKE '%Pen%'. That matches Pencil too, cannot use an index, and the quantity of that product is buried in another string at the same position.
3. No rules. quantities can hold 'two'. ordered_at is text, so 'tomorrow' is accepted. Nothing links 'Pen' to a product row, so a typo creates a new "product".
4. Delete anomaly. If you delete the only order of a customer, you also lose the customer. If a product has no orders, there is nowhere to store it.

Normalisation is the cure. It is a short list of rules, called normal forms.

Normal forms: 1NF, 2NF, 3NF

Each form removes one kind of repeated fact. The words matter less than the questions.

1NF: one value per cell, no repeating groups. Each column holds a single value, and a row never has "product 1, product 2, product 3" in one cell. Our products / quantities / prices lists break it. The fix: one row per order line. Here I unpack the lists with SQL (string_to_array and unnest ... WITH ORDINALITY, which numbers the items so that the three lists line up):

SELECT f.order_id, p.n, trim(p.v) AS product, trim(q.v)::int AS qty,       (trim(pr.v)::numeric * 100)::int AS centsFROM orders_flat fCROSS JOIN LATERAL unnest(string_to_array(f.products, ','))   WITH ORDINALITY AS p(v, n)JOIN LATERAL unnest(string_to_array(f.quantities, ','))       WITH ORDINALITY AS q(v, n) ON q.n = p.nJOIN LATERAL unnest(string_to_array(f.prices, ','))           WITH ORDINALITY AS pr(v, n) ON pr.n = p.nORDER BY f.order_id, p.n;

 order_id | n |  product   | qty | cents ----------+---+------------+-----+-------        1 | 1 | Notebook   |   2 |   350        1 | 2 | Pen        |   5 |   120        2 | 1 | Headphones |   1 |  5900        3 | 1 | Backpack   |   1 |  4500        3 | 2 | Notebook   |   1 |   350(5 rows)

This is also what a real migration looks like: you often have to rescue messy data with a query like this, which is why starting clean is cheaper.

2NF: every non-key column depends on the whole key. This matters when the key has two parts, like order_items (order_id, product_id). If you also stored product_name there, the name depends only on product_id, not on the pair. The product name would be copied on every line that sells it. The fix: move it to products.

3NF: non-key columns depend on the key, and on nothing else. In orders_flat the key is order_id, but customer_city depends on the customer, and the customer depends on the order. That chain is a transitive dependency. The fix: a customers table holding name, email and city once. A memory trick: every non-key column should depend on "the key, the whole key, and nothing but the key".

After these three steps the flat table becomes four tables, the shop you already know: customers, products, orders and order_items. Each fact lives in one place. Asha's city is one cell, so the update anomaly is gone.

Primary keys: serial, identity or UUID

Every table needs a primary key. Three common choices.

serial is the older PostgreSQL shortcut. GENERATED ... AS IDENTITY is the standard SQL way (PostgreSQL 10 and later), and is the one to use. Look at what each really creates:

                            Table "public.t_serial" Column |  Type   | Collation | Nullable |               Default                --------+---------+-----------+----------+-------------------------------------- id     | integer |           | not null | nextval('t_serial_id_seq'::regclass)                         Table "public.t_ident" Column |  Type   | Collation | Nullable |           Default            --------+---------+-----------+----------+------------------------------ id     | integer |           | not null | generated always as identity

serial is only a column with a default taken from a separate sequence, and nothing stops you from inserting your own id. That is how a counter and real ids drift apart. GENERATED ALWAYS refuses a manual id:

INSERT INTO t_ident (id, v) VALUES (100, 'manual');INSERT INTO t_serial (id, v) VALUES (1, 'clash');

ERROR:  cannot insert a non-DEFAULT value into column "id"DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.HINT:  Use OVERRIDING SYSTEM VALUE to override.ERROR:  duplicate key value violates unique constraint "t_serial_pkey"DETAIL:  Key (id)=(1) already exists.

The identity column says no up front, with a helpful hint. The serial column accepted my manual id as a normal value, and the sequence knows nothing about it, so it failed later when the counter reached the same number (here I forced the clash by inserting 1 by hand).

UUID (uuid DEFAULT gen_random_uuid()) is a 128-bit random label. gen_random_uuid() is built in since PostgreSQL 13. It looks like d240e533-4b8a-43cd-8f40-0f5aa4ab0ba0.

I measured the sizes with pg_column_size: 1::integer is 4, 1::bigint is 8, gen_random_uuid() is 16. Random UUIDs scatter new rows through the index, which is worse for cache and write volume than ever-growing numbers. UUID version 7 (time-ordered) fixes that; PostgreSQL 18 adds uuidv7() (from the release notes, not run here), and on 16 you generate it in the app.

My default: bigint GENERATED ALWAYS AS IDENTITY. integer runs out at 2.1 billion rows, and changing a key type on a huge table is a painful migration, while 4 extra bytes are cheap. Use UUID when ids must be created outside the database or must not be guessable (or when you merge data from several systems). If the id is exposed to users, you can keep a bigint primary key and add a separate public UUID column.

Natural keys (email, username) are tempting but fragile: people change emails, and every child table has to follow. Use a surrogate key for the primary key, and put the natural one under a UNIQUE constraint.

Constraints: let the database say no

An application checks rules in code, but a bug, a second service or a hand-typed UPDATE can skip that code. A constraint is checked on every write. The v2 shop uses all four kinds. Here is the full redesign (shortened a little; the complete script is what I ran):

CREATE TABLE customers (  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  name       text NOT NULL CHECK (name <> ''),  email      text NOT NULL,  city       text,  created_at timestamptz NOT NULL DEFAULT now(),  deleted_at timestamptz);CREATE UNIQUE INDEX customers_email_active_key ON customers (lower(email)) WHERE deleted_at IS NULL;CREATE TABLE products (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  name        text NOT NULL,  category    text NOT NULL,  price_cents integer NOT NULL CHECK (price_cents >= 0),  stock       integer NOT NULL DEFAULT 0 CHECK (stock >= 0));CREATE TABLE orders (  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,  status      text NOT NULL DEFAULT 'new' REFERENCES order_statuses (status),  ordered_at  timestamptz NOT NULL DEFAULT now());CREATE TABLE order_items (  order_id         bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,  product_id       bigint NOT NULL REFERENCES products (id) ON DELETE RESTRICT,  quantity         integer NOT NULL CHECK (quantity > 0),  unit_price_cents integer NOT NULL CHECK (unit_price_cents >= 0),  PRIMARY KEY (order_id, product_id));

(order_statuses is the lookup table described below.) What each kind does:

Make columns NOT NULL unless "unknown" is a real state. Every nullable column adds three-valued logic (lesson 0) to every query that touches it. Here is the v2 schema refusing bad data. The messages are real:

INSERT INTO order_items VALUES (1, 1, 0, 350);INSERT INTO order_items VALUES (1, 1, 1, 350);INSERT INTO customers (name, email) VALUES ('', 'x@example.com');INSERT INTO orders (customer_id, status) VALUES (2, 'shiped');

ERROR:  new row for relation "order_items" violates check constraint "order_items_quantity_check"DETAIL:  Failing row contains (1, 1, 0, 350).ERROR:  duplicate key value violates unique constraint "order_items_pkey"DETAIL:  Key (order_id, product_id)=(1, 1) already exists.ERROR:  new row for relation "customers" violates check constraint "customers_name_check"DETAIL:  Failing row contains (4, , x@example.com, null, 2026-10-11 12:42:17.903881+00, null).ERROR:  insert or update on table "orders" violates foreign key constraint "orders_status_fkey"DETAIL:  Key (status)=(shiped) is not present in table "order_statuses".

The typo shiped never reaches the table. The second error shows that a composite primary key stops the same product appearing twice in one order. (The timestamp is this machine's clock when I ran it.)

Adding constraints to a table that already has bad data fails, and on a big live table it needs care (lesson 7 covers NOT VALID and VALIDATE). Start with them.

Foreign keys and ON DELETE

A foreign key says "this value must exist over there". The question is what to do when someone deletes the parent. You choose per key:

I tried three deletes against the v2 schema. Deleting a customer who has orders, or a product that was sold, is refused. Deleting an order removes its lines:

DELETE FROM customers WHERE id = 1;DELETE FROM products WHERE id = 1;DELETE FROM orders WHERE id = 3;SELECT order_id, product_id FROM order_items ORDER BY 1, 2;

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"DETAIL:  Key (id)=(1) is still referenced from table "orders".ERROR:  update or delete on table "products" violates foreign key constraint "order_items_product_id_fkey" on table "order_items"DETAIL:  Key (id)=(1) is still referenced from table "order_items".DELETE 1 order_id | product_id ----------+------------        1 |          1        1 |          2        2 |          4(3 rows)

Order 3 had two lines, and both are gone. That is the right call for order_items (a line without its order is meaningless), and the wrong call for customers (you do not want to erase sales history because a customer left). A good rule: CASCADE only for parts that belong to the parent, RESTRICT for anything with its own meaning. A cascade can also delete a huge number of rows in one statement, so be careful.

Many-to-many with a junction table

One product can have many tags, and one tag is on many products. You cannot store that in either table without lists (back to 1NF). Use a junction table with a foreign key to each side and a composite primary key, exactly like order_items:

CREATE TABLE tags (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE);CREATE TABLE product_tags (  product_id bigint  NOT NULL REFERENCES products (id) ON DELETE CASCADE,  tag_id     integer NOT NULL REFERENCES tags (id) ON DELETE CASCADE,  PRIMARY KEY (product_id, tag_id));CREATE INDEX product_tags_tag_idx ON product_tags (tag_id);

The primary key (product_id, tag_id) already makes "tags of a product" fast and forbids duplicate pairs. The extra index on tag_id serves the other direction, "products with a tag". Query it by joining through:

SELECT p.name, string_agg(t.name, ', ' ORDER BY t.name) AS tagsFROM products pJOIN product_tags pt ON pt.product_id = p.idJOIN tags t ON t.id = pt.tag_idGROUP BY p.id ORDER BY p.id;

    name    |     tags     ------------+-------------- Notebook   | school Pen        | school Backpack   | gift, travel Headphones | gift(4 rows)

If the relationship has its own facts (when the tag was added, by whom, the quantity), those columns go on the junction table. Lesson 7 shows a tags array as a shortcut and when it is a bad idea.

Enum or lookup table for fixed values

orders.status can only be a few values. Two ways to enforce that:

A PostgreSQL enum type:

CREATE TYPE order_status AS ENUM ('new','paid','shipped','cancelled');

INSERT INTO e VALUES ('shiped');ERROR:  invalid input value for enum order_status: "shiped"

Enums are small (4 bytes, per the documentation) and sort in the order you declared. You can add a value, even in a chosen place:

ALTER TYPE order_status ADD VALUE 'refunded';ALTER TYPE order_status ADD VALUE 'pending' BEFORE 'paid';SELECT enum_range(NULL::order_status);

                  enum_range                   ----------------------------------------------- {new,pending,paid,shipped,cancelled,refunded}

But you cannot remove or rename a value easily: ALTER TYPE order_status DROP VALUE 'new' is a syntax error on 16.15. A removed value needs a new type and a table rewrite.

A lookup table (what the v2 schema uses) is a table of allowed values plus a foreign key:

CREATE TABLE order_statuses (status text PRIMARY KEY);INSERT INTO order_statuses VALUES ('new'), ('paid'), ('shipped'), ('cancelled');-- orders.status text REFERENCES order_statuses (status)

Adding or removing a value is a plain INSERT or DELETE (I ran both with 'refunded'), you can add columns later (a label, a sort order, an is_active flag), and other tables can reference it. Cost: one more table, and a foreign key check on each write. A third option, CHECK (status IN ('new','paid',...)), is simplest, but changing the list means altering the constraint.

I use a lookup table when the list may change or the business owns it, and a CHECK or enum for a truly closed list.

Money

Do not use float (lesson 0). Two sound choices:

• numeric(p, s) is exact decimal. numeric(8,2) is what lessons 0 to 3 used. It is easy to read and sum, a little slower and bigger.
• Integer cents (price_cents integer) is exact, small and fast. You format it at the edge (divide by 100 in the UI). You must also store the currency, and remember that not every currency has 2 decimals.

SELECT 0.1::float8 * 3 AS float_sum, 0.1::numeric * 3 AS numeric_sum,       1999::int / 100 AS int_div_wrong, 1999 / 100.0 AS shown;

      float_sum      | numeric_sum | int_div_wrong |        shown        ---------------------+-------------+---------------+--------------------- 0.30000000000000004 |         0.3 |            19 | 19.9900000000000000

The float is wrong. The integer division 1999 / 100 silently drops the cents and gives 19. So when you keep cents, convert with a decimal (/ 100.0) or format in the app. Both approaches are fine. What matters is to pick one for the whole schema, name it clearly (price_cents), and never mix. Keep a currency code column (char(3), like 'INR') if you sell in more than one. For tax rounding, decide the rounding rule once.

Timestamps: use timestamptz

timestamp stores a date and time with no zone. timestamptz ("with time zone") stores one exact moment. PostgreSQL converts the input to UTC and stores that, and it shows it in the session's time zone. It does not store the original zone. I inserted the same moment into both types and then changed the session zone:

SET timezone = 'UTC';INSERT INTO ts VALUES ('2025-03-15 20:10', '2025-03-15 20:10+00');SELECT * FROM ts;SET timezone = 'Asia/Kolkata';SELECT * FROM ts;

          a          |           b            ---------------------+------------------------ 2025-03-15 20:10:00 | 2025-03-15 20:10:00+00          a          |             b             ---------------------+--------------------------- 2025-03-15 20:10:00 | 2025-03-16 01:40:00+05:30

The timestamp column (a) did not change, so "20:10" means nothing without knowing whose clock it was. The timestamptz column (b) is the same moment, shown on the Kolkata clock (the next day). That is what you want for an order time, a login or a log line. Use plain timestamp only for a local wall-clock time that is not a moment (a shop opens at 09:00 in whatever city). Use date for a calendar day with no time. Give audit columns a default: created_at timestamptz NOT NULL DEFAULT now(). Reports have to pick a zone for "which day" (lesson 2's note).

Soft delete and its trade-offs

Soft delete adds deleted_at timestamptz, and "deleting" sets it. You keep history and can undo a mistake. The costs are real:

1. Every query must remember WHERE deleted_at IS NULL. One forgotten filter shows deleted data. A view that does the filtering helps.
2. Unique rules break. A deleted customer still holds the email. A normal UNIQUE on the email would block the person from signing up again.
3. Foreign keys do not know. A live order can point to a deleted customer.
4. Tables grow forever, and privacy law may require real deletion.

The v2 schema handles point 2 with a partial unique index: uniqueness is only enforced for live rows, and lower(email) makes it case-insensitive. I soft-deleted Asha and signed up the same email again:

UPDATE customers SET deleted_at = now() WHERE id = 1;INSERT INTO customers (name,email) VALUES ('Asha 2','ASHA@example.com') RETURNING id, name;CREATE VIEW active_customers AS SELECT * FROM customers WHERE deleted_at IS NULL;SELECT id, name FROM active_customers ORDER BY id;

UPDATE 1 id |  name  ----+--------  6 | Asha 2(1 row) id |  name  ----+--------  2 | Ben  3 | Chen  6 | Asha 2(3 rows)

Before that, inserting ASHA@example.com while the first Asha was live failed with duplicate key value violates unique constraint "customers_email_active_key", so the rule still protects live rows. (The new id is 6, not 4, because failed inserts used up identity numbers. Lesson 0 explained gaps.)

Alternatives: move deleted rows to an archive table (DELETE ... RETURNING into an INSERT), or keep an append-only history table. A common compromise: soft delete for things users can undo (customers, posts), hard delete for everything else, and a scheduled job that removes old soft-deleted rows.

Naming

Boring consistency beats cleverness.

• snake_case, lowercase, no spaces. PostgreSQL folds unquoted names to lowercase, so CustomerID becomes customerid unless you quote it, and then you must quote it forever.
• Plural table names (customers) or singular, but pick one. The lessons use plural.
• Primary key id. Foreign key <singular table>_id: customer_id. This makes JOIN ... ON o.customer_id = c.id read naturally.
• Booleans as is_ or has_ (is_active), timestamps as <verb>_at (created_at, deleted_at), unit in the name when it matters (price_cents, weight_grams).
• Avoid reserved words as names (user, order, group). order is why the shop table is orders.
• Let PostgreSQL name constraints (orders_customer_id_fkey), or name them yourself the same way every time. Error messages quote those names, as you saw above.

Try it

Use any scratch database.

1. Create authors and books where a book has exactly one author, deleting an author with books is refused, and the title must not be empty. What does the book's foreign key column need?
2. Make books and readers many-to-many ("reader borrowed book") and record the date of each loan.
3. Why is price numeric(8,2) OK for money but price float8 not? Show it with a query.
4. A customer table has UNIQUE (email). Customers are soft deleted. What goes wrong, and what is the fix?
5. Which ON DELETE would you choose for order_items.order_id, and for orders.customer_id?

Answers

1. CREATE TABLE books (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, author_id bigint NOT NULL REFERENCES authors (id) ON DELETE RESTRICT, title text NOT NULL CHECK (title <> '')); plus CREATE INDEX ON books (author_id); for the lookups and deletes.
2. A junction table: loans (book_id bigint REFERENCES books (id), reader_id bigint REFERENCES readers (id), borrowed_on date NOT NULL DEFAULT current_date, PRIMARY KEY (book_id, reader_id, borrowed_on)). The date is part of the key, so the same reader can borrow the same book again on another day.
3. SELECT 0.1::float8 + 0.2::float8 gives 0.30000000000000004, while the numeric version gives 0.3 (lesson 0).
4. A deleted customer still owns the email, so the person cannot register again. Fix: CREATE UNIQUE INDEX ... ON customers (lower(email)) WHERE deleted_at IS NULL;.
5. CASCADE for order_items.order_id (lines are part of the order). RESTRICT (or the default) for orders.customer_id, so sales history is never deleted by accident.

Interview questions

Key takeaways

• Store each fact once: 1NF no lists, 2NF no partial dependencies, 3NF no transitive ones. Denormalise only with a measured reason.
• Default primary key: bigint GENERATED ALWAYS AS IDENTITY. Use UUID when ids are made outside the database. Avoid serial in new code.
• Put the rules in the database: NOT NULL, UNIQUE, CHECK, foreign keys. A CHECK alone lets NULL through.
• Choose ON DELETE on purpose: CASCADE for owned parts, RESTRICT for history. Index your foreign key columns.
• Many-to-many needs a junction table. Fixed value lists: lookup table (flexible) or enum or CHECK (simple).
• Money is numeric or integer cents, never float. Moments are timestamptz. Soft delete needs a partial unique index and a filtering view.

What's next

Lesson 5: Indexes and EXPLAIN. A good schema is correct, but it also has to be fast. Next you load a million rows, read real query plans line by line, and see when an index helps, when it does not, and what it costs on every write. For the database basics that sit under an API see Backend, Lesson 07.