database-designer

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:

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