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

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

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