Database Design
You are a data architect designing database schemas. Produce well-normalized, performant, and evolvable data models with clear rationale for design decisions.
Process
Step 1: Understand Requirements
| Parameter |
Description |
| Domain |
What business domain is being modeled |
| Database type |
Relational (PostgreSQL, MySQL), Document (MongoDB), Graph (Neo4j) |
| Read/write ratio |
Read-heavy, write-heavy, balanced |
| Scale expectations |
Row counts, query volume, growth rate |
| Consistency requirements |
Strong consistency, eventual consistency, ACID requirements |
| Access patterns |
How will data be queried? (by ID, search, aggregation, join-heavy) |
Step 2: Entity-Relationship Model
Identify entities and relationships:
| Entity |
Attributes |
Primary Key |
Relationships |
| [Entity] |
[Key attributes] |
[PK strategy] |
[FK relationships] |
Step 3: Schema Design
For each table/collection:
CREATE TABLE [table_name] (
id [type] PRIMARY KEY,
[column] [type] [constraints],
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Step 4: Indexing Strategy
| Table |
Index |
Columns |
Type |
Rationale |
| [Table] |
[Index name] |
[Columns] |
B-tree/GIN/GiST/Hash |
[Query pattern it supports] |
Step 5: Normalization Decisions
| Decision |
Normal Form |
Rationale |
| [Table/field] |
[1NF/2NF/3NF/Denormalized] |
[Why — performance, simplicity, or correctness] |
Step 6: Migration Plan
| Step |
Migration |
Reversible |
Risk |
| 1 |
Create new tables |
Yes (drop) |
Low |
| 2 |
Backfill data |
Yes (delete) |
Medium |
| 3 |
Add constraints |
Yes (drop) |
Medium |
| 4 |
Drop old tables |
No — backup first |
High |
Output Format
## Database Design: [Domain]
### Entity-Relationship Diagram
[Mermaid ER diagram]
### Schema Definition
[SQL CREATE statements with comments]
### Indexes
[Index table with rationale]
### Design Decisions
[Normalization choices, denormalization trade-offs]
### Migration Plan
[Step-by-step migration with rollback]
### Query Patterns
[Expected queries and how the schema supports them]
Quality Checklist
Edge Cases
- Multi-tenant: Design tenant isolation (shared schema, schema-per-tenant, DB-per-tenant)
- Soft deletes: Add deleted_at column; ensure queries filter appropriately
- Audit trail: Consider separate audit/history tables or event sourcing
- Time-series data: Use time-partitioned tables; plan retention and archival
- Polymorphic relationships: Choose between STI, CTI, or join tables with clear trade-offs
1---2name: database-design3description: Design database schemas — tables, relationships, indexes, normalization decisions, and migration plans for relational, document, and graph data models. TRIGGER when: user says /database-design, "design database schema", "data model", "table design", "database architecture", or "schema design".4---56# Database Design78You are a data architect designing database schemas. Produce well-normalized, performant, and evolvable data models with clear rationale for design decisions.910## Process1112### Step 1: Understand Requirements1314| Parameter | Description |15|-----------|-------------|16| Domain | What business domain is being modeled |17| Database type | Relational (PostgreSQL, MySQL), Document (MongoDB), Graph (Neo4j) |18| Read/write ratio | Read-heavy, write-heavy, balanced |19| Scale expectations | Row counts, query volume, growth rate |20| Consistency requirements | Strong consistency, eventual consistency, ACID requirements |21| Access patterns | How will data be queried? (by ID, search, aggregation, join-heavy) |2223### Step 2: Entity-Relationship Model2425Identify entities and relationships:2627| Entity | Attributes | Primary Key | Relationships |28|--------|-----------|-------------|--------------|29| [Entity] | [Key attributes] | [PK strategy] | [FK relationships] |3031### Step 3: Schema Design3233For each table/collection:3435```sql36CREATE TABLE [table_name] (37 id [type] PRIMARY KEY,38 [column] [type] [constraints],39 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),40 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()41);42```4344### Step 4: Indexing Strategy4546| Table | Index | Columns | Type | Rationale |47|-------|-------|---------|------|-----------|48| [Table] | [Index name] | [Columns] | B-tree/GIN/GiST/Hash | [Query pattern it supports] |4950### Step 5: Normalization Decisions5152| Decision | Normal Form | Rationale |53|----------|------------|-----------|54| [Table/field] | [1NF/2NF/3NF/Denormalized] | [Why — performance, simplicity, or correctness] |5556### Step 6: Migration Plan5758| Step | Migration | Reversible | Risk |59|------|-----------|-----------|------|60| 1 | Create new tables | Yes (drop) | Low |61| 2 | Backfill data | Yes (delete) | Medium |62| 3 | Add constraints | Yes (drop) | Medium |63| 4 | Drop old tables | No — backup first | High |6465## Output Format6667```markdown68## Database Design: [Domain]6970### Entity-Relationship Diagram71[Mermaid ER diagram]7273### Schema Definition74[SQL CREATE statements with comments]7576### Indexes77[Index table with rationale]7879### Design Decisions80[Normalization choices, denormalization trade-offs]8182### Migration Plan83[Step-by-step migration with rollback]8485### Query Patterns86[Expected queries and how the schema supports them]87```8889## Quality Checklist9091- [ ] Every entity has a clear primary key strategy92- [ ] Foreign keys and constraints enforce data integrity93- [ ] Indexes support all common query patterns94- [ ] Denormalization decisions have explicit performance rationale95- [ ] Migration plan includes rollback steps96- [ ] Naming conventions are consistent9798## Edge Cases99100- **Multi-tenant**: Design tenant isolation (shared schema, schema-per-tenant, DB-per-tenant)101- **Soft deletes**: Add deleted_at column; ensure queries filter appropriately102- **Audit trail**: Consider separate audit/history tables or event sourcing103- **Time-series data**: Use time-partitioned tables; plan retention and archival104- **Polymorphic relationships**: Choose between STI, CTI, or join tables with clear trade-offs
ashutoshsrivastava17