Schema Design

Schema design decides where each fact lives. Good designs make wrong data hard to store; the examples show the problems a design prevents, not just the design itself.

The Update Anomaly

This flat table copies the customer’s email into every order. Then one copy changes.

-- flat.sql
CREATE TABLE orders_flat (
    order_id       INT PRIMARY KEY,
    customer_name  TEXT,
    customer_email TEXT,
    product        TEXT,
    price          NUMERIC(8, 2)
);
INSERT INTO orders_flat VALUES
    (1, 'Ana', 'ana@old.com', 'Lamp', 25.00),
    (2, 'Ana', 'ana@old.com', 'Desk', 90.00),
    (3, 'Ben', 'ben@x.com',   'Lamp', 25.00);
UPDATE orders_flat SET customer_email = 'ana@new.com' WHERE order_id = 2;
SELECT customer_name, array_agg(DISTINCT customer_email) AS emails
FROM orders_flat GROUP BY customer_name ORDER BY customer_name;

$ psql -d sqldemo -f flat.sql
CREATE TABLE
INSERT 0 3
UPDATE 1
 customer_name |          emails           
---------------+---------------------------
 Ana           | {ana@new.com,ana@old.com}
 Ben           | {ben@x.com}
(2 rows)

Ana now has two email addresses, and nothing in the table can say which is right. The update touched one copy of a fact that was stored twice. This is the update anomaly that normalization exists to prevent; its cousins are being unable to record a customer until they order (insert anomaly) and losing the customer when their last order is deleted (delete anomaly).

The Normalized Version

Give each entity its own table and link them with keys.

-- normalized.sql
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE);
CREATE TABLE products  (id INT PRIMARY KEY, name TEXT NOT NULL, price NUMERIC(8, 2) NOT NULL);
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    product_id  INT NOT NULL REFERENCES products(id),
    price_paid  NUMERIC(8, 2) NOT NULL
);
INSERT INTO customers VALUES (1, 'Ana', 'ana@old.com'), (2, 'Ben', 'ben@x.com');
INSERT INTO products  VALUES (1, 'Lamp', 25.00), (2, 'Desk', 90.00);
INSERT INTO orders    VALUES (1, 1, 1, 25.00), (2, 1, 2, 90.00), (3, 2, 1, 25.00);
UPDATE customers SET email = 'ana@new.com' WHERE id = 1;
SELECT o.id, c.name, c.email, p.name AS product
FROM orders o JOIN customers c ON c.id = o.customer_id JOIN products p ON p.id = o.product_id
ORDER BY o.id;

$ psql -d sqldemo -f normalized.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 3
UPDATE 1
 id | name |    email    | product 
----+------+-------------+---------
  1 | Ana  | ana@new.com | Lamp
  2 | Ana  | ana@new.com | Desk
  3 | Ben  | ben@x.com   | Lamp
(3 rows)

One UPDATE changed Ana’s email everywhere, because it is stored once. Notice price_paid on orders: that is not duplication. The product’s current price can change, but the price on a past order must not, so it is a different fact and belongs on the order.

Many-to-Many: Junction Tables

A student takes many courses and a course has many students. A third table holds the pairs.

-- many_to_many.sql
CREATE TABLE students (id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses  (id INT PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id  INT REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES (1, 'Ana'), (2, 'Ben');
INSERT INTO courses  VALUES (10, 'SQL'), (20, 'Go');
INSERT INTO enrollments VALUES (1, 10), (1, 20), (2, 10);
INSERT INTO enrollments VALUES (1, 10);

$ psql -d sqldemo -f many_to_many.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 3
psql:many_to_many.sql:12: ERROR:  duplicate key value violates unique constraint "enrollments_pkey"
DETAIL:  Key (student_id, course_id)=(1, 10) already exists.

The composite primary key (student_id, course_id) makes each pair unique, so enrolling Ana in SQL twice fails. One-to-many needs no extra table (a foreign key on the “many” side), and one-to-one is a foreign key with a UNIQUE constraint.

Soft Deletes and Uniqueness

A soft delete marks a row as deleted instead of removing it. That breaks a plain UNIQUE constraint; a partial unique index fixes it.

-- soft_delete.sql
CREATE TABLE users (
    id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      TEXT NOT NULL,
    deleted_at TIMESTAMPTZ
);
CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;
INSERT INTO users (email) VALUES ('ana@x.com');
INSERT INTO users (email) VALUES ('ana@x.com');
UPDATE users SET deleted_at = now() WHERE email = 'ana@x.com';
INSERT INTO users (email) VALUES ('ana@x.com');
SELECT id, email, deleted_at IS NOT NULL AS deleted FROM users ORDER BY id;

$ psql -d sqldemo -f soft_delete.sql
CREATE TABLE
CREATE INDEX
INSERT 0 1
psql:soft_delete.sql:9: ERROR:  duplicate key value violates unique constraint "users_email_live"
DETAIL:  Key (email)=(ana@x.com) already exists.
UPDATE 1
INSERT 0 1
 id |   email   | deleted 
----+-----------+---------
  1 | ana@x.com | t
  3 | ana@x.com | f
(2 rows)

While Ana’s account was live, a second signup with the same email was refused. After the soft delete, the same email registered again, because the index only covers rows where deleted_at IS NULL. The new account got id 3, not 2: the failed insert had already drawn 2 from the identity sequence, and sequences never give numbers back.

UUID vs BIGINT Keys

Key type affects every index that contains the key. Compare the primary-key indexes of 100,000 rows.

-- key_sizes.sql
CREATE TABLE ids_bigint AS SELECT g::bigint AS id FROM generate_series(1, 100000) g;
CREATE TABLE ids_uuid   AS SELECT gen_random_uuid() AS id FROM generate_series(1, 100000);
ALTER TABLE ids_bigint ADD PRIMARY KEY (id);
ALTER TABLE ids_uuid   ADD PRIMARY KEY (id);
SELECT 'bigint' AS key_type, pg_column_size(1::bigint) AS bytes,
       pg_size_pretty(pg_relation_size('ids_bigint_pkey')) AS pk_index
UNION ALL
SELECT 'uuid', pg_column_size(gen_random_uuid()),
       pg_size_pretty(pg_relation_size('ids_uuid_pkey'));

$ psql -d sqldemo -f key_sizes.sql
SELECT 100000
SELECT 100000
ALTER TABLE
ALTER TABLE
 key_type | bytes | pk_index 
----------+-------+----------
 bigint   |     8 | 2208 kB
 uuid     |    16 | 3104 kB
(2 rows)

A UUID is twice the size of a BIGINT, and its index is about 40% larger here. That cost repeats in every foreign key and index that references the key. UUIDs are still the right choice when ids must be generated outside the database or exposed publicly without revealing row counts. Random gen_random_uuid() values also land all over the index; PostgreSQL 18’s uuidv7() generates time-ordered UUIDs that insert more like a sequence.

Naming and Standard Columns

Pick one convention and keep it: plural or singular table names, snake_case columns, and foreign keys named <table>_id. Most tables also benefit from created_at TIMESTAMPTZ NOT NULL DEFAULT now() and an updated_at maintained by a trigger, as in the functions and triggers lesson. Change schemas with versioned migration files rather than by hand, so every environment gets the same steps in the same order.

Practice

1. Add a one-to-one customer_profiles table for Ana’s shipping address, and show that a second profile for Ana is rejected.

-- practice_one_to_one.sql
CREATE TABLE customer_profiles (
    customer_id INT PRIMARY KEY REFERENCES customers(id),
    address     TEXT NOT NULL
);
INSERT INTO customer_profiles VALUES (1, '1 Main St');
INSERT INTO customer_profiles VALUES (1, '2 Side St');

$ psql -d sqldemo -f practice_one_to_one.sql
CREATE TABLE
INSERT 0 1
psql:practice_one_to_one.sql:7: ERROR:  duplicate key value violates unique constraint "customer_profiles_pkey"
DETAIL:  Key (customer_id)=(1) already exists.

2. List each course with the number of enrolled students, including courses with none.

-- practice_course_counts.sql
INSERT INTO courses VALUES (30, 'Rust');
SELECT c.title, count(e.student_id) AS students
FROM courses c LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.title ORDER BY students DESC, c.title;

$ psql -d sqldemo -f practice_course_counts.sql
INSERT 0 1
 title | students 
-------+----------
 SQL   |        2
 Go    |        1
 Rust  |        0
(3 rows)

3. Soft-delete the new ana@x.com account and write a query that returns only live users.

-- practice_live_users.sql
UPDATE users SET deleted_at = now() WHERE id = 3;
SELECT count(*) AS all_rows,
       count(*) FILTER (WHERE deleted_at IS NULL) AS live_users
FROM users;

$ psql -d sqldemo -f practice_live_users.sql
UPDATE 1
 all_rows | live_users 
----------+------------
        2 |          0
(1 row)