name: database-designer description: "Comprehensive database design tool creating complete schemas with tables, fields, indexes, and ER diagrams. Use when users request database design for any system. Generates Markdown docs, SQL scripts, and DrawDB-compatible JSON/DBML files. Supports MySQL, PostgreSQL, SQL Server. Triggers: database design, schema, ER diagram, SQL, data model. | 全面的数据库设计工具，创建完整的数据库架构。触发词：数据库设计、数据库架构、ER图、SQL、数据模型、表设计。"

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:

Database type: MySQL 8.0 (default), PostgreSQL, SQL Server, etc.
Business domain: E-commerce, blog, CRM, ERP, etc.
Core entities: What are the main tables needed?
Key features: What functionality should the database support?
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:

WHERE clause fields: Fields frequently used in filtering
JOIN fields: All xxx_id foreign key fields
ORDER BY / GROUP BY fields: Sorting and grouping fields
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:

One-to-Many (1:N): Most common
- Example: user (1) → order (N)
- Implementation: Add user_id in order table
- DrawDB: many-to-one relationship
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
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:

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

Quality Verification

Before finalizing, verify:

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

database-designer

$ Instalar

Database Designer

Core Design Principles

Workflow

Step 1: Understand Requirements

Step 2: Load Reference Documentation

Step 3: Design Database Schema

Table Design Checklist

Index Design Strategy

Relationship Design

Step 4: Generate Outputs

4.1 Comprehensive Design Document (Markdown)

4.2 SQL Script

4.3 DrawDB JSON Format

4.4 DrawDB DBML Format

Step 5: Finalize and Deliver

5.1 Output Directory Convention

5.2 Output File List

5.3 File Naming Convention

5.4 Delivery Summary

Special Handling

When User Says "No System Fields"

When User Specifies Non-MySQL Database

When Requirements Are Vague

Quality Verification

Example Invocations

References

Repository

Actions

Related Skills