Data Design

Overview

Principles for designing data structures, schemas, and data flows that are efficient, maintainable, and scalable.

Data Modeling

Entity-Relationship Diagrams

┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ User │ │ Order │ │ Product │ ├─────────────┤ ├─────────────┤ ├─────────────┤ │ id (PK) │──┐ │ id (PK) │ ┌──│ id (PK) │ │ email │ │ │ user_id(FK) │←───┘ │ name │ │ name │ └───→│ status │ │ price │ │ created_at │ │ total │ │ stock │ └─────────────┘ │ created_at │ └─────────────┘ └─────────────┘ │ │ │ ┌──────┴──────┐ │ ↓ ↓ │ ┌─────────────┐ │ │ OrderItem │ │ ├─────────────┤ │ │ id (PK) │ │ │ order_id(FK)│ │ │ product_id │─────────────────────┘ │ quantity │ │ price │ └─────────────┘

Relationship Types

Type Description Example

1:1 One to one User ↔ Profile

1:N One to many User → Orders

M:N Many to many Students ↔ Courses

-- 1:1 (profile extends user) CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE );

CREATE TABLE profiles ( user_id INTEGER PRIMARY KEY REFERENCES users(id), bio TEXT, avatar_url VARCHAR(255) );

-- 1:N (user has many orders) CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id), total DECIMAL(10,2) );

-- M:N (students ↔ courses via junction table) CREATE TABLE enrollments ( student_id INTEGER REFERENCES students(id), course_id INTEGER REFERENCES courses(id), enrolled_at TIMESTAMP DEFAULT NOW(), PRIMARY KEY (student_id, course_id) );

Normalization

Normal Forms

Form Rule Example Violation

1NF Atomic values, no repeating groups tags: "a,b,c"

2NF 1NF + no partial dependencies Non-key depends on part of composite key

3NF 2NF + no transitive dependencies zip → city in orders table

BCNF Every determinant is a candidate key Rare edge cases

-- ❌ Violates 1NF (non-atomic) CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255), tags VARCHAR(255) -- "electronics,sale,featured" );

-- ✅ 1NF compliant CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) );

CREATE TABLE product_tags ( product_id INTEGER REFERENCES products(id), tag VARCHAR(50), PRIMARY KEY (product_id, tag) );

-- ❌ Violates 3NF (transitive dependency) CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_zip VARCHAR(10), customer_city VARCHAR(100) -- Depends on zip, not order );

-- ✅ 3NF compliant CREATE TABLE customers ( id SERIAL PRIMARY KEY, zip VARCHAR(10), city VARCHAR(100) );

CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES customers(id) );

Denormalization

When to Denormalize

Normalize for: ✅ Write-heavy workloads ✅ Data integrity requirements ✅ Storage efficiency ✅ Flexibility in queries

Denormalize for: ✅ Read-heavy workloads ✅ Complex joins hurting performance ✅ Reporting/analytics ✅ Known access patterns

Denormalization Patterns

-- Computed columns CREATE TABLE orders ( id SERIAL PRIMARY KEY, items JSONB, item_count INTEGER GENERATED ALWAYS AS (jsonb_array_length(items)) STORED, total DECIMAL(10,2) );

-- Duplicated data for read performance CREATE TABLE posts ( id SERIAL PRIMARY KEY, author_id INTEGER REFERENCES users(id), author_name VARCHAR(100), -- Duplicated from users author_avatar VARCHAR(255), -- Duplicated from users content TEXT );

-- Materialized view for complex queries CREATE MATERIALIZED VIEW monthly_sales AS SELECT DATE_TRUNC('month', created_at) as month, product_id, SUM(quantity) as units_sold, SUM(total) as revenue FROM order_items GROUP BY 1, 2;

-- Refresh periodically REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Schema Design Patterns

Soft Deletes

CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255), deleted_at TIMESTAMP NULL, -- Partial unique index CONSTRAINT unique_active_email UNIQUE (email) WHERE deleted_at IS NULL );

-- Query active users only SELECT * FROM users WHERE deleted_at IS NULL;

Audit Trail

CREATE TABLE audit_log ( id SERIAL PRIMARY KEY, table_name VARCHAR(100), record_id INTEGER, action VARCHAR(10), -- INSERT, UPDATE, DELETE old_data JSONB, new_data JSONB, changed_by INTEGER REFERENCES users(id), changed_at TIMESTAMP DEFAULT NOW() );

-- Trigger for automatic auditing CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$ BEGIN INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by) VALUES ( TG_TABLE_NAME, COALESCE(NEW.id, OLD.id), TG_OP, CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END, CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END, current_setting('app.user_id', true)::INTEGER ); RETURN COALESCE(NEW, OLD); END; $$ LANGUAGE plpgsql;

Multi-Tenancy

-- Row-level security CREATE TABLE organizations ( id SERIAL PRIMARY KEY, name VARCHAR(255) );

CREATE TABLE projects ( id SERIAL PRIMARY KEY, org_id INTEGER REFERENCES organizations(id), name VARCHAR(255) );

-- Enable RLS ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY org_isolation ON projects USING (org_id = current_setting('app.org_id')::INTEGER);

-- Set org context per request SET app.org_id = 123; SELECT * FROM projects; -- Only sees org 123's projects

Versioning / History

-- Version table pattern CREATE TABLE documents ( id SERIAL PRIMARY KEY, current_version_id INTEGER );

CREATE TABLE document_versions ( id SERIAL PRIMARY KEY, document_id INTEGER REFERENCES documents(id), version INTEGER, content TEXT, created_at TIMESTAMP DEFAULT NOW(), created_by INTEGER REFERENCES users(id), UNIQUE (document_id, version) );

-- Temporal tables (PostgreSQL) CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255), price DECIMAL(10,2), valid_from TIMESTAMP DEFAULT NOW(), valid_to TIMESTAMP DEFAULT 'infinity' );

-- Query historical state SELECT * FROM products WHERE valid_from <= '2024-01-01' AND valid_to > '2024-01-01';

NoSQL Schema Design

Document Store (MongoDB)

// Embedded vs Referenced

// ✅ Embed when: data is accessed together, 1:few relationship { _id: ObjectId("..."), title: "Blog Post", author: { name: "John", email: "john@example.com" }, comments: [ { user: "Jane", text: "Great post!", date: ISODate("...") } ] }

// ✅ Reference when: data is accessed independently, 1:many or M:N { _id: ObjectId("..."), title: "Blog Post", authorId: ObjectId("..."), // Reference to users collection commentIds: [ObjectId("..."), ObjectId("...")] }

// ❌ Anti-pattern: Unbounded arrays { _id: ObjectId("..."), logs: [...] // Can grow to millions, hits 16MB limit }

// ✅ Better: Bucket pattern { _id: ObjectId("..."), sensorId: "sensor-123", date: ISODate("2024-01-15"), readings: [...] // Max ~1000 per document }

Key-Value Store (Redis)

Naming conventions

user:123 # User object user:123:sessions # User's sessions (set) user:123:orders # User's orders (list) order:456 # Order object orders:pending # Queue of pending orders (list) products:category:electronics # Products in category (set)

Expiration patterns

session:{token} # Expires after 30 min rate_limit:ip:1.2.3.4 # Expires after 1 min cache:api:/users/123 # Expires after 5 min

Data Pipeline Design

ETL vs ELT

ETL (Extract, Transform, Load): Source → Transform (external) → Data Warehouse Use: Traditional, when transformation is complex

ELT (Extract, Load, Transform): Source → Data Lake/Warehouse → Transform (in-place) Use: Modern, leverages warehouse compute power

Event Sourcing

// Events are the source of truth interface Event { id: string; aggregateId: string; type: string; payload: unknown; timestamp: Date; version: number; }

// Event store class EventStore { async append(aggregateId: string, events: Event[]) { await db.events.insertMany(events); }

async getEvents(aggregateId: string): Promise<Event[]> { return db.events .find({ aggregateId }) .sort({ version: 1 }) .toArray(); } }

// Rebuild state from events function rebuildAccount(events: Event[]): Account { return events.reduce((account, event) => { switch (event.type) { case 'AccountOpened': return { balance: 0, ...event.payload }; case 'MoneyDeposited': return { ...account, balance: account.balance + event.payload.amount }; case 'MoneyWithdrawn': return { ...account, balance: account.balance - event.payload.amount }; default: return account; } }, {} as Account); }

Data Governance

Data Quality Dimensions

Dimension Description Example Check

Accuracy Correct values Email format validation

Completeness No missing data Required fields present

Consistency Same across systems User ID matches in all tables

Timeliness Up to date Last updated within 24h

Uniqueness No duplicates Unique email per user

Schema Evolution

-- Safe migrations

-- ✅ Adding nullable column ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;

-- ✅ Adding column with default ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- ⚠️ Making column non-null (multi-step) -- Step 1: Add with default ALTER TABLE users ADD COLUMN verified BOOLEAN DEFAULT false; -- Step 2: Backfill data UPDATE users SET verified = true WHERE email_verified_at IS NOT NULL; -- Step 3: Add constraint ALTER TABLE users ALTER COLUMN verified SET NOT NULL;

-- ❌ Dangerous: Renaming column -- Instead: Add new, migrate data, remove old (over multiple deploys)

Related Skills

• [[database]] - Database implementation

• [[architecture-patterns]] - Data architecture patterns

• [[api-design]] - Data in APIs