PostgreSQL Schema Design & Data Modeling - Complete Notes

A practical and interview-focused guide to relational modeling, normalization, keys, constraints, PostgreSQL data types, deliberate denormalization, arrays, enums and JSONB design.

00. The schema-design mental model

A schema is an executable description of what the business believes is true. Good design makes valid states easy to store, invalid states hard to store, and important questions easy to ask.

Tables are not application objects copied into a database. A relational model starts with facts, identity and relationships. Each row states one fact at a declared grain. Each key explains how that fact is identified. Each constraint states a rule that must remain true regardless of which application, script or administrator writes the data.

01. From requirements to a relational model

Model stable business facts first. Table names and columns should describe the domain, not the current screen layout or one API response.

1. Collect business rules. Ask what must be unique, what is optional, what may change, what must be retained, and what should happen when related data is deleted.
2. Find entities. Customers, products and orders usually have independent identity and life cycles, so they are strong table candidates.
3. State the grain. Write the meaning of one row before choosing columns.
4. Find candidate keys. Identify every minimal set of attributes that could uniquely identify the row, then select a primary key.
5. List attributes and dependencies. Ask which key determines each value.
6. Model relationships. Decide cardinality, optionality and ownership.
7. Encode invariants. Choose types and constraints before adding performance indexes.
8. Test with operations. Walk through inserts, corrections, deletes, reporting, concurrent writes and future changes.

Cardinality and optionality

Optionality appears through nullability or through the absence of a related row. A nullable manager_id can mean a top-level employee. A separate optional profile table avoids a wide user row full of nulls. Pick the representation that matches the fact's identity and life cycle, not merely the representation with fewer joins.

CREATE TABLE user_profiles (
    user_id      bigint PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
    display_name text NOT NULL,
    biography    text
);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id bigint NOT NULL REFERENCES products(product_id) ON DELETE RESTRICT,
    quantity   integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, product_id)
);

02. Normalization from 1NF to 3NF

Normalization places each fact where its key determines it. The goal is not to create the maximum number of tables. The goal is to prevent one fact from being represented inconsistently in many places.

Functional dependencies and keys

A functional dependency X -> Y means that one value of X determines exactly one value of Y within the modeled rules. If product_id -> product_name, every row with the same product id must describe the same product name. A candidate key determines every attribute in the row and is minimal, meaning no part can be removed while preserving uniqueness.

A superkey also determines the row but may include unnecessary columns. The primary key is one chosen candidate key. Other candidate keys should normally remain protected by UNIQUE. A surrogate id does not erase natural uniqueness. If email is a business identifier, adding customer_id does not make duplicate emails valid.

First normal form (1NF)

In practical database design, 1NF means each row follows one table shape, columns contain values from their declared domains, and repeating groups are not encoded as numbered columns. A design with product_1, product_2, and product_3 has a hidden list, an arbitrary maximum and awkward queries.

-- Fragile: a fourth product requires a schema change.
orders(order_id, product_1, product_2, product_3)

-- Relational: any number of items, one declared grain.
orders(order_id, customer_id, ordered_at)
order_items(order_id, line_number, product_id, quantity)
PRIMARY KEY (order_id, line_number)

PostgreSQL arrays are still single typed values, so using an array does not mechanically violate PostgreSQL's type system. The design question is behavioral: if elements need independent keys, foreign keys, attributes, updates or joins, model them as rows. Use arrays when the collection is truly one bounded value, not as a shortcut around a child table.

Second normal form (2NF)

A table is in 2NF when it is in 1NF and every non-key attribute depends on the whole candidate key, not only part of a composite key. This issue is visible in a table keyed by (order_id, product_id) that also stores product_name. Product name depends only on product id, so repeating it on every order item causes update anomalies.

-- Before: product_name depends only on product_id.
order_items(order_id, product_id, product_name, quantity)

-- After: each fact lives with its determinant.
products(product_id PRIMARY KEY, product_name)
order_items(order_id, product_id, quantity,
            PRIMARY KEY (order_id, product_id),
            FOREIGN KEY (product_id) REFERENCES products)

A table with a single-column candidate key cannot have a partial dependency on that key, so it is automatically safe from this specific 2NF violation. It can still violate 3NF.

Third normal form (3NF)

A table is in 3NF when it is in 2NF and non-key facts do not depend transitively on a key through another non-key fact. If employee_id -> department_id and department_id -> department_name, storing department name in every employee row duplicates a department fact.

-- Before: department_name is determined through department_id.
employees(employee_id, full_name, department_id, department_name)

-- After: department facts have one home.
departments(department_id PRIMARY KEY, department_name UNIQUE)
employees(employee_id PRIMARY KEY, full_name,
          department_id REFERENCES departments)

3NF, BCNF and practical judgment

Boyce-Codd normal form (BCNF) strengthens the rule: for every non-trivial dependency X -> Y, X must be a superkey. Most everyday transactional models that correctly reach 3NF also satisfy BCNF. Rare overlapping candidate-key cases can satisfy 3NF but not BCNF. In interviews, explain dependencies and anomalies instead of reciting only definitions.

03. Deliberate denormalization

Denormalization is an intentional duplicate or precomputed representation added for a measured read requirement. It is not the accidental duplication that normalization helps discover.

Reasonable examples include:

• An immutable order line stores the product name and unit price agreed at purchase time. These are historical snapshot facts, not merely caches of the current product row.
• A summary table stores daily sales totals for a heavily used analytical dashboard.
• A materialized view precomputes an expensive, refreshable report.
• A search document combines text from several tables for specialized indexing.
• A bounded JSONB payload retains a third-party event exactly enough for audit or replay.

Before denormalizing, state the source of truth, the synchronization mechanism, acceptable staleness, repair procedure and measurable read benefit. Options include updating both copies in one transaction, generated columns for same-row expressions, triggers, asynchronous consumers, scheduled refreshes and rebuildable caches. Each option changes failure modes and operational cost.

04. Keys and identity

Keys are integrity rules before they are performance tools. They define identity, prevent duplicates and give relationships a stable target.

Natural, surrogate and composite keys

A country code may be a stable natural key in one system. An email address is often a poor primary key because case rules, correction and reassignment complicate its identity. A junction table often has a natural composite key such as (student_id, course_id), unless the domain permits multiple enrollment attempts and therefore needs another identifying attribute.

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

GENERATED ALWAYS AS IDENTITY is SQL-standard syntax backed by an implicit sequence. BY DEFAULT permits an explicit inserted value to take precedence, which can help controlled imports but weakens protection against accidental manual ids. Sequence-generated values can have gaps after rollbacks, caching or conflicts. A primary key promises uniqueness, not consecutive numbering.

UUID keys

PostgreSQL's native uuid type stores a 128-bit identifier. UUIDs are useful when many independent writers must create identifiers without first coordinating with one database sequence, when public ids should not expose simple counts, or when offline object creation is required.

CREATE TABLE documents (
    document_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    title       text NOT NULL
);

Random UUIDs are wider than bigint keys and have less index locality, so primary and foreign-key indexes consume more space and may perform more random page work. Time-ordered UUID variants can improve locality while keeping distributed generation properties. Current PostgreSQL releases expose native UUID generation functions, but verify the functions available in the exact server version before choosing migration syntax. Never store UUIDs as text merely for convenience: the native type validates input and is more compact.

05. Constraints as executable rules

Constraints protect data at the shared storage boundary. Application validation improves error messages, while database constraints guarantee that every writer obeys the invariant.

NOT NULL and defaults

Use NOT NULL when absence has no valid business meaning. PostgreSQL treats an unmarked column as nullable. A default supplies a value only when an insert omits the column or explicitly asks for DEFAULT; it does not replace an explicit SQL null.

CREATE TABLE accounts (
    account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    active     boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at  timestamptz,
    CONSTRAINT accounts_closed_state CHECK (
        (active AND closed_at IS NULL)
        OR (NOT active AND closed_at IS NOT NULL)
    )
);

Null is not an empty string, zero, false or a magic date. It means unknown or absent according to the model. If several different missing meanings matter, model a status or related entity rather than asking one null to carry hidden categories.

CHECK constraints

A check constraint accepts a row when its expression is true or null. Therefore CHECK (price >= 0) does not reject a null price. Add NOT NULL when the value is required. Name important constraints so errors and migrations explain the violated rule.

CREATE TABLE promotions (
    promotion_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code         text NOT NULL,
    starts_at    timestamptz NOT NULL,
    ends_at      timestamptz NOT NULL,
    percent_off  numeric(5, 2) NOT NULL,
    CONSTRAINT promotions_percent_range
        CHECK (percent_off > 0 AND percent_off <= 100),
    CONSTRAINT promotions_time_order
        CHECK (ends_at > starts_at),
    CONSTRAINT promotions_code_unique UNIQUE (code)
);

UNIQUE and null semantics

A unique constraint can cover one or many columns and automatically creates a unique B-tree index. By default, PostgreSQL considers nulls distinct for uniqueness, so multiple null entries are allowed. Use NULLS NOT DISTINCT when the business rule treats null as one shared value.

CREATE TABLE memberships (
    organization_id bigint NOT NULL REFERENCES organizations,
    user_id         bigint NOT NULL REFERENCES users,
    external_ref    text,
    role            text NOT NULL,
    UNIQUE (organization_id, user_id),
    UNIQUE NULLS NOT DISTINCT (organization_id, external_ref)
);

A partial unique index can express rules such as one active subscription per customer while retaining historical inactive rows. It is an index-backed rule rather than a SQL unique constraint, and foreign keys cannot generally target a partial unique index.

CREATE UNIQUE INDEX subscriptions_one_active_per_customer
    ON subscriptions (customer_id)
    WHERE ended_at IS NULL;

PRIMARY KEY

A primary key combines uniqueness and non-nullability, creates a unique B-tree index, documents the table's chosen identity and becomes the default referenced target when a foreign key omits a column list. PostgreSQL allows only one primary key per table but any number of alternate unique constraints.

FOREIGN KEY and referential actions

A foreign key requires each non-null referencing value to match a row in a referenced primary key or suitable unique key. Column count and compatible types must match. Composite foreign keys preserve multi-column identity, and a self-reference models a hierarchy.

CREATE TABLE order_items (
    order_id   bigint NOT NULL
        REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id bigint NOT NULL
        REFERENCES products(product_id) ON DELETE RESTRICT,
    line_no    integer NOT NULL CHECK (line_no > 0),
    PRIMARY KEY (order_id, line_no)
);

CREATE INDEX order_items_product_id_idx ON order_items (product_id);

PostgreSQL indexes referenced primary or unique keys, but it does not automatically create an index on the referencing columns. Add child-side indexes when parent updates or deletes and joins need to locate referencing rows efficiently. A composite primary key beginning with order_id already supports lookups by order id, but it does not efficiently support a lookup by product_id alone.

Deferrable constraints

PostgreSQL can defer eligible UNIQUE, PRIMARY KEY, FOREIGN KEY and EXCLUDE checks until transaction end when declared DEFERRABLE. This helps operations that temporarily violate a rule, such as swapping two unique positions. It does not make invalid committed data legal.

CREATE TABLE queue_items (
    queue_id bigint NOT NULL,
    item_id  bigint NOT NULL,
    position integer NOT NULL,
    CONSTRAINT queue_position_unique
        UNIQUE (queue_id, position) DEFERRABLE INITIALLY IMMEDIATE
);

BEGIN;
SET CONSTRAINTS queue_position_unique DEFERRED;
UPDATE queue_items SET position = -1 WHERE item_id = 10;
UPDATE queue_items SET position = 1  WHERE item_id = 20;
UPDATE queue_items SET position = 2  WHERE item_id = 10;
COMMIT;

06. Choosing data types deliberately

A type defines representation, valid operations, comparison rules, storage behavior and available indexes. Choose the type that matches meaning, not the format used by one client.

PostgreSQL's text and varchar have the same general storage and performance characteristics. Use varchar(n) only when length n is a genuine rule, not a guess. char(n) blank-pads values and is rarely a good default. Phone numbers, postal codes and account codes are text even when they contain digits, because arithmetic is not their meaning and leading zeros may matter.

07. TIMESTAMPTZ versus TIMESTAMP

Choose between an instant on the global timeline and a local calendar or wall-clock value. The type names are confusing, so reason from meaning rather than spelling.

PostgreSQL converts timestamptz input to an internal UTC instant. It does not retain the original zone name. On output, it converts the instant to the session's TimeZone. Therefore the same value can display as different local times in Kolkata and New York while still representing one instant.

SET TIME ZONE 'Asia/Kolkata';
SELECT TIMESTAMPTZ '2026-07-22 10:00:00+05:30';

SET TIME ZONE 'America/New_York';
SELECT TIMESTAMPTZ '2026-07-22 10:00:00+05:30';
-- The displayed wall time changes; the instant does not.

Plain timestamp means timestamp without time zone. If input contains an offset but the target type is plain timestamp, PostgreSQL ignores the zone indication rather than converting the value. Use explicit types in literals and parameterized client values to avoid accidental interpretation.

Future schedules and named zones

A future appointment described as "09:00 in Europe/Paris" may need both a local timestamp and an IANA zone name. Storing only the current UTC conversion can lose the user's intended civil time if time-zone rules later change. Conversely, an event that already occurred should normally be stored as an instant. Model the intent.

CREATE TABLE appointments (
    appointment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    local_starts_at timestamp NOT NULL,
    time_zone_name  text NOT NULL,
    CONSTRAINT appointments_zone_known CHECK (
        time_zone_name = ANY (
            ARRAY['Asia/Kolkata', 'Europe/Paris', 'America/New_York']
        )
    )
);

A fixed application-managed allow-list check is shown only for a small bounded domain. If zones are configurable, use a referenced table populated from supported policy or validate against pg_timezone_names in controlled application logic. A CHECK constraint should not query a changing catalog.

08. NUMERIC versus floating point

Use NUMERIC when decimal exactness is part of correctness. Use floating point when approximation is expected and its range or calculation speed is valuable.

In numeric(12, 2), precision 12 is the total significant decimal digits and scale 2 is the fractional digits. PostgreSQL rounds inputs with more fractional digits to the declared scale, then rejects a result whose integer part is too wide. Do not assume excessive fractional input always raises an error.

CREATE TABLE invoice_lines (
    invoice_id bigint NOT NULL REFERENCES invoices ON DELETE CASCADE,
    line_no    integer NOT NULL,
    quantity   numeric(12, 3) NOT NULL CHECK (quantity > 0),
    unit_price numeric(14, 4) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (invoice_id, line_no)
);

SELECT quantity * unit_price AS unrounded_line_total
FROM invoice_lines;

Define where rounding occurs and which rounding rule the business accepts. Rounding each line and then summing can differ from summing high-precision values and rounding once. Currency also needs a currency code; the number 100 alone does not distinguish INR from USD. For a single-currency, fixed-scale domain, integer minor units can be excellent, but the contract must handle currencies with different minor-unit scales.

09. Arrays and enums

Arrays and enums are strong PostgreSQL features when their boundaries match the domain. They become costly when used to hide relationships or fast-changing reference data.

Arrays

PostgreSQL supports arrays of built-in and user-defined types. They are useful for small, bounded collections that are naturally read and replaced with the parent, such as RGB samples, fixed configuration coordinates or tags in a simple document-like model. Operators include containment @>, contained-by <@, overlap &&, concatenation and = ANY(array). GIN indexes can accelerate common containment and overlap queries.

CREATE TABLE brand_themes (
    brand_id bigint PRIMARY KEY REFERENCES brands ON DELETE CASCADE,
    rgb      smallint[] NOT NULL,
    CONSTRAINT brand_themes_rgb_shape CHECK (
        array_ndims(rgb) = 1
        AND cardinality(rgb) = 3
        AND 0 <= ALL (rgb)
        AND 255 >= ALL (rgb)
    )
);

CREATE INDEX articles_tags_gin ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];

Declared array dimensions and lengths in a column type are documentation only in PostgreSQL; the database does not enforce them. Add explicit checks when shape matters. Array lower bounds can differ from 1, especially after construction or slicing, so use functions such as cardinality, array_length and unnest instead of assuming a bound.

Enums

A PostgreSQL enum is a separate ordered type with a static set of case-sensitive labels. It gives compact storage, type safety and meaningful ordering. It fits a small, stable, shared vocabulary such as compass direction or a truly stable state machine.

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

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status   order_status NOT NULL DEFAULT 'pending'
);

ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'paid';

Existing enum labels can be added or renamed, but cannot be removed and cannot have their sort order rearranged without replacing the type. Each enum type is distinct, even if two types contain identical labels. These migration constraints make enums a poor fit for user-managed categories, translated labels, frequently changing workflows or values with metadata.

10. JSONB without abandoning the model

JSONB is a typed, queryable document value inside PostgreSQL. It is best for genuinely flexible or externally shaped data, not as a default replacement for columns, keys and relationships.

JSON versus JSONB

json stores an exact copy of input text and reparses it during processing. It preserves insignificant whitespace, key order and duplicate keys. jsonb stores a decomposed binary representation, discards whitespace and object-key order, and keeps only the last duplicate object key. JSONB is normally faster to process and supports indexes, so most application schemas should prefer it. Use json only when preserving the original lexical representation is itself a requirement.

SQL null and JSON null are different. A SQL null means the whole column is absent. The JSONB value 'null'::jsonb is a present JSON scalar. A missing object key is different again. Define which states are legal before writing extraction logic.

Column, related table or JSONB?

A useful hybrid keeps stable searchable facts as columns and flexible attributes in JSONB. For a product catalog, product_id, seller id, name, price, currency and lifecycle status are strong columns. Category-specific attributes such as screen size or fabric weave may fit a bounded JSONB object if their shapes vary substantially.

CREATE TABLE products (
    product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    seller_id   bigint NOT NULL REFERENCES sellers,
    sku         text NOT NULL,
    name        text NOT NULL,
    price       numeric(12, 2) NOT NULL CHECK (price >= 0),
    currency    text NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
    attributes  jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT products_seller_sku_unique UNIQUE (seller_id, sku),
    CONSTRAINT products_attributes_object CHECK (
        jsonb_typeof(attributes) = 'object'
    )
);

Reading and updating JSONB

-- -> returns JSONB; ->> returns text.
SELECT
    attributes -> 'dimensions' AS dimensions_json,
    attributes ->> 'color' AS color_text
FROM products;

-- Structure-aware containment.
SELECT *
FROM products
WHERE attributes @> '{"color":"black"}'::jsonb;

-- Top-level key existence.
SELECT *
FROM products
WHERE attributes ? 'warranty_months';

-- Replace or create a nested path and return a new document value.
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{dimensions,width_cm}',
    to_jsonb(42.5::numeric),
    true
)
WHERE product_id = 10;

Extraction operators return null rather than failing when the requested structure is absent, so a typo can look like missing data. Cast extracted text only after accounting for missing, JSON null, empty and malformed values. Parameterize JSON values through the client driver instead of building JSON strings manually.

11. Indexing inside JSONB

Match the index expression and operator class to the exact query shapes. A JSONB column is not automatically fast merely because PostgreSQL can query it.

Whole-document GIN indexes

CREATE INDEX products_attributes_gin
    ON products USING GIN (attributes);

SELECT *
FROM products
WHERE attributes @> '{"color":"black"}'::jsonb;

SELECT *
FROM products
WHERE attributes ? 'warranty_months';

The default jsonb_ops GIN operator class supports key-existence operators ?, ?|, ?&, containment @>, and JSON path matching with @? and @@. It is flexible because it indexes every key and value, but that flexibility can produce a large index and write overhead.

CREATE INDEX products_attributes_path_gin
    ON products USING GIN (attributes jsonb_path_ops);

jsonb_path_ops supports @>, @? and @@, but not the key-existence operators. It is usually smaller and more specific for containment-heavy workloads. It can perform poorly for searches for structures with no values, such as an empty object, because it does not create entries for every key by itself. Choose from observed query operators, not from a generic rule.

Targeted expression indexes

A B-tree expression index is often better for equality, range or ordering on one frequently used scalar. The query expression must match the indexed expression, including casts.

CREATE INDEX products_color_idx
    ON products ((attributes ->> 'color'));

CREATE INDEX products_warranty_months_idx
    ON products (((attributes ->> 'warranty_months')::integer));

SELECT *
FROM products
WHERE (attributes ->> 'warranty_months')::integer >= 24;

If malformed JSON can make the cast fail, the index cannot be built reliably. Validate the field, use a safe immutable extraction function, or promote the value to a real typed column. A targeted GIN expression index can also index one nested collection.

CREATE INDEX products_compatibility_gin
    ON products USING GIN ((attributes -> 'compatible_with'));

SELECT *
FROM products
WHERE attributes -> 'compatible_with' ? 'device-42';

Promoting a stable JSON field

When a JSON key becomes required, heavily joined, frequently sorted or governed by a stable type, promote it to a column. During a transition, a stored generated column can expose a typed same-row expression for constraints and ordinary indexes, provided the expression is immutable and all existing documents are valid.

ALTER TABLE products
ADD COLUMN color text
GENERATED ALWAYS AS (attributes ->> 'color') STORED;

CREATE INDEX products_generated_color_idx ON products (color);

12. Complete transactional schema example

This compact shop model combines grain, normalization, keys, historical snapshots, constraints, deliberate types and bounded JSONB.

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

CREATE TABLE customers (
    customer_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email       text NOT NULL,
    full_name   text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT customers_email_unique UNIQUE (email),
    CONSTRAINT customers_email_nonblank CHECK (btrim(email) <> '')
);

CREATE TABLE products (
    product_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku           text NOT NULL UNIQUE,
    name          text NOT NULL,
    current_price numeric(12, 2) NOT NULL CHECK (current_price >= 0),
    attributes    jsonb NOT NULL DEFAULT '{}'::jsonb,
    active        boolean NOT NULL DEFAULT true,
    CONSTRAINT products_attributes_object
        CHECK (jsonb_typeof(attributes) = 'object')
);

CREATE TABLE orders (
    order_id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id      uuid NOT NULL REFERENCES customers ON DELETE RESTRICT,
    status           order_status NOT NULL DEFAULT 'pending',
    currency         text NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
    ordered_at       timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    shipping_name    text NOT NULL,
    shipping_address jsonb NOT NULL,
    CONSTRAINT orders_shipping_address_object
        CHECK (jsonb_typeof(shipping_address) = 'object')
);

CREATE TABLE order_items (
    order_id          bigint NOT NULL REFERENCES orders ON DELETE CASCADE,
    line_no           integer NOT NULL CHECK (line_no > 0),
    product_id        bigint NOT NULL REFERENCES products ON DELETE RESTRICT,
    product_name      text NOT NULL,
    quantity          integer NOT NULL CHECK (quantity > 0),
    agreed_unit_price numeric(12, 2) NOT NULL CHECK (agreed_unit_price >= 0),
    PRIMARY KEY (order_id, line_no)
);

CREATE INDEX orders_customer_ordered_idx
    ON orders (customer_id, ordered_at DESC, order_id DESC);
CREATE INDEX order_items_product_idx ON order_items (product_id);
CREATE INDEX products_attributes_gin ON products USING GIN (attributes);

The product's current name and price are normalized in products. The order line also stores the name and price accepted at checkout because these are historical agreement facts. They should not silently change when a catalog editor changes the product. The shipping address is an order-owned snapshot whose structure may vary by country, so bounded JSONB can be reasonable. If the system must query postal code or country heavily, promote those fields to typed columns.

The database alone does not make an order immutable after payment. A state transition procedure, permissions or trigger may be needed if only specific transitions are legal. Constraint design and authorization design are related but separate responsibilities.

13. Schema evolution and safe migrations

A production schema is changed while old data and often old application versions still exist. Safe changes separate expansion, backfill, validation and cleanup.

1. Expand. Add a nullable column, new table or compatible representation without breaking current code.
2. Write both or translate. Deploy code that can populate the new representation while still serving the old contract if needed.
3. Backfill in bounded batches. Avoid one enormous transaction, monitor locks, WAL, replication lag and application latency.
4. Validate. Compare old and new representations, then validate constraints.
5. Switch reads. Move consumers after data and indexes are ready.
6. Contract. Stop old writes, remove obsolete columns or compatibility code in a later release.

ALTER TABLE products
ADD CONSTRAINT products_price_nonnegative
CHECK (current_price >= 0) NOT VALID;

-- New and changed rows are protected while existing rows can be repaired.
ALTER TABLE products
VALIDATE CONSTRAINT products_price_nonnegative;

Adding a foreign key with NOT VALID follows the same broad idea: new writes are checked, and existing rows are scanned later during validation. Lock behavior and version-specific DDL optimizations matter on large tables, so test the exact migration on production-shaped data.

Promoting JSONB to a column

1. Add the new nullable typed column.
2. Backfill only documents with a valid convertible value.
3. Record and repair exceptions instead of hiding failed casts.
4. Deploy dual-write or make one representation generated from the other.
5. Add indexes and constraints after the values are trustworthy.
6. Switch reads and later remove the old JSON key if duplication is unnecessary.

14. Performance implications of modeling choices

Correct grain and constraints often improve performance because they give the optimizer reliable structure and keep rows and indexes focused. Every convenience still has a cost profile.

Foreign keys perform checks during writes and can acquire locks needed to preserve referential integrity. Missing child-side indexes can make parent deletes and updates scan the child table. Unique constraints add indexes and therefore write amplification. These are not reasons to remove integrity; they are reasons to select intentional keys and indexes.

Very wide rows reduce the number of rows that fit on a page. Frequently changed columns can make updates more expensive, and large out-of-line values add TOAST access. Separate a large optional one-to-one payload when its access and update pattern differs from the core row, but do not split tables without measuring the resulting joins and life-cycle complexity.

15. Common mistakes and diagnostic questions

1. State the grain and candidate keys for every table.
2. Look for facts repeated across rows and identify their determinant.
3. Test null, empty, duplicate, boundary and invalid relationship inputs.
4. Walk every foreign key's delete and update action in plain business language.
5. Check whether child foreign-key columns have indexes for important access paths.
6. Classify each time value as instant, date, local time or local timestamp plus named zone.
7. Classify each number as exact count, exact decimal or approximate measurement.
8. For every JSON key, ask whether it now deserves a column or related table.
9. For every duplicate, identify source of truth, synchronization and repair.
10. Measure query plans and write cost using production-shaped data.

16. Practice problems

17. Interview Q&A

18. One-screen cheat sheet

• Grain: state exactly what one row represents before choosing columns.
• Candidate key: minimal attributes that uniquely identify the row.
• 1NF: consistent rows and no numbered repeating groups.
• 2NF: non-key facts depend on the whole composite key.
• 3NF: non-key facts do not depend on other non-key facts.
• Denormalize: only with source of truth, sync, staleness and repair plans.
• Surrogate id: does not replace a business UNIQUE constraint.
• Primary key: unique, non-null chosen identity with a B-tree index.
• Foreign key: protects relationships; index important child-side lookups.
• CASCADE: use for owned components, not independent objects.
• CHECK: passes true or null; pair with NOT NULL when required.
• Unique nulls: distinct by default; use NULLS NOT DISTINCT when needed.
• UUID: distributed identity, wider indexes; use the native type.
• TIMESTAMPTZ: instant; TIMESTAMP: local wall-clock fields.
• NUMERIC: exact decimal; DOUBLE: approximate measurement.
• Array: bounded atomic collection, not a hidden relationship table.
• Enum: small stable set; reference table for configurable metadata.
• JSONB: flexible atomic data; keep core stable facts as columns.
• GIN: broad containment; expression B-tree for typed scalar access.
• Migrations: expand, backfill, validate, switch, then contract.

19. Official reference map

• Constraints, keys and referential actions
• Identity columns
• PostgreSQL data types
• UUID type
• Date, time and time-zone behavior
• Numeric types
• Array types
• Enumerated types
• JSON and JSONB design and indexing
• JSON functions, operators and SQL/JSON paths
• Generated columns
• ALTER TABLE and constraint validation