Database Design and Schema Modeling

TLDR: Turn the things your application tracks (users, products, orders) into tables. Give every table a primary key, usually a bigint identity column or a UUID. Link tables with foreign keys and index those columns yourself. Use a junction table for many-to-many relationships. Normalize so each fact is stored once, and denormalize only when you have measured a reason. Use PostgreSQL schemas to group tables and control access.

Schema design decides how easy your data is to query and how hard it is to corrupt. A good design lets the database reject bad data before it reaches your tables. A poor one leads to duplicated values that drift apart, slow joins and migrations that lock busy tables. This part shows the building blocks with PostgreSQL 18 syntax and real error messages, then puts them together in a small e-commerce schema.

Prerequisites

• PostgreSQL and psql from Part 2.
• The SQL basics from Part 3.
• A scratch database: CREATE DATABASE shop; then \c shop.

Thinking About Entities and Relationships

Start with the nouns. List what your application stores, and each noun becomes a table:

• Users write posts.
• Posts belong to one user.
• Comments belong to one post and one user.
• Tags label many posts, and a post has many tags.

Then write down how they relate ("a user has many posts", "a post has many tags"). Those sentences decide where the foreign keys go.

Primary Keys

Every table needs a primary key: one or more columns whose value is unique and never NULL. PostgreSQL creates a unique index for it automatically.

Auto-incrementing Integers

Use an identity column. It is the SQL-standard replacement for SERIAL:

GENERATED ALWAYS stops code from inserting its own ids by accident, which would later collide with generated values. Use GENERATED BY DEFAULT AS IDENTITY when you must load existing ids, for example during a migration.

You will still see id SERIAL PRIMARY KEY in older code. It works, but it creates a sequence that is only loosely tied to the table: privileges, ownership and table copies with CREATE TABLE ... (LIKE ...) behave in surprising ways. Identity columns avoid that.

Prefer bigint over integer for keys. integer stops at 2,147,483,647, and changing the type of a primary key on a large, busy table later means rewriting it and every foreign key that points to it.

UUIDs

UUIDs are 128-bit identifiers that any machine can generate without asking the database. They are useful when clients create ids offline, when you merge data from several databases, or when you do not want to expose how many rows you have. PostgreSQL has two generators built in, with no extension needed:

SELECT gen_random_uuid() AS v4, uuidv7() AS v7;

                  v4                  |                  v7
--------------------------------------+--------------------------------------
 0f858735-b6c9-4a96-9e27-e0650403c533 | 01a16917-93d0-7fff-a1fd-190902082f20
(1 row)

• gen_random_uuid() returns a random version 4 UUID.
• uuidv7() (new in PostgreSQL 18) starts with a millisecond timestamp, so new values sort after older ones. Inserts then land at the end of the primary key index instead of on random pages, which keeps the index compact and the cache warm.

CREATE TABLE sessions (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id bigint NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now()
);

The trade-off: a UUID takes 16 bytes against 8 for a bigint, and it is harder to read in logs. Version 7 UUIDs also reveal when a row was created. Older tutorials use CREATE EXTENSION "uuid-ossp" and uuid_generate_v4(); you no longer need that extension.

Natural Keys

Sometimes a real-world identifier works as the key:

CREATE TABLE countries (
  code char(2) PRIMARY KEY,  -- ISO 3166 code: US, GB, FR
  name text NOT NULL
);

Use a natural key only if it is truly unique and never changes. Email addresses look unique but people change them, and product codes get reused. When in doubt, add a generated key and put a UNIQUE constraint on the natural value.

Foreign Keys

A foreign key says "this column must match a row in that table". The database checks it on every insert, update and delete.

One-to-Many Relationships

A user has many posts, and each post belongs to one user:

CREATE TABLE posts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id),
  title text NOT NULL,
  body text
);

-- PostgreSQL does not index the referencing column for you
CREATE INDEX posts_user_id_idx ON posts (user_id);

Now PostgreSQL refuses rows that point nowhere, and refuses to delete a user who still has posts:

Foreign Key Actions

ON DELETE and ON UPDATE decide what happens to child rows when the parent changes:

CREATE TABLE comments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id bigint NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
  body text NOT NULL
);

Action	Effect when the parent row is deleted
NO ACTION (default)	Error if child rows exist. The check can be deferred to the end of the transaction
RESTRICT	Error if child rows exist, checked immediately
CASCADE	Delete the child rows too
SET NULL	Set the foreign key column to NULL (the column must allow it)
SET DEFAULT	Set the column to its default value

Choose per relationship:

• Comments without their post are useless: ON DELETE CASCADE.
• Order lines must survive when someone tries to delete a product: keep the default, so the delete fails and you retire the product instead.
• A post whose author deletes their account might stay with no author: ON DELETE SET NULL on a nullable user_id.

ON DELETE CASCADE can remove far more rows than you expect when relationships chain. Use it where the child data has no meaning without the parent.

Many-to-Many Relationships

A post has many tags, and a tag belongs to many posts. Model it with a junction table that holds one row per pair:

CREATE TABLE tags (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id bigint NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
  tag_id bigint NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

-- The primary key covers lookups by post_id; add one for tag_id
CREATE INDEX post_tags_tag_id_idx ON post_tags (tag_id);

The composite primary key stops the same tag from being attached twice. Query it from either side:

-- Posts with tag 5
SELECT p.id, p.title
FROM posts AS p
JOIN post_tags AS pt ON pt.post_id = p.id
WHERE pt.tag_id = 5;

-- Tags of post 10
SELECT t.name
FROM tags AS t
JOIN post_tags AS pt ON pt.tag_id = t.id
WHERE pt.post_id = 10;

One-to-One Relationships

Use one-to-one when some data is optional, large or accessed separately:

CREATE TABLE user_profiles (
  user_id bigint PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
  bio text,
  avatar_url text
);

user_id is both the primary key and the foreign key, so each user has at most one profile. If every user always has exactly one profile, put the columns in users instead.

Normalization

Normalization arranges columns so each fact is stored in exactly one place. The first three normal forms cover almost every practical case. The examples in this section reuse table names from the shop schema later in this part, so read them, or try them in a separate database.

First Normal Form (1NF)

Each column holds one value, and there are no repeating groups.

Bad:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_name text,
  products text  -- 'Widget, Gadget, Thing'
);

You cannot count widgets sold, store a quantity per product, or rename a product safely.

Good:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_name text
);

CREATE TABLE order_lines (
  order_id bigint NOT NULL REFERENCES orders (id),
  product_name text NOT NULL,
  quantity integer NOT NULL,
  PRIMARY KEY (order_id, product_name)
);

Second Normal Form (2NF)

Every non-key column depends on the whole primary key, not just part of it. This matters for composite keys.

Bad:

CREATE TABLE order_items (
  order_id bigint,
  product_id bigint,
  product_name text,  -- depends only on product_id
  quantity integer,
  PRIMARY KEY (order_id, product_id)
);

The product name repeats on every order line and goes stale when the product is renamed.

Good: keep product data in products, and reference it.

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE order_items (
  order_id bigint,
  product_id bigint REFERENCES products (id),
  quantity integer,
  PRIMARY KEY (order_id, product_id)
);

Third Normal Form (3NF)

Non-key columns depend only on the key, not on other non-key columns.

Bad:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint,
  customer_name text,   -- depends on customer_id, not on the order
  customer_email text
);

Good: move customer details to customers and keep only customer_id in orders.

When to Denormalize

Sometimes a copy is the right call:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  shipping_address text NOT NULL,     -- a snapshot: must not change if the customer moves
  total_amount numeric(10, 2) NOT NULL -- cached sum of the order lines
);

Good reasons to denormalize:

• Historical snapshots: an invoice must show the price and address at the time of the order, not today's values.
• Measured performance problems: a join or aggregate that runs constantly and is too slow, even with indexes.

Every copy needs a rule for keeping it correct (application code, a trigger or a materialized view). Denormalize on purpose, and write the rule down.

Schemas (Namespaces)

In PostgreSQL, a schema is also a namespace inside a database: a folder for tables, views and functions. (People also say "schema" for the table structure as a whole; the context tells you which one is meant.) Every database starts with a schema called public.

Creating Schemas

CREATE SCHEMA auth;
CREATE SCHEMA billing;

CREATE TABLE auth.users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE billing.invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES auth.users (id),
  amount numeric(10, 2) NOT NULL
);

Refer to a table with schema.table, for example SELECT * FROM auth.users;.

Search Path

When a query names a table without a schema, PostgreSQL looks through the schemas in search_path, in order. The default is "$user", public: first a schema named like your role (if it exists), then public.

This database now has two tables called users. The search path decides which one an unqualified name means:

Set a default for every session in a database, or for one role:

ALTER DATABASE shop SET search_path TO app, public;
ALTER ROLE reporting SET search_path TO reporting, public;

Schemas are useful to:

• group related tables (auth, billing, analytics);
• grant privileges per schema (GRANT USAGE ON SCHEMA analytics TO analyst;);
• give an application its own schema that it owns, which is the cleanest fix for the PostgreSQL 15 change that removed CREATE on public for ordinary roles (see Part 2).

Practical Schema Example

Here is a complete schema for a small shop, using everything above. Paste it into an empty database:

-- Customers
CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Catalog: categories can nest (parent_id points to another category)
CREATE TABLE categories (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  parent_id bigint REFERENCES categories (id)
);

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category_id bigint REFERENCES categories (id),
  name text NOT NULL,
  description text,
  price numeric(10, 2) NOT NULL CHECK (price >= 0),
  stock_quantity integer NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Orders and their lines
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  status text NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
  total_amount numeric(10, 2) NOT NULL,
  created_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),
  quantity integer NOT NULL CHECK (quantity > 0),
  unit_price numeric(10, 2) NOT NULL,  -- price at the time of the order
  PRIMARY KEY (order_id, product_id)
);

-- A customer can have several addresses
CREATE TABLE addresses (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  street text NOT NULL,
  city text NOT NULL,
  region text,
  country char(2) NOT NULL,
  postal_code text,
  is_default boolean NOT NULL DEFAULT false
);

-- One review per product per customer
CREATE TABLE reviews (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id bigint NOT NULL REFERENCES products (id) ON DELETE CASCADE,
  customer_id bigint NOT NULL REFERENCES customers (id),
  rating smallint NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment text,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (product_id, customer_id)
);

-- Index the foreign key columns that no other index covers
CREATE INDEX products_category_id_idx ON products (category_id);
CREATE INDEX categories_parent_id_idx ON categories (parent_id);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX order_items_product_id_idx ON order_items (product_id);
CREATE INDEX addresses_customer_id_idx ON addresses (customer_id);
CREATE INDEX reviews_customer_id_idx ON reviews (customer_id);

\d shows the result, including every constraint and index:

This schema shows:

• One-to-many: customers have orders and addresses.
• Many-to-many: orders and products, through order_items, which also carries a quantity and the price paid.
• Self-reference: categories can have a parent category.
• Constraints: prices and stock cannot go negative, ratings stay between 1 and 5, and an order status must be one of five values.
• A deliberate snapshot: unit_price and total_amount keep what the customer paid even if product prices change later.

Database Diagrams

Draw the relationships before you write SQL; a missing or reversed arrow is much cheaper to fix on paper. Here is the shop schema (hover a table for its key columns):

Tools such as pgAdmin's ERD tool and DBeaver can draw a diagram from an existing database, which helps when you join a project with an unfamiliar schema.

Common Design Patterns

Audit Columns

Track when rows were created and last changed. A trigger keeps updated_at correct no matter which code path updates the row:

CREATE TABLE articles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER articles_set_updated_at
BEFORE UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Add created_by and updated_by columns referencing users (id) if you also need to know who made the change.

Soft Deletes

Mark rows as deleted instead of removing them:

ALTER TABLE customers ADD COLUMN deleted_at timestamptz;

UPDATE customers SET deleted_at = now() WHERE id = 5;

SELECT * FROM customers WHERE deleted_at IS NULL;

A unique constraint still sees soft-deleted rows, so a deleted customer's email blocks a new sign-up. A partial unique index fixes that:

ALTER TABLE customers DROP CONSTRAINT customers_email_key;
CREATE UNIQUE INDEX customers_email_active_key
  ON customers (email) WHERE deleted_at IS NULL;

Versioning

Keep every version of a record in its own row:

CREATE TABLE document_versions (
  document_id bigint NOT NULL,
  version integer NOT NULL,
  title text NOT NULL,
  body text,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by bigint REFERENCES users (id),
  PRIMARY KEY (document_id, version)
);

-- Latest version of document 10
SELECT * FROM document_versions
WHERE document_id = 10
ORDER BY version DESC
LIMIT 1;

Polymorphic Associations

Some frameworks store "comments on anything" with a type column and an id:

CREATE TABLE comments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  commentable_type text NOT NULL,  -- 'post' or 'photo'
  commentable_id bigint NOT NULL,
  body text NOT NULL
);

The database cannot enforce a foreign key on commentable_id, because it points at different tables. Prefer one nullable foreign key per target with a check that exactly one is set:

CREATE TABLE comments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id bigint REFERENCES posts (id) ON DELETE CASCADE,
  photo_id bigint REFERENCES photos (id) ON DELETE CASCADE,
  body text NOT NULL,
  CHECK (num_nonnulls(post_id, photo_id) = 1)
);

Design Anti-Patterns to Avoid

God tables: one table with dozens of loosely related columns, many of them mostly NULL. Split it by entity.

Entity-attribute-value (EAV): a generic (entity_id, attribute, value) table for everything. Queries become self-joins, and types and constraints are lost. Use real columns for known attributes and a jsonb column for truly free-form data.

Lists in a text column: 'red,green,blue'. Use a child table, or an array if you never need to join on the values.

Unclear NULL meaning: decide what NULL means for each nullable column ("unknown", "not applicable") and use NOT NULL everywhere else.

Inconsistent naming: pick one style and keep it. Lowercase snake_case avoids quoting, because PostgreSQL folds unquoted names to lowercase: a table created as "UserAccounts" must be quoted forever after.

Common Mistakes

• Not indexing foreign key columns. PostgreSQL does not do it for you, and deletes on the parent table then scan the child table.
• Using integer keys on tables that grow fast. Running out at 2.1 billion forces a slow, risky migration. Start with bigint.
• Cascading deletes everywhere. One DELETE on a parent can remove thousands of child rows. Reserve CASCADE for data that has no meaning on its own.
• Copying mutable data without a sync rule. Duplicated values drift apart. Normalize, or document how the copy stays correct.
• Natural keys that change. Email addresses and usernames make poor primary keys. Use a generated key plus a UNIQUE constraint.

Planning Your Schema

Before you write CREATE TABLE:

1. List the entities your application manages.
2. List each entity's attributes and pick a type for each (Part 5).
3. Write down the relationships ("one customer has many orders").
4. Choose primary keys: bigint identity by default, UUIDv7 when ids are generated outside the database.
5. Add foreign keys and decide the ON DELETE action for each.
6. Normalize to third normal form.
7. Denormalize only where you have a snapshot requirement or a measured performance problem.
8. Add constraints for every rule the data must follow.
9. Plan indexes for foreign keys and for your most common queries (Part 7).

Summary

You have seen how to turn entities into tables, how primary and foreign keys protect relationships, how to model one-to-many, many-to-many and one-to-one links, and how normalization keeps each fact in one place. You also used PostgreSQL schemas to group tables, and built a complete shop schema with the constraints and indexes it needs.

Next, in Data Types and Constraints in PostgreSQL, you will choose the right type for each column and add the constraints that keep bad data out.