database-schema-design
Comprehensive database schema design patterns for PostgreSQL and MySQL with normalization, relationships, constraints, and error prevention.
Quick Start (10 Minutes)
Step 1: Choose your schema pattern from templates:
cat templates/basic-schema.sql
cat templates/relationships.sql
cat templates/constraints.sql
cat templates/audit-columns.sql
Step 2: Apply normalization rules (at minimum 3NF):
- 1NF: No repeating groups, atomic values
- 2NF: No partial dependencies on composite keys
- 3NF: No transitive dependencies
- Load
references/normalization-guide.md for detailed examples
Step 3: Add essential elements to every table:
CREATE TABLE your_table (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(200) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
Critical Rules
✓ Always Do
| Rule |
Reason |
| Every table has PRIMARY KEY |
Ensures row uniqueness, enables relationships |
| Foreign keys defined explicitly |
Enforces referential integrity, prevents orphans |
| Index all foreign keys |
Prevents slow JOINs, critical for performance |
| NOT NULL on required fields |
Data integrity, prevents NULL pollution |
| Audit columns (created_at, updated_at) |
Track changes, debugging, compliance |
| Appropriate data types |
Storage efficiency, validation, indexing |
| Check constraints for enums |
Enforces valid values at database level |
| ON DELETE/UPDATE rules specified |
Prevents accidental data loss or orphans |
✗ Never Do
| Anti-Pattern |
Why It's Bad |
| VARCHAR(MAX) everywhere |
Wastes space, slows indexes, no validation |
| Dates as VARCHAR |
No date math, no validation, sorting broken |
| Missing foreign keys |
No referential integrity, orphaned records |
| Premature denormalization |
Hard to maintain, data anomalies |
| EAV (Entity-Attribute-Value) |
Query complexity, no type safety, slow |
| Polymorphic associations |
No foreign key integrity, complex queries |
| Circular dependencies |
Impossible to populate, breaks CASCADE |
| No indexes on foreign keys |
Extremely slow JOINs, performance killer |
Top 7 Critical Errors
Error 1: Missing Primary Key
Symptom: Cannot uniquely identify rows, duplicate data
Fix:
CREATE TABLE users (
email VARCHAR(255),
name VARCHAR(100)
);
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL
);
Error 2: No Foreign Key Constraints
Symptom: Orphaned records, data inconsistency
Fix:
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_id UUID
);
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
Error 3: VARCHAR(MAX) Everywhere
Symptom: Wasted space, slow indexes, no validation
Fix:
CREATE TABLE products (
name VARCHAR(MAX),
sku VARCHAR(MAX),
status VARCHAR(MAX)
);
CREATE TABLE products (
name VARCHAR(200) NOT NULL,
sku VARCHAR(50) UNIQUE NOT NULL,
status VARCHAR(20) NOT NULL
CHECK (status IN ('draft', 'active', 'archived'))
);
Error 4: Wrong Data Types (Dates as Strings)
Symptom: No date validation, broken sorting, no date math
Fix:
CREATE TABLE events (
event_date VARCHAR(50)
);
CREATE TABLE events (
event_date DATE NOT NULL,
event_time TIMESTAMPTZ
);
Error 5: No Indexes on Foreign Keys
Symptom: Extremely slow JOINs, poor query performance
Fix:
CREATE TABLE order_items (
order_id UUID NOT NULL REFERENCES orders(id),
product_id UUID NOT NULL REFERENCES products(id)
);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
Error 6: Missing Audit Columns
Symptom: Cannot track when records created/modified
Fix:
CREATE TABLE products (
id UUID PRIMARY KEY,
name VARCHAR(200)
);
CREATE TABLE products (
id UUID PRIMARY KEY,
name VARCHAR(200) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
Error 7: EAV Anti-Pattern
Symptom: Complex queries, no type safety, slow performance
Fix:
CREATE TABLE product_attributes (
product_id UUID,
attribute_name VARCHAR(100),
attribute_value TEXT
);
CREATE TABLE products (
id UUID PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL,
color VARCHAR(50),
size VARCHAR(20),
attributes JSONB
);
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);
Load references/error-catalog.md for all 12 errors with detailed fixes.
Common Schema Patterns
<
