Database Schema Designer

Design production-ready database schemas with best practices built-in.

Quick Start

Just describe your data model:

design a schema for an e-commerce platform with users, products, orders

You'll get a complete SQL schema like:

CREATE TABLE users ( id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), total DECIMAL(10,2) NOT NULL, INDEX idx_orders_user (user_id) );

What to include in your request:

Entities (users, products, orders)
Key relationships (users have orders, orders have items)
Scale hints (high-traffic, millions of records)
Database preference (SQL/NoSQL) - defaults to SQL if not specified

Triggers

Trigger Example

design schema

"design a schema for user authentication"

database design

"database design for multi-tenant SaaS"

create tables

"create tables for a blog system"

schema for

"schema for inventory management"

model data

"model data for real-time analytics"

I need a database

"I need a database for tracking orders"

design NoSQL

"design NoSQL schema for product catalog"

Key Terms

Term Definition

Normalization Organizing data to reduce redundancy (1NF → 2NF → 3NF)

3NF Third Normal Form - no transitive dependencies between columns

OLTP Online Transaction Processing - write-heavy, needs normalization

OLAP Online Analytical Processing - read-heavy, benefits from denormalization

Foreign Key (FK) Column that references another table's primary key

Index Data structure that speeds up queries (at cost of slower writes)

Access Pattern How your app reads/writes data (queries, joins, filters)

Denormalization Intentionally duplicating data to speed up reads

Quick Reference

Task Approach Key Consideration

New schema Normalize to 3NF first Domain modeling over UI

SQL vs NoSQL Access patterns decide Read/write ratio matters

Primary keys INT or UUID UUID for distributed systems

Foreign keys Always constrain ON DELETE strategy critical

Indexes FKs + WHERE columns Column order matters

Migrations Always reversible Backward compatible first

Process Overview

Commands

Command When to Use Action

design schema for {domain}

Starting fresh Full schema generation

normalize {table}

Fixing existing table Apply normalization rules

add indexes for {table}

Performance issues Generate index strategy

migration for {change}

Schema evolution Create reversible migration

review schema

Code review Audit existing schema

Workflow: Start with design schema → iterate with normalize → optimize with add indexes → evolve with migration

Core Principles

Principle WHY Implementation

Model the Domain UI changes, domain doesn't Entity names reflect business concepts

Data Integrity First Corruption is costly to fix Constraints at database level

Optimize for Access Pattern Can't optimize for both OLTP: normalized, OLAP: denormalized

Plan for Scale Retrofitting is painful Index strategy + partitioning plan

Anti-Patterns

Avoid Why Instead

VARCHAR(255) everywhere Wastes storage, hides intent Size appropriately per field

FLOAT for money Rounding errors DECIMAL(10,2)

Missing FK constraints Orphaned data Always define foreign keys

No indexes on FKs Slow JOINs Index every foreign key

Storing dates as strings Can't compare/sort DATE, TIMESTAMP types

SELECT * in queries Fetches unnecessary data Explicit column lists

Non-reversible migrations Can't rollback Always write DOWN migration

Adding NOT NULL without default Breaks existing rows Add nullable, backfill, then constrain

Verification Checklist

After designing a schema:

Every table has a primary key
All relationships have foreign key constraints
ON DELETE strategy defined for each FK
Indexes exist on all foreign keys
Indexes exist on frequently queried columns
Appropriate data types (DECIMAL for money, etc.)
NOT NULL on required fields
UNIQUE constraints where needed
CHECK constraints for validation
created_at and updated_at timestamps
Migration scripts are reversible
Tested on staging with production data

Normal Forms

Form Rule Violation Example

1NF Atomic values, no repeating groups product_ids = '1,2,3'

2NF 1NF + no partial dependencies customer_name in order_items

3NF 2NF + no transitive dependencies country derived from postal_code

1st Normal Form (1NF)

-- BAD: Multiple values in column CREATE TABLE orders ( id INT PRIMARY KEY, product_ids VARCHAR(255) -- '101,102,103' );

-- GOOD: Separate table for items CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT );

CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT REFERENCES orders(id), product_id INT );

2nd Normal Form (2NF)

-- BAD: customer_name depends only on customer_id CREATE TABLE order_items ( order_id INT, product_id INT, customer_name VARCHAR(100), -- Partial dependency! PRIMARY KEY (order_id, product_id) );

-- GOOD: Customer data in separate table CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(100) );

3rd Normal Form (3NF)

-- BAD: country depends on postal_code CREATE TABLE customers ( id INT PRIMARY KEY, postal_code VARCHAR(10), country VARCHAR(50) -- Transitive dependency! );

-- GOOD: Separate postal_codes table CREATE TABLE postal_codes ( code VARCHAR(10) PRIMARY KEY, country VARCHAR(50) );

When to Denormalize

Scenario Denormalization Strategy

Read-heavy reporting Pre-calculated aggregates

Expensive JOINs Cached derived columns

Analytics dashboards Materialized views

-- Denormalized for performance CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, total_amount DECIMAL(10,2), -- Calculated item_count INT -- Calculated );

String Types

Type Use Case Example

CHAR(n) Fixed length State codes, ISO dates

VARCHAR(n) Variable length Names, emails

TEXT Long content Articles, descriptions

-- Good sizing email VARCHAR(255) phone VARCHAR(20) country_code CHAR(2)

Numeric Types

Type Range Use Case

TINYINT -128 to 127 Age, status codes

SMALLINT -32K to 32K Quantities

INT -2.1B to 2.1B IDs, counts

BIGINT Very large Large IDs, timestamps

DECIMAL(p,s) Exact precision Money

FLOAT/DOUBLE Approximate Scientific data

-- ALWAYS use DECIMAL for money price DECIMAL(10, 2) -- $99,999,999.99

-- NEVER use FLOAT for money price FLOAT -- Rounding errors!

Date/Time Types

DATE -- 2025-10-31 TIME -- 14:30:00 DATETIME -- 2025-10-31 14:30:00 TIMESTAMP -- Auto timezone conversion

-- Always store in UTC created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Boolean

-- PostgreSQL is_active BOOLEAN DEFAULT TRUE

-- MySQL is_active TINYINT(1) DEFAULT 1

When to Create Indexes

Always Index Reason

Foreign keys Speed up JOINs

WHERE clause columns Speed up filtering

ORDER BY columns Speed up sorting

Unique constraints Enforced uniqueness

-- Foreign key index CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Query pattern index CREATE INDEX idx_orders_status_date ON orders(status, created_at);

Index Types

Type Best For Example

B-Tree Ranges, equality price > 100

Hash Exact matches only email = 'x@y.com'

Full-text Text search MATCH AGAINST

Partial Subset of rows WHERE is_active = true

Composite Index Order

CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- Uses index (customer_id first) SELECT * FROM orders WHERE customer_id = 123; SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- Does NOT use index (status alone) SELECT * FROM orders WHERE status = 'pending';

Rule: Most selective column first, or column most queried alone.

Index Pitfalls

Pitfall Problem Solution

Over-indexing Slow writes Only index what's queried

Wrong column order Unused index Match query patterns

Missing FK indexes Slow JOINs Always index FKs

Primary Keys

-- Auto-increment (simple) id INT AUTO_INCREMENT PRIMARY KEY

-- UUID (distributed systems) id CHAR(36) PRIMARY KEY DEFAULT (UUID())

-- Composite (junction tables) PRIMARY KEY (student_id, course_id)

Foreign Keys

FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE -- Delete children with parent ON DELETE RESTRICT -- Prevent deletion if referenced ON DELETE SET NULL -- Set to NULL when parent deleted ON UPDATE CASCADE -- Update children when parent changes

Strategy Use When

CASCADE Dependent data (order_items)

RESTRICT Important references (prevent accidents)

SET NULL Optional relationships

Other Constraints

-- Unique email VARCHAR(255) UNIQUE NOT NULL

-- Composite unique UNIQUE (student_id, course_id)

-- Check price DECIMAL(10,2) CHECK (price >= 0) discount INT CHECK (discount BETWEEN 0 AND 100)

-- Not null name VARCHAR(100) NOT NULL

One-to-Many

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT NOT NULL REFERENCES customers(id) );

CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id INT NOT NULL, quantity INT NOT NULL );

Many-to-Many

-- Junction table CREATE TABLE enrollments ( student_id INT REFERENCES students(id) ON DELETE CASCADE, course_id INT REFERENCES courses(id) ON DELETE CASCADE, enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (student_id, course_id) );

Self-Referencing

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT REFERENCES employees(id) );

Polymorphic

-- Approach 1: Separate FKs (stronger integrity) CREATE TABLE comments ( id INT PRIMARY KEY, content TEXT NOT NULL, post_id INT REFERENCES posts(id), photo_id INT REFERENCES photos(id), CHECK ( (post_id IS NOT NULL AND photo_id IS NULL) OR (post_id IS NULL AND photo_id IS NOT NULL) ) );

-- Approach 2: Type + ID (flexible, weaker integrity) CREATE TABLE comments ( id INT PRIMARY KEY, content TEXT NOT NULL, commentable_type VARCHAR(50) NOT NULL, commentable_id INT NOT NULL );

Embedding vs Referencing

Factor Embed Reference

Access pattern Read together Read separately

Relationship 1:few 1:many

Document size Small Approaching 16MB

Update frequency Rarely Frequently

Embedded Document

{ "_id": "order_123", "customer": { "id": "cust_456", "name": "Jane Smith", "email": "jane@example.com" }, "items": [ { "product_id": "prod_789", "quantity": 2, "price": 29.99 } ], "total": 109.97 }

Referenced Document

{ "_id": "order_123", "customer_id": "cust_456", "item_ids": ["item_1", "item_2"], "total": 109.97 }

MongoDB Indexes

// Single field db.users.createIndex({ email: 1 }, { unique: true });

// Composite db.orders.createIndex({ customer_id: 1, created_at: -1 });

// Text search db.articles.createIndex({ title: "text", content: "text" });

// Geospatial db.stores.createIndex({ location: "2dsphere" });

Migration Best Practices

Practice WHY

Always reversible Need to rollback

Backward compatible Zero-downtime deploys

Schema before data Separate concerns

Test on staging Catch issues early

Adding a Column (Zero-Downtime)

-- Step 1: Add nullable column ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Step 2: Deploy code that writes to new column

-- Step 3: Backfill existing rows UPDATE users SET phone = '' WHERE phone IS NULL;

-- Step 4: Make required (if needed) ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;

Renaming a Column (Zero-Downtime)

-- Step 1: Add new column ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Step 2: Copy data UPDATE users SET email_address = email;

-- Step 3: Deploy code reading from new column -- Step 4: Deploy code writing to new column

-- Step 5: Drop old column ALTER TABLE users DROP COLUMN email;

Migration Template

-- Migration: YYYYMMDDHHMMSS_description.sql

-- UP BEGIN; ALTER TABLE users ADD COLUMN phone VARCHAR(20); CREATE INDEX idx_users_phone ON users(phone); COMMIT;

-- DOWN BEGIN; DROP INDEX idx_users_phone ON users; ALTER TABLE users DROP COLUMN phone; COMMIT;

Query Analysis

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

Look For Meaning

type: ALL Full table scan (bad)

type: ref Index used (good)

key: NULL No index used

rows: high Many rows scanned

N+1 Query Problem

BAD: N+1 queries

orders = db.query("SELECT * FROM orders") for order in orders: customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")

GOOD: Single JOIN

results = db.query(""" SELECT orders.*, customers.name FROM orders JOIN customers ON orders.customer_id = customers.id """)

Optimization Techniques

Technique When to Use

Add indexes Slow WHERE/ORDER BY

Denormalize Expensive JOINs

Pagination Large result sets

Caching Repeated queries

Read replicas Read-heavy load

Partitioning Very large tables

Extension Points

Database-Specific Patterns: Add MySQL vs PostgreSQL vs SQLite variations
Advanced Patterns: Time-series, event sourcing, CQRS, multi-tenancy
ORM Integration: TypeORM, Prisma, SQLAlchemy patterns
Monitoring: Query performance tracking, slow query alerts

database-schema-designer

Safety Notice

Copy this and send it to your AI assistant to learn

BAD: N+1 queries

GOOD: Single JOIN

Source Transparency

Related Skills

senior-data-scientist

senior-backend

senior-frontend