2. Relational Design and Data Modeling

This chapter is about deciding which tables, columns, keys, and constraints a database should have before any data goes in. It goes from conceptual modeling with entity-relationship diagrams, through the keys and constraints that let PostgreSQL enforce correctness, to the normalization theory that removes redundancy, and finally to the design patterns used in production schemas. A well-designed schema makes queries simpler, keeps bad data out, and holds up as an application changes over the years. A badly designed one causes bugs that no amount of application code can fully fix.

<a id="21-conceptual-modeling"></a>

2.1 Conceptual Modeling

This subchapter shows how to describe a problem domain as entities, attributes, and relationships, how to state the rules that connect them precisely, and how to turn the resulting diagram into PostgreSQL tables step by step.

Entity-Relationship Modeling

Building a schema straight from a list of screens or API endpoints usually goes wrong. Tables end up shaped like today's user interface rather than the business, and rules such as "every order belongs to exactly one customer" stay in developers' heads instead of in the database. Entity-relationship (ER) modeling, introduced by Peter Chen in 1976, is a way to describe the things a business cares about and how they relate, independently of any database product. It is the architect's floor plan, drawn before anyone pours concrete. Moving a wall on paper is cheap. Moving it after the building stands is not.

Data modeling usually proceeds through three levels of detail:

Level	Question it answers	Audience	Typical artifact
Conceptual	What things exist and how do they relate?	Domain experts, analysts, developers	ER diagram with entities, relationships, and key attributes
Logical	What relations, columns, and keys represent them?	Developers, data architects	Relational schema, normalized (2.3)
Physical	How exactly is it stored in this DBMS?	Developers, DBAs	CREATE TABLE DDL with types, indexes, partitions (6.3)

An ER model has three building blocks, each covered in its own topic:

• Entities: the things the business keeps data about (customer, product, order).
• Attributes: the facts recorded about each entity (a customer's email, a product's price).
• Relationships: meaningful associations between entities (a customer places an order), together with cardinality rules (how many of each can participate).

Notations. The same model can be drawn in several notations. They express the same ideas with different symbols:

Notation	Entity	Relationship	Cardinality shown as	Common in
Chen	Rectangle	Diamond	1, N, M labels on lines	Textbooks, university courses
Crow's foot (Information Engineering)	Rectangle listing attributes	Line between entities	Symbols at line ends (||, o{, ...)	Industry tools (pgAdmin ERD, DBeaver, dbdiagram.io, Mermaid)
UML class diagram	Class box	Association line	1, 0..1, 0..*, 1..*	Object-oriented design

This guide uses crow's foot notation in text form, as tools like Mermaid do:

 Symbol at a line end      Meaning (how many of THIS entity per one of the other)
 --------------------      ------------------------------------------------------
 ||                        exactly one
 o|                        zero or one
 |{                        one or more
 o{                        zero or more

 Read it as:  CUSTOMER ||--o{ ORDER
              "one customer places zero or more orders;
               each order is placed by exactly one customer"

A practical modeling process

1. Collect requirements in plain language. Interview domain experts and collect real documents: invoices, forms, reports.
2. Find candidate entities. Nouns that have their own identity and multiple facts about them ("customer", "product") are entities. Nouns that only describe something else ("email", "color") are attributes.
3. Find relationships. Verbs that connect entities ("customer places order", "order contains product").
4. Pin down cardinality and participation for each relationship by asking "can there be zero? exactly one? many?" (Cardinality and Participation).
5. Choose identifiers for each entity (2.2).
6. Validate the model by walking through real scenarios with domain experts: "a customer returns half an order; where is that recorded?"
7. Map to tables (ER Diagrams to Relational Schemas) and normalize (2.3).

Example: from requirements to a conceptual model

Requirements for a small online shop:

Customers register with an email address and name. A customer places orders. Each order has a date and a status and contains one or more products, each with a quantity and the price paid at the time. Products belong to exactly one category. A customer can save several shipping addresses.

 Nouns  -> customer, email, name, order, date, status, product, quantity,
           price, category, shipping address
 Verbs  -> places, contains, belongs to, saves

 Entities:       CUSTOMER, ORDER, PRODUCT, CATEGORY, ADDRESS
 Attributes:     email, name (customer); date, status (order); quantity, price paid (?)
 Relationships:  places, contains, belongs to, saves

 CUSTOMER ||--o{ ORDER    : places
 ORDER    }o--|{ PRODUCT  : contains      (attributes: quantity, unit_price)
 PRODUCT  }o--|| CATEGORY : belongs to
 CUSTOMER ||--o{ ADDRESS  : saves

quantity and price paid describe neither the order alone nor the product alone. They describe one product within one order, so they are attributes of the contains relationship. Spotting where an attribute really belongs is one of the most valuable things ER modeling does.

Key takeaways

• ER modeling describes the domain (entities, attributes, relationships) before committing to tables.
• Modeling moves from conceptual to logical to physical, adding detail at each step.
• Crow's foot notation shows cardinality with symbols at each end of a relationship line.
• Nouns suggest entities and attributes, verbs suggest relationships. Always confirm with domain experts.

🧪 Practice

1. (Beginner) Read this requirement and list the entities, attributes, and relationships: "A library has members who borrow copies of books. A book can have several authors and several physical copies. Each loan records a borrow date and a due date."
2. (Intermediate) Draw the library model in crow's foot text notation (as above). Decide which entity or relationship owns borrow_date and due_date, and justify the choice.
3. (Interview) "Why do an ER model at all? Can't we just write migrations as features come in?" Hint: Think about where business rules end up, how expensive it is to change table structure after data exists, and who besides developers needs to understand the model.

Entities, Attributes, and Relationships

Each of the three building blocks has finer distinctions. Getting them right at the conceptual level decides whether a fact becomes a column, a separate table, or a calculation.

Entities. An entity type is a category of things with the same kinds of facts (CUSTOMER). An entity instance is one particular thing (the customer Ada Lovelace). An entity type becomes a table and its instances become rows. A good test: an entity has an identity that persists while its attributes change. A customer who changes their email is still the same customer.

Attributes come in several kinds:

Kind	Meaning	Example	Typical relational mapping
Simple (atomic)	Cannot be usefully divided	price	One column
Composite	Made of sub-parts	address = street, city, postal code, country	One column per part (or a composite type, 3.3)
Single-valued	One value per instance	date_of_birth	One column
Multivalued	Several values per instance	A customer's phone numbers	A separate table, one row per value
Derived	Computable from other data	age from date_of_birth, order total from its lines	Not stored, or a generated column or view
Key	Uniquely identifies an instance	email, sku	PRIMARY KEY or UNIQUE (2.2)
Optional	May be missing	middle_name	Nullable column

Relationships. A relationship type associates entity types; a relationship instance associates particular entities (Ada placed order #1001). Relationships have:

• Degree: how many entity types take part. Unary (recursive), binary (by far the most common), ternary, or higher.
• Roles: the part each entity plays. In a unary manages relationship on EMPLOYEE, one employee plays manager and the other plays subordinate.
• Attributes of their own: facts about the association itself, such as quantity on order-contains-product or enrolled_on on student-enrolls-in-course.

 Unary (recursive)            Binary                         Ternary
 -----------------            ------                         -------
   +----------+          +----------+     +---------+     +----------+   +----------+
   | EMPLOYEE |<-+       | CUSTOMER |-----|  ORDER  |     | SUPPLIER |---| PROJECT  |
   +----------+  |       +----------+     +---------+     +----------+   +----------+
        |  manages            places                            \    supplies   /
        +--------+                                                \   +------+  /
                                                                    --| PART |--
                                                                      +------+

A ternary relationship is not always equivalent to three binary ones. "Supplier S supplies part P to project J" is a single fact. Splitting it into supplier-part, part-project, and supplier-project pairs can produce combinations that never happened (see Fourth and Fifth Normal Form).

Example: each kind of attribute in PostgreSQL

CREATE TABLE customers (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email          text NOT NULL UNIQUE,              -- key attribute (a natural candidate key)
  full_name      text NOT NULL,                     -- simple, single-valued
  middle_name    text,                              -- optional: nullable
  date_of_birth  date,
  -- composite attribute "billing address" flattened into its parts
  billing_street      text,
  billing_city        text,
  billing_postal_code text,
  billing_country     char(2)
);

-- Multivalued attribute "phone numbers": its own table, one row per value
CREATE TABLE customer_phones (
  customer_id  bigint NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  phone        text   NOT NULL,
  kind         text   NOT NULL DEFAULT 'mobile',
  PRIMARY KEY (customer_id, phone)                  -- the same number cannot be listed twice
);

-- Derived attribute "age": computed when queried, never stored (it changes every day)
SELECT full_name,
       date_part('year', age(date_of_birth)) AS age_years
FROM customers;

age cannot be a stored generated column because generated columns must use immutable expressions, and the current date changes. A derived value that depends only on the row itself, such as price * quantity, can be a generated column:

CREATE TABLE order_lines (
  order_id    bigint        NOT NULL,
  line_no     int           NOT NULL,
  quantity    int           NOT NULL CHECK (quantity > 0),
  unit_price  numeric(10,2) NOT NULL,
  line_total  numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,  -- derived, kept in sync automatically
  PRIMARY KEY (order_id, line_no)
);

Key takeaways

• An entity type becomes a table. Its instances are rows identified by a key.
• Composite attributes are split into parts. Multivalued attributes get their own table.
• Derived attributes are usually computed in queries or views. Store them only for performance, ideally as generated columns.
• Relationships have a degree, roles, and sometimes attributes of their own. Those attributes belong to the relationship, not to either entity.

🧪 Practice

1. (Beginner) Classify each attribute of an EMPLOYEE as simple or composite, single- or multivalued, stored or derived: employee_no, name, home_address, skills, hire_date, years_of_service, salary.
2. (Intermediate) Model a unary relationship "employee reports to employee" and write the CREATE TABLE. Then write a query listing each employee with their manager's name, keeping employees who have no manager.
3. (Interview) "Should an order's total be a column on orders or computed from order_lines?" Hint: Consider read frequency, the risk of the stored value disagreeing with the lines, whether lines can change after the order is placed, and tools like generated columns, triggers, or views.

Cardinality and Participation

Saying "customers and orders are related" is not enough. The database must know whether an order can exist without a customer, and whether a customer can have several orders. Those answers decide where the foreign key goes, whether it is NOT NULL, and whether a UNIQUE constraint or a separate table is needed. Two independent properties describe each side of a relationship:

• Cardinality (maximum): at most how many instances of the other entity one instance can be linked to: one or many.
• Participation (minimum): whether every instance must take part. Total (mandatory, minimum 1) or partial (optional, minimum 0).

An analogy: in a marriage register in a monogamous system, each person is married to at most one person (cardinality 1), and not everyone is married (partial participation).

The three cardinality ratios

Ratio	Meaning	Example	Typical mapping
One-to-one (1:1)	Each A relates to at most one B and vice versa	User and user profile, country and capital city	FK on one side with UNIQUE
One-to-many (1:N)	One A relates to many B; each B to at most one A	Customer and orders, department and employees	FK on the "many" side
Many-to-many (M:N)	Each A relates to many B and vice versa	Students and courses, orders and products	A separate junction table with two FKs

Min-max pairs in crow's foot

 CUSTOMER ||--o{ ORDER
   ||  next to CUSTOMER: each order has exactly one customer   (min 1, max 1)
   o{  next to ORDER:    each customer has zero or more orders (min 0, max N)

 ORDER    ||--|{ ORDER_LINE
   |{  next to ORDER_LINE: each order has at least one line    (min 1, max N)

 EMPLOYEE |o--o| PARKING_SPOT
   o|  on both ends: an employee has zero or one spot, a spot has zero or one employee

The symbol nearest an entity describes how many of that entity are associated with one instance of the entity at the other end. Reading direction trips up beginners, so say each relationship out loud in both directions.

How the rules become constraints

Rule	PostgreSQL enforcement
Each order has exactly one customer (total, max 1)	orders.customer_id bigint NOT NULL REFERENCES customers
An order may have no assigned courier (partial)	orders.courier_id bigint REFERENCES couriers (nullable)
Each user has at most one profile (1:1)	profiles.user_id bigint NOT NULL UNIQUE REFERENCES users
Many-to-many	Junction table with composite primary key
Each order has at least one line (total on the "one" side)	Not expressible as a simple declarative constraint. Use a deferred constraint trigger (9.3) or enforce in the application
A team has at most 5 members	Not a plain constraint. Use a trigger, or a member_slot int CHECK (member_slot BETWEEN 1 AND 5) with UNIQUE (team_id, member_slot)

Example: 1:1, 1:N, and M:N in DDL

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

-- 1:1, partial on the users side (a user may lack a profile), total on the profile side
CREATE TABLE user_profiles (
  user_id  bigint PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,  -- PK = FK makes it 1:1
  bio      text,
  avatar   text
);

-- 1:N: each post has exactly one author, an author has zero or more posts
CREATE TABLE posts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES users (id),   -- NOT NULL = total participation of posts
  title      text   NOT NULL
);

-- M:N: posts and tags, through a junction table
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)          -- a tag is attached to a post at most once
);
CREATE INDEX ON post_tags (tag_id);      -- the PK index covers post_id lookups; this one covers tag_id

Key takeaways

• Each side of a relationship has a maximum (one or many) and a minimum (zero or one).
• 1:N puts a foreign key on the "many" side. 1:1 adds UNIQUE to that foreign key. M:N needs a junction table.
• Total participation on the FK side becomes NOT NULL. Partial participation leaves the FK nullable.
• Some rules ("at least one line per order", "at most five members") need triggers or application logic, not just declarative constraints.

🧪 Practice

1. (Beginner) State the cardinality and participation for each pair, then write it in crow's foot text: country and capital city; author and book; patient and hospital bed (at a moment in time).
2. (Intermediate) A company says "each department has exactly one manager, and an employee manages at most one department". Write DDL that enforces both rules, and explain which rule your design cannot enforce on its own (for example, that the manager works in that department).
3. (Interview) "How do you enforce that every order has at least one order line?" Hint: Consider why a CHECK or foreign key cannot express it, what a DEFERRABLE INITIALLY DEFERRED constraint trigger would check at commit, and whether the rule is worth enforcing in the database at all.

ER Diagrams to Relational Schemas

Once the conceptual model is agreed, it has to become tables. This step is mechanical: a small set of mapping rules handles almost every ER construct, so two careful designers will produce nearly the same schema from the same diagram. Learning the rules also makes the reverse skill easier, which is reading an unfamiliar schema and recovering the domain model behind it.

The mapping rules

ER construct	Relational mapping
Strong entity	A table. Simple attributes become columns. Choose a primary key
Composite attribute	One column per component (or a composite type)
Multivalued attribute	A new table: owner's key plus the value, both in the PK
Derived attribute	Usually omitted, computed in a view or query. Optionally a generated column
Weak entity	A table whose PK = owner's PK + partial key, with FK to the owner (next topic)
1:N relationship	FK on the "many" side referencing the "one" side. Relationship attributes go on the "many" side
1:1 relationship	FK with UNIQUE on the side with total participation (or merge both into one table)
M:N relationship	A junction table with FKs to both sides. PK is the pair (or a surrogate plus UNIQUE on the pair). Relationship attributes go here
Unary relationship	Self-referencing FK (1:N) or junction table referencing the same table twice (M:N)
n-ary relationship (n > 2)	A table with an FK to each participant. The PK is the combination of keys that is unique
Specialization (subtypes)	One table per hierarchy, one table per subtype, or a supertype table plus subtype tables sharing its PK

Example: mapping the online shop model

                     +-----------+
                     | CATEGORY  |
                     +-----------+
                          ||
                          o{  belongs to
 +----------+ places  +-----------+  contains (quantity, unit_price)  +-----------+
 | CUSTOMER |||-----o{|   ORDER   |}o----------------------------|{|  PRODUCT  |
 +----------+         +-----------+                                 +-----------+
      ||
      o{  saves
 +-----------+
 |  ADDRESS  |
 +-----------+

CREATE TABLE categories (                      -- strong entity
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  text NOT NULL UNIQUE
);

CREATE TABLE products (                        -- strong entity + 1:N "belongs to"
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku          text          NOT NULL UNIQUE,
  name         text          NOT NULL,
  list_price   numeric(10,2) NOT NULL CHECK (list_price >= 0),
  category_id  bigint        NOT NULL REFERENCES categories (id)   -- FK on the "many" side
);

CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  full_name  text NOT NULL
);

CREATE TABLE addresses (                       -- 1:N "saves"
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint  NOT NULL REFERENCES customers (id) ON DELETE CASCADE,
  street       text    NOT NULL,               -- composite attribute, flattened
  city         text    NOT NULL,
  postal_code  text    NOT NULL,
  country      char(2) NOT NULL
);

CREATE TABLE orders (                          -- 1:N "places"
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint      NOT NULL REFERENCES customers (id),
  placed_at    timestamptz NOT NULL DEFAULT now(),
  status       text        NOT NULL DEFAULT 'pending'
);

CREATE TABLE order_items (                     -- M:N "contains" + its attributes
  order_id    bigint        NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  product_id  bigint        NOT NULL REFERENCES products (id),
  quantity    int           NOT NULL CHECK (quantity > 0),     -- relationship attribute
  unit_price  numeric(10,2) NOT NULL CHECK (unit_price >= 0),  -- price at purchase time, not list_price
  PRIMARY KEY (order_id, product_id)
);
CREATE INDEX ON order_items (product_id);      -- "which orders contain product X?"

unit_price is stored even though products.list_price exists. They are different facts: the price charged then versus the price offered now. That is not redundancy, and normalization would not remove it.

Mapping specialization. Suppose PAYMENT has subtypes CARD_PAYMENT (card brand, last four digits) and BANK_TRANSFER (IBAN, reference):

Strategy	Shape	Pros	Cons
Single table	payments with all columns, a kind discriminator, many nullable columns	Simple queries, no joins	Weak constraints unless you add CHECKs per kind
Table per subtype	card_payments, bank_transfers, each with shared columns repeated	Strong constraints per subtype	"All payments" requires UNION ALL. Other tables cannot easily reference "a payment"
Supertype + subtypes	payments(id, amount, ...) plus card_payments(payment_id PK/FK, ...)	Strong constraints, single FK target	Extra joins, and enforcing "exactly one subtype row" needs extra care

-- Supertype + subtypes, with the discriminator included in the key so a
-- card_payments row can only point at a payment whose kind is 'card'
CREATE TABLE payments (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind    text NOT NULL CHECK (kind IN ('card', 'bank')),
  amount  numeric(12,2) NOT NULL,
  UNIQUE (id, kind)                            -- target for the composite FKs below
);

CREATE TABLE card_payments (
  payment_id  bigint PRIMARY KEY,
  kind        text NOT NULL DEFAULT 'card' CHECK (kind = 'card'),
  brand       text NOT NULL,
  last4       char(4) NOT NULL,
  FOREIGN KEY (payment_id, kind) REFERENCES payments (id, kind)
);

Key takeaways

• A small set of rules maps any ER diagram to tables. Apply them systematically.
• Relationship attributes go with the foreign key (1:N) or into the junction table (M:N).
• Store historical facts such as "price at time of purchase" separately from current values. They are different facts.
• Choose a specialization strategy based on how strong the constraints must be and how the subtypes are queried.

🧪 Practice

1. (Beginner) Map this model to DDL: DEPARTMENT ||--o{ EMPLOYEE : employs, EMPLOYEE }o--o{ PROJECT : works_on (hours_per_week).
2. (Intermediate) Map the ternary relationship "supplier supplies part to project, with quantity" to a table. Decide on the primary key, and explain what changes if the rule is "each project gets a given part from only one supplier".
3. (Interview) "How would you model users who can be either individuals or companies, where invoices reference 'a customer'?" Hint: Compare the three specialization strategies by how invoices reference the customer, which columns can be NOT NULL, and how common queries look.

Weak Entities and Identifying Relationships

Some things cannot be identified on their own. "Line 3" means nothing until you say of which order. "Room 101" is ambiguous until you name the building. A weak entity is an entity whose identity depends on another entity, its owner (or identifying entity). The relationship connecting them is the identifying relationship. The weak entity's own identifier, called the partial key (or discriminator), is unique only within one owner.

An analogy: apartment numbers. "Apartment 4B" is unique within a building but not within a city. The full address is building address plus apartment number.

Concept	Order lines example	Building rooms example
Owner (strong entity)	ORDER	BUILDING
Weak entity	ORDER_LINE	ROOM
Partial key	line_no	room_number
Full key	(order_id, line_no)	(building_id, room_number)

Properties of a weak entity:

• Existence dependency. It cannot exist without its owner. Its participation in the identifying relationship is always total.
• Identity dependency. Its primary key includes the owner's key.
• Deletion follows the owner. Deleting the owner normally deletes its weak entities (ON DELETE CASCADE).

Existence dependency alone does not make an entity weak. An ORDER cannot exist without a CUSTOMER, but orders have their own global identifier, so ORDER is a strong entity with a mandatory relationship.

Notation

 Chen notation:                       Crow's foot (identifying relationship drawn solid,
                                      non-identifying drawn dashed in many tools):
 +--------+    //======\\    ++=========++
 | ORDER  |----|| has  ||====|| ORDER_  ||       ORDER ||--|{ ORDER_LINE      (identifying)
 +--------+    \\======//    || LINE    ||       CUSTOMER ||..o{ ORDER       (non-identifying)
                             ++=========++
   double diamond = identifying relationship
   double rectangle = weak entity
   partial key underlined with a dashed line: line_no

Example: a weak entity in PostgreSQL

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

CREATE TABLE rooms (
  building_id  bigint NOT NULL REFERENCES buildings (id) ON DELETE CASCADE,  -- owner's key, part of the PK
  room_number  text   NOT NULL,                                              -- partial key
  capacity     int    NOT NULL CHECK (capacity > 0),
  PRIMARY KEY (building_id, room_number)        -- "101" may exist in many buildings, once per building
);

-- Anything that references a room must use the full composite key
CREATE TABLE bookings (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  building_id  bigint      NOT NULL,
  room_number  text        NOT NULL,
  starts_at    timestamptz NOT NULL,
  ends_at      timestamptz NOT NULL,
  FOREIGN KEY (building_id, room_number) REFERENCES rooms (building_id, room_number)
);

INSERT INTO buildings (name) VALUES ('North'), ('South');
INSERT INTO rooms VALUES (1, '101', 30), (2, '101', 12);   -- same room number, different buildings: allowed
INSERT INTO rooms VALUES (1, '101', 40);
-- ERROR:  duplicate key value violates unique constraint "rooms_pkey"

Surrogate keys for weak entities. Many teams give weak entities their own surrogate id and keep the identifying key as a UNIQUE constraint:

CREATE TABLE rooms (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- simple FK target for other tables
  building_id  bigint NOT NULL REFERENCES buildings (id) ON DELETE CASCADE,
  room_number  text   NOT NULL,
  UNIQUE (building_id, room_number)                              -- the real-world identity is still enforced
);

Approach	Advantages	Disadvantages
Composite natural PK	Models the domain exactly. Children carry building_id, so "bookings in building 1" needs no join	Wide FKs. Renumbering a room means cascading key updates
Surrogate PK + UNIQUE	Narrow, stable FKs. Friendlier to ORMs	Extra column and index. You must remember the UNIQUE constraint

Key takeaways

• A weak entity is identified by its owner's key plus a partial key that is unique only within one owner.
• It depends on its owner for both existence and identity, so its FK is part of the PK and usually ON DELETE CASCADE.
• A mandatory relationship alone does not make an entity weak. What matters is whether it has its own identifier.
• Implementing it with a surrogate key is fine as long as a UNIQUE (owner_id, partial_key) constraint keeps the real identity.

🧪 Practice

1. (Beginner) For each, decide whether it is a weak entity and name its owner and partial key: an invoice line, a hotel booking, a chapter of a book, a bank account's monthly statement.
2. (Intermediate) Model ORDER and ORDER_LINE with a composite key (order_id, line_no). Write a query or trigger idea that assigns the next line_no within an order, and explain what can go wrong when two sessions add lines to the same order at once.
3. (Interview) "Your ORM requires every table to have a single-column id. How do you model a weak entity without losing its semantics?" Hint: Think about which constraint carries the real-world identity once the surrogate key becomes the PK.

<a id="22-keys-and-integrity"></a>

2.2 Keys and Integrity

This subchapter covers how rows are identified and how PostgreSQL's declarative constraints (keys, foreign keys, NOT NULL, UNIQUE, CHECK, and exclusion constraints) keep data valid, along with what happens when referenced rows change and when constraints are checked.

Candidate, Primary, and Surrogate Keys

A relation is a set of tuples, and in a set every element is distinct, so there must be some way to tell rows apart. Without one, "update Ada's email" might update two rows, or none, and nothing could reliably point at a specific row. Keys are how the relational model guarantees identity.

Keys build on each other:

Term	Definition	Example in employees(id, tax_code, email, name, dept)
Superkey	Any set of columns whose values are unique for every row	{id}, {id, name}, {tax_code, dept}, {email}
Candidate key	A minimal superkey: remove any column and it stops being unique	{id}, {tax_code}, {email}
Primary key	The one candidate key chosen as the main identifier	{id}
Alternate key	Every other candidate key	{tax_code}, {email}, enforced with UNIQUE
Composite key	A key made of more than one column	{order_id, line_no} in order lines
Surrogate key	A key with no business meaning, generated by the system	id bigint GENERATED ALWAYS AS IDENTITY

Think of a person: their passport number, tax code, and fingerprints each identify them (candidate keys). A particular office decides to file people by tax code (the primary key). "Tax code plus favorite color" is also unique, but it is not minimal, so it is only a superkey.

What PRIMARY KEY does in PostgreSQL

• Enforces UNIQUE and NOT NULL on the column(s) together.
• Creates a unique B-tree index named <table>_pkey.
• Is the default target of REFERENCES other_table when no columns are listed.
• Allows only one per table. Alternate keys use UNIQUE constraints.
• Lets GROUP BY pk include other columns of the table in the SELECT list (they are functionally dependent on the PK; see Functional Dependencies).

A table without a primary key is legal in PostgreSQL, but it is almost always a mistake. Duplicate rows creep in, logical replication cannot replicate UPDATEs and DELETEs without a replica identity (12.2), and many tools assume a key exists.

Example: candidate keys, primary key, and alternate keys

CREATE TABLE employees (
  id        bigint GENERATED ALWAYS AS IDENTITY,   -- surrogate key
  tax_code  text NOT NULL,
  email     text NOT NULL,
  name      text NOT NULL,
  dept      text NOT NULL,
  CONSTRAINT employees_pkey          PRIMARY KEY (id),          -- the chosen candidate key
  CONSTRAINT employees_tax_code_key  UNIQUE (tax_code),         -- alternate key
  CONSTRAINT employees_email_key     UNIQUE (email)             -- alternate key
);

-- Composite primary key: neither column is unique alone, the pair is
CREATE TABLE enrollments (
  student_id  bigint NOT NULL,
  course_id   bigint NOT NULL,
  enrolled_on date   NOT NULL DEFAULT current_date,
  PRIMARY KEY (student_id, course_id)
);

-- Inspect keys and constraints through the catalog
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;
--          conname          | contype |      definition
-- --------------------------+---------+----------------------
--  employees_pkey           | p       | PRIMARY KEY (id)
--  employees_tax_code_key   | u       | UNIQUE (tax_code)
--  employees_email_key      | u       | UNIQUE (email)

Surrogate key generation options

Option	Example	Notes
Identity column	bigint GENERATED ALWAYS AS IDENTITY	SQL standard, recommended for most tables
serial / bigserial	id bigserial	Legacy shorthand for a sequence plus default. Prefer identity
UUID	id uuid DEFAULT uuidv7() (PostgreSQL 18), gen_random_uuid() (13+)	Globally unique, can be generated anywhere. Time-ordered v7 indexes much better than random v4 (3.1)

Use bigint rather than int for surrogate keys on tables that may grow. An int runs out at about 2.1 billion values, and changing a primary key's type on a large table is a painful migration.

Key takeaways

• A superkey is any unique column set. A candidate key is a minimal one. The primary key is the chosen candidate key.
• Every candidate key that is not the primary key should still be enforced with UNIQUE.
• PRIMARY KEY means UNIQUE + NOT NULL + an index. A table can have only one.
• Use bigint identity columns, or UUIDv7 when keys must be generated outside the database.

🧪 Practice

1. (Beginner) For flights(airline_code, flight_number, departure_date, aircraft_reg, gate), list two superkeys and identify the candidate key(s). Assume a flight number is reused every day and an aircraft flies several flights per day.
2. (Intermediate) Create the flights table with a surrogate primary key and the natural candidate key enforced as UNIQUE. Query pg_constraint and pg_indexes to list every constraint and index it created.
3. (Interview) "What problems does a table without a primary key cause in PostgreSQL?" Hint: Think about duplicate rows, deleting exactly one of two identical rows, foreign keys pointing at it, logical replication of updates, and ORMs.

Natural vs. Synthetic Keys

Once you know a table's candidate keys, you must pick one as the primary key. The choice is between a natural key (a value that already exists in the business domain, such as an ISO country code, an ISBN, or an email) and a synthetic (surrogate) key that the database generates. This is one of the longest-running debates in database design, because both sides have real advantages.

An analogy: a natural key is like identifying people by their name and birth date, which is meaningful but changes on marriage and has collisions. A synthetic key is like a library card number, which means nothing on its own but stays the same forever and is never shared.

Aspect	Natural key	Synthetic key
Meaning	Readable, meaningful in queries and logs	Opaque number or UUID
Stability	Can change (emails, names, product codes, even "permanent" government IDs after corrections)	Never changes
Uniqueness in reality	Often less unique than assumed (shared emails, reused codes, data-entry errors)	Guaranteed by the generator
Size	Can be wide (text, multi-column)	8 bytes (bigint) or 16 bytes (uuid)
FK propagation	Every child table carries the full natural key	Children carry one small column
Joins	Sometimes avoidable, since the child already holds the meaningful value	Needed to show meaningful values
Duplicate protection	Built in	None unless you also add UNIQUE on the natural key
Exposure	May leak business data in URLs	Sequential ids reveal volume and allow enumeration; UUIDs do not

Common practice

• Use a synthetic primary key for most entity tables (customers, orders, products), and a UNIQUE constraint on each natural candidate key. You get stable references and protection against duplicates.
• Use a natural primary key when the value is truly stable, standardized, and compact: ISO 4217 currency codes ('EUR'), ISO 3166 country codes ('IT'), language tags, or small lookup tables (Lookup and Reference Tables).
• Use a composite natural key for pure junction tables (PRIMARY KEY (post_id, tag_id)). A surrogate key there adds a column and an index for little benefit.

Example: a surrogate key without a natural UNIQUE invites duplicates

CREATE TABLE products_bad (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   text NOT NULL,                  -- natural key, but not declared UNIQUE
  name  text NOT NULL
);

INSERT INTO products_bad (sku, name) VALUES ('KB-001', 'Keyboard');
INSERT INTO products_bad (sku, name) VALUES ('KB-001', 'Keyboard');  -- accepted: now two "different" products
-- The surrogate key made the rows distinct, but the business entity is duplicated.

CREATE TABLE products_good (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- stable reference for FKs
  sku   text NOT NULL UNIQUE,                               -- natural identity still enforced
  name  text NOT NULL
);

Example: a natural key that works well

CREATE TABLE currencies (
  code       char(3) PRIMARY KEY CHECK (code ~ '^[A-Z]{3}$'),  -- ISO 4217: stable, compact, meaningful
  name       text NOT NULL,
  minor_unit smallint NOT NULL                                  -- e.g. 2 for EUR, 0 for JPY
);

CREATE TABLE prices (
  product_id bigint        NOT NULL REFERENCES products_good (id),
  currency   char(3)       NOT NULL REFERENCES currencies (code),  -- readable without a join
  amount     numeric(12,2) NOT NULL,
  PRIMARY KEY (product_id, currency)
);

SELECT * FROM prices WHERE currency = 'EUR';   -- self-explanatory, no join to currencies needed

Example: a natural key that changes

-- Email as primary key: every referencing row must change when a user changes their email
CREATE TABLE accounts (email text PRIMARY KEY);
CREATE TABLE logins (
  email     text REFERENCES accounts (email) ON UPDATE CASCADE,   -- rewrites every login row
  logged_at timestamptz NOT NULL
);
-- With millions of logins, one email change becomes a large, lock-holding update.

Key takeaways

• Natural keys are meaningful but can change and are often less unique than they look.
• Synthetic keys are stable and compact but prevent no duplicates on their own.
• The usual approach: a synthetic primary key plus UNIQUE on every natural candidate key.
• Natural primary keys suit stable, standardized codes and junction tables.

🧪 Practice

1. (Beginner) For each, choose a natural or synthetic primary key and justify it: countries, customers, ISBN-identified books, blog post slugs, airport codes.
2. (Intermediate) A users table uses username as its primary key and eight other tables reference it. Product now wants users to be able to rename themselves. Write a migration plan to a bigint identity key with minimal downtime, keeping username unique.
3. (Interview) "Should public URLs expose your auto-increment ids?" Hint: Think about what sequential ids reveal (volume, growth rate), enumeration attacks, and alternatives such as UUIDs or a separate public identifier column.

Foreign Keys and Referential Integrity

A foreign key records that a row refers to another row. Without enforcement, references break: an order points at customer 42 after customer 42 is deleted, or a typo creates an order for customer 4200 who never existed. Such orphan rows quietly break joins and reports. Referential integrity is the rule that every non-null foreign key value must match an existing key in the referenced table, and a FOREIGN KEY constraint makes PostgreSQL enforce it on every insert, update, and delete.

Think of a library card catalog: every card must point at a book that is actually on a shelf. The library forbids adding a card for a book it does not own, and forbids discarding a book while cards still point to it, unless the cards are removed too.

Terminology

   referenced (parent) table              referencing (child) table
  +-------------------------+            +-----------------------------------+
  | customers               |            | orders                            |
  |-------------------------|            |-----------------------------------|
  | id  (PK) <------------- | ---------- | customer_id (FK) REFERENCES       |
  | email                   |            |   customers (id)                  |
  +-------------------------+            +-----------------------------------+

  Checked when:  a child row is inserted or its FK changes  -> parent must exist
                 a parent row is deleted or its key changes -> no child may still point at it
                                                               (or a cascade action runs)

Rules PostgreSQL applies

• The referenced columns must have a PRIMARY KEY or non-deferrable UNIQUE constraint.
• Data types must be compatible. Use exactly the same type: bigint to bigint.
• A NULL in the FK column means "no reference" and is not checked.
• For multi-column FKs, the default MATCH SIMPLE skips the check if any column is null. MATCH FULL requires all columns to be null or none of them.
• PostgreSQL does not automatically index the referencing columns. Without an index, every parent delete or key update scans the whole child table, and joins from parent to child are slow.

Example: declaring and violating foreign keys

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

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL,
  placed_at    timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT orders_customer_fk
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);   -- always index FK columns

INSERT INTO customers (email) VALUES ('ada@example.com');      -- gets id 1
INSERT INTO orders (customer_id) VALUES (1);                    -- OK

INSERT INTO orders (customer_id) VALUES (42);
-- ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_fk"
-- DETAIL:  Key (customer_id)=(42) is not present in table "customers".

DELETE FROM customers WHERE id = 1;
-- ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_fk" on table "orders"
-- DETAIL:  Key (id)=(1) is still referenced from table "orders".

Example: self-referencing and composite foreign keys

-- Hierarchy: each category may have a parent category
CREATE TABLE categories (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  parent_id  bigint REFERENCES categories (id),   -- NULL for root categories
  name       text NOT NULL
);

-- Composite FK to a weak entity (see 2.1)
CREATE TABLE seat_reservations (
  flight_id  bigint NOT NULL,
  seat_no    text   NOT NULL,
  passenger  text   NOT NULL,
  FOREIGN KEY (flight_id, seat_no) REFERENCES seats (flight_id, seat_no) MATCH FULL
);

Example: adding a foreign key to a large live table

ALTER TABLE ... ADD FOREIGN KEY normally scans the whole child table to validate existing rows while holding locks that block writes. Split it into two steps:

-- Step 1: fast. New and changed rows are checked from now on; existing rows are not scanned.
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
  REFERENCES customers (id) NOT VALID;

-- Step 2: scans existing rows with a weaker lock, so normal reads and writes continue
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

Example: finding foreign keys without a supporting index

SELECT c.conrelid::regclass AS child_table,
       c.conname            AS fk_name,
       pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = c.conrelid
      -- the index's leading columns must be exactly the FK columns (in any order)
      AND (i.indkey::int2[])[0:cardinality(c.conkey) - 1] @> c.conkey
  );

Key takeaways

• A foreign key guarantees every non-null reference points at an existing parent row.
• The referenced columns need a primary key or unique constraint. The referencing columns need an index you create yourself.
• NULL FK values are not checked. Use NOT NULL when the relationship is mandatory, and MATCH FULL for composite keys if partial nulls make no sense.
• On large tables, add FKs as NOT VALID and then VALIDATE CONSTRAINT to avoid long write-blocking locks.

🧪 Practice

1. (Beginner) Create authors and books(author_id) with a foreign key. Insert a book for a nonexistent author and delete an author who has books. Read both error messages and explain each.
2. (Intermediate) Load 1 million rows into orders without an index on customer_id. Time DELETE FROM customers WHERE id = <a customer with no orders>, then add the index and time it again. Explain the difference.
3. (Interview) "Many high-traffic companies drop foreign keys and enforce integrity in the application. Is that a good idea?" Hint: Weigh the cost of FK checks and the locks they take against race conditions in application-level checks, data imported by other tools, and the cost of cleaning up orphans later.

Unique and Not Null Constraints

The two simplest constraints prevent two of the most common data problems: duplicates ("we have three customer records for the same email") and missing values ("this order has no customer"). Declaring them in the schema means every application, script, and manual fix is held to the same rules.

NOT NULL says a column must always have a value. A useful default: make every column NOT NULL unless you can name a real situation where the value is legitimately unknown or not applicable. Nullable columns force every query to deal with three-valued logic (4.1).

UNIQUE says no two rows may have the same value in the column, or the same combination across several columns. PostgreSQL enforces it with a unique B-tree index.

The NULL subtlety. NULL means "unknown", and two unknowns are not considered equal. So by default a UNIQUE column accepts any number of NULLs. Since PostgreSQL 15 you can change that with NULLS NOT DISTINCT.

Declaration	Rows (NULL), (NULL)	Rows ('a'), ('a')
UNIQUE (col)	Allowed	Rejected
UNIQUE NULLS NOT DISTINCT (col)	Rejected	Rejected
PRIMARY KEY (col)	Rejected (NOT NULL)	Rejected

Example: basic declarations and errors

CREATE TABLE users (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     text NOT NULL UNIQUE,                   -- required, no duplicates
  username  text UNIQUE,                            -- optional, but unique when present
  phone     text,
  CONSTRAINT users_phone_key UNIQUE NULLS NOT DISTINCT (phone)   -- at most one user without a phone (PG 15+)
);

INSERT INTO users (email) VALUES (NULL);
-- ERROR:  null value in column "email" of relation "users" violates not-null constraint

INSERT INTO users (email, username, phone) VALUES ('a@x.io', NULL, '555-0101'),
                                                  ('b@x.io', NULL, '555-0102');  -- OK: two NULL usernames are distinct

INSERT INTO users (email, phone) VALUES ('c@x.io', NULL);   -- OK: the first user without a phone
INSERT INTO users (email, phone) VALUES ('d@x.io', NULL);
-- ERROR:  duplicate key value violates unique constraint "users_phone_key"
-- DETAIL:  Key (phone)=(null) already exists.             -- NULLS NOT DISTINCT treats the NULLs as equal

Beyond plain constraints: unique indexes

A UNIQUE constraint covers exact column values only. A unique index can also be partial (only some rows) or on an expression:

-- Case-insensitive uniqueness: 'Ada@X.io' and 'ada@x.io' count as the same email
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));

-- Partial: only one ACTIVE subscription per user; cancelled ones may pile up
CREATE TABLE subscriptions (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id  bigint NOT NULL REFERENCES users (id),
  status   text   NOT NULL CHECK (status IN ('active', 'cancelled'))
);
CREATE UNIQUE INDEX one_active_subscription
  ON subscriptions (user_id)
  WHERE status = 'active';

-- Multi-column uniqueness: a SKU is unique within a store, not globally
CREATE TABLE store_products (
  store_id  bigint NOT NULL,
  sku       text   NOT NULL,
  UNIQUE (store_id, sku)
);

Feature	UNIQUE constraint	Unique index
Appears in pg_constraint	Yes	No (only in pg_index)
Can be a foreign key target	Yes	Yes, if it is non-partial and on plain columns
Partial (WHERE ...)	No	Yes
Expressions (lower(email))	No	Yes
DEFERRABLE	Yes	No
ON CONFLICT arbiter	Yes	Yes (with matching WHERE for partial indexes)

Adding NOT NULL to a large existing table

ALTER TABLE t ALTER COLUMN c SET NOT NULL scans the whole table under an exclusive lock. Two ways to keep the lock short:

-- Works on all supported versions: a NOT VALID check constraint proves it first
ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;       -- scans without blocking writes
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;           -- skips the scan: the CHECK already proves it
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;

-- PostgreSQL 18+: NOT NULL itself can be added as NOT VALID and validated later
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null NOT NULL status NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

Key takeaways

• Make columns NOT NULL by default. Allow NULL only for values that can legitimately be unknown or not applicable.
• UNIQUE allows many NULLs unless you declare NULLS NOT DISTINCT (PostgreSQL 15+).
• Unique indexes add partial and expression-based uniqueness, such as case-insensitive emails or "one active row per user".
• On large tables, add NOT NULL via a validated CHECK, or with NOT VALID on PostgreSQL 18+, to avoid long locks.

🧪 Practice

1. (Beginner) Create a table with UNIQUE (code). Insert two rows with code = NULL, then recreate it with NULLS NOT DISTINCT and repeat. Explain the difference.
2. (Intermediate) Enforce "a user can have at most one default shipping address, but any number of non-default addresses" using a partial unique index. Write the inserts that prove it works.
3. (Interview) "Two requests try to register the same email at the same moment. Your code first runs SELECT ... WHERE email = $1 and inserts only if nothing is found. What goes wrong and how do you fix it?" Hint: Think about the gap between the check and the insert, and what a unique constraint plus INSERT ... ON CONFLICT changes.

Check Constraints

Types alone cannot express most business rules. A numeric column happily stores a negative price, and a date column accepts an end date before the start date. A CHECK constraint attaches a Boolean expression to a table, and PostgreSQL rejects any row for which it evaluates to false. It is the database's version of form validation, except it cannot be bypassed by a script, a forgotten API endpoint, or a manual UPDATE.

Column vs. table constraints

• A column constraint is written next to one column and conventionally refers only to it: price numeric CHECK (price >= 0).
• A table constraint is written separately and can compare several columns of the same row: CHECK (ends_at > starts_at).

Rules and limitations

Rule	Consequence
A row passes unless the expression is false	NULL (unknown) passes. CHECK (price > 0) accepts a NULL price; combine with NOT NULL
Only the current row is visible	No subqueries, no looking at other rows or tables. Use foreign keys, exclusion constraints, or triggers instead
Expressions should be immutable	Do not use now() or functions that read other tables. PostgreSQL only checks on insert and update, so time-dependent results go stale
Checked on INSERT and UPDATE	Existing rows are validated when the constraint is added, unless NOT VALID is used
Constraints are named	Name them yourself (orders_dates_chk) so errors and migrations are readable

Example: common patterns

CREATE TABLE events (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title        text        NOT NULL CHECK (length(trim(title)) > 0),         -- no blank titles
  capacity     int         NOT NULL CHECK (capacity BETWEEN 1 AND 10000),
  status       text        NOT NULL DEFAULT 'draft'
                           CHECK (status IN ('draft', 'published', 'cancelled')),
  starts_at    timestamptz NOT NULL,
  ends_at      timestamptz NOT NULL,
  cancelled_at timestamptz,
  cancel_reason text,
  country      char(2)     NOT NULL CHECK (country ~ '^[A-Z]{2}$'),           -- regex on format

  CONSTRAINT events_time_order_chk CHECK (ends_at > starts_at),              -- multi-column rule

  -- Conditional rule: cancellation details exist exactly when the status is 'cancelled'
  CONSTRAINT events_cancel_chk CHECK (
    (status = 'cancelled') = (cancelled_at IS NOT NULL AND cancel_reason IS NOT NULL)
  )
);

INSERT INTO events (title, capacity, starts_at, ends_at, country)
VALUES ('Meetup', 50, '2026-11-01 18:00Z', '2026-11-01 17:00Z', 'IT');
-- ERROR:  new row for relation "events" violates check constraint "events_time_order_chk"

Example: exactly one of several columns

num_nonnulls() counts non-null arguments, which makes "exactly one of" rules short:

CREATE TABLE attachments (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  post_id      bigint REFERENCES posts (id),
  comment_id   bigint REFERENCES comments (id),
  message_id   bigint REFERENCES messages (id),
  CONSTRAINT attachments_one_parent_chk CHECK (num_nonnulls(post_id, comment_id, message_id) = 1)
);

Example: adding a constraint to existing data

-- Fails immediately if any existing row violates it
ALTER TABLE products ADD CONSTRAINT products_price_chk CHECK (price >= 0);
-- ERROR:  check constraint "products_price_chk" of relation "products" is violated by some row

-- Enforce for new data now, fix old data, then validate
ALTER TABLE products ADD CONSTRAINT products_price_chk CHECK (price >= 0) NOT VALID;
UPDATE products SET price = 0 WHERE price < 0;                 -- or investigate each row
ALTER TABLE products VALIDATE CONSTRAINT products_price_chk;

CHECK vs. other tools

Rule	Best tool
Value range, format, allowed set, relation between columns of one row	CHECK
Same rule reused across many tables (e.g. email format)	A domain with a CHECK (3.3)
Allowed values that change at runtime or carry extra data	Lookup table + FK (Lookup and Reference Tables)
Rule involving other rows (no overlaps, at most N)	Exclusion constraint, unique index, or trigger
Rule involving other tables	Foreign key or trigger

Key takeaways

• CHECK enforces any single-row Boolean rule. Use it for ranges, formats, allowed values, and cross-column rules.
• NULL makes a check pass. Pair CHECK with NOT NULL when the value is required.
• Checks cannot look at other rows or tables, and should not depend on the current time.
• Name your constraints, and use NOT VALID + VALIDATE CONSTRAINT when adding them to existing data.

🧪 Practice

1. (Beginner) Add CHECK constraints to a products table so that price is positive, discount_pct is between 0 and 90, and sku matches the pattern three uppercase letters, a dash, and four digits.
2. (Intermediate) For a shipments table with status, shipped_at, and delivered_at, write one or more checks enforcing: shipped_at is set once the status is shipped or delivered; delivered_at is set only when the status is delivered; delivery never precedes shipping.
3. (Interview) "Why doesn't CHECK (expires_at > now()) work as intended?" Hint: Think about when check constraints are evaluated, what happens to a row that was valid yesterday, and what a dump and restore does with it.

Exclusion Constraints

Some rules involve pairs of rows in a way that UNIQUE cannot express. A meeting room must not be booked twice for overlapping times. Booking 10:00 to 11:00 and 10:30 to 12:00 are different values, so a unique constraint accepts both, yet they clearly conflict. Checking in application code has a race: two sessions can each see "no overlap" and both insert. An exclusion constraint solves this inside the database.

An exclusion constraint generalizes UNIQUE. UNIQUE (a) means "no two rows where a = a". EXCLUDE lets you choose the operator for each column: "no two rows where room = room and during && during" (&& means "overlaps").

 room 1  |----- A -----|                      A: [10:00, 11:00)
 room 1          |------- B -------|          B: [10:30, 12:00)  -> rejected: same room AND overlaps A
 room 2          |------- C -------|          C: [10:30, 12:00)  -> allowed: different room
 room 1                |---- D ----|          D: [11:00, 12:00)  -> allowed: [ ) ranges touch but do not overlap

How it works. The constraint needs an index that supports all the chosen operators, usually GiST. Overlap (&&) on range types is a GiST operator, but plain equality on bigint is not, so the btree_gist extension is needed to combine them.

Example: non-overlapping room bookings

CREATE EXTENSION IF NOT EXISTS btree_gist;     -- lets GiST handle "=" on scalar types like bigint

CREATE TABLE room_bookings (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  bigint    NOT NULL REFERENCES rooms (id),
  during   tstzrange NOT NULL,                  -- time range, half-open [start, end) by default
  CONSTRAINT room_bookings_no_overlap
    EXCLUDE USING gist (
      room_id WITH =,                           -- same room...
      during  WITH &&                           -- ...and overlapping time
    )
);

INSERT INTO room_bookings (room_id, during)
VALUES (1, tstzrange('2026-10-05 10:00+00', '2026-10-05 11:00+00'));   -- OK

INSERT INTO room_bookings (room_id, during)
VALUES (1, tstzrange('2026-10-05 10:30+00', '2026-10-05 12:00+00'));
-- ERROR:  conflicting key value violates exclusion constraint "room_bookings_no_overlap"
-- DETAIL:  Key (room_id, during)=(1, ["2026-10-05 10:30:00+00","2026-10-05 12:00:00+00"))
--          conflicts with existing key (room_id, during)=(1, ["2026-10-05 10:00:00+00","2026-10-05 11:00:00+00")).

INSERT INTO room_bookings (room_id, during)
VALUES (1, tstzrange('2026-10-05 11:00+00', '2026-10-05 12:00+00'));   -- OK: touches, does not overlap

Partial exclusion. Like a partial index, an exclusion constraint can apply to only some rows, for example ignoring cancelled bookings:

ALTER TABLE room_bookings ADD COLUMN cancelled boolean NOT NULL DEFAULT false;
ALTER TABLE room_bookings DROP CONSTRAINT room_bookings_no_overlap;
ALTER TABLE room_bookings ADD CONSTRAINT room_bookings_no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
  WHERE (NOT cancelled);                        -- cancelled bookings never block new ones

Temporal keys in PostgreSQL 18. PostgreSQL 18 added SQL-standard syntax for the most common exclusion pattern. WITHOUT OVERLAPS in a primary key or unique constraint builds the same GiST-backed exclusion, and PERIOD in a foreign key checks that the referenced row is valid for the whole referencing period (Temporal and Slowly Changing Data):

CREATE TABLE room_prices (
  room_id  bigint    NOT NULL,
  valid    daterange NOT NULL,
  price    numeric(10,2) NOT NULL,
  PRIMARY KEY (room_id, valid WITHOUT OVERLAPS)   -- one price per room at any date (PG 18+, needs btree_gist)
);

Other uses

Rule	Exclusion constraint
One price per product at any point in time	EXCLUDE USING gist (product_id WITH =, valid_period WITH &&)
An employee is in at most one shift at a time	EXCLUDE USING gist (employee_id WITH =, shift WITH &&)
Delivery zones on a map must not overlap	EXCLUDE USING gist (zone WITH &&) on a geometric type
Equivalent to UNIQUE (a)	EXCLUDE USING btree (a WITH =) (use UNIQUE instead; it is faster and can be an FK target)

Key takeaways

• Exclusion constraints guarantee that no two rows satisfy a combination of operator comparisons, typically "same key and overlapping range".
• They need a GiST (or other suitable) index. btree_gist adds support for = on ordinary scalar types.
• They avoid the race condition in application-level "check then insert" logic.
• PostgreSQL 18's WITHOUT OVERLAPS and PERIOD give standard syntax for temporal primary, unique, and foreign keys.

🧪 Practice

1. (Beginner) Create the room_bookings table above and insert bookings that test overlapping, touching, and different-room cases. Then change one booking's range bounds to '[]' (inclusive) and observe what happens to touching bookings.
2. (Intermediate) Build a product_prices(product_id, valid daterange, price) table that rejects overlapping price periods for the same product. Write a query that returns the price valid on a given date.
3. (Interview) "How would you prevent double-booking of hotel rooms under high concurrency?" Hint: Compare application checks with locks (SELECT ... FOR UPDATE), SERIALIZABLE isolation, and an exclusion constraint, and think about which one is correct even when two transactions run at exactly the same time.

Cascading Actions on Delete and Update

A foreign key says what must be true. It must also say what happens when that truth is threatened: someone deletes a customer who has orders, or changes a referenced key. Referential actions (ON DELETE and ON UPDATE) decide whether PostgreSQL refuses the change or adjusts the child rows automatically. Choosing them is a business decision. "Delete the customer's addresses" is reasonable. "Delete the customer's invoices" may be illegal.

Action	Effect on child rows when the parent is deleted (or its key updated)	Typical use
NO ACTION (default)	Error if children still reference it. Checked at the end of the statement, and can be deferred	General default
RESTRICT	Error, checked immediately, even inside a deferred transaction	Explicitly forbidding deletes of referenced rows
CASCADE	Delete the children too (or update their FK values to the new key)	Weak entities and owned data: order lines, a user's sessions
SET NULL	Set the FK column(s) to NULL	Optional relationships: an employee's manager leaves
SET DEFAULT	Set the FK column(s) to their default value	Reassigning to a placeholder ("unassigned" queue)

NO ACTION vs. RESTRICT. Both refuse the change. NO ACTION checks at the end of the statement (or at commit if the constraint is deferred), so if the same statement or deferred transaction makes the reference valid again, for example by re-creating a parent with the same key, the change succeeds. RESTRICT checks immediately and can never be deferred. For simple deletes they behave the same.

Example: different actions for different relationships

CREATE TABLE customers (
  id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE addresses (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id) ON DELETE CASCADE   -- owned: goes with the customer
);

CREATE TABLE orders (
  id                   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id          bigint NOT NULL REFERENCES customers (id) ON DELETE RESTRICT,  -- legal records: never silently delete
  shipping_address_id  bigint REFERENCES addresses (id) ON DELETE SET NULL           -- keep the order, lose the link
);

CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,   -- weak entity of orders
  product_id  bigint NOT NULL,
  quantity    int    NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

 DELETE FROM customers WHERE id = 7;

   customers(7)
     |-- addresses (customer_id = 7)      ON DELETE CASCADE  -> deleted
     |      `-- orders.shipping_address_id ON DELETE SET NULL -> set to NULL
     `-- orders (customer_id = 7)         ON DELETE RESTRICT -> ERROR if any exist, whole statement rolls back

Example: SET NULL on only some columns (PostgreSQL 15+)

With a composite FK in a multi-tenant table, setting all FK columns to null would also null the tenant_id. A column list limits the action:

CREATE TABLE tasks (
  tenant_id    bigint NOT NULL,
  id           bigint NOT NULL,
  assignee_id  bigint,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, assignee_id) REFERENCES users (tenant_id, id)
    ON DELETE SET NULL (assignee_id)       -- clear only the assignee; tenant_id stays
);

ON UPDATE. With surrogate keys, parent keys never change, so ON UPDATE rarely matters. With natural keys, ON UPDATE CASCADE rewrites every child row, which can be a large, lock-heavy operation.

Dangers of CASCADE

• Large hidden deletes. One DELETE can remove millions of rows across many tables, all inside one transaction.
• Missing indexes. Each cascade step searches the child table by FK. Without an index on the FK column, each step is a full table scan.
• Surprise data loss. A chain like tenants -> projects -> tasks -> comments means deleting a tenant deletes everything. Sometimes that is exactly what you want, but it should be intentional.
• Triggers still fire. Cascaded deletes fire row-level DELETE triggers on the child tables, so audit triggers (Soft Deletes and Audit Columns) still record them.

Key takeaways

• ON DELETE and ON UPDATE decide what happens to children when a referenced parent changes.
• Use CASCADE for owned or weak data, RESTRICT or NO ACTION for records that must not disappear silently, and SET NULL for optional links.
• NO ACTION checks at the end of the statement (deferrable). RESTRICT checks immediately.
• Index every FK column involved in a cascade, and be deliberate about long cascade chains.

🧪 Practice

1. (Beginner) Using the schema above, insert a customer with two addresses and one order. Predict, then verify, what happens when you delete the customer, and what happens after you first delete the order.
2. (Intermediate) Design the referential actions for a blogging system: users, posts, comments (by any user on any post), and post_likes. Decide what happens to each when a user is deleted, and justify each choice in one sentence.
3. (Interview) "A developer ran DELETE FROM accounts WHERE id = 5 in production, and it took 40 minutes and blocked other traffic. What likely happened?" Hint: Follow the cascade chain and think about unindexed FK columns, the number of rows involved, and the locks held for the whole transaction.

Deferrable Constraints

Some valid changes pass through an invalid intermediate state. Two employees swap desk numbers: after the first update, both temporarily have the same desk, which violates UNIQUE. Two tables reference each other: whichever row you insert first points at a row that does not exist yet. The final state is fine, but a constraint checked after every row rejects the change. Deferrable constraints move the check to the end of the transaction, at COMMIT.

The analogy is double-entry bookkeeping. Within one transaction the debit and the credit are written separately, and the books are only required to balance when the transaction is closed.

Timing options

Declaration	When it is checked	Can a transaction change it?
NOT DEFERRABLE (default)	Immediately (per row for UNIQUE/PRIMARY KEY, end of statement for FKs)	No
DEFERRABLE INITIALLY IMMEDIATE	End of each statement by default	Yes, with SET CONSTRAINTS ... DEFERRED
DEFERRABLE INITIALLY DEFERRED	At COMMIT by default	Yes, with SET CONSTRAINTS ... IMMEDIATE

Only UNIQUE, PRIMARY KEY, EXCLUDE, and FOREIGN KEY constraints can be deferrable. NOT NULL and CHECK are always checked immediately.

A PostgreSQL detail about UNIQUE. A non-deferrable unique constraint is checked row by row, not at the end of the statement as the SQL standard specifies. So an update that shifts values can fail partway, depending on the physical order in which rows are visited:

CREATE TABLE playlist_items (
  playlist_id  bigint NOT NULL,
  position     int    NOT NULL,
  track        text   NOT NULL,
  CONSTRAINT playlist_items_pos_key UNIQUE (playlist_id, position)
);
INSERT INTO playlist_items VALUES (1, 1, 'Intro'), (1, 2, 'Song A'), (1, 3, 'Song B');

-- Insert a new track at position 1 by shifting everything down
UPDATE playlist_items SET position = position + 1 WHERE playlist_id = 1;
-- ERROR:  duplicate key value violates unique constraint "playlist_items_pos_key"
-- DETAIL:  Key (playlist_id, "position")=(1, 2) already exists.
-- (row 1 moved to 2 while the old row 2 was still there)

Making the constraint deferrable fixes it, because uniqueness is checked when the statement ends:

ALTER TABLE playlist_items DROP CONSTRAINT playlist_items_pos_key;
ALTER TABLE playlist_items ADD CONSTRAINT playlist_items_pos_key
  UNIQUE (playlist_id, position) DEFERRABLE INITIALLY IMMEDIATE;

UPDATE playlist_items SET position = position + 1 WHERE playlist_id = 1;   -- OK now
INSERT INTO playlist_items VALUES (1, 1, 'New opener');

Example: circular foreign keys

Each department has a manager (an employee), and each employee belongs to a department. Neither row can be inserted first unless one FK is deferred:

CREATE TABLE departments (
  id          bigint PRIMARY KEY,
  name        text   NOT NULL,
  manager_id  bigint NOT NULL
);

CREATE TABLE employees (
  id             bigint PRIMARY KEY,
  name           text   NOT NULL,
  department_id  bigint NOT NULL REFERENCES departments (id)
);

ALTER TABLE departments
  ADD CONSTRAINT departments_manager_fk FOREIGN KEY (manager_id)
  REFERENCES employees (id) DEFERRABLE INITIALLY DEFERRED;     -- checked at COMMIT

BEGIN;
INSERT INTO departments VALUES (10, 'Research', 100);   -- employee 100 does not exist yet: allowed for now
INSERT INTO employees   VALUES (100, 'Grace', 10);
COMMIT;                                                  -- check runs here and succeeds

BEGIN;
INSERT INTO departments VALUES (20, 'Sales', 999);       -- nobody inserts employee 999
COMMIT;
-- ERROR:  insert or update on table "departments" violates foreign key constraint "departments_manager_fk"
-- DETAIL:  Key (manager_id)=(999) is not present in table "employees".
-- The whole transaction is rolled back.

Changing timing inside a transaction

BEGIN;
SET CONSTRAINTS playlist_items_pos_key DEFERRED;   -- only affects deferrable constraints
-- ... several statements that pass through invalid states ...
SET CONSTRAINTS ALL IMMEDIATE;                      -- check everything pending now, fail early if needed
COMMIT;

Costs and limitations

• Errors appear at COMMIT, far from the statement that caused them, which makes debugging harder.
• Deferrable unique constraints are slower: possible duplicates are tracked and re-checked later.
• A deferrable UNIQUE or PRIMARY KEY cannot be the target of a foreign key, and cannot be used as an ON CONFLICT arbiter.
• Use deferral where a valid intermediate state is genuinely impossible. Do not use it to hide bugs in write order.

Key takeaways

• Deferrable constraints are checked at the end of the statement or at COMMIT, which allows temporary violations inside a transaction.
• Only UNIQUE, PRIMARY KEY, EXCLUDE, and foreign key constraints can be deferred.
• PostgreSQL checks non-deferrable unique constraints row by row, so "shift all positions" updates need a deferrable constraint.
• Circular foreign keys are the classic case for DEFERRABLE INITIALLY DEFERRED.

🧪 Practice

1. (Beginner) Reproduce the playlist error above, then fix it with a deferrable constraint. Confirm in \d playlist_items that the constraint is shown as deferrable.
2. (Intermediate) Write a transaction that swaps the desk numbers of two employees in a table with UNIQUE (desk_no) DEFERRABLE. Show that it fails without SET CONSTRAINTS ... DEFERRED when the constraint is INITIALLY IMMEDIATE and you use two separate UPDATE statements.
3. (Interview) "Why not make every constraint DEFERRABLE INITIALLY DEFERRED to avoid ordering problems?" Hint: Think about where errors are reported, performance, which features stop working with deferrable unique constraints, and what it says about the application's write logic.

<a id="23-normalization"></a>

2.3 Normalization

This subchapter covers the theory used to judge whether a table design is sound: functional dependencies, the normal forms from first through fifth, the anomalies that badly normalized tables cause, and when it is reasonable to denormalize on purpose.

Functional Dependencies

When you look at a table and feel that "this column belongs somewhere else", you are noticing a functional dependency (FD). Functional dependencies are the formal tool behind normalization. They let you state exactly why a design causes redundancy, and they show how to split it.

Definition. In a relation R, a set of attributes X functionally determines a set of attributes Y, written X -> Y, if any two rows that agree on X must also agree on Y. X is the determinant, Y is the dependent.

An analogy: a country's dialling code determines the country. Every time you see +39, the country is Italy. The reverse does not hold for every pair (+1 covers the USA, Canada, and others), so dialling_code -> country might hold in one direction only, depending on the data you model.

Examples in an order-line table order_items(order_id, product_id, quantity, product_name, customer_id, customer_city):

FD	Meaning
{order_id, product_id} -> quantity	One quantity per product per order
product_id -> product_name	A product has one name
order_id -> customer_id	An order belongs to one customer
customer_id -> customer_city	A customer lives in one city
{order_id, product_id} -> order_id	Trivial: the right side is part of the left side

FDs come from business rules, not from data. A sample where every zip happens to have one city does not prove zip -> city. Data can only refute an FD. You confirm FDs by asking domain experts, and then you check that existing data does not contradict them:

-- Does zip -> city hold in the current data? Any row returned is a counterexample.
SELECT zip, array_agg(DISTINCT city) AS cities
FROM addresses
GROUP BY zip
HAVING count(DISTINCT city) > 1;

Armstrong's axioms. Given some FDs, these rules derive every other FD that must also hold:

Rule	Statement	Example
Reflexivity	If Y is a subset of X, then X -> Y	{order_id, product_id} -> product_id
Augmentation	If X -> Y, then XZ -> YZ	product_id -> name gives {product_id, order_id} -> {name, order_id}
Transitivity	If X -> Y and Y -> Z, then X -> Z	order_id -> customer_id -> customer_city gives order_id -> customer_city
Union (derived)	If X -> Y and X -> Z, then X -> YZ
Decomposition (derived)	If X -> YZ, then X -> Y and X -> Z

Attribute closure and finding keys. The closure X+ is the set of all attributes that X determines. If X+ contains every attribute of the relation, X is a superkey. If no proper subset of X has that property, X is a candidate key.

 Relation R(A, B, C, D, E)
 FDs:     A -> B,   B -> C,   C D -> E

 Compute {A, D}+ :
   start            {A, D}
   A -> B           {A, B, D}
   B -> C           {A, B, C, D}
   C D -> E         {A, B, C, D, E}     <- every attribute: {A, D} is a superkey

 Is it minimal?
   {A}+ = {A, B, C}         not all attributes
   {D}+ = {D}               not all attributes
   => {A, D} is a candidate key.

 Shortcut: A and D never appear on the right side of any FD,
           so every key must contain both of them.

Prime vs. non-prime attributes. An attribute that belongs to some candidate key is prime. All others are non-prime. The normal forms in the next topics are defined in terms of which FDs non-prime attributes may have.

FDs in PostgreSQL. PostgreSQL knows one FD automatically: the primary key determines every column. That is why this is legal:

SELECT c.id, c.email, c.full_name, count(o.id) AS orders   -- email and full_name are not grouped...
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;                                              -- ...but c.id (the PK) determines them

Every other FD is either enforced by your design (each determinant becomes a key of some table) or not enforced at all.

Key takeaways

• X -> Y means rows that agree on X must agree on Y. FDs come from business rules, and data can only refute them.
• Armstrong's axioms (reflexivity, augmentation, transitivity) derive all implied FDs.
• The closure X+ tests whether X is a superkey. Attributes that never appear on a right-hand side must be in every key.
• Normalization aims for every non-trivial FD to have a key as its determinant, so a key constraint enforces it.

🧪 Practice

1. (Beginner) For employees(emp_id, name, dept_id, dept_name, manager_id), list the FDs you would expect from common business rules, and identify one trivial FD.
2. (Intermediate) For R(A, B, C, D, E, F) with A -> BC, C -> D, BE -> F, compute {A}+ and {A, E}+, and find all candidate keys.
3. (Interview) "How would you discover the functional dependencies in a legacy table that has no documentation?" Hint: Combine GROUP BY ... HAVING count(DISTINCT ...) > 1 queries with domain interviews, and explain why the queries alone cannot prove an FD holds.

First, Second, and Third Normal Form

Normal forms are a ladder of increasingly strict rules. Each step removes one kind of redundancy. The first three, defined by Edgar Codd in the early 1970s, remove the most common problems, and "third normal form" is the usual target for operational (OLTP) schemas. A memorable summary: every non-key attribute must depend on the key (1NF), the whole key (2NF), and nothing but the key (3NF).

Starting point: a spreadsheet-style table

 order_report
 +----------+------------+---------+-----------+-------+------------+---------------+-------+-----+------------------------+
 | order_id | order_date | cust_id | cust_name | city  | product_id | product_name  | price | qty | cust_phones            |
 +----------+------------+---------+-----------+-------+------------+---------------+-------+-----+------------------------+
 | 1001     | 2026-09-01 | C1      | Ada       | Turin | P1         | Keyboard      | 89.90 | 1   | 555-0101, 555-0199     |
 | 1001     | 2026-09-01 | C1      | Ada       | Turin | P2         | Mouse         | 24.50 | 2   | 555-0101, 555-0199     |
 | 1002     | 2026-09-03 | C2      | Grace     | Milan | P1         | Keyboard      | 89.90 | 1   | 555-0303               |
 +----------+------------+---------+-----------+-------+------------+---------------+-------+-----+------------------------+

 FDs:  {order_id, product_id} -> qty
       order_id   -> order_date, cust_id
       cust_id    -> cust_name, city
       product_id -> product_name, price

First Normal Form (1NF): atomic values, no repeating groups

A relation is in 1NF when every column holds a single value of its domain (no lists packed into one field, no phone1, phone2, phone3 column groups) and rows are identified by a key. cust_phones violates 1NF: you cannot easily query "who has phone 555-0199", index it, or constrain it.

Fix: move the multivalued attribute to its own table.

 order_lines_1nf(order_id, order_date, cust_id, cust_name, city, product_id, product_name, price, qty)
   key: {order_id, product_id}
 customer_phones(cust_id, phone)
   key: {cust_id, phone}

"Atomic" is relative to how the data is used. A date is a single value even though it has a year and a month. PostgreSQL's arrays and jsonb technically allow non-atomic columns. They are reasonable for data you always read and write as a whole (a list of tags you never join on), and a 1NF violation when you need to search, constrain, or reference individual elements (3.4).

Second Normal Form (2NF): no partial dependencies

A relation is in 2NF when it is in 1NF and no non-prime attribute depends on only part of a candidate key. This only matters for composite keys.

In order_lines_1nf, the key is {order_id, product_id}, but:

• order_date, cust_id, cust_name, city depend on order_id alone.
• product_name, price depend on product_id alone.

So the order date is repeated on every line of the order, and the product name on every line that mentions it. Fix: move each partial dependency into a table keyed by its determinant.

 orders_2nf(order_id, order_date, cust_id, cust_name, city)   key: order_id
 products(product_id, product_name, price)                    key: product_id
 order_lines(order_id, product_id, qty)                       key: {order_id, product_id}

Third Normal Form (3NF): no transitive dependencies

A relation is in 3NF when it is in 2NF and no non-prime attribute depends on another non-prime attribute. Formally: for every non-trivial FD X -> A, either X is a superkey or A is prime.

In orders_2nf, order_id -> cust_id and cust_id -> cust_name, city. Customer details depend on the key only through cust_id, a transitive dependency. Ada's name is repeated on each of her orders. Fix: split out customers.

 customers(cust_id, cust_name, city)            key: cust_id
 orders(order_id, order_date, cust_id)          key: order_id     FK -> customers
 products(product_id, product_name, price)      key: product_id
 order_lines(order_id, product_id, qty)         key: {order_id, product_id}
 customer_phones(cust_id, phone)                key: {cust_id, phone}

Every non-key attribute now depends on the key, the whole key, and nothing but the key.

Example: the 3NF schema in PostgreSQL

CREATE TABLE customers (
  cust_id    text PRIMARY KEY,
  cust_name  text NOT NULL,
  city       text NOT NULL
);

CREATE TABLE customer_phones (
  cust_id  text NOT NULL REFERENCES customers ON DELETE CASCADE,
  phone    text NOT NULL,
  PRIMARY KEY (cust_id, phone)                          -- 1NF: one value per row
);

CREATE TABLE products (
  product_id    text PRIMARY KEY,
  product_name  text          NOT NULL,
  price         numeric(10,2) NOT NULL
);

CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  order_date  date   NOT NULL,
  cust_id     text   NOT NULL REFERENCES customers      -- 3NF: customer facts live in customers
);

CREATE TABLE order_lines (
  order_id    bigint NOT NULL REFERENCES orders ON DELETE CASCADE,
  product_id  text   NOT NULL REFERENCES products,
  qty         int    NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)                    -- 2NF: qty depends on the whole key
);

-- The original report is now a join, never a stored copy
CREATE VIEW order_report AS
SELECT o.order_id, o.order_date, c.cust_id, c.cust_name, c.city,
       p.product_id, p.product_name, p.price, l.qty
FROM order_lines l
JOIN orders    o USING (order_id)
JOIN customers c USING (cust_id)
JOIN products  p USING (product_id);

There is a modeling question hidden here: if products.price changes, old orders now show the new price. That is not a normalization problem. "Price paid" is a different fact from "current price" and belongs on order_lines (see ER Diagrams to Relational Schemas).

Normal form	Requirement	Removes
1NF	Atomic values, no repeating groups, a key exists	Lists in cells, col1/col2/col3 groups
2NF	1NF + no non-prime attribute depends on part of a candidate key	Facts about part of a composite key
3NF	2NF + no non-prime attribute depends on another non-prime attribute	Facts about non-key attributes

Key takeaways

• 1NF: one value per cell and no repeating column groups. Multivalued data goes in its own table.
• 2NF: with composite keys, every non-key column must depend on the whole key.
• 3NF: non-key columns must not depend on other non-key columns.
• Each fix is a decomposition: move the offending dependency into a table keyed by its determinant, and rebuild the original with joins or views.

🧪 Practice

1. (Beginner) Identify the highest normal form of enrollments(student_id, course_id, student_name, course_title, grade) with key {student_id, course_id}, and decompose it to 3NF.
2. (Intermediate) employees(emp_id, name, dept_id, dept_name, dept_floor, skill1, skill2, skill3): list every violation of 1NF, 2NF, and 3NF, then write the DDL for a 3NF design with appropriate keys and foreign keys.
3. (Interview) "Is a table with a tags text[] column a violation of first normal form?" Hint: Discuss how the column is used: whether you search by tag, need tag-level constraints or foreign keys, or only ever display the list.

Boyce-Codd Normal Form

Third normal form has a loophole. It allows a dependency X -> A with a non-superkey determinant as long as A is prime (part of some candidate key). Tables with several overlapping candidate keys can therefore be in 3NF and still contain redundancy. Boyce-Codd normal form (BCNF) closes the loophole with a simpler rule:

For every non-trivial functional dependency X -> Y, X is a superkey.

In words: every determinant must be a key. If something determines something else, the database should be able to enforce it with a key constraint.

Example: a 3NF table that is not in BCNF

A university assigns tutors. Business rules:

1. For each course, a student has exactly one tutor: {student, course} -> tutor.
2. Each tutor teaches exactly one course: tutor -> course.
3. A course has several tutors.

 tutoring(student, course, tutor)

 +---------+-----------+-----------+
 | student | course    | tutor     |
 +---------+-----------+-----------+
 | Ada     | Databases | Dr. Codd  |
 | Alan    | Databases | Dr. Codd  |     <- "Dr. Codd teaches Databases" stored twice
 | Ada     | Compilers | Dr. Aho   |
 | Grace   | Databases | Dr. Date  |
 +---------+-----------+-----------+

 Candidate keys: {student, course}   and   {student, tutor}
 Prime attributes: student, course, tutor   (all of them)

 FD tutor -> course:
   - tutor is not a superkey               -> violates BCNF
   - course is prime                       -> allowed by 3NF

The fact "Dr. Codd teaches Databases" is repeated for every student. If Dr. Codd switches to teaching Compilers, several rows must change. A tutor without students cannot be recorded at all.

Decomposing to BCNF. Split on the violating FD: one table for the dependency itself, one for the rest.

 tutors(tutor, course)              key: tutor           ("who teaches what")
 assignments(student, tutor)        key: {student, tutor}

Lossless join. A decomposition is lossless if joining the pieces gives back exactly the original rows, with no extra ones. Splitting R into R1 and R2 is lossless if the shared attributes form a key of at least one piece. Here the shared attribute is tutor, a key of tutors, so the join is lossless.

Dependency preservation. BCNF has a price. The original FD {student, course} -> tutor spans both new tables, so no single key can enforce it. Nothing stops Ada from having two Databases tutors:

CREATE TABLE tutors (
  tutor   text PRIMARY KEY,                    -- enforces tutor -> course
  course  text NOT NULL,
  UNIQUE (tutor, course)                       -- target for the composite FK below
);

CREATE TABLE assignments (
  student  text NOT NULL,
  tutor    text NOT NULL,
  course   text NOT NULL,                      -- copied in deliberately so the rule can be enforced
  PRIMARY KEY (student, tutor),
  FOREIGN KEY (tutor, course) REFERENCES tutors (tutor, course) ON UPDATE CASCADE,  -- keeps course consistent
  UNIQUE (student, course)                     -- enforces {student, course} -> tutor
);

This version keeps a controlled copy of course in assignments. The composite foreign key guarantees the copy always agrees with tutors, and the UNIQUE constraint restores the lost dependency. That is a common practical compromise: a little redundancy, fully guarded by constraints.

	3NF	BCNF
Rule for non-trivial X -> A	X is a superkey or A is prime	X is a superkey
Always lossless decomposition possible	Yes	Yes
Always dependency-preserving decomposition possible	Yes	No
Differs from 3NF only when		There are multiple overlapping composite candidate keys

Key takeaways

• BCNF: every determinant is a superkey. It removes the redundancy 3NF allows when candidate keys overlap.
• Most 3NF tables are already in BCNF. The difference appears only with overlapping composite candidate keys.
• Decompose on the violating FD. The result is lossless if the shared attributes are a key of one piece.
• BCNF decomposition can lose dependency enforcement. Composite FKs plus UNIQUE can restore it at the cost of a guarded copy.

🧪 Practice

1. (Beginner) Explain in one sentence each why R(A, B, C) with FDs AB -> C and C -> B is in 3NF but not in BCNF.
2. (Intermediate) bookings(client, date, court, coach) with rules: a client books one court per date; a coach works on one court for the whole day ({coach, date} -> court); a court has one coach per date ({court, date} -> coach). Find the candidate keys and decide whether the table is in BCNF.
3. (Interview) "If BCNF is stricter, why do textbooks say 3NF is often good enough?" Hint: Think about dependency preservation, how rarely the two differ in practice, and what you would have to enforce with triggers after a BCNF split.

Fourth and Fifth Normal Form

BCNF removes all redundancy caused by functional dependencies. Some redundancy is not caused by FDs at all. It appears when a table records independent many-valued facts together, or when one fact can be reconstructed from three smaller ones. Fourth and fifth normal forms handle these cases. They are rare in practice, but recognizing them helps you avoid M:N tables that mix unrelated facts.

Multivalued dependencies and 4NF

A multivalued dependency (MVD) X ->> Y says: for a given X, the set of Y values is independent of the other attributes. Each X has a set of Ys and a set of Zs that have nothing to do with each other.

A course has a set of teachers and a set of textbooks. Any teacher may use any textbook. Storing both in one table forces every combination:

 course_info(course, teacher, textbook)       key: all three columns (it is in BCNF)

 +-----------+---------+-------------------+
 | course    | teacher | textbook          |
 +-----------+---------+-------------------+
 | Databases | Codd    | Database Systems  |
 | Databases | Codd    | SQL Internals     |
 | Databases | Date    | Database Systems  |
 | Databases | Date    | SQL Internals     |
 +-----------+---------+-------------------+

 MVDs:  course ->> teacher,  course ->> textbook

 Add a third textbook  -> must insert 2 rows (one per teacher)
 Add a third teacher   -> must insert 2 rows (one per textbook)
 Forget one row        -> the data now claims Date does not use SQL Internals, which nobody meant

Fourth normal form (4NF): for every non-trivial MVD X ->> Y, X is a superkey. Fix by storing each independent fact separately:

CREATE TABLE course_teachers (
  course   text NOT NULL,
  teacher  text NOT NULL,
  PRIMARY KEY (course, teacher)
);

CREATE TABLE course_textbooks (
  course    text NOT NULL,
  textbook  text NOT NULL,
  PRIMARY KEY (course, textbook)
);

-- The combinations are derived when needed, never stored
SELECT t.course, t.teacher, b.textbook
FROM course_teachers  t
JOIN course_textbooks b USING (course);

The test: is there a real relationship among all three, or only between course and each of the others? If each teacher chooses their own textbooks, then (course, teacher, textbook) is one fact, no MVD exists, and the three-column table is correct.

Join dependencies and 5NF

A join dependency says a table equals the join of some of its projections. Fifth normal form (also called project-join normal form) requires every join dependency to follow from the candidate keys. A violation appears with a cyclic business rule over three entities.

Rule: "If an agent represents a company, and that company makes a product, and the agent sells that kind of product, then the agent sells that product for that company."

 sales(agent, company, product)

 Under this rule, the three-column table is exactly the join of:
   agent_company(agent, company)       who represents whom
   company_product(company, product)   who makes what
   agent_product(agent, product)       who sells which kinds of product

 Storing the three-column table repeats information the three pairs already imply.
 Splitting into any TWO of the pairs is lossy (the join produces combinations that never happened).
 Splitting into all THREE is lossless, because of the rule.

Without the rule, an agent might sell vacuum cleaners for Acme but not for Globex even though both make them. Then the three-column table is a genuine ternary fact, and it is already in 5NF.

Normal form	Dependency handled	Symptom	Fix
4NF	Multivalued dependency X ->> Y	Every combination of independent lists stored	One table per independent list
5NF	Join dependency across three or more projections	A ternary table fully implied by a cyclic rule	Store the pairwise relationships, derive the triples

Key takeaways

• MVDs appear when one entity has several independent multivalued facts. 4NF stores each in its own table.
• Before splitting, check whether the attributes form one real relationship or several independent ones.
• 5NF violations come from cyclic rules that make a ternary table derivable from its pairwise projections.
• In practice: never store independent M:N relationships in one table. Most schemas in 3NF/BCNF built from a good ER model already satisfy 4NF and 5NF.

🧪 Practice

1. (Beginner) employee_profile(emp_id, skill, language) stores every combination of an employee's skills and spoken languages. Show the redundancy with sample rows and decompose to 4NF.
2. (Intermediate) For prescriptions(doctor, patient, drug), describe one set of business rules under which the table is already in 5NF, and one under which it can be losslessly split into three pairwise tables.
3. (Interview) "A table user_roles_permissions(user_id, role, permission) keeps growing much faster than expected. What would you look for?" Hint: Ask whether permissions depend on the user or only on the role, and what that means as an MVD.

Denormalization Trade-offs

A fully normalized schema stores each fact once, which makes writes safe and simple. Reads, however, may need many joins and aggregations. On a busy system, a dashboard that sums ten million order lines on every page load is a problem. Denormalization is the deliberate reintroduction of redundancy to make specific reads faster or simpler. The key word is deliberate: normalize first, measure, then denormalize a specific hot path and take on the job of keeping the copies consistent.

An analogy: a phone's contact list caches names next to numbers so it does not have to look them up elsewhere. That is convenient until someone changes their name, and then the cache has to be updated or it is wrong.

Common techniques

Technique	Example	Kept consistent by
Copy a column into a child table	order_items.product_name	Application writes or triggers. Often intentionally frozen as a historical snapshot
Stored aggregate / counter	posts.comment_count	Triggers, or periodic recalculation
Generated column	order_lines.line_total from qty * unit_price	PostgreSQL itself (same row only)
Materialized view	Daily revenue per product	REFRESH MATERIALIZED VIEW [CONCURRENTLY] (9.4)
Summary / rollup table	sales_daily(date, product_id, revenue)	Batch jobs or incremental updates
Embedded document	orders.shipping_address jsonb	Written once with the order
Star schema for analytics	Wide dimension tables	ETL process (Star and Snowflake Schemas)

Example: a counter column maintained by a trigger

ALTER TABLE posts ADD COLUMN comment_count int NOT NULL DEFAULT 0;

CREATE FUNCTION comments_count_trg() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;                                   -- AFTER trigger: return value is ignored
END $$;

CREATE TRIGGER comments_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION comments_count_trg();

-- Reads become trivial...
SELECT title, comment_count FROM posts ORDER BY comment_count DESC LIMIT 10;
-- ...but every comment insert now also updates (and row-locks) the post row.
-- A viral post with thousands of comments per second turns that row into a hotspot.

Example: a materialized view for a reporting query

CREATE MATERIALIZED VIEW product_revenue_daily AS
SELECT date_trunc('day', o.placed_at)::date AS day,
       i.product_id,
       sum(i.quantity * i.unit_price)       AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON product_revenue_daily (day, product_id);   -- required for CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY product_revenue_daily;     -- readers are not blocked during refresh

Normalized vs. denormalized

Aspect	Normalized	Denormalized
Storage	Minimal	Larger
Write cost	One place to update	Several places, plus maintenance logic
Read cost	Joins and aggregations	Fewer joins, precomputed values
Consistency risk	Low: each fact stored once	Copies can drift out of sync
Schema changes	Localized	Changes ripple to every copy
Best fit	OLTP, source-of-truth data	Read-heavy paths, reporting, analytics

Before denormalizing, try

1. Proper indexes, including covering indexes (6.3).
2. Rewriting the query and reading its execution plan (7.3).
3. A materialized view, which keeps the source normalized.
4. Caching in the application or a read replica.

Key takeaways

• Denormalization trades write complexity and consistency risk for read speed. Do it only for measured hot paths.
• Normalize first. Keep a single source of truth and treat denormalized data as derived from it.
• Prefer mechanisms PostgreSQL maintains (generated columns, materialized views) over hand-maintained copies.
• A copy that is a historical snapshot (price at purchase) is not denormalization. It is a different fact.

🧪 Practice

1. (Beginner) For each, say whether it is denormalization or a separate fact: order_items.unit_price, posts.comment_count, invoices.customer_address_at_issue, users.full_name alongside first_name and last_name.
2. (Intermediate) Implement comment_count with the trigger above, then handle UPDATEs that move a comment to a different post. Write a query that detects posts whose stored count is wrong.
3. (Interview) "The product listing page is slow because it joins six tables. Someone proposes one wide denormalized products_flat table. How do you evaluate the proposal?" Hint: Ask what the plan shows, how often the source data changes, how the flat table would be kept up to date, how stale it may be, and whether a materialized view or better indexes would solve it.

Anomalies and Redundancy

Why go through all the normal forms? Because redundancy is not just wasted space. When the same fact is stored in several places, the copies can disagree, and some facts become impossible to record or easy to lose. These failures are called anomalies, and they are the practical reason normalization exists. This topic ties the theory together: you can use anomalies as a checklist to judge any table.

An analogy: a company that keeps each employee's address in the HR file, the payroll file, and the badge system. When someone moves, one copy is always forgotten, and nobody knows which one is right.

The three anomalies

Consider the unnormalized table from First, Second, and Third Normal Form:

 order_report(order_id, order_date, cust_id, cust_name, city, product_id, product_name, price, qty)
 key: {order_id, product_id}

 +----------+------------+---------+-----------+-------+------------+--------------+-------+-----+
 | order_id | order_date | cust_id | cust_name | city  | product_id | product_name | price | qty |
 +----------+------------+---------+-----------+-------+------------+--------------+-------+-----+
 | 1001     | 2026-09-01 | C1      | Ada       | Turin | P1         | Keyboard     | 89.90 | 1   |
 | 1001     | 2026-09-01 | C1      | Ada       | Turin | P2         | Mouse        | 24.50 | 2   |
 | 1003     | 2026-09-07 | C1      | Ada       | Turin | P2         | Mouse        | 24.50 | 1   |
 | 1002     | 2026-09-03 | C2      | Grace     | Milan | P1         | Keyboard     | 89.90 | 1   |
 +----------+------------+---------+-----------+-------+------------+--------------+-------+-----+

Anomaly	What goes wrong	In this table
Update anomaly	A fact stored several times must be changed everywhere. Missing one copy leaves contradictory data	Ada moves to Rome: three rows must change. Updating only one leaves her in two cities
Insertion anomaly	A fact cannot be recorded without an unrelated fact	A new product cannot be added until someone orders it, because order_id is part of the key and cannot be NULL
Deletion anomaly	Deleting one fact unintentionally deletes another	Deleting order 1002 deletes the only record that customer Grace exists and lives in Milan

Example: anomalies in SQL

-- Update anomaly: a partial update produces contradictory data
UPDATE order_report SET city = 'Rome' WHERE order_id = 1001 AND cust_id = 'C1';
SELECT DISTINCT cust_id, city FROM order_report WHERE cust_id = 'C1';
--  cust_id | city
-- ---------+-------
--  C1      | Rome
--  C1      | Turin      <- which one is true?

-- Insertion anomaly: no way to add a product nobody has ordered
INSERT INTO order_report (product_id, product_name, price) VALUES ('P3', 'Monitor', 229.00);
-- ERROR:  null value in column "order_id" of relation "order_report" violates not-null constraint

-- Deletion anomaly: cancelling an order erases a customer
DELETE FROM order_report WHERE order_id = 1002;
SELECT * FROM order_report WHERE cust_id = 'C2';   -- 0 rows: Grace is gone entirely

In the 3NF design each of these becomes a single, safe operation:

UPDATE customers SET city = 'Rome' WHERE cust_id = 'C1';                       -- one row, no contradiction
INSERT INTO products (product_id, product_name, price) VALUES ('P3', 'Monitor', 229.00);  -- no order needed
DELETE FROM orders WHERE order_id = 1002;                                       -- Grace stays in customers

Finding redundancy in existing data. If a supposed FD X -> Y is stored in a table whose key is not X, you can measure how many copies exist and whether they already disagree:

-- How many times is each customer's city repeated, and is it consistent?
SELECT cust_id,
       count(*)              AS copies,                 -- > 1 means redundancy
       count(DISTINCT city)  AS distinct_values         -- > 1 means an update anomaly has already happened
FROM order_report
GROUP BY cust_id
ORDER BY distinct_values DESC, copies DESC;

From anomaly to cause to fix

Symptom	Underlying cause	Normal form that fixes it
Lists or repeated column groups are hard to query	Non-atomic values, repeating groups	1NF
Facts about one part of a composite key repeated per row	Partial dependency	2NF
Facts about a non-key attribute repeated per row	Transitive dependency	3NF
Facts about one of several overlapping keys repeated	Determinant that is not a superkey	BCNF
Every combination of independent lists stored	Multivalued dependency	4NF
Ternary table that a cyclic rule makes derivable	Join dependency	5NF

Not all repetition is redundancy. A value that appears many times is redundant only if it is the same fact stored repeatedly. Many orders with status = 'shipped' are separate facts about separate orders. Many rows saying "customer C1 lives in Turin" are the same fact copied.

Key takeaways

• Redundancy causes update, insertion, and deletion anomalies: contradictions, facts that cannot be stored, and facts lost by accident.
• Each anomaly points to a dependency whose determinant is not a key. Moving it to its own table removes the anomaly.
• GROUP BY with count(DISTINCT ...) measures redundancy and finds contradictions that already exist.
• Repeated values are not necessarily redundant. Repeated facts are.

🧪 Practice

1. (Beginner) For courses_offered(course_id, title, instructor_id, instructor_email, room, term), give one concrete example each of an update, insertion, and deletion anomaly.
2. (Intermediate) Load the order_report sample above into a table and introduce an update anomaly. Write the query that detects it, then write SQL that migrates the data into the 3NF tables, choosing the most recent value when copies disagree.
3. (Interview) "A table has a column country_name repeated across 50 million rows, and it never changes. Is that a normalization problem?" Hint: Separate update-anomaly risk from storage and index size, and consider what happens the day a country's name does change.

<a id="24-schema-design-patterns"></a>

2.4 Schema Design Patterns

This subchapter covers recurring, proven solutions to design problems that come up in almost every production database: naming, reference data, analytical schemas, history tracking, deletion and auditing, multi-tenancy, and one common anti-pattern with its alternatives.

Naming Conventions

A schema is read far more often than it is written: by developers, analysts, ORMs, migration tools, and on-call engineers at 3 a.m. Consistent names make queries guessable (customer_id always references customers.id) and errors readable (orders_customer_id_fkey tells you exactly what failed). PostgreSQL has some specific rules about identifiers that make certain conventions much more practical than others.

How PostgreSQL treats identifiers

• Unquoted identifiers are folded to lowercase. CREATE TABLE Customers creates customers, and SELECT * FROM CUSTOMERS finds it.
• Quoted identifiers are case-sensitive and kept exactly. CREATE TABLE "Customers" must then always be written "Customers". Forgetting the quotes gives relation "customers" does not exist.
• Maximum length is 63 bytes (NAMEDATALEN - 1). Longer names are silently truncated, which can make two long constraint names collide.
• Reserved words (user, order, table, group) need quoting as identifiers. Avoid them.

These rules make lowercase snake_case, never quoted the standard convention in PostgreSQL.

CREATE TABLE "UserAccounts" ("userId" int, "createdAt" timestamptz);
SELECT userId FROM UserAccounts;
-- ERROR:  relation "useraccounts" does not exist         -- folded to lowercase, but the table name has capitals
SELECT "userId" FROM "UserAccounts";                       -- works, and every query forever needs quotes

CREATE TABLE user_accounts (user_id int, created_at timestamptz);
SELECT user_id FROM User_Accounts;                         -- works: no quoting, case does not matter

A common convention set

Object	Convention	Example
Tables	Plural nouns, snake_case (or singular; pick one and be consistent)	customers, order_items
Junction tables	Both table names, or a meaningful noun	post_tags, enrollments
Primary key	id	customers.id
Foreign key column	<singular referenced table>_id, or a role name + _id	customer_id, manager_id, billing_address_id
Booleans	is_ / has_ / can_ prefix	is_active, has_shipped
Timestamps	<verb>_at (timestamptz)	created_at, deleted_at, shipped_at
Dates	<noun>_on or <noun>_date	born_on, due_date
Amounts with units	Include the unit	weight_grams, duration_seconds, price_cents
Indexes	<table>_<columns>_idx	orders_customer_id_idx
Constraints	<table>_<columns>_<pkey/key/fkey/check/excl> (matches PostgreSQL's defaults)	orders_customer_id_fkey, events_dates_check
Views	Noun describing the result, optionally a _v suffix	active_customers
Schemas	Short, lowercase, by domain or ownership	billing, inventory, audit

Example: a table that follows the conventions

CREATE TABLE invoices (
  id                 bigint GENERATED ALWAYS AS IDENTITY,
  customer_id        bigint        NOT NULL,
  billing_address_id bigint        NOT NULL,         -- role name: which address, not just "address_id"
  total_cents        bigint        NOT NULL,          -- unit in the name
  currency           char(3)       NOT NULL,
  is_paid            boolean       NOT NULL DEFAULT false,
  issued_on          date          NOT NULL,
  created_at         timestamptz   NOT NULL DEFAULT now(),

  CONSTRAINT invoices_pkey                   PRIMARY KEY (id),
  CONSTRAINT invoices_customer_id_fkey       FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT invoices_billing_address_id_fkey FOREIGN KEY (billing_address_id) REFERENCES addresses (id),
  CONSTRAINT invoices_total_cents_check      CHECK (total_cents >= 0)
);

CREATE INDEX invoices_customer_id_idx ON invoices (customer_id);

Avoid

• Hungarian prefixes such as tbl_customers or str_name. The catalog already knows what each object is.
• Abbreviations only some people understand (cst_nm).
• Generic names such as data, value, type, or info without qualification.
• Names that differ only by case, or names that require quoting.
• Different names for the same thing (user_id in one table, uid in another, account_id in a third).

Key takeaways

• Use lowercase snake_case and never quote identifiers. PostgreSQL folds unquoted names to lowercase.
• Identifiers are limited to 63 bytes. Longer names are silently truncated.
• Name FK columns after what they reference (customer_id), or after their role when a table references the same table twice.
• Name constraints and indexes explicitly and consistently so error messages and migrations are self-explanatory.

🧪 Practice

1. (Beginner) Rename everything in this table to follow the conventions above: CREATE TABLE "tblOrder" ("OrderID" int, "CustID" int, "Shipped" bool, "Date" timestamp, amt numeric);
2. (Intermediate) Write a catalog query that lists every column in schema public whose name contains an uppercase letter or would need quoting. Hint: compare attname with quote_ident(attname).
3. (Interview) "Your team argues about singular vs. plural table names. How do you settle it?" Hint: Consider which choice matters less than consistency, how ORMs map names, and how you would enforce the decision automatically (linting migrations, CI checks).

Lookup and Reference Tables

Many columns hold a value from a small, fixed-ish set: order status, country, currency, priority level. You can store the allowed values in a CHECK constraint, in a PostgreSQL enum type, or in a separate lookup table referenced by a foreign key. The right choice depends on how often the set changes and whether the values carry extra data such as display labels, sort order, or an "is active" flag.

An analogy: a CHECK list is a rule printed on the form ("choose A, B, or C"). A lookup table is a managed drop-down list that administrators can edit, with descriptions next to each option.

Option	Adding a value	Extra attributes (label, sort order, active flag)	Removing or renaming a value	Readability in queries
CHECK (status IN (...))	ALTER TABLE to replace the constraint	No	Easy (replace constraint)	Values readable directly
enum type (3.3)	ALTER TYPE ... ADD VALUE	No (only a fixed sort order)	Renaming is possible; removing a value is not supported	Values readable directly
Lookup table + FK, natural key (code text)	INSERT a row, no schema change	Yes	UPDATE ... ON UPDATE CASCADE, or deactivate	Values readable directly
Lookup table + FK, surrogate key (id smallint)	INSERT a row	Yes	Rename freely; ids never change	Needs a join to see the value

Example: a lookup table with a natural text key

CREATE TABLE order_statuses (
  code         text     PRIMARY KEY,                -- the value stored in orders
  label        text     NOT NULL,                   -- human-friendly text for the UI
  sort_order   smallint NOT NULL,
  is_terminal  boolean  NOT NULL DEFAULT false,     -- business attribute of the status itself
  is_active    boolean  NOT NULL DEFAULT true       -- retire a value without deleting history
);

INSERT INTO order_statuses (code, label, sort_order, is_terminal) VALUES
  ('pending',   'Pending payment', 10, false),
  ('paid',      'Paid',            20, false),
  ('shipped',   'Shipped',         30, false),
  ('delivered', 'Delivered',       40, true),
  ('cancelled', 'Cancelled',       90, true);

CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  text   NOT NULL DEFAULT 'pending'
          REFERENCES order_statuses (code) ON UPDATE CASCADE   -- readable without a join
);

-- Readable without a join...
SELECT id FROM orders WHERE status = 'shipped';

-- ...and the extra attributes are there when needed
SELECT o.id, s.label
FROM orders o
JOIN order_statuses s ON s.code = o.status
WHERE NOT s.is_terminal
ORDER BY s.sort_order;

Example: shared reference data

Reference tables for standardized code lists (countries, currencies, time zones, units of measure) are often shared across the whole database, sometimes in their own schema:

CREATE SCHEMA ref;

CREATE TABLE ref.countries (
  iso2   char(2) PRIMARY KEY CHECK (iso2 ~ '^[A-Z]{2}$'),
  iso3   char(3) NOT NULL UNIQUE,
  name   text    NOT NULL
);

CREATE TABLE addresses (
  id       bigint  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  country  char(2) NOT NULL REFERENCES ref.countries (iso2)
);

Avoid the "one true lookup table". A single generic table lookups(type, code, label) holding statuses, countries, and priorities together looks tidy, but a foreign key to it cannot check that orders.status points to a status rather than a country. Each set of values deserves its own table.

Key takeaways

• Use CHECK for small, stable sets with no extra data, and enum for stable sets where you want a compact type.
• Use a lookup table when values change at runtime, carry extra attributes, or need to be deactivated rather than deleted.
• A natural text key ('shipped') keeps queries readable without joins. Surrogate ids make renaming trivial but need joins.
• Do not combine unrelated value sets into one generic lookup table.

🧪 Practice

1. (Beginner) Create a priorities lookup table (low, medium, high, urgent, with a sort order) and a tickets table that references it. Query tickets ordered by priority.
2. (Intermediate) Migrate an orders.status column that uses CHECK (status IN (...)) to a lookup table without downtime. List the steps, including how to add the foreign key on a large table.
3. (Interview) "Should order status be an enum or a lookup table?" Hint: Compare adding, renaming, and removing values; whether statuses need metadata such as labels or flags; and who needs to change them (developers through migrations, or administrators at runtime).

Star and Snowflake Schemas

Normalized OLTP schemas are designed for many small, safe writes. Analytical questions such as "revenue by product category, region, and month for the last three years" need large scans, many joins, and aggregations. Data warehouses therefore use dimensional modeling (popularized by Ralph Kimball): a schema shaped around the questions business users ask, deliberately denormalized for simple, fast queries (1.1 covers OLTP vs. OLAP).

Two kinds of tables:

• Fact table: one row per measurable event at a defined level of detail (the grain), such as one row per order line. It holds numeric measures (quantity, revenue) and foreign keys to dimensions. Fact tables are long and narrow, often billions of rows.
• Dimension tables: the context of events. Who, what, where, when. They hold descriptive attributes used to filter and group (product name, category, brand; customer city, segment; date, month, quarter). Dimensions are short and wide.

An analogy: a fact table is a cash register's receipt roll. Dimensions are the catalogs that explain each line: the product catalog, the store directory, the calendar.

Star schema. Each dimension is a single denormalized table connected directly to the fact table:

                       +----------------+
                       |   dim_date     |
                       | date_key  (PK) |
                       | date, month,   |
                       | quarter, year, |
                       | is_weekend     |
                       +-------+--------+
                               |
 +----------------+    +-------+---------+    +-----------------+
 |  dim_product   |    |   fact_sales    |    |  dim_customer   |
 | product_key PK |----| date_key     FK |----| customer_key PK |
 | name, brand,   |    | product_key  FK |    | name, city,     |
 | category,      |    | customer_key FK |    | country,        |
 | department     |    | store_key    FK |    | segment         |
 +----------------+    | quantity        |    +-----------------+
                       | revenue         |
                       | discount        |
                       +-------+---------+
                               |
                       +-------+--------+
                       |   dim_store    |
                       | store_key (PK) |
                       | name, region   |
                       +----------------+

Snowflake schema. Dimensions are normalized into sub-dimensions, so the diagram branches out like a snowflake:

 fact_sales --> dim_product --> dim_category --> dim_department
            --> dim_customer --> dim_city --> dim_country

Aspect	Star	Snowflake
Dimension structure	One flat, denormalized table per dimension	Normalized into several tables
Joins per query	One per dimension	More, following each hierarchy
Query simplicity	High, which BI tools prefer	Lower
Storage	Repeats hierarchy text (category name on every product)	Less repetition
Dimension maintenance	Update many rows when a category is renamed	Update one row
Typical use	Most data marts and BI workloads	Very large or deep, frequently changing hierarchies

Example: a star schema in PostgreSQL

CREATE TABLE dim_date (
  date_key     int  PRIMARY KEY,                -- e.g. 20261005: compact and readable
  full_date    date NOT NULL UNIQUE,
  year         int  NOT NULL,
  quarter      int  NOT NULL,
  month        int  NOT NULL,
  month_name   text NOT NULL,
  is_weekend   boolean NOT NULL
);

INSERT INTO dim_date
SELECT to_char(d, 'YYYYMMDD')::int, d,
       extract(year FROM d), extract(quarter FROM d), extract(month FROM d),
       to_char(d, 'FMMonth'), extract(isodow FROM d) IN (6, 7)
FROM generate_series('2020-01-01'::date, '2030-12-31', interval '1 day') AS g(d);

CREATE TABLE dim_product (
  product_key  int  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- warehouse surrogate key
  product_id   bigint NOT NULL,                                 -- key from the source system
  name         text NOT NULL,
  brand        text NOT NULL,
  category     text NOT NULL,                                   -- denormalized hierarchy (star)
  department   text NOT NULL
);

CREATE TABLE fact_sales (
  date_key     int    NOT NULL REFERENCES dim_date,
  product_key  int    NOT NULL REFERENCES dim_product,
  customer_key int    NOT NULL,
  quantity     int    NOT NULL,                                 -- additive measure
  revenue      numeric(14,2) NOT NULL                           -- additive measure
);                                                              -- grain: one row per order line

-- A typical star query: filter and group by dimension attributes, aggregate fact measures
SELECT d.year, d.quarter, p.category, sum(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year >= 2025
GROUP BY d.year, d.quarter, p.category
ORDER BY d.year, d.quarter, revenue DESC;

For large fact tables in PostgreSQL, consider partitioning by date (6.4), BRIN indexes on date keys (6.2), and pre-aggregated materialized views.

Key takeaways

• Dimensional models separate facts (measurable events at a declared grain) from dimensions (descriptive context).
• A star schema has flat, denormalized dimensions. Queries are simple and BI tools handle them well.
• A snowflake schema normalizes dimensions. Storage and maintenance are better, at the cost of more joins.
• Always declare the fact table's grain first. Mixing grains in one fact table produces wrong totals.

🧪 Practice

1. (Beginner) For a ride-sharing company, choose a grain for a fact_trips table and list its measures and at least four dimensions.
2. (Intermediate) Build dim_date, dim_product, and fact_sales as above. Populate the fact table from the OLTP orders/order_items tables with an INSERT ... SELECT, and write a query for monthly revenue per department.
3. (Interview) "Why not run analytics directly on the normalized OLTP schema?" Hint: Think about the number of joins, how readable the queries are for analysts, historical attributes that the OLTP system overwrites, and the load on the production database.

Temporal and Slowly Changing Data

Most tables store only the current state: a customer's current address, a product's current price. Many questions, though, are about the past. Where was the customer when the order shipped? What did the product cost on 1 March? Who had access to this account last year? If an UPDATE overwrites the old value, those answers are gone. Temporal modeling keeps history by recording when each fact was true.

Two time dimensions

Time	Question	Example
Valid time (application or business time)	When was this true in the real world?	The price was 10 EUR from 1 Jan to 31 Mar
Transaction time (system time)	When did the database record it?	We entered that price on 5 Jan and corrected it on 7 Jan

A table with both is bitemporal. Most applications need only valid time. Auditing needs transaction time (Soft Deletes and Audit Columns).

Slowly changing dimensions (SCD). Data warehouses classify the strategies for attributes that change occasionally:

Type	Strategy	History kept	Example
Type 0	Never change the value	Original value only	Date of birth, original signup channel
Type 1	Overwrite	None	Correcting a misspelled name
Type 2	Add a new row per version, with validity period and current flag	Full	Customer moves city: old orders stay linked to the old city
Type 3	Keep a previous_value column	One prior value	current_region, previous_region after a reorganization
Type 4	Current table plus separate history table	Full, in another table	products and product_history
Type 6	Combination of 1 + 2 + 3	Full plus easy access to current value	Every version row also carries the current value

Example: SCD Type 2 with a validity range and no overlaps

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE product_prices (
  product_id  bigint        NOT NULL REFERENCES products (id),
  valid       daterange     NOT NULL,                          -- [from, to): half-open, NULL upper bound = open-ended
  price       numeric(10,2) NOT NULL CHECK (price >= 0),
  CONSTRAINT product_prices_no_overlap
    EXCLUDE USING gist (product_id WITH =, valid WITH &&)      -- one price per product per day
);

-- Price history for product 1
INSERT INTO product_prices VALUES
  (1, daterange('2026-01-01', '2026-04-01'), 10.00),
  (1, daterange('2026-04-01', NULL),         12.50);           -- current price, open-ended

-- Price on a given date
SELECT price FROM product_prices
WHERE product_id = 1 AND valid @> date '2026-02-15';           -- @> means "contains": returns 10.00

-- Change the price on 2026-10-01: close the current version, open a new one, in one transaction
BEGIN;
UPDATE product_prices
SET valid = daterange(lower(valid), '2026-10-01')
WHERE product_id = 1 AND upper_inf(valid);                     -- the open-ended (current) row
INSERT INTO product_prices VALUES (1, daterange('2026-10-01', NULL), 13.90);
COMMIT;

PostgreSQL 18 temporal constraints. PostgreSQL 18 supports WITHOUT OVERLAPS in primary keys and unique constraints, and PERIOD in foreign keys. A temporal foreign key checks that the referenced row is valid for the entire period of the referencing row:

CREATE TABLE employees_t (
  id     bigint    NOT NULL,
  valid  daterange NOT NULL,
  name   text      NOT NULL,
  PRIMARY KEY (id, valid WITHOUT OVERLAPS)                     -- many versions per id, never overlapping
);

CREATE TABLE assignments_t (
  employee_id  bigint    NOT NULL,
  project      text      NOT NULL,
  valid        daterange NOT NULL,
  FOREIGN KEY (employee_id, PERIOD valid)
    REFERENCES employees_t (id, PERIOD valid)                  -- the employee must exist during the whole assignment
);

Transaction-time history with a trigger. For "what did the row look like before this change?", copy old versions into a history table on every update or delete:

CREATE TABLE customers_history (LIKE customers);               -- same columns as customers
ALTER TABLE customers_history
  ADD COLUMN changed_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN operation  text        NOT NULL;

CREATE FUNCTION customers_history_trg() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO customers_history SELECT OLD.*, now(), TG_OP;    -- store the version being replaced
  RETURN NULL;
END $$;

CREATE TRIGGER customers_history
AFTER UPDATE OR DELETE ON customers
FOR EACH ROW EXECUTE FUNCTION customers_history_trg();

Design tips

• Use half-open ranges [from, to). Consecutive periods then meet exactly, without gaps or a day counted twice.
• Use an open upper bound (NULL/infinity) for "still current", and a partial index or upper_inf(valid) to find current rows quickly.
• Point facts at the version that was valid at the time (SCD 2 surrogate key), or store a snapshot of what matters (price paid, shipping address used).

Key takeaways

• UPDATE destroys history. Temporal modeling records when each fact was valid, when it was recorded, or both.
• SCD Type 1 overwrites, Type 2 adds versioned rows, Type 3 keeps one previous value, Type 4 uses a separate history table.
• Range types plus exclusion constraints guarantee non-overlapping versions. PostgreSQL 18 adds WITHOUT OVERLAPS and PERIOD foreign keys.
• Use half-open ranges, and change versions in a single transaction.

🧪 Practice

1. (Beginner) For each attribute of a customer, choose an SCD type and justify it: email, date_of_birth, loyalty_tier, home_country, misspelled_name_fix.
2. (Intermediate) Using product_prices above, write a query that returns the revenue at the price valid on the order date for each order line, joining orders, order_items, and product_prices on the range.
3. (Interview) "An auditor asks what the system believed a customer's address was on 1 June, even though it was later corrected. How do you model that?" Hint: Distinguish valid time from transaction time, and think about what a bitemporal table stores for a corrected value.

Soft Deletes and Audit Columns

A DELETE is final. If a user deletes something by mistake, if support needs to see what an account looked like before closure, or if regulations require records to be kept, a hard delete is a problem. Two related patterns address this: soft deletes mark rows as deleted instead of removing them, and audit columns and logs record who changed what and when.

Audit columns. Most tables benefit from a standard set of metadata columns:

Column	Type	Set by
created_at	timestamptz NOT NULL DEFAULT now()	Default on insert
updated_at	timestamptz NOT NULL DEFAULT now()	A BEFORE UPDATE trigger (a default only applies on insert)
created_by / updated_by	FK to users, or text	The application, or a session setting read by a trigger
deleted_at	timestamptz (nullable)	Soft delete: NULL means not deleted
version	int NOT NULL DEFAULT 1	Incremented on update, for optimistic locking

Example: audit columns and an updated_at trigger

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();                     -- now() = transaction start time
  NEW.updated_by := current_setting('app.user_id', true);  -- set by the application per transaction; NULL if missing
  RETURN NEW;                                  -- BEFORE trigger: the modified row is what gets stored
END $$;

CREATE TABLE documents (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_id    bigint      NOT NULL,
  slug        text        NOT NULL,
  title       text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  created_by  text        DEFAULT current_setting('app.user_id', true),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  updated_by  text,
  deleted_at  timestamptz                       -- NULL = live, otherwise when it was soft-deleted
);

CREATE TRIGGER documents_set_updated_at
BEFORE UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- The application identifies the acting user once per transaction
BEGIN;
SET LOCAL app.user_id = '42';
UPDATE documents SET title = 'Q3 Report (final)' WHERE id = 1;
COMMIT;

Soft deletes and their consequences. Once rows are "deleted" by setting deleted_at, every query must exclude them, and constraints must ignore them:

-- Soft delete
UPDATE documents SET deleted_at = now() WHERE id = 1;

-- Uniqueness only among live rows: a deleted "q3-report" must not block a new one
CREATE UNIQUE INDEX documents_owner_slug_live_key
  ON documents (owner_id, slug)
  WHERE deleted_at IS NULL;

-- Most queries filter live rows; a partial index keeps them fast
CREATE INDEX documents_owner_live_idx ON documents (owner_id) WHERE deleted_at IS NULL;

-- A view so that application code cannot forget the filter
CREATE VIEW live_documents AS
SELECT * FROM documents WHERE deleted_at IS NULL;

Concern	Hard delete	Soft delete (deleted_at)
Undo / restore	Only from backups	UPDATE ... SET deleted_at = NULL
Query complexity	None	Every query and join must filter (or use views or row-level security, 10.3)
Unique constraints	Work as usual	Need partial unique indexes
Foreign keys	Cascades and restrictions apply	FKs still see "deleted" parents as present; cascades do not fire
Table size	Shrinks	Grows forever unless purged
Privacy laws (right to erasure)	Satisfied	Not satisfied: data is still there

Alternative: an archive table. Move deleted rows to a separate table in the same transaction. Live tables stay clean, constraints work normally, and history is still available:

CREATE TABLE documents_archive (LIKE documents INCLUDING DEFAULTS);   -- same columns, no constraints

WITH removed AS (
  DELETE FROM documents WHERE id = 1 RETURNING *                     -- hard delete from the live table...
)
INSERT INTO documents_archive SELECT * FROM removed;                  -- ...and keep a copy, atomically

For a full change history (old and new values of every change), use a generic audit trigger that writes to_jsonb(OLD) and to_jsonb(NEW) into an audit table, or an extension such as pgaudit for statement-level logging (10.4).

Key takeaways

• Add created_at and updated_at (timestamptz) to most tables. updated_at needs a BEFORE UPDATE trigger.
• Soft deletes allow undo and history, but every query, unique constraint, and index must account for deleted rows.
• Use partial unique indexes (WHERE deleted_at IS NULL) and views to make soft deletes safe.
• An archive table or audit log often gives the same benefits with fewer pitfalls. Neither satisfies a legal erasure requirement on its own.

🧪 Practice

1. (Beginner) Add created_at, updated_at, and an updated_at trigger to a products table. Update a row twice and verify that created_at stays fixed while updated_at changes.
2. (Intermediate) Implement soft deletes for users(email) such that a deleted user's email can be reused by a new account, and restoring the deleted account fails cleanly if the email has been taken in the meantime.
3. (Interview) "Your team wants soft deletes on every table. What concerns would you raise?" Hint: Think about foreign keys and cascades, query mistakes that show deleted data, table growth, unique constraints, and legal requirements to really erase personal data.

Multi-Tenant Schema Strategies

A SaaS application serves many customers (tenants) from one system: every company that signs up gets its own projects, users, and invoices, and must never see another company's data. The database design must decide where the boundary between tenants lives. The choice affects isolation, cost, operations, and how far the system can scale, and it is very hard to change later.

An analogy: tenants can share one building with locked doors between apartments (shared tables with a tenant_id), have separate floors (schema per tenant), or have separate buildings (database per tenant). More separation means more security and independence but higher costs.

 Shared tables                 Schema per tenant              Database per tenant
 --------------------------    ---------------------------    ---------------------------
 db: app                       db: app                        db: tenant_acme
   projects                      schema acme.projects           projects
     tenant_id | id | name       schema acme.tasks              tasks
     acme      | 1  | ...        schema globex.projects       db: tenant_globex
     globex    | 1  | ...        schema globex.tasks            projects
                                                                tasks

Aspect	Shared tables (tenant_id column)	Schema per tenant	Database per tenant
Isolation	Logical: depends on every query filtering, or on row-level security	Separate namespaces; search_path mistakes can still leak	Strong: separate databases, separate connections
Number of tenants it handles well	Millions	Hundreds to a few thousand (catalog bloat, migration time)	Tens to hundreds per server
Migrations	One schema change	Run once per schema	Run once per database
Cross-tenant analytics	Easy	UNION across schemas	Separate connections or a warehouse
Per-tenant backup and restore	Hard	pg_dump -n schema	Easy
Connection pooling	Simple	Simple (switch search_path)	One pool per database
Noisy-neighbor isolation	Weak	Weak	Better (and best on separate servers)
Per-tenant customization	Hard	Possible	Easy

Example: shared tables with composite keys and row-level security

The most common choice for B2B SaaS. Two techniques make it safe:

1. Put tenant_id into keys and foreign keys, so a row can never reference another tenant's row.
2. Use row-level security (RLS) so the database filters by tenant even if a query forgets to (10.3).

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

CREATE TABLE projects (
  tenant_id  bigint NOT NULL REFERENCES tenants (id),
  id         bigint GENERATED ALWAYS AS IDENTITY,
  name       text   NOT NULL,
  PRIMARY KEY (tenant_id, id)                   -- tenant first: index also serves "all projects of a tenant"
);

CREATE TABLE tasks (
  tenant_id   bigint NOT NULL,
  id          bigint GENERATED ALWAYS AS IDENTITY,
  project_id  bigint NOT NULL,
  title       text   NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)   -- cannot point at another tenant's project
);

-- Row-level security: rows are visible only for the tenant set in the session
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks    ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
CREATE POLICY tenant_isolation ON tasks
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- Application, per request (connecting as a non-owner role, since table owners bypass RLS by default):
BEGIN;
SET LOCAL app.tenant_id = '7';
SELECT * FROM tasks;                            -- only tenant 7's tasks, even without a WHERE clause
COMMIT;

Example: schema per tenant

CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.projects (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);

-- Per request, route queries to the tenant's schema
SET search_path = tenant_acme;
SELECT * FROM projects;                         -- resolves to tenant_acme.projects

Hybrid approaches are common: shared tables for most tenants, and a dedicated database for large or regulated customers. Tools such as the Citus extension distribute shared tables across nodes by tenant_id (12.4), which works best when tenant_id is in every key from day one.

Key takeaways

• The three strategies are shared tables, schema per tenant, and database per tenant. They trade isolation against cost and operational effort.
• Shared tables scale to the most tenants. Make them safe with tenant_id in composite keys and FKs, plus row-level security.
• Schema-per-tenant and database-per-tenant isolate better but multiply migrations and catalog size.
• Put tenant_id first in keys and indexes, and decide on the strategy early, since changing it later is very expensive.

🧪 Practice

1. (Beginner) For each, recommend a strategy and justify it in one sentence: a free to-do app with 2 million users; a B2B CRM with 400 company customers; a healthcare platform where each hospital demands a separate backup and encryption key.
2. (Intermediate) Build the shared-tables example with RLS. Create a non-owner role, set app.tenant_id to different values, and prove that a SELECT without a WHERE clause and an INSERT with a foreign tenant's project_id both respect tenant boundaries.
3. (Interview) "A bug in one endpoint forgot the WHERE tenant_id = ? clause and leaked data. How do you make that class of bug impossible?" Hint: Think about row-level security, composite foreign keys that include tenant_id, which role the application connects as, and automated tests that run queries under the wrong tenant.

Polymorphic Association Anti-Patterns

Comments can be attached to posts, photos, and videos. Likes can apply to almost anything. Frameworks such as Rails and Laravel offer a shortcut called a polymorphic association: one table with a "type" column and an "id" column that, together, point to a row in one of several tables. It is convenient in application code, and it gives up something important in the database: the foreign key cannot be declared, so referential integrity is no longer enforced.

The anti-pattern

CREATE TABLE comments (
  id                bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  commentable_type  text   NOT NULL,    -- 'post', 'photo', 'video'
  commentable_id    bigint NOT NULL,    -- id in whichever table commentable_type names
  body              text   NOT NULL
);
-- There is no valid REFERENCES clause: a foreign key must point at exactly one table.

INSERT INTO comments (commentable_type, commentable_id, body)
VALUES ('post', 999999, 'Nice!');      -- post 999999 does not exist: accepted
INSERT INTO comments (commentable_type, commentable_id, body)
VALUES ('psot', 1, 'Typo type');       -- nonexistent type: accepted
DELETE FROM posts WHERE id = 1;        -- its comments become orphans: no cascade, no error

Problems

Problem	Consequence
No foreign key	Orphans and dangling references accumulate silently
No cascades	Deleting a parent leaves its children behind
Awkward joins	JOIN posts ON c.commentable_type = 'post' AND c.commentable_id = p.id, repeated per type
Type stored as data	Renaming a class or table breaks existing rows
Weak indexing	Indexes on (commentable_type, commentable_id) are wider and less selective

Alternative 1: exclusive arcs (one nullable FK per target)

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,
  video_id  bigint REFERENCES videos (id) ON DELETE CASCADE,
  body      text   NOT NULL,
  CONSTRAINT comments_one_parent_check
    CHECK (num_nonnulls(post_id, photo_id, video_id) = 1)   -- exactly one parent
);
CREATE INDEX ON comments (post_id)  WHERE post_id  IS NOT NULL;   -- small partial indexes
CREATE INDEX ON comments (photo_id) WHERE photo_id IS NOT NULL;
CREATE INDEX ON comments (video_id) WHERE video_id IS NOT NULL;

Real foreign keys and cascades, at the cost of one column per target type. Good for a small, stable number of targets.

Alternative 2: separate junction tables per target

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

CREATE TABLE post_comments (
  post_id     bigint NOT NULL REFERENCES posts (id)    ON DELETE CASCADE,
  comment_id  bigint NOT NULL UNIQUE REFERENCES comments (id) ON DELETE CASCADE,  -- a comment belongs to one parent
  PRIMARY KEY (post_id, comment_id)
);

CREATE TABLE photo_comments (
  photo_id    bigint NOT NULL REFERENCES photos (id)   ON DELETE CASCADE,
  comment_id  bigint NOT NULL UNIQUE REFERENCES comments (id) ON DELETE CASCADE,
  PRIMARY KEY (photo_id, comment_id)
);

Clean per-type relationships. Enforcing "a comment belongs to exactly one parent across all link tables" needs a trigger, and deleting a post leaves the comment rows themselves behind unless you clean them up.

Alternative 3: a common supertype

Make every commentable thing a subtype of one parent table, so comments reference a single table (the supertype + subtypes mapping from ER Diagrams to Relational Schemas):

CREATE TABLE commentables (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind  text NOT NULL CHECK (kind IN ('post', 'photo', 'video'))
);

CREATE TABLE posts (
  id     bigint PRIMARY KEY REFERENCES commentables (id) ON DELETE CASCADE,  -- shares the supertype's id
  title  text NOT NULL
);
CREATE TABLE photos (
  id   bigint PRIMARY KEY REFERENCES commentables (id) ON DELETE CASCADE,
  url  text NOT NULL
);

CREATE TABLE comments (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  commentable_id  bigint NOT NULL REFERENCES commentables (id) ON DELETE CASCADE,  -- one real FK
  body            text   NOT NULL
);

-- Creating a post: first the supertype row, then the subtype row with the same id
WITH c AS (INSERT INTO commentables (kind) VALUES ('post') RETURNING id)
INSERT INTO posts (id, title) SELECT id, 'Hello world' FROM c;

One foreign key, which scales to new target types without changing comments. Deleting from commentables cascades to both the subtype and its comments.

Approach	Real FK	Cascades	Adding a new target type	Best for
Polymorphic type + id	No	No	No schema change	Avoid, unless integrity is enforced by triggers
Exclusive arcs	Yes	Yes	Add a column + CHECK change	Few, stable target types
Junction table per target	Yes	Partly	Add a table	Per-type metadata on the link
Common supertype	Yes	Yes	Add a subtype table	Many target types, shared features (comments, likes, tags)

Key takeaways

• A polymorphic (type, id) column pair cannot have a foreign key, so orphans and invalid types are never caught.
• Exclusive arcs keep real FKs with one nullable column per target and a num_nonnulls(...) = 1 check.
• A common supertype table gives every target a shared id that one FK can reference.
• Framework convenience is not a reason to give up integrity. Choose an alternative based on how many target types exist and how often new ones appear.

🧪 Practice

1. (Beginner) Create the polymorphic comments table and demonstrate three integrity problems with INSERT and DELETE statements.
2. (Intermediate) Convert an existing polymorphic likes(likeable_type, likeable_id, user_id) table with data into the exclusive-arcs design. Write the migration, including a query that finds and reports orphaned rows before adding the foreign keys.
3. (Interview) "Our Rails app uses polymorphic associations everywhere. Should we rewrite them?" Hint: Weigh how many orphans actually exist and how much damage they cause, how many target types each association has, framework support for the alternatives, and whether constraint triggers are an acceptable middle ground.

<a id="3-data-types-and-domain-modeling"></a>