database-designer

Database Designer

Design complete, production-ready database schemas based on user requirements. Generate comprehensive documentation, SQL scripts, and visual ER diagram files compatible with DrawDB.

Core Design Principles

Before starting any design, always read references/design-principles.md to understand:

• No physical foreign keys (logical relationships only)
• Realistic field sizes based on actual usage
• Minimal, strategic index design
• Mandatory comments on all tables and fields
• Default system fields (id, timestamps, soft delete)
• snake_case naming conventions

Workflow

Step 1: Understand Requirements

Gather information about the database design:

1. Database type: MySQL 8.0 (default), PostgreSQL, SQL Server, etc.
2. Business domain: E-commerce, blog, CRM, ERP, etc.
3. Core entities: What are the main tables needed?
4. Key features: What functionality should the database support?
5. Special requirements: Any specific constraints or preferences?

If the user provides minimal information, intelligently infer missing details based on common business scenarios and best practices documented in references/design-examples.md.

Key inference scenarios:

• User says "design a user table" → Infer: username, password, email, phone, status
• User says "e-commerce system" → Infer: user, product, order, order_detail tables
• User says "blog system" → Infer: user, article, comment, tag, article_tag tables

Step 2: Load Reference Documentation

Based on the design requirements, load appropriate references:

• Always load: references/design-principles.md for core design rules
• For format generation: references/drawdb-formats.md for JSON/DBML specifications
• For examples: references/design-examples.md for similar system designs

Step 3: Design Database Schema

Create complete table structures following these guidelines:

Table Design Checklist

For each table:

• ✅ Add default system fields (unless user specifies otherwise):

  • id BIGINT AUTO_INCREMENT PRIMARY KEY
  • created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  • updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  • is_deleted TINYINT(1) DEFAULT 0

• ✅ Design business fields with realistic sizes:

  • username: VARCHAR(50)
  • email: VARCHAR(100)
  • phone: VARCHAR(20)
  • title: VARCHAR(200)
  • description: TEXT
  • See references/design-principles.md for complete size standards

• ✅ Add appropriate constraints:

  • NOT NULL for required fields
  • DEFAULT values for optional fields
  • UNIQUE for fields requiring uniqueness (but not as foreign keys)

• ✅ Add COMMENT to every table and field (in Chinese)

• ✅ Do NOT create physical FOREIGN KEY constraints

Index Design Strategy

For each table, analyze and create indexes for:

1. WHERE clause fields: Fields frequently used in filtering
2. JOIN fields: All xxx_id foreign key fields
3. ORDER BY / GROUP BY fields: Sorting and grouping fields
4. Unique constraints: email, phone, order_no, etc. → UNIQUE indexes

Index naming conventions:

• Ordinary index: idx_field_name or idx_field1_field2
• Unique index: uk_field_name

Index limits:

• Maximum 5 indexes per table (unless user requests more)
• Only create indexes that directly support business queries

Relationship Design

Identify logical relationships between tables:

1. One-to-Many (1:N): Most common

   • Example: user (1) → order (N)
   • Implementation: Add user_id in order table
   • DrawDB: many-to-one relationship

2. One-to-One (1:1): For table splitting

   • Example: user (1) → user_profile (1)
   • Implementation: Add user_id UNIQUE in user_profile table
   • DrawDB: one-to-one relationship

3. Many-to-Many (N:N): Requires junction table

   • Example: article (N) ↔ tag (N)
   • Implementation: Create article_tag junction table with article_id + tag_id
   • DrawDB: Two many-to-one relationships

Step 4: Generate Outputs

Create all required output files:

4.1 Comprehensive Design Document (Markdown)

Create a single, well-structured Markdown file containing:

Structure:

# [Project Name] 数据库设计文档

## 1. 数据库概览
- 数据库类型
- 字符集
- 核心表数量
- 主要功能模块

## 2. 表结构设计

### 2.1 [Table Name]
**表名**: table_name
**说明**: Table description

**字段列表**:
| 字段名 | 类型 | 允许空 | 默认值 | 说明 |
|--------|------|--------|--------|------|
| id | BIGINT | NO | | Primary key |
| ... | ... | ... | ... | ... |

**索引列表**:
| 索引名 | 类型 | 字段 |
|--------|------|------|
| uk_email | UNIQUE | email |
| idx_username | INDEX | username |

### 2.2 [Next Table]
...

## 3. 表关系说明
- table1 → table2 (1:N): Description
- table3 ↔ table4 (N:N): Description via junction table

## 4. 索引策略说明
Explain the rationale behind index design decisions

4.2 SQL Script

Create executable SQL script with:

-- Database: project_name
-- Generated: YYYY-MM-DD

-- Drop tables if exists (in reverse dependency order)
DROP TABLE IF EXISTS `table3`;
DROP TABLE IF EXISTS `table2`;
DROP TABLE IF EXISTS `table1`;

-- Create tables (in dependency order)
CREATE TABLE `table1` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Comment',
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_field` (`field`),
  KEY `idx_field` (`field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table comment';

CREATE TABLE `table2` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table comment';

4.3 DrawDB JSON Format

Generate DrawDB-compatible JSON file following references/drawdb-formats.md:

Critical Format Requirements:

• Table IDs: Use random 21-character strings (e.g., "_KV5MtPf2m4sI7Inu8pat")
• Field IDs: Use random 21-character strings (e.g., "B8rPRTDtOv9oD2Gp4bhWL")
• Index IDs: Use numeric increments starting from 0 (across ALL tables)
• Relationship IDs: Use random 21-character strings
• Field type: Include complete type definition (e.g., "VARCHAR(100)", not separate type and size)
• No size field: Type info is embedded in the type string
• Index fields: Use field name strings, NOT field IDs (e.g., ["email"] not [1])
• Coordinates: Use floating-point numbers for x/y positions

ID Generation:

import random
import string

def generate_id():
    chars = string.ascii_letters + string.digits + '_-'
    return ''.join(random.choice(chars) for _ in range(21))

Layout Strategy:

• Use grid layout: x = (index % 3) * 450 + 50, y = floor(index / 3) * 400 + 50
• Rotate through predefined colors: ["#6360f7", "#bc49c4", "#ffe159", "#89e667", "#ff9159", "#59d9ff", "#ff5959", "#a0a0a0"]

Relationship Generation:

• Generate relationships for all xxx_id foreign key fields
• Use the actual random string IDs for tables and fields
• Set cardinality based on UNIQUE constraint:
  • Has UNIQUE → "one_to_one"
  • No UNIQUE → "many_to_one"

Structure:

{
  "tables": [
    {
      "id": "random_string",
      "name": "table_name",
      "comment": "表注释",
      "color": "#6360f7",
      "fields": [
        {
          "id": "random_string",
          "name": "id",
          "type": "BIGINT",
          "default": "",
          "check": "",
          "primary": true,
          "unique": true,
          "notNull": true,
          "increment": true,
          "comment": "主键ID"
        }
      ],
      "indices": [
        {
          "id": 0,
          "fields": ["field_name"],
          "name": "idx_field_name",
          "unique": false
        }
      ],
      "x": 50.0,
      "y": 50.0
    }
  ],
  "relationships": [
    {
      "name": "",
      "startTableId": "random_string",
      "endTableId": "random_string",
      "endFieldId": "random_string",
      "startFieldId": "random_string",
      "id": "random_string",
      "updateConstraint": "No action",
      "deleteConstraint": "No action",
      "cardinality": "many_to_one"
    }
  ],
  "notes": [],
  "subjectAreas": [],
  "database": "generic",
  "types": [],
  "title": "Project Database"
}

4.4 DrawDB DBML Format

Generate DBML file following references/drawdb-formats.md:

Key points:

• Use lowercase data types: bigint, varchar(50), datetime
• Add attributes in brackets: [pk, increment, not null, unique, note: '注释']
• Define indexes inside table definition in indexes { } block
• Define relationships outside tables using Ref blocks
• Use proper relationship symbols: > (many-to-one), - (one-to-one)

Structure:

Table table_name [headercolor: #6360f7] {
  id bigint [pk, increment, not null, unique, note: 'Comment']
  field varchar(100) [not null, note: 'Comment']
  
  indexes {
    field [unique, name: 'uk_field']
  }
  
  Note: 'Table comment'
}

Ref fk_name {
  table1.field > table2.id [delete: no action, update: no action]
}

Step 5: Finalize and Deliver

5.1 Output Directory Convention

Recommended Approach (Following Claude Code Official Standards):

Save all database design files to outputs/<project-name>/database/:

outputs/
└── <project-name>/              # Project name (e.g., e-commerce-system)
    └── database/
        ├── schema-design.md     # Comprehensive design document
        ├── schema.sql           # Executable SQL script
        ├── drawdb-schema.json   # DrawDB JSON format
        └── drawdb-schema.dbml   # DrawDB DBML format

Example:

outputs/
├── e-commerce-system/
│   └── database/
│       ├── schema-design.md
│       ├── schema.sql
│       ├── drawdb-schema.json
│       └── drawdb-schema.dbml
└── task-management-app/
    └── database/
        ├── schema-design.md
        └── schema.sql

Alternative Approach (Traditional Project Structure):

If your project has an existing directory structure, you can also use:

project-root/
└── database/
    ├── schema-design.md
    ├── schema.sql
    ├── drawdb-schema.json
    └── drawdb-schema.dbml

5.2 Output File List

Required Outputs (4 files):

• schema-design.md - Comprehensive database design document (Chinese)
• schema.sql - Executable SQL script
• drawdb-schema.json - DrawDB JSON format
• drawdb-schema.dbml - DrawDB DBML format

Optional Outputs:

• er-diagram.png - ER diagram visualization (if tools available)
• index-strategy.md - Index strategy documentation

5.3 File Naming Convention

• Use kebab-case: user-authentication-schema.sql
• Include version/date when needed: schema-v1.0.sql or schema-2024-12-10.sql
• Use descriptive names: e-commerce-database-schema.sql

5.4 Delivery Summary

After generating all files, provide a summary with:

• Brief overview of design decisions
• Number of tables created
• Key relationships and their rationale
• How to import into DrawDB (JSON or DBML)
• File save location confirmation
• Next steps suggestions (e.g., review indexes, implement in development environment)

Special Handling

When User Says "No System Fields"

Only create minimal structure:

CREATE TABLE `table_name` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  /* business fields only */
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table comment';

When User Specifies Non-MySQL Database

Adapt SQL syntax accordingly:

PostgreSQL:

• Use SERIAL or BIGSERIAL instead of AUTO_INCREMENT
• Use TIMESTAMP instead of DATETIME
• Different syntax for defaults and constraints

SQL Server:

• Use IDENTITY(1,1) instead of AUTO_INCREMENT
• Use DATETIME2 instead of DATETIME
• Use NVARCHAR for Unicode support

Oracle:

• Use SEQUENCE for auto-increment
• Use VARCHAR2 instead of VARCHAR
• Use DATE or TIMESTAMP for time fields

When Requirements Are Vague

Apply intelligent inference:

1. Identify the business domain (e-commerce, blog, CRM, etc.)
2. Reference similar examples in references/design-examples.md
3. Include common fields appropriate to the domain
4. Design reasonable indexes based on typical query patterns
5. Explain assumptions made in the design document

Quality Verification

Before finalizing, verify:

• All tables have COMMENT
• All fields have COMMENT
• Field sizes are realistic (not generic VARCHAR(255))
• Index count per table ≤ 5
• No physical FOREIGN KEY constraints
• System fields added (unless user specified otherwise)
• All names use snake_case
• Logical relationships documented clearly
• SQL syntax matches target database type
• JSON/DBML formats follow DrawDB specifications
• All foreign key relationships have corresponding Ref entries

Example Invocations

User says: "设计一个电商系统的数据库，包括用户、商品、订单功能" → Read design-principles.md and design-examples.md → Design: user_info, product_info, order_info, order_detail tables → Generate all 4 output files

User says: "Design a blog database" → Infer: user, article, comment, tag, article_tag tables → Follow standard design principles → Generate all outputs

User says: "Create a user table for PostgreSQL" → Single table design → Adapt SQL syntax for PostgreSQL → Include in all output formats

References

• references/design-principles.md - Core design rules and standards
• references/drawdb-formats.md - JSON and DBML format specifications
• references/design-examples.md - Real-world design examples