Database Schema Designer

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

---

Key Terms

---

Quick Reference

---

Process Overview

Your Data Requirements
    |
    v
+-----------------------------------------------------+
| Phase 1: ANALYSIS                                   |
| * Identify entities and relationships               |
| * Determine access patterns (read vs write heavy)   |
| * Choose SQL or NoSQL based on requirements         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 2: DESIGN                                     |
| * Normalize to 3NF (SQL) or embed/reference (NoSQL) |
| * Define primary keys and foreign keys              |
| * Choose appropriate data types                     |
| * Add constraints (UNIQUE, CHECK, NOT NULL)         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 3: OPTIMIZE                                   |
| * Plan indexing strategy                            |
| * Consider denormalization for read-heavy queries   |
| * Add timestamps (created_at, updated_at)           |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 4: MIGRATE                                    |
| * Generate migration scripts (up + down)            |
| * Ensure backward compatibility                     |
| * Plan zero-downtime deployment                     |
+-----------------------------------------------------+
    |
    v
Production-Ready Schema

---

Commands

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

---

Core Principles

---

Anti-Patterns

---

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

---

<details> <summary><strong>Deep Dive: Normalization (SQL)</strong></summary>

Normal Forms

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

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

</details>

<details> <summary><strong>Deep Dive: Data Types</strong></summary>

String Types

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

Numeric Types

-- 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

</details>

<details> <summary><strong>Deep Dive: Indexing Strategy</strong></summary>

When to Create Indexes

-- 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

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

</details>

<details> <summary><strong>Deep Dive: Constraints</strong></summary>

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

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

</details>

<details> <summary><strong>Deep Dive: Relationship Patterns</strong></summary>

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
);

</details>

<details> <summary><strong>Deep Dive: NoSQL Design (MongoDB)</strong></summary>

Embedding vs Referencing

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" });

</details>

<details> <summary><strong>Deep Dive: Migrations</strong></summary>

Migration Best Practices

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;

</details>

<details> <summary><strong>Deep Dive: Performance Optimization</strong></summary>

Query Analysis

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

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

</details>

---

Extension Points

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

Database Schema Designer

A comprehensive skill for designing production-ready database schemas with built-in best practices for both SQL and NoSQL databases.

Purpose

The Database Schema Designer skill helps you create robust, scalable database schemas by providing:

• Normalization guidance - Apply proper normal forms (1NF, 2NF, 3NF) to eliminate data redundancy
• Indexing strategies - Optimize query performance with the right indexes
• Migration patterns - Evolve schemas safely with reversible, zero-downtime migrations
• Constraint design - Ensure data integrity with proper foreign keys, checks, and unique constraints
• Performance optimization - Design for your specific access patterns (OLTP vs OLAP)

Whether you are starting a new project or evolving an existing database, this skill ensures your schema follows industry best practices and avoids common pitfalls.

When to Use

Use this skill when you need to:

• Design a new database schema from scratch
• Normalize an existing table structure
• Add indexes to improve query performance
• Create migration scripts for schema changes
• Review and audit existing schemas
• Choose between SQL and NoSQL approaches

Trigger Phrases

How It Works

The skill follows a four-phase process:

Phase 1: Analysis

• Identify entities and their relationships
• Determine access patterns (read-heavy vs write-heavy)
• Choose SQL or NoSQL based on requirements

Phase 2: Design

• Normalize to 3NF for SQL or determine embed/reference strategy for NoSQL
• Define primary keys and foreign keys
• Choose appropriate data types
• Add constraints (UNIQUE, CHECK, NOT NULL)

Phase 3: Optimize

• Plan indexing strategy based on query patterns
• Consider denormalization for read-heavy queries
• Add audit timestamps (created_at, updated_at)

Phase 4: Migrate

• Generate reversible migration scripts (up + down)
• Ensure backward compatibility
• Plan for zero-downtime deployment

Key Features

SQL Schema Design

• Normalization - Automatic application of 1NF, 2NF, and 3NF rules
• Data Types - Appropriate type selection (DECIMAL for money, proper VARCHAR sizing)
• Constraints - Foreign keys with ON DELETE strategies, CHECK constraints, UNIQUE constraints
• Indexes - B-Tree, Hash, Full-text, and Partial index recommendations

NoSQL Schema Design (MongoDB)

• Embedding vs Referencing - Guidance on when to embed documents vs use references
• Index Strategies - Single field, composite, text search, and geospatial indexes
• Document Structure - Optimal document design based on access patterns

Relationship Patterns

• One-to-Many relationships
• Many-to-Many with junction tables
• Self-referencing hierarchies
• Polymorphic associations

Migration Support

• Zero-downtime migration patterns
• Reversible migration templates
• Safe column addition/rename strategies
• Backward compatible changes

Usage Examples

Basic Schema Design

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

Output:

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)
);

Available Commands

Request Tips

Include these details in your request for best results:

• Entities - users, products, orders
• Key relationships - users have orders, orders have items
• Scale hints - high-traffic, millions of records
• Database preference - SQL or NoSQL (defaults to SQL if not specified)
• Access patterns - read-heavy analytics, write-heavy transactions

Prerequisites

No special tools or dependencies required. The skill generates standard SQL or NoSQL schema definitions that work with:

• MySQL / MariaDB
• PostgreSQL
• SQLite
• MongoDB
• And other compatible databases

Output

The skill produces:

1. Schema DDL - Complete CREATE TABLE statements with all constraints 2. Index Definitions - Optimized indexes for your query patterns 3. Migration Scripts - Reversible UP and DOWN migrations 4. Mermaid Diagrams - Entity-relationship diagrams (when requested) 5. Verification Checklist - Items to review before deploying

Verification Checklist

After designing a schema, verify:

• [ ] 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

Best Practices

Do

• Start with domain modeling, not UI requirements
• Normalize to 3NF first, then selectively denormalize
• Use DECIMAL for money (never FLOAT)
• Always define foreign key constraints
• Index every foreign key column
• Size VARCHAR columns appropriately
• Store dates in DATE/TIMESTAMP types
• Always write reversible migrations
• Test migrations on staging with production-like data

Avoid

Key Terminology

License

MIT

Database Schema Design Checklist

Complete checklist for designing and reviewing database schemas.

---

Pre-Design

• [ ] Requirements Gathered: Understand data entities and relationships
• [ ] Access Patterns Identified: Know how data will be queried
• [ ] SQL vs NoSQL Decision: Chosen appropriate database type
• [ ] Scale Estimate: Expected data volume and growth rate
• [ ] Read/Write Ratio: Understand if read-heavy or write-heavy

---

Normalization (SQL)

• [ ] 1NF: Atomic values, no repeating groups
• [ ] 2NF: No partial dependencies on composite keys
• [ ] 3NF: No transitive dependencies
• [ ] Denormalization Justified: If denormalized, reason documented

---

Table Design

Primary Keys

• [ ] Primary Key Defined: Every table has primary key
• [ ] Key Type Chosen: INT auto-increment or UUID
• [ ] Meaningful Keys Avoided: Not using email/username as PK

Data Types

• [ ] Appropriate Types: Correct data types for each column
• [ ] String Sizes: VARCHAR sized appropriately
• [ ] Numeric Precision: DECIMAL for money, INT for counts
• [ ] Dates in UTC: TIMESTAMP for datetime columns

Constraints

• [ ] NOT NULL: Required columns marked NOT NULL
• [ ] Unique Constraints: Unique columns (email, username)
• [ ] Check Constraints: Validation rules (price >= 0)
• [ ] Default Values: Sensible defaults where appropriate

---

Relationships

Foreign Keys

• [ ] Foreign Keys Defined: All relationships have FK constraints
• [ ] ON DELETE Strategy: CASCADE, RESTRICT, SET NULL chosen
• [ ] ON UPDATE Strategy: Usually CASCADE
• [ ] Indexes on Foreign Keys: All FKs are indexed

Relationship Types

• [ ] One-to-Many: Modeled correctly
• [ ] Many-to-Many: Junction table created
• [ ] Self-Referencing: Parent-child relationships handled
• [ ] Polymorphic: Strategy chosen (separate FKs or type+id)

---

Indexing

Index Strategy

• [ ] Primary Key Indexed: Automatic, verify
• [ ] Foreign Keys Indexed: All FKs have indexes
• [ ] WHERE Columns: Columns in WHERE clauses indexed
• [ ] ORDER BY Columns: Sort columns indexed
• [ ] Composite Indexes: Multi-column queries optimized
• [ ] Column Order: Most selective column first

Index Limits

• [ ] Not Over-Indexed: Only necessary indexes
• [ ] Index Maintenance: Aware of write impact

---

Performance

• [ ] Joins Optimized: N+1 queries avoided
• [ ] *SELECT Avoided**: Only fetch needed columns
• [ ] Pagination: LIMIT/OFFSET or cursor-based
• [ ] Aggregations: Pre-calculated for expensive queries

---

Migrations

• [ ] Backward Compatible: New columns nullable initially
• [ ] Up and Down: Rollback scripts provided
• [ ] Data Migrations Separate: Schema vs data separated
• [ ] Tested on Staging: Migrations tested

---

Security

• [ ] Least Privilege: Minimal database permissions
• [ ] Separate Accounts: Read-only vs read-write
• [ ] Sensitive Data: Passwords hashed, PII encrypted
• [ ] Parameterized Queries: SQL injection prevented

---

Documentation

• [ ] ERD Created: Entity-relationship diagram
• [ ] Schema Documented: Column descriptions
• [ ] Indexes Documented: Why each index exists
• [ ] Migration History: Changelog of changes