db-schema-designer

DB Schema Designer - Database Schema Design

Overview

Transforms domain descriptions into optimized database schemas with migrations, indexes, and relationship diagrams.

---

1. When to Apply

Trigger	Behavior
New project data modeling	Full schema design workflow
"database schema", "ERD"	Interactive schema builder
Adding new entity/table	Incremental schema update

---

2. Design Workflow

Step 1: Domain Analysis

From user description, extract:

• Entities: Nouns → tables/collections
• Relationships: Verbs → foreign keys/references
• Attributes: Properties → columns/fields
• Cardinality: one-to-one, one-to-many, many-to-many

Step 2: Schema Generation

Prisma Example:

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
}

model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  String
  tags      Tag[]
  createdAt DateTime @default(now())

  @@index([authorId])
  @@index([published, createdAt])
}

Step 3: Index Strategy

## Index Recommendations

| Table | Index | Type | Reason |
|-------|-------|------|--------|
| users | email | UNIQUE | Login lookup |
| posts | authorId | B-TREE | Filter by author |
| posts | (published, createdAt) | COMPOSITE | Published feed query |
| posts | title | GIN (trigram) | Full-text search |

Step 4: Migration Script

Generate migration for chosen ORM/tool.

Step 5: ERD (Mermaid)

erDiagram
    User ||--o{ Post : writes
    Post }o--o{ Tag : has
    User {
        string id PK
        string email UK
        string name
        enum role
    }
    Post {
        string id PK
        string title
        string content
        boolean published
        string authorId FK
    }