database-schema-designer

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Database Schema Designer

数据库模式设计指南

Design optimized, scalable database schemas with proper relationships and indexes.

设计经过优化、可扩展的数据库模式，包含合理的关系与索引。

Instructions

操作指南

When a user needs database schema design:

Gather Requirements:
- What type of database (PostgreSQL, MySQL, MongoDB, etc.)?
- What is the application domain?
- What are the main entities/resources?
- What queries will be most common?
- Expected data volume and growth?
- Performance requirements?
- Specific constraints or compliance needs?
Design Schema Following Best Practices:

For SQL Databases:
- Identify entities and their attributes
- Define primary keys (prefer UUIDs for distributed systems)
- Establish relationships (1:1, 1:N, N:M)
- Normalize to 3NF (unless denormalization needed for performance)
- Add appropriate indexes
- Define foreign key constraints
- Include timestamps (created_at, updated_at)
- Add soft delete flags if needed
- Plan for data archival
For NoSQL Databases:
- Design for access patterns (query-first approach)
- Embed vs reference decision
- Plan for denormalization
- Design indexes for common queries
- Consider document size limits
- Plan for eventual consistency

Generate Complete Schema:

SQL Schema Output:

sql

-- [Entity Name] Table
-- Purpose: [Description]

CREATE TABLE [table_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  [field_name] [TYPE] [CONSTRAINTS],
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP
);

-- Indexes
CREATE INDEX idx_[table]_[field] ON [table]([field]);
CREATE INDEX idx_[table]_[field1]_[field2] ON [table]([field1], [field2]);

-- Foreign Keys
ALTER TABLE [child_table]
  ADD CONSTRAINT fk_[constraint_name]
  FOREIGN KEY ([foreign_key_field])
  REFERENCES [parent_table](id)
  ON DELETE CASCADE;

NoSQL Schema Output (MongoDB example):

javascript

// [Collection Name]
// Purpose: [Description]

{
  _id: ObjectId,
  [field_name]: [type],

  // Embedded document
  [embedded_object]: {
    field1: type,
    field2: type
  },

  // Reference
  [related_id]: ObjectId,  // Ref to [other_collection]

  created_at: ISODate,
  updated_at: ISODate
}

// Indexes
db.[collection].createIndex({ field: 1 })
db.[collection].createIndex({ field1: 1, field2: -1 })
db.[collection].createIndex({ field: "text" })  // Text search

Create Entity Relationship Diagram (text format):

┌─────────────────────┐
│      users          │
├─────────────────────┤
│ id (PK)             │
│ email (UNIQUE)      │
│ name                │
│ created_at          │
└──────────┬──────────┘
           │
           │ 1:N
           │
┌──────────▼──────────┐
│      posts          │
├─────────────────────┤
│ id (PK)             │
│ user_id (FK)        │
│ title               │
│ content             │
│ created_at          │
└──────────┬──────────┘
           │
           │ N:M (via post_tags)
           │
┌──────────▼──────────┐
│       tags          │
├─────────────────────┤
│ id (PK)             │
│ name (UNIQUE)       │
└─────────────────────┘

Provide Migration Scripts:

sql

-- Migration: create_users_table
-- Date: 2024-01-15

BEGIN;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

COMMIT;

sql

-- Rollback
BEGIN;
DROP TABLE users;
COMMIT;

Format Complete Output:

🗄️ DATABASE SCHEMA DESIGN
Database: [PostgreSQL/MySQL/MongoDB/etc.]
Domain: [Application type]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📋 ENTITY RELATIONSHIP DIAGRAM
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[ASCII ERD]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📊 TABLE DEFINITIONS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[SQL CREATE TABLE statements]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🔗 RELATIONSHIPS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[Foreign key constraints]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
⚡ INDEXES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[Index definitions with rationale]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🔄 MIGRATION SCRIPTS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[Up and down migrations]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
💡 OPTIMIZATION NOTES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Performance Considerations:
• [Index strategy]
• [Partitioning recommendations]
• [Denormalization opportunities]

Scaling Strategy:
• [Sharding approach]
• [Read replicas]
• [Caching layer]

Data Integrity:
• [Constraint strategy]
• [Validation rules]
• [Audit logging]

Schema Design Best Practices:

Naming Conventions:
- Use snake_case for table and column names
- Pluralize table names (users, posts)
- Use descriptive foreign key names (user_id, not uid)
- Prefix indexes (idx_table_column)
- Prefix constraints (fk_, uk_, ck_)
Data Types:
- Use appropriate types (INT vs BIGINT, VARCHAR vs TEXT)
- Consider storage size
- Use ENUM for fixed sets of values
- Use JSON/JSONB for flexible attributes
- Use proper date/time types (TIMESTAMP vs DATETIME)
Indexes:
- Index foreign keys
- Index columns in WHERE clauses
- Composite indexes for multi-column queries
- Consider covering indexes
- Monitor index usage and remove unused ones
Relationships:
- Always use foreign keys in relational DBs
- Cascade deletes where appropriate
- Consider soft deletes for audit trails
- Use junction tables for many-to-many
Performance:
- Denormalize for read-heavy workloads
- Partition large tables
- Use materialized views for complex queries
- Consider read replicas
- Plan for archival of old data

当用户需要数据库模式设计时：

收集需求：
- 数据库类型（PostgreSQL、MySQL、MongoDB等）？
- 应用领域是什么？
- 主要实体/资源有哪些？
- 最常见的查询类型是什么？
- 预期的数据量及增长情况？
- 性能要求是什么？
- 是否有特定的约束或合规需求？
遵循最佳实践设计模式：

针对SQL数据库：
- 识别实体及其属性
- 定义主键（分布式系统优先使用UUID）
- 建立关系（一对一、一对多、多对多）
- 规范化至第三范式（3NF），除非为了性能需要进行反规范化
- 添加合适的索引
- 定义外键约束
- 包含时间戳（created_at、updated_at）
- 按需添加软删除标记
- 规划数据归档策略
针对NoSQL数据库：
- 围绕访问模式设计（查询优先方法）
- 决定嵌入还是引用
- 规划反规范化策略
- 为常见查询设计索引
- 考虑文档大小限制
- 规划最终一致性

生成完整模式：

SQL模式输出：

sql

-- [Entity Name] Table
-- Purpose: [Description]

CREATE TABLE [table_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  [field_name] [TYPE] [CONSTRAINTS],
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP
);

-- Indexes
CREATE INDEX idx_[table]_[field] ON [table]([field]);
CREATE INDEX idx_[table]_[field1]_[field2] ON [table]([field1], [field2]);

-- Foreign Keys
ALTER TABLE [child_table]
  ADD CONSTRAINT fk_[constraint_name]
  FOREIGN KEY ([foreign_key_field])
  REFERENCES [parent_table](id)
  ON DELETE CASCADE;

NoSQL模式输出（MongoDB示例）：

javascript

// [Collection Name]
// Purpose: [Description]

{
  _id: ObjectId,
  [field_name]: [type],

  // Embedded document
  [embedded_object]: {
    field1: type,
    field2: type
  },

  // Reference
  [related_id]: ObjectId,  // Ref to [other_collection]

  created_at: ISODate,
  updated_at: ISODate
}

// Indexes
db.[collection].createIndex({ field: 1 })
db.[collection].createIndex({ field1: 1, field2: -1 })
db.[collection].createIndex({ field: "text" })  // Text search

创建实体关系图（文本格式）：

┌─────────────────────┐
│      users          │
├─────────────────────┤
│ id (PK)             │
│ email (UNIQUE)      │
│ name                │
│ created_at          │
└──────────┬──────────┘
           │
           │ 1:N
           │
┌──────────▼──────────┐
│      posts          │
├─────────────────────┤
│ id (PK)             │
│ user_id (FK)        │
│ title               │
│ content             │
│ created_at          │
└──────────┬──────────┘
           │
           │ N:M (via post_tags)
           │
┌──────────▼──────────┐
│       tags          │
├─────────────────────┤
│ id (PK)             │
│ name (UNIQUE)       │
└─────────────────────┘

提供迁移脚本：

sql

-- Migration: create_users_table
-- Date: 2024-01-15

BEGIN;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

COMMIT;

sql

-- Rollback
BEGIN;
DROP TABLE users;
COMMIT;

格式化完整输出：

🗄️ DATABASE SCHEMA DESIGN
Database: [PostgreSQL/MySQL/MongoDB/etc.]
Domain: [Application type]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📋 ENTITY RELATIONSHIP DIAGRAM
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[ASCII ERD]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
📊 TABLE DEFINITIONS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[SQL CREATE TABLE statements]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🔗 RELATIONSHIPS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[Foreign key constraints]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
⚡ INDEXES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[Index definitions with rationale]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
🔄 MIGRATION SCRIPTS
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

[Up and down migrations]

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
💡 OPTIMIZATION NOTES
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Performance Considerations:
• [Index strategy]
• [Partitioning recommendations]
• [Denormalization opportunities]

Scaling Strategy:
• [Sharding approach]
• [Read replicas]
• [Caching layer]

Data Integrity:
• [Constraint strategy]
• [Validation rules]
• [Audit logging]

模式设计最佳实践：

命名规范：
- 表名和列名使用蛇形命名法（snake_case）
- 表名使用复数形式（users、posts）
- 使用描述性的外键名称（user_id，而非uid）
- 索引添加前缀（idx_table_column）
- 约束添加前缀（fk_、uk_、ck_）
数据类型：
- 使用合适的类型（INT vs BIGINT、VARCHAR vs TEXT）
- 考虑存储大小
- 对固定值集合使用ENUM类型
- 对灵活属性使用JSON/JSONB类型
- 使用正确的日期/时间类型（TIMESTAMP vs DATETIME）
索引：
- 为外键创建索引
- 为WHERE子句中的列创建索引
- 为多列查询创建复合索引
- 考虑覆盖索引
- 监控索引使用情况，移除未使用的索引
关系：
- 关系型数据库中始终使用外键
- 在合适的场景使用级联删除
- 考虑为审计追踪使用软删除
- 多对多关系使用关联表
性能：
- 针对读密集型工作负载进行反规范化
- 对大表进行分区
- 为复杂查询使用物化视图
- 考虑使用只读副本
- 规划旧数据的归档策略

Example Triggers

示例触发词

"Design a database schema for an e-commerce platform"
"Create SQL tables for a blog system"
"Help me design a MongoDB schema for a social network"
"Optimize this database schema for performance"
"Generate migration scripts for my schema"

"为电商平台设计数据库模式"
"为博客系统创建SQL表"
"帮我为社交网络设计MongoDB模式"
"优化这个数据库模式以提升性能"
"为我的模式生成迁移脚本"

Output Quality

输出质量要求

Ensure schemas:

Follow normalization principles (unless deliberately denormalized)
Include all necessary constraints
Have appropriate indexes
Use proper data types
Include timestamps
Have clear relationships
Consider scalability
Include migration scripts
Follow naming conventions
Are documented with comments
Consider performance implications
Include rollback capability

Generate production-ready, optimized database schemas that scale.

确保模式：

遵循规范化原则（除非刻意进行反规范化）
包含所有必要的约束
拥有合适的索引
使用正确的数据类型
包含时间戳
关系清晰
考虑可扩展性
包含迁移脚本
遵循命名规范
带有注释说明
考虑性能影响
包含回滚能力

生成可用于生产环境、经过优化且可扩展的数据库模式。