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 

...