database-design

name

database-design

description

Designs database schemas, data models, relationships, indexes, and migrations for relational, NoSQL, time-series, and warehouse databases. Covers normalization, denormalization, ETL optimization, event sourcing, star schema, and performance tuning. Trigger keywords: schema, table, column, migration, ERD, normalize, denormalize, index, foreign key, primary key, constraint, relationship, SQL, DDL, data model, database design, data warehouse, star schema, snowflake schema, time-series, event sourcing, dimension table, fact table, ETL, data pipeline, OLAP, OLTP.

Database Design

Overview

This skill focuses on designing efficient, scalable, and maintainable database schemas and data models. It covers:

• OLTP Systems: Relational databases (PostgreSQL, MySQL) with normalization and transactional integrity
• OLAP Systems: Data warehouses with star/snowflake schemas for analytics
• NoSQL: Document stores (MongoDB), key-value (Redis), wide-column (Cassandra)
• Time-Series: Specialized databases for metrics and events (TimescaleDB, InfluxDB)
• Event Sourcing: Append-only event stores for audit and temporal queries
• Data Pipelines: Schema design considerations for ETL/ELT workflows

This skill incorporates data modeling expertise for both operational and analytical workloads.

Instructions

1. Understand Data Requirements

• Identify entities and their attributes
• Map relationships between entities (one-to-one, one-to-many, many-to-many)
• Determine data access patterns (read vs write heavy, query patterns)
• Estimate data volumes, growth rate, and retention requirements
• Distinguish OLTP (transactional) vs OLAP (analytical) needs

2. Design Schema

For OLTP (Transactional Systems):

• Normalize to 3NF to eliminate redundancy
• Define primary keys (surrogate vs natural)
• Establish foreign key relationships with appropriate cascade rules
• Choose appropriate data types for storage efficiency
• Plan for NULL handling and default values
• Add CHECK constraints for data integrity

For OLAP (Data Warehouses):

• Design star schema (central fact table with dimension tables)
• Or snowflake schema (normalized dimensions) if cardinality is high
• Create slowly changing dimensions (SCD Type 1, 2, or 3)
• Denormalize for query performance
• Add surrogate keys for dimension tables
• Design fact tables with foreign keys to dimensions and measure columns

For Time-Series:

• Use timestamp as primary key component
• Partition by time ranges (day, week, month)
• Design for append-only writes
• Consider downsampling and aggregation tables
• Use appropriate retention policies

For Event Sourcing:

• Store events as immutable append-only records
• Include event type, aggregate ID, timestamp, payload
• Design projections for read models
• Plan for event versioning and schema evolution

3. Optimize for Performance

• Design indexes for query patterns (WHERE, JOIN, ORDER BY, GROUP BY)
• Consider covering indexes to avoid table lookups
• Use partial indexes for filtered queries
• Plan denormalization for read-heavy workloads
• Design partitioning strategy for large tables (range, hash, list)
• Add materialized views for expensive aggregations
• Design for concurrent access (optimistic vs pessimistic locking)

4. Plan Migrations

• Create reversible migrations with UP and DOWN scripts
• Handle data transformations safely (backfill, defaults)
• Plan for zero-downtime deployments (expand/contract pattern)
• Version control all schema changes
• Test migrations on production-like data volumes
• Document breaking changes and migration dependencies

5. Consider ETL/Data Pipeline Impact

• Design schemas that support efficient bulk loading
• Add staging tables for incremental updates
• Include audit columns (created_at, updated_at, loaded_at)
• Plan for change data capture (CDC) if needed
• Design idempotent upsert operations
• Consider schema evolution and backward compatibility

Best Practices

1. Choose Appropriate Types: Use correct data types for storage efficiency (INT vs BIGINT, VARCHAR vs TEXT, DECIMAL vs FLOAT)
2. Index Wisely: Index columns used in WHERE, JOIN, ORDER BY, GROUP BY, but avoid over-indexing (write cost)
3. Normalize First: Start normalized (3NF) for OLTP, denormalize strategically for OLAP or read-heavy workloads
4. Use Constraints: Enforce data integrity at database level (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL)
5. Plan for Scale: Consider sharding, partitioning, and replication early for high-volume tables
6. Document Schemas: Maintain ERD, data dictionary, and relationship diagrams
7. Test Migrations: Always test on production-like data volumes and monitor performance
8. Audit Everything: Add created_at, updated_at, created_by for accountability
9. Version Events: For event sourcing, include schema version in event payload
10. Optimize for Cardinality: High-cardinality columns benefit from indexes, low-cardinality may not
11. Separate Reads from Writes: For high-scale systems, consider CQRS pattern with separate read/write models
12. Design for Idempotency: Ensure ETL operations can safely retry without duplicates

Examples

Example 1: E-Commerce Schema (PostgreSQL)

-- Users table with proper constraints
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,

    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- Products with proper indexing
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category_id UUID REFERENCES categories(id),
    created_at TIMESTAMPTZ   NOW(),
    updated_at TIMESTAMPTZ   NOW()
);


 INDEX idx_products_category  products(category_id);
 INDEX idx_products_price  products(price);
 INDEX idx_products_name_search  products  gin(to_tsvector(, name));


 orders (
    id UUID   gen_random_uuid(),
    user_id UUID   users(id),
    status ()   ,
    total_amount (, ) ,
    shipping_address JSONB ,
    created_at TIMESTAMPTZ   NOW(),
    updated_at TIMESTAMPTZ   NOW(),

     valid_status  (status  (, , , , ))
);

 INDEX idx_orders_user  orders(user_id);
 INDEX idx_orders_status  orders(status);
 INDEX idx_orders_created  orders(created_at );


 order_items (
    id UUID   gen_random_uuid(),
    order_id UUID   orders(id)   CASCADE,
    product_id UUID   products(id),
    quantity    (quantity  ),
    unit_price (, ) ,

    (order_id, product_id)
);

Example 2: Migration Script

-- Migration: Add customer loyalty program
-- Version: 20240115_001

BEGIN;

-- Add loyalty tier to users
ALTER TABLE users
ADD COLUMN loyalty_tier VARCHAR(20) DEFAULT 'bronze',
ADD COLUMN loyalty_points INTEGER DEFAULT 0;

-- Add constraint for valid tiers
ALTER TABLE users
ADD CONSTRAINT valid_loyalty_tier
CHECK (loyalty_tier IN ('bronze', 'silver', 'gold', 'platinum'));

-- Create points history table
CREATE TABLE loyalty_points_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    points_change INTEGER NOT NULL,
    reason VARCHAR(100) NOT NULL,
    reference_type VARCHAR(50),
    reference_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_loyalty_history_user ON loyalty_points_history(user_id);
CREATE INDEX idx_loyalty_history_created ON loyalty_points_history(created_at DESC);

COMMIT;

-- Rollback script (save separately)

Example 3: MongoDB Document Design

// User document with embedded addresses
{
  _id: ObjectId("..."),
  email: "[e-mail]",
  profile: {
    name: "John Doe",
    avatar_url: "https://..."
  },
  addresses: [
    {
      type: "shipping",
      street: "123 Main St",
      city: "Boston",
      state: "MA",
      zip: "02101",
      is_default: true
    }
  ],
  preferences: {
    newsletter: true,
    notifications: {
      email: true,
      push: false
    }
  },
  created_at: ISODate("2024-01-15T10:00:00Z")
}

// Indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.users.createIndex({ "addresses.zip": 1 });
db.users.createIndex({ created_at: -1 });

Example 4: Data Warehouse Star Schema (PostgreSQL)

-- Dimension: Date (conformed dimension)
CREATE TABLE dim_date (
    date_key INTEGER PRIMARY KEY,  -- YYYYMMDD format
    full_date DATE NOT NULL,
    day_of_week INTEGER,
    day_name VARCHAR(10),
    month INTEGER,
    month_name VARCHAR(10),
    quarter INTEGER,
    year INTEGER,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN
);

-- Dimension: Product
CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,  -- Surrogate key
    product_id VARCHAR(50) NOT NULL,  -- Natural key from source
    product_name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    brand VARCHAR(100),
    unit_cost DECIMAL(10, 2),

    -- SCD Type 2 columns for tracking changes
    effective_date DATE NOT NULL,
    expiration_date DATE,
    is_current BOOLEAN DEFAULT TRUE,

    UNIQUE(product_id, effective_date)
);

CREATE INDEX idx_dim_product_current ON dim_product(product_id)  is_current  ;


 dim_customer (
    customer_key SERIAL ,
    customer_id () ,
    customer_name (),
    customer_segment (),
    region (),
    country (),

    
    
    effective_date  ,
    expiration_date ,
    is_current   
);


 fact_sales (
    sale_id BIGSERIAL ,
    date_key    dim_date(date_key),
    product_key    dim_product(product_key),
    customer_key    dim_customer(customer_key),

    
    quantity  ,
    unit_price (, ) ,
    discount_amount (, )  ,
    tax_amount (, ) ,
    total_amount (, ) ,
    cost_amount (, ) ,

    
    order_number (),
    transaction_time  
);


 INDEX idx_fact_sales_date  fact_sales(date_key);
 INDEX idx_fact_sales_product  fact_sales(product_key);
 INDEX idx_fact_sales_customer  fact_sales(customer_key);
 INDEX idx_fact_sales_composite  fact_sales(date_key, product_key, customer_key);


 MATERIALIZED  mv_monthly_sales 

    d.year,
    d.month,
    p.category,
    c.region,
    (f.quantity)  total_quantity,
    (f.total_amount)  total_revenue,
    (f.cost_amount)  total_cost,
    (f.total_amount  f.cost_amount)  total_profit
 fact_sales f
 dim_date d  f.date_key  d.date_key
 dim_product p  f.product_key  p.product_key
 dim_customer c  f.customer_key  c.customer_key
  d.year, d.month, p.category, c.region;

 INDEX idx_mv_monthly_sales  mv_monthly_sales(, , category);

Example 5: Time-Series Database (TimescaleDB)

-- Create hypertable for metrics
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id VARCHAR(50) NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    value DOUBLE PRECISION NOT NULL,
    tags JSONB,

    PRIMARY KEY (time, device_id, metric_name)
);

-- Convert to hypertable with 1-day chunks
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');

-- Create indexes for common query patterns
CREATE INDEX idx_metrics_device_time ON metrics(device_id, time DESC);
CREATE INDEX idx_metrics_name_time ON metrics(metric_name, time DESC);
CREATE INDEX idx_metrics_tags ON metrics USING gin(tags);

-- Compression policy (compress chunks older than 7 days)
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id, metric_name'
);

SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- Retention policy (drop chunks older than 90 days)
SELECT add_retention_policy('metrics',  );


 MATERIALIZED  metrics_hourly
 (timescaledb.continuous) 

    time_bucket(, )  ,
    device_id,
    metric_name,
    ()  avg_value,
    ()  max_value,
    ()  min_value,
    ()  count
 metrics
  , device_id, metric_name;


 add_continuous_aggregate_policy(,
    start_offset   ,
    end_offset   ,
    schedule_interval   );

Example 6: Event Sourcing Pattern (PostgreSQL)

-- Event store (append-only)
CREATE TABLE events (
    event_id BIGSERIAL PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    aggregate_type VARCHAR(100) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_version INTEGER NOT NULL,
    payload JSONB NOT NULL,
    metadata JSONB,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- For optimistic concurrency control
    sequence_number INTEGER NOT NULL,

    CONSTRAINT unique_sequence UNIQUE(aggregate_id, sequence_number)
);

-- Indexes for event replay
CREATE INDEX idx_events_aggregate ON events(aggregate_id, sequence_number);
CREATE INDEX idx_events_type_time ON events(event_type, occurred_at);
CREATE INDEX idx_events_occurred ON events(occurred_at DESC);

-- Snapshots for performance (optional, reduces replay cost)
CREATE TABLE snapshots (
    snapshot_id BIGSERIAL PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    aggregate_type VARCHAR(100) NOT NULL,
    sequence_number INTEGER NOT NULL,
    state JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT unique_snapshot UNIQUE(aggregate_id, sequence_number)
);

CREATE INDEX idx_snapshots_aggregate  snapshots(aggregate_id, sequence_number );


 account_balances (
    account_id UUID ,
    current_balance (, ) ,
    last_event_sequence  ,
    updated_at TIMESTAMPTZ 
);







  REPLACE  rebuild_account_balance(p_account_id UUID)
   $$

    v_balance (, ) : ;

     ((
        
             event_type    (payload)::
             event_type    (payload)::
             
        
    ), )
     v_balance
     events
     aggregate_id  p_account_id
     aggregate_type  
      sequence_number;

     v_balance;
;
$$  plpgsql;

Example 7: ETL Staging Pattern (PostgreSQL)

-- Staging table for incremental loads
CREATE TABLE staging_orders (
    order_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    order_date TIMESTAMPTZ,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),

    -- ETL metadata
    source_system VARCHAR(50),
    extracted_at TIMESTAMPTZ NOT NULL,
    loaded_at TIMESTAMPTZ DEFAULT NOW(),
    batch_id VARCHAR(100),

    -- For change detection
    source_hash VARCHAR(64),
    is_processed BOOLEAN DEFAULT FALSE
);

-- Production table
CREATE TABLE orders (
    order_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50),
    order_date TIMESTAMPTZ,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),

    -- Audit columns
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    source_system VARCHAR(50),

    -- For CDC
    source_hash VARCHAR(64),
    version INTEGER DEFAULT 
);


  REPLACE  merge_orders()
   $$

    v_rows_affected ;

    
     orders (order_id, customer_id, order_date, total_amount, status, source_system, source_hash)
     order_id, customer_id, order_date, total_amount, status, source_system, source_hash
     staging_orders s
       (   orders o  o.order_id  s.order_id)
      is_processed;

     DIAGNOSTICS v_rows_affected  ROW_COUNT;

    
     orders o
    
        customer_id  s.customer_id,
        order_date  s.order_date,
        total_amount  s.total_amount,
        status  s.status,
        updated_at  NOW(),
        source_hash  s.source_hash,
        version  o.version  
     staging_orders s
     o.order_id  s.order_id
     o.source_hash  s.source_hash
      s.is_processed;

     DIAGNOSTICS v_rows_affected  v_rows_affected  ROW_COUNT;

    
     staging_orders  is_processed     is_processed;

     v_rows_affected;
;
$$  plpgsql;