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:
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)
CREATE TABLE orders (
id INT PRIMARY KEY,
product_ids VARCHAR(255)
);
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)
CREATE TABLE order_items (
order_id INT,
product_id INT,
customer_name VARCHAR(100),
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100)
);
3rd Normal Form (3NF)
CREATE TABLE customers (
id INT PRIMARY KEY,
postal_code VARCHAR(10),
country VARCHAR(50)
);
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 |
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
total_amount DECIMAL(10,2),
item_count INT
);
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 |
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 |
price DECIMAL(10, 2)
price FLOAT
Date/Time Types
DATE
