xiaowen-jiang/agent-enterprise/database-schema-design

Process

1. Identify entities — Map domain objects to tables (users, orders, products, etc.)
2. Define relationships — One-to-one, one-to-many, many-to-many
3. Normalize to 3NF — Eliminate redundancy, ensure each fact is stored once
4. Choose primary keys — UUID vs auto-increment, composite keys when needed
5. Design indexes — Based on query patterns (WHERE, JOIN, ORDER BY)
6. Plan constraints — NOT NULL, UNIQUE, FOREIGN KEY, CHECK
7. Write migration — CREATE TABLE statements, ALTER TABLE for existing schemas

Output Format

## Schema Design: [Feature/Module Name]
 
### Entity-Relationship Summary
- User (1) → (N) Order
- Order (N) ← → (N) Product (via order_items)
 
### Tables
 
#### users
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | UUID | PK | |
| email | VARCHAR(255) | UNIQUE, NOT NULL | Login identifier |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW() | |
 
#### orders
| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| id | UUID | PK | |
| user_id | UUID | FK → users.id, NOT NULL | |
 
### Indexes
- users(email) — unique lookup
- orders(user_id, created_at) — user's recent orders
 
### Migration SQL
[CREATE TABLE / ALTER TABLE statements]

Guidelines

• Prefer UUIDs for primary keys in distributed systems, auto-increment for simple apps
• Always add created_at and updated_at timestamps
• Use soft deletes (deleted_at) for data that might need recovery
• Index foreign keys — they're used in JOINs
• Don't over-normalize — denormalize for read-heavy queries when justified
• Include rollback SQL in migrations (DROP TABLE, ALTER TABLE DROP COLUMN)
• Consider data volume and growth rate when designing indexes