---
name: Database Schema Designer
description: A comprehensive skill for designing production-ready database schemas with built-in best practices for both SQL and NoSQL databases.
---

# Database Schema Designer

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

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

`npx skills add https://github.com/softaworks/agent-toolkit --skill database-schema-designer`Download ZIPGitHub

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

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

TermDefinitionNormalizationOrganizing data to reduce redundancy (1NF → 2NF → 3NF)3NFThird Normal Form - no transitive dependencies between columnsOLTPOnline Transaction Processing - write-heavy, needs normalizationOLAPOnline Analytical Processing - read-heavy, benefits from denormalizationForeign Key (FK)Column that references another table's primary keyIndexData structure that speeds up queries (at cost of slower writes)Access PatternHow your app reads/writes data (queries, joins, filters)DenormalizationIntentionally duplicating data to speed up reads

## Quick Reference

TaskApproachKey ConsiderationNew schemaNormalize to 3NF firstDomain modeling over UISQL vs NoSQLAccess patterns decideRead/write ratio mattersPrimary keysINT or UUIDUUID for distributed systemsForeign keysAlways constrainON DELETE strategy criticalIndexesFKs + WHERE columnsColumn order mattersMigrationsAlways reversibleBackward 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

CommandWhen to UseAction`design schema for {domain}`Starting freshFull schema generation`normalize {table}`Fixing existing tableApply normalization rules`add indexes for {table}`Performance issuesGenerate index strategy`migration for {change}`Schema evolutionCreate reversible migration`review schema`Code reviewAudit existing schema
Workflow: Start with `design schema` → iterate with `normalize` → optimize with `add indexes` → evolve with `migration`

## Core Principles

PrincipleWHYImplementationModel the DomainUI changes, domain doesn'tEntity names reflect business conceptsData Integrity FirstCorruption is costly to fixConstraints at database levelOptimize for Access PatternCan't optimize for bothOLTP: normalized, OLAP: denormalizedPlan for ScaleRetrofitting is painfulIndex strategy + partitioning plan

## Anti-Patterns

AvoidWhyInsteadVARCHAR(255) everywhereWastes storage, hides intentSize appropriately per fieldFLOAT for moneyRounding errorsDECIMAL(10,2)Missing FK constraintsOrphaned dataAlways define foreign keysNo indexes on FKsSlow JOINsIndex every foreign keyStoring dates as stringsCan't compare/sortDATE, TIMESTAMP typesSELECT * in queriesFetches unnecessary dataExplicit column listsNon-reversible migrationsCan't rollbackAlways write DOWN migrationAdding NOT NULL without defaultBreaks existing rowsAdd 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

Deep Dive: Normalization (SQL)

### Normal Forms

FormRuleViolation Example1NFAtomic values, no repeating groups`product_ids = '1,2,3'`2NF1NF + no partial dependenciescustomer_name in order_items3NF2NF + no transitive dependenciescountry 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

ScenarioDenormalization StrategyRead-heavy reportingPre-calculated aggregatesExpensive JOINsCached derived columnsAnalytics dashboardsMaterialized views

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

Deep Dive: Data Types

### String Types

TypeUse CaseExampleCHAR(n)Fixed lengthState codes, ISO datesVARCHAR(n)Variable lengthNames, emailsTEXTLong contentArticles, descriptions

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

### Numeric Types

TypeRangeUse CaseTINYINT-128 to 127Age, status codesSMALLINT-32K to 32KQuantitiesINT-2.1B to 2.1BIDs, countsBIGINTVery largeLarge IDs, timestampsDECIMAL(p,s)Exact precisionMoneyFLOAT/DOUBLEApproximateScientific 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
`
```

Deep Dive: Indexing Strategy

### When to Create Indexes

Always IndexReasonForeign keysSpeed up JOINsWHERE clause columnsSpeed up filteringORDER BY columnsSpeed up sortingUnique constraintsEnforced 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

TypeBest ForExampleB-TreeRanges, equality`price > 100`HashExact matches only`email = '[email protected]'`Full-textText search`MATCH AGAINST`PartialSubset 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

PitfallProblemSolutionOver-indexingSlow writesOnly index what's queriedWrong column orderUnused indexMatch query patternsMissing FK indexesSlow JOINsAlways index FKs

Deep Dive: Constraints

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

StrategyUse WhenCASCADEDependent data (order_items)RESTRICTImportant references (prevent accidents)SET NULLOptional 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
`
```

Deep Dive: Relationship Patterns

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

Deep Dive: NoSQL Design (MongoDB)

### Embedding vs Referencing

FactorEmbedReferenceAccess patternRead togetherRead separatelyRelationship1:few1:manyDocument sizeSmallApproaching 16MBUpdate frequencyRarelyFrequently

### Embedded Document

```
`{
"_id": "order_123",
"customer": {
"id": "cust_456",
"name": "Jane Smith",
"email": "[email protected]"
},
"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" });
`
```

Deep Dive: Migrations

### Migration Best Practices

PracticeWHYAlways reversibleNeed to rollbackBackward compatibleZero-downtime deploysSchema before dataSeparate concernsTest on stagingCatch 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;
`
```

Deep Dive: Performance Optimization

### Query Analysis

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

Look ForMeaningtype: ALLFull table scan (bad)type: refIndex used (good)key: NULLNo index usedrows: highMany 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

TechniqueWhen to UseAdd indexesSlow WHERE/ORDER BYDenormalizeExpensive JOINsPaginationLarge result setsCachingRepeated queriesRead replicasRead-heavy loadPartitioningVery 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

## Related Skills
install-vscode-extensionby microsoftHow to install a VS Code extension from an extension ID. Useful when the user wants to add new capabilities to their VS Code environment by installing…wp-playgroundby automatticUse for WordPress Playground workflows: fast disposable WP instances in the browser or locally via @wp-playground/cli (server, run-blueprint, build-snapshot),…encore-go-authby encoredevImplement authentication with Encore Go.push-to-registryby hashicorpPush Packer build metadata to HCP Packer registry for tracking and managing image lifecycle. Use when integrating Packer builds with HCP Packer for version…stripe-best-practicesby anthropicAuthoritative guidance for implementing Stripe payment integrations across all use cases. Prioritizes Checkout Sessions API for on-session payments and subscriptions; recommends Stripe-hosted or embedded Checkout as the primary web integration surface Covers payment flows, subscription models, webhooks, Connect platforms, and fund management with explicit guidance on modern APIs versus deprecated endpoints Includes pre-launch checklist requirements, PCI compliance considerations, and...triaging-issuesby pytorchTriages GitHub issues by routing to oncall teams, applying labels, and closing questions. Use when processing new PyTorch issues or when asked to triage an…cellxgene-skillby openaiSubmit compact CELLxGENE Discover API requests for public collection and dataset metadata. Use when a user wants concise single-cell collection summariesbreak-traceby anthropicRoot-cause a reconciliation break to its source transaction or posting — follow the audit trail from the break row back to the originating entry on each side…

---

**Source**: https://github.com/softaworks/agent-toolkit/tree/main/skills/database-schema-designer
**Author**: softaworks
**Discovered via**: mcpservers.org