database-schema-design

Database Schema Design Skill

Produce a complete database schema design document for a given domain. A schema document is not just a list of tables — it is a record of decisions: what was modelled, how entities relate, which queries the schema is optimised for, and what trade-offs were made.

A good schema design document lets an engineer understand the data model, query it correctly, extend it safely, and write migrations without breaking things.

Required Inputs

Ask for these if not already provided:

• Domain description — what the system does; what business objects are being modelled
• Entities and relationships — the main things in the domain and how they relate (e.g. "a User has many Orders; an Order has many OrderItems; an OrderItem references a Product")
• Expected query patterns — the most important read and write queries (e.g. "fetch all orders for a user, sorted by date"; "look up a product by SKU")
• Database engine — PostgreSQL, MySQL, SQLite, CockroachDB, etc. — this affects DDL syntax and available types
• Expected data volume — approximate row counts, growth rate, and any partitioning needs
• Constraints — any existing conventions, naming standards, or migration constraints to respect

Output Format

Database Schema Design: [Domain / Service Name]

Service: [Name] | Team: [Team name] Author: [Name] | Reviewed by: [Name] Date: [Date] | Database engine: [PostgreSQL X.X / MySQL X.X / etc.] Status: [Draft / Reviewed / Approved]

1. Overview

[2–3 sentences describing the domain being modelled, the scope of this schema, and any key design philosophy (e.g. "this schema prioritises read performance for the customer-facing API over write simplicity", or "designed for eventual migration to multi-tenancy")]

In scope:

• [Entity or subsystem]
• [Entity or subsystem]

Out of scope:

• [e.g. Analytics / reporting tables — separate schema]
• [e.g. Audit log tables — covered in separate design doc]

2. Entity Relationship Diagram

┌───────────────────┐         ┌───────────────────────┐
│      users        │         │       organisations    │
│─────────────────  │         │─────────────────────── │
│ id (PK)           │    ┌───▶│ id (PK)                │
│ org_id (FK)  ─────┼────┘    │ name                   │
│ email             │         │ plan                   │
│ display_name      │         │ created_at             │
│ created_at        │         └───────────────────────┘
│ updated_at        │
└─────────┬─────────┘
          │ 1
          │
          │ N
┌─────────▼─────────┐         ┌───────────────────────┐
│      [table_a]    │         │      [table_b]         │
│─────────────────  │         │─────────────────────── │
│ id (PK)           │    N    │ id (PK)                │
│ user_id (FK) ─────┼────────▶│ [table_a]_id (FK)      │
│ [field]           │    │    │ [field]                │
│ [field]           │    │    │ [field]                │
│ created_at        │         │ created_at             │
└───────────────────┘         └───────────────────────┘

Relationship summary:

| Entity A | Relationship | Entity B | Notes | |---|---|---|---| | organisations | has many | users | An org can have many users | | users | has many | [table_a] | Soft-deleted on user deletion | | [table_a] | has many | [table_b] | Cascade delete | | [table_b] | belongs to | [table_a] | Non-nullable FK | | [table_c] | many-to-many (via [join_table]) | [table_d] | Join table with metadata |

3. Table Definitions

organisations

[1 sentence describing what this table stores and its role in the domain.]

CREATE TABLE organisations (
    id          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(255)    NOT NULL,
    slug        VARCHAR(100)    NOT NULL UNIQUE,
    plan        VARCHAR(50)     NOT NULL DEFAULT 'free'
                                CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings    JSONB           NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT now()
);

| Column | Type | Nullable | Default | Notes | |---|---|---|---|---| | id | UUID | No | gen_random_uuid() | Surrogate PK — UUID preferred over serial for distributed use | | name | VARCHAR(255) | No | — | Display name; not unique | | slug | VARCHAR(100) | No | — | URL-safe identifier; unique across all orgs | | plan | VARCHAR(50) | No | 'free' | Constrained to known values via CHECK | | settings | JSONB | No | {} | Flexible config; avoid for queryable fields | | created_at | TIMESTAMPTZ | No | now() | Always use TIMESTAMPTZ, not TIMESTAMP | | updated_at | TIMESTAMPTZ | No | now() | Updated via trigger (see below) |

users

[1 sentence describing what this table stores.]

CREATE TABLE users (
    id              UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id          UUID            NOT NULL REFERENCES organisations(id)
                                    ON DELETE RESTRICT,
    email           VARCHAR(254)    NOT NULL,
    display_name    VARCHAR(255)    NOT NULL DEFAULT '',
    role            VARCHAR(50)     NOT NULL DEFAULT 'member'
                                    CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    email_verified  BOOLEAN         NOT NULL DEFAULT false,
    deleted_at      TIMESTAMPTZ     NULL,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT now(),

    CONSTRAINT users_email_org_unique UNIQUE (email, org_id)
);

| Column | Type | Nullable | Default | Notes | |---|---|---|---|---| | id | UUID | No | gen_random_uuid() | — | | org_id | UUID | No | — | FK to organisations; RESTRICT prevents orphaning | | email | VARCHAR(254) | No | — | RFC 5321 max length; unique per org (not globally) | | role | VARCHAR(50) | No | 'member' | Application-level RBAC | | deleted_at | TIMESTAMPTZ | Yes | NULL | Soft delete; NULL = active |

Soft delete policy: Rows with deleted_at IS NOT NULL are considered deleted. All application queries MUST filter WHERE deleted_at IS NULL unless explicitly fetching deleted records. Use a view or ORM scope to enforce this.

[table_a]

[Description of what this table models.]

CREATE TABLE [table_a] (
    id          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID            NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    [field_1]   VARCHAR(255)    NOT NULL,
    [field_2]   TEXT            NULL,
    [field_3]   INTEGER         NOT NULL DEFAULT 0 CHECK ([field_3] >= 0),
    status      VARCHAR(50)     NOT NULL DEFAULT 'pending'
                                CHECK (status IN ('pending', 'active', 'archived')),
    metadata    JSONB           NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT now()
);

| Column | Type | Nullable | Notes | |---|---|---|---| | user_id | UUID | No | CASCADE delete — when user is deleted, their [table_a] rows are too | | [field_1] | VARCHAR(255) | No | [Reason for length constraint] | | status | VARCHAR(50) | No | State machine: pending → active → archived (no other transitions) | | metadata | JSONB | No | [What is stored here and why it's not a typed column] |

[join_table] (Many-to-many)

[Description of the relationship this table represents.]

CREATE TABLE [join_table] (
    [table_c]_id    UUID        NOT NULL REFERENCES [table_c](id) ON DELETE CASCADE,
    [table_d]_id    UUID        NOT NULL REFERENCES [table_d](id) ON DELETE CASCADE,
    granted_by      UUID        NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    granted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY ([table_c]_id, [table_d]_id)
);

Why a composite PK: The combination of [table_c]_id + [table_d]_id is the natural key — eac