Skip to content

Database Design | Databases - Wyatt's Notes

The Design Process

Section titled “The Design Process”

Database design is not a one-step activity. It is a disciplined process that moves from abstract Requirements to concrete physical implementation. Skipping steps leads to schemas that cannot Evolve, queries that cannot perform, and data that cannot be trusted.

Phase 1: Requirements Analysis

Section titled “Phase 1: Requirements Analysis”

Before writing a single CREATE TABLE, you must understand:

  1. Data requirements: what data will be stored, what are the entities and their attributes, what are the relationships, what are the constraints
  2. Functional requirements: what queries will the application execute, how frequently, what is the expected latency, what is the tolerance for stale data
  3. Non-functional requirements: expected data volume, growth rate, read/write ratio, RTO/RPO (recovery time/recovery point objectives), compliance requirements
  4. Access patterns: who reads what, when, and how often. The most important question in database design is: “what are the top 10 queries this system will execute?”

Phase 2: Conceptual Design

Section titled “Phase 2: Conceptual Design”

Translate requirements into an Entity-Relationship model. This phase is independent of any specific Database technology. The output is an ER diagram that captures entities, attributes, relationships, And cardinality constraints.

Phase 3: Logical Design

Section titled “Phase 3: Logical Design”

Convert the ER model into relational schema (tables, columns, keys, constraints). Apply Normalisation to eliminate redundancy. Define views for common access patterns. This phase is still Largely independent of the specific RDBMS, though you may start considering data types.

Phase 4: Physical Design

Section titled “Phase 4: Physical Design”

Map the logical schema to the specific database system. Choose data types, define indexes, decide on Partitioning strategy, configure storage parameters, and set up replication. This is where you Optimise for the specific workload based on measured query performance.

graph LR
    A["Requirements Analysis"] --> B["Conceptual Design<br/>(ER Model)"]
    B --> C["Logical Design<br/>(Tables, Keys, Normalisation)"]
    C --> D["Physical Design<br/>(Data Types, Indexes, Partitioning)"]
    D --> E["Implementation<br/>(DDL, Migrations, Replication)"]
    E --> F["Monitoring &amp; Refinement<br/>(Query Plans, Schema Evolution)"]
    F --> C


    style A fill:#e74c3c,color:#fff
    style B fill:#e67e22,color:#fff
    style C fill:#f1c40f,color:#333
    style D fill:#2ecc71,color:#fff
    style E fill:#3498db,color:#fff
    style F fill:#9b59b6,color:#fff

ER Modeling

Section titled “ER Modeling”

Entities

Section titled “Entities”

An entity represents a distinct object or concept in the domain. Entities have:

  • A unique name (singular noun)
  • Attributes (properties)
  • An identifier (primary key)
erDiagram
    CUSTOMER {
        int customer_id PK
        string name
        string email UK
        string phone
        date created_at
        date updated_at
    }


    PRODUCT {
        int product_id PK
        string name
        string sku UK
        string category
        numeric price
        int stock_quantity
        boolean is_active
    }


    ORDER {
        int order_id PK
        int customer_id FK
        timestamp ordered_at
        timestamp shipped_at
        string status
        numeric total_amount
        string shipping_address
    }


    ORDER_ITEM {
        int order_id FK
        int product_id FK
        int quantity
        numeric unit_price
    }


    CUSTOMER ||--o{ ORDER : "places"
    ORDER ||--|{ ORDER_ITEM : "contains"
    PRODUCT ||--o{ ORDER_ITEM : "included_in"

Relationships

Section titled “Relationships”
CardinalityER NotationSQL Implementation
1:1One line, one markForeign key in either table with UNIQUE constraint
1:NOne line, many marksForeign key in the “many” table
M:NMany lines, many marksAssociation table with composite PK

Attributes

Section titled “Attributes”
  • Simple vs composite: birth_date (simple) vs full_name (composite: first, middle, last)
  • Single-valued vs multi-valued: email (single) vs phone_numbers (multi-valued — model as separate table)
  • Stored vs derived: unit_price (stored) vs order_total (derived from SUM(quantity * unit_price))
  • Null vs not-null: middle_name (nullable) vs email (not null)

Schema Design Patterns

Section titled “Schema Design Patterns”

Single Table Inheritance

Section titled “Single Table Inheritance”

Store all subclasses in one table with a discriminator column:

CREATE TABLE people (
    person_id    INTEGER PRIMARY KEY,
    person_type  VARCHAR(20) NOT NULL,  -- "employee', 'contractor', 'customer'
    name         VARCHAR(200) NOT NULL,
    email        VARCHAR(255),
    -- Employee-specific (NULL for non-employees):
    employee_id  VARCHAR(20),
    salary       NUMERIC(10,2),
    -- Contractor-specific (NULL for non-contractors):
    company_name VARCHAR(200),
    hourly_rate  NUMERIC(10,2),
    -- Customer-specific (NULL for non-customers):
    loyalty_points INTEGER,
    CHECK (
        (person_type = 'employee' AND employee_id IS NOT NULL AND salary IS NOT NULL) OR
        (person_type = 'contractor' AND company_name IS NOT NULL AND hourly_rate IS NOT NULL) OR
        (person_type = 'customer' AND loyalty_points IS NOT NULL)
    )
);

Pros: simple queries, no joins, single source of truth Cons: many NULL columns, CHECK Constraints become complex as types proliferate

Class Table Inheritance

Section titled “Class Table Inheritance”

One table per class in the hierarchy, with shared columns in the parent table:

CREATE TABLE people (
    person_id INTEGER PRIMARY KEY,
    name      VARCHAR(200) NOT NULL,
    email     VARCHAR(255)
);


CREATE TABLE employees (
    person_id INTEGER PRIMARY KEY REFERENCES people(person_id),
    employee_id VARCHAR(20) NOT NULL,
    salary     NUMERIC(10,2) NOT NULL
);


CREATE TABLE contractors (
    person_id   INTEGER PRIMARY KEY REFERENCES people(person_id),
    company_name VARCHAR(200) NOT NULL,
    hourly_rate  NUMERIC(10,2) NOT NULL
);


CREATE TABLE customers (
    person_id     INTEGER PRIMARY KEY REFERENCES people(person_id),
    loyalty_points INTEGER NOT NULL DEFAULT 0
);

Pros: no NULL columns for unrelated attributes, clean normalisation Cons: every query Requires a JOIN to the parent table, inserting requires multiple INSERT statements

Shared Table (Concrete Table Inheritance)

Section titled “Shared Table (Concrete Table Inheritance)”

One table per concrete class, with shared columns duplicated:

CREATE TABLE employees (
    person_id   INTEGER PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    email       VARCHAR(255),
    employee_id VARCHAR(20) NOT NULL,
    salary      NUMERIC(10,2) NOT NULL
);


CREATE TABLE customers (
    person_id     INTEGER PRIMARY KEY,
    name          VARCHAR(200) NOT NULL,
    email         VARCHAR(255),
    loyalty_points INTEGER NOT NULL DEFAULT 0
);

Pros: each table is self-contained, no joins for single-type queries Cons: shared columns Are duplicated, cross-type queries require UNION ALL, schema changes to shared columns must be Applied to every table

Indexing Strategy

Section titled “Indexing Strategy”

Index Selection Methodology

Section titled “Index Selection Methodology”
  1. Identify top queries: what are the most frequently executed queries? What queries have the strictest latency requirements?
  2. EXPLAIN ANALYZE each query: find full table scans, nested loop joins without indexes, and sequential scans on large tables
  3. Add indexes for the top queries: start with single-column indexes on WHERE clause columns
  4. Evaluate composite indexes: for multi-column WHERE clauses, test the leftmost prefix rule
  5. Evaluate covering indexes: if a query accesses a small number of columns, a covering index can eliminate heap access entirely
  6. Monitor index usage: after deployment, check which indexes are actually used
-- Find unused indexes (candidates for removal):
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan &lt; 50
ORDER BY pg_relation_size(indexrelid) DESC;

Index for Common Patterns

Section titled “Index for Common Patterns”
-- Primary key lookups: already indexed by PRIMARY KEY
SELECT * FROM users WHERE id = 42;


-- Foreign key lookups: index the foreign key column
CREATE INDEX idx_orders_customer_id ON orders(customer_id);


-- Unique constraints: UNIQUE already creates an index
-- But verify it is being used by your queries


-- Status filtering with range: composite index with equality first
CREATE INDEX idx_orders_status_date ON orders(status, created_at);


-- Sorting: include ORDER BY columns in the index
CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at DESC);


-- Partial index for common filter: only index what you query
CREATE INDEX idx_orders_pending ON orders(customer_id, created_at) WHERE status = 'pending';

Partitioning

Section titled “Partitioning”

Partitioning divides a large table into smaller, more manageable pieces while presenting a single Table interface to queries. PostgreSQL supports declarative partitioning.

Range Partitioning

Section titled “Range Partitioning”

Divides data based on a range of values ( time):

CREATE TABLE orders (
    order_id    BIGSERIAL,
    customer_id INTEGER NOT NULL,
    total       NUMERIC(10,2) NOT NULL,
    status      VARCHAR(20) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (order_id, created_at)
) PARTITION BY RANGE (created_at);


CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');


CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');


CREATE TABLE orders_2024_q3 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');


CREATE TABLE orders_2024_q4 PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');


-- Default partition catches all rows not matching any range
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

List Partitioning

Section titled “List Partitioning”

Divides data based on discrete values:

CREATE TABLE customers (
    customer_id BIGSERIAL,
    name        VARCHAR(200) NOT NULL,
    region      VARCHAR(50) NOT NULL,
    PRIMARY KEY (customer_id, region)
) PARTITION BY LIST (region);


CREATE TABLE customers_europe PARTITION OF customers
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES', 'IT', 'NL');


CREATE TABLE customers_americas PARTITION OF customers
    FOR VALUES IN ('US', 'CA', 'BR', 'MX');


CREATE TABLE customers_apac PARTITION OF customers
    FOR VALUES IN ('JP', 'AU', 'IN', 'SG', 'KR');


CREATE TABLE customers_other PARTITION OF customers DEFAULT;

Hash Partitioning

Section titled “Hash Partitioning”

Divides data evenly across a fixed number of partitions:

CREATE TABLE events (
    event_id    BIGSERIAL,
    event_type  VARCHAR(50) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (event_id, created_at)
) PARTITION BY HASH (event_id);


CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

When to Partition

Section titled “When to Partition”
FactorPartitionDo Not Partition
Table size> 10-50 GB< 5 GB
Query patternFrequently queries a subset (date range, region)Always queries all rows
MaintenanceNeed to drop/archive old data quicklyData lifecycle is uniform
Write patternInserts target specific partitionsInserts are spread uniformly
Index sizeIndex maintenance is becoming expensiveIndexes fit comfortably in memory