Schema Design: Keys, Relationships, Tenancy, and Audit

Problem Context

Schema design is the decision layer above SQL. It outlives your framework, your ORM, your team, and usually your company. A clean schema makes 90% of features one query; a tangled one turns every feature into a migration. The 2026 reality is that schemas span relational engines (Postgres 17/18), document stores (Mongo 8.2, Cosmos), caches (Redis 8 / Valkey), and vector stores — and they all need to agree on the same conceptual entities.

This guide is the rules-of-thumb playbook: how to pick keys, model relationships, handle multi-tenancy, audit changes, and keep options open for the inevitable schema migration. None of it is novel; all of it gets skipped under deadline pressure, and that's where every long-lived data bug comes from.

Concept Explanation

Schema design boils down to four decisions you make per table:

• Keys — surrogate (auto-generated, opaque) vs natural (meaningful but mutable). Surrogate wins by default.
• Relationships — 1:1, 1:N, N:N (junction table), polymorphic (avoid).
• Audit — created_at / updated_at / created_by / updated_by + history table or temporal range.
• Tenancy — shared schema (tenant_id column), schema-per-tenant, or database-per-tenant.

flowchart LR
    R["Requirements"] --> E["Entities & relationships"]
    E --> K["Keys<br/>(surrogate UUIDv7 / bigint)"]
    K --> N["Normalize 3NF"]
    N --> D["Denormalize for hot reads"]
    D --> A["Audit + soft-delete + tenancy"]
    A --> M["Migration plan<br/>(forward + backward)"]

    style E fill:#0078D4,color:#fff,stroke:#005a9e
    style M fill:#16a34a,color:#fff,stroke:#15803d

Implementation

Step 1: Surrogate keys with UUIDv7 (or bigint identity)

-- UUIDv7 is time-ordered (Dec 2024 RFC) — index-friendly, globally unique
CREATE TABLE customers (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    email       citext NOT NULL UNIQUE,
    display_name text  NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);
-- bigint IDENTITY is fine when you don't need globally-unique IDs.
-- Avoid email/username/SKU as PK: they change.

Step 2: Foreign keys are not optional

CREATE TABLE orders (
    id          uuid PRIMARY KEY DEFAULT uuidv7(),
    tenant_id   uuid NOT NULL,
    customer_id uuid NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    status      text NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
    total_cents bigint NOT NULL CHECK (total_cents >= 0),
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_orders_tenant_customer ON orders (tenant_id, customer_id);
CREATE INDEX idx_orders_tenant_status   ON orders (tenant_id, status);
-- ON DELETE: RESTRICT (default), CASCADE (sparingly), SET NULL (only when intentional)

Step 3: Audit columns + history table

-- Trigger that bumps updated_at and writes a history row
CREATE OR REPLACE FUNCTION track_history() RETURNS trigger AS $$
BEGIN
    NEW.updated_at := now();
    INSERT INTO orders_history(order_id, snapshot, changed_at, changed_by)
    VALUES (NEW.id, to_jsonb(NEW), now(), current_setting('app.user_id', true));
    RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_history
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION track_history();

Step 4: Soft delete (only where you need history)

ALTER TABLE customers ADD COLUMN deleted_at timestamptz;

-- Partial index keeps queries on live rows fast
CREATE UNIQUE INDEX idx_customers_email_live
ON customers (lower(email))
WHERE deleted_at IS NULL;

-- A view enforces the filter so devs don't forget
CREATE VIEW v_customers AS
SELECT * FROM customers WHERE deleted_at IS NULL;

Step 5: Multi-tenancy — pick one and stick with it

-- Pattern A (most common): shared schema with tenant_id everywhere
-- Pros: simple ops, easy joins, cheap. Cons: noisy-neighbor, harder per-tenant restore.

-- Use Postgres Row-Level Security to enforce isolation in DB, not just app
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Pattern B: schema-per-tenant — strong isolation, painful migrations > 100 tenants
-- Pattern C: database-per-tenant — best isolation, highest cost. Reserve for regulated tiers.

Step 6: Many-to-many through a junction table (with metadata)

CREATE TABLE order_tags (
    order_id  uuid NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    tag_id    uuid NOT NULL REFERENCES tags(id)   ON DELETE CASCADE,
    added_at  timestamptz NOT NULL DEFAULT now(),
    added_by  uuid NOT NULL,
    PRIMARY KEY (order_id, tag_id)
);
CREATE INDEX idx_order_tags_tag ON order_tags (tag_id);
-- Composite PK = natural unique constraint + the index you'd add anyway.

Common Pitfalls

1. Natural primary keys. Email, SKU, ISBN — they all change. Use a surrogate, put a unique constraint on the natural key, and keep your options open.
2. Polymorphic associations. commentable_type + commentable_idcan't be a foreign key. Either model the relationships explicitly (post_comments, video_comments) or accept the integrity loss with eyes open.
3. EAV (entity-attribute-value)."User-defined fields" in a key/value table is the classic anti-pattern. Use JSONB columns instead — typed, indexable, and faster.
4. Booleans where enums belong. is_active, is_archived, is_pending — three booleans collapse into one status column with a CHECK constraint and you stop having impossible states.
5. No tenant_id on indexes. Every multi-tenant index should start with tenant_id. Otherwise you scan the world to find one tenant's rows.
6. Soft delete everywhere. Soft delete adds a WHERE deleted_at IS NULL tax to every query. Use it where you genuinely need recovery / audit; otherwise hard-delete and rely on backups.

Practical Takeaways

• Surrogate keys (UUIDv7 or bigint) by default, with unique constraints on natural keys.
• Foreign keys + CHECK constraints + NOT NULL — let the DB enforce what you can't see in code reviews.
• Every table gets created_at, updated_at, and a tenant column where applicable.
• Pick one tenancy pattern per system. Mixing them is operational debt.
• Use Row-Level Security as a defense-in-depth on tenant_id, not as your only line.
• Avoid polymorphic associations and EAV. Use JSONB for genuinely flexible fields.
• Treat schema migrations as code: forward + backward + tested in CI.