Back to Details

database-schema-designer

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Schema Designer

数据库Schema设计工具

This skill provides comprehensive guidance for designing robust, scalable database schemas for both SQL and NoSQL databases. Whether building from scratch or evolving existing schemas, this framework ensures data integrity, performance, and maintainability.
本Skill为SQL和NoSQL数据库提供全面的健壮、可扩展数据库Schema设计指南。无论是从零开始构建还是迭代现有Schema，该框架都能确保数据完整性、性能和可维护性。

Overview

概述

  • Designing new database schemas
  • Refactoring or migrating existing schemas
  • Optimizing database performance
  • Choosing between SQL and NoSQL approaches
  • Creating database migrations
  • Establishing indexing strategies
  • Modeling complex relationships
  • Planning data archival and partitioning
  • 设计新的数据库Schema
  • 重构或迁移现有Schema
  • 优化数据库性能
  • 选择SQL或NoSQL方案
  • 创建数据库迁移脚本
  • 制定索引策略
  • 建模复杂关系
  • 规划数据归档与分区

Database Design Philosophy

数据库设计理念

Core Principles

核心原则

1. Model the Domain, Not the UI
  • Schema reflects business entities and relationships
  • Don't let UI requirements drive data structure
  • Separate presentation concerns from data model
2. Optimize for Reads or Writes (Not Both)
  • OLTP (transactional): Normalized, optimized for writes
  • OLAP (analytical): Denormalized, optimized for reads
  • Choose based on access patterns
3. Plan for Scale From Day One
  • Indexing strategy
  • Partitioning approach
  • Caching layer
  • Read replicas
4. Data Integrity Over Performance
  • Use constraints, foreign keys, validation
  • Performance issues can be optimized later
  • Data corruption is costly to fix
1. 建模业务领域，而非UI
  • Schema需反映业务实体与关系
  • 不要让UI需求主导数据结构
  • 将展示层关注点与数据模型分离
2. 优先优化读或写（而非两者兼顾）
  • OLTP（事务型）：规范化，优先优化写操作
  • OLAP（分析型）：反规范化，优先优化读操作
  • 根据数据访问模式选择
3. 从设计初期就规划可扩展性
  • 索引策略
  • 分区方案
  • 缓存层
  • 只读副本
4. 数据完整性优先于性能
  • 使用约束、外键和验证机制
  • 性能问题可后续优化
  • 数据损坏的修复成本极高

SQL Database Design

SQL数据库设计

Normalization

规范化

Database normalization reduces redundancy and ensures data integrity.
数据库规范化可减少数据冗余并确保数据完整性。

1st Normal Form (1NF)

第一范式（1NF）

Rule: Each column contains atomic (indivisible) values, no repeating groups.
sql
-- ❌ Violates 1NF (multiple values in one column)
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  product_ids VARCHAR(255)  -- '101,102,103' (bad!)
);

-- ✅ Follows 1NF
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);
规则：每个字段包含原子（不可拆分）的值，无重复分组。
sql
-- ❌ 违反1NF（单个字段包含多个值）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  product_ids VARCHAR(255)  -- '101,102,103'（错误！）
);

-- ✅ 符合1NF
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT,
  product_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

2nd Normal Form (2NF)

第二范式（2NF）

Rule: Must be in 1NF + all non-key columns depend on the entire primary key.
规则：必须符合1NF + 所有非主键字段完全依赖于整个主键。

3rd Normal Form (3NF)

第三范式（3NF）

Rule: Must be in 2NF + no transitive dependencies (non-key columns depend only on primary key).
规则：必须符合2NF + 非主键字段仅依赖于主键，无传递依赖。

Indexing Strategies

索引策略

Indexes speed up reads but slow down writes. Use strategically.
索引可加速读操作但会减慢写操作，需按需使用。

When to Create Indexes

何时创建索引

sql
-- ✅ Index foreign keys
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- ✅ Index frequently queried columns
CREATE INDEX idx_users_email ON users(email);

-- ✅ Index columns used in WHERE, ORDER BY, GROUP BY
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- ✅ Composite index for multi-column queries
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
sql
-- ✅ 为外键创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- ✅ 为频繁查询的字段创建索引
CREATE INDEX idx_users_email ON users(email);

-- ✅ 为WHERE、ORDER BY、GROUP BY中使用的字段创建索引
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- ✅ 为多字段查询创建复合索引
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Composite Indexes (Column Order Matters)

复合索引（字段顺序至关重要）

sql
-- ✅ Good: Index supports both queries
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Query 1: Uses index efficiently
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- Query 2: Uses index (customer_id only)
SELECT * FROM orders WHERE customer_id = 123;

-- ❌ Query 3: Doesn't use index (status is second column)
SELECT * FROM orders WHERE status = 'pending';
Rule of Thumb: Put most selective column first, or most frequently queried alone.
sql
-- ✅ 合理：索引可支持以下两种查询
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- 查询1：高效利用索引
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- 查询2：可利用索引（仅使用customer_id）
SELECT * FROM orders WHERE customer_id = 123;

-- ❌ 查询3：无法利用索引（status是第二个字段）
SELECT * FROM orders WHERE status = 'pending';
经验法则：将选择性最高的字段放在前面，或最常单独查询的字段放在前面。

Constraints

约束机制

Use constraints to enforce data integrity at the database level.
sql
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2) CHECK (price >= 0),
  stock INT CHECK (stock >= 0),
  discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100)
);
使用约束在数据库层面强制数据完整性。
sql
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10, 2) CHECK (price >= 0),
  stock INT CHECK (stock >= 0),
  discount_percent INT CHECK (discount_percent BETWEEN 0 AND 100)
);

Database Migrations

数据库迁移

Migration Best Practices

迁移最佳实践

1. Always Reversible
sql
-- Up migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Down migration
ALTER TABLE users DROP COLUMN phone;
2. Backward Compatible
sql
-- ✅ Good: Add nullable column
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50);

-- ❌ Bad: Add required column (breaks existing code)
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) NOT NULL;
3. Data Migrations Separate from Schema Changes
sql
-- Migration 1: Schema change
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Migration 2: Data migration
UPDATE orders SET status = 'completed' WHERE completed_at IS NOT NULL;
1. 始终支持回滚
sql
-- 升级迁移
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 回滚迁移
ALTER TABLE users DROP COLUMN phone;
2. 保持向后兼容
sql
-- ✅ 合理：添加可为空的字段
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50);

-- ❌ 错误：添加必填字段（会破坏现有代码）
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50) NOT NULL;
3. 数据迁移与Schema变更分离
sql
-- 迁移1：Schema变更
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- 迁移2：数据迁移
UPDATE orders SET status = 'completed' WHERE completed_at IS NOT NULL;

Quick Start Checklist

快速开始检查清单

When designing a new schema:
  • Identify entities and relationships
  • Choose SQL or NoSQL based on requirements
  • Normalize to 3NF (SQL) or decide embed/reference (NoSQL)
  • Define primary keys (INT auto-increment or UUID)
  • Add foreign key constraints
  • Choose appropriate data types
  • Add unique constraints where needed
  • Plan indexing strategy (foreign keys, WHERE columns)
  • Add NOT NULL constraints for required fields
  • Create CHECK constraints for validation
  • Plan for soft deletes (deleted_at column) if needed
  • Add timestamps (created_at, updated_at)
  • Design migration scripts (up and down)
  • Test migrations on staging
设计新Schema时：
  • 识别业务实体与关系
  • 根据需求选择SQL或NoSQL
  • SQL数据库规范化至3NF，NoSQL数据库决定嵌入/引用模式
  • 定义主键（自增INT或UUID）
  • 添加外键约束
  • 选择合适的数据类型
  • 按需添加唯一约束
  • 规划索引策略（外键、WHERE子句字段）
  • 为必填字段添加NOT NULL约束
  • 创建CHECK约束实现数据验证
  • 按需规划软删除（deleted_at字段）
  • 添加时间戳字段（created_at、updated_at）
  • 设计迁移脚本（升级与回滚）
  • 在预发布环境测试迁移

Related Skills

相关Skill

  • alembic-migrations
    - Alembic-specific migration patterns for SQLAlchemy projects
  • zero-downtime-migration
    - Safe schema changes without service interruption
  • database-versioning
    - Version control strategies for database objects
  • caching-strategies
    - Cache layer design to complement database performance
  • alembic-migrations
    - 适用于SQLAlchemy项目的Alembic专属迁移模式
  • zero-downtime-migration
    - 无需中断服务的安全Schema变更方案
  • database-versioning
    - 数据库对象的版本控制策略
  • caching-strategies
    - 用于提升数据库性能的缓存层设计

Key Decisions

关键决策

DecisionChoiceRationale
Normalization target3NF for OLTPReduces redundancy while maintaining query performance
Primary key strategyINT auto-increment or UUIDUUIDs for distributed systems, INT for single-database
Soft deletes
deleted_at
timestamp column		Preserves audit trail, enables recovery, supports compliance
Composite index orderMost selective column firstOptimizes index usage for common query patterns
Skill Version: 2.0.0 Last Updated: 2026-01-08 Maintained by: AI Agent Hub Team
决策项选择方案理由
规范化目标OLTP场景采用3NF在保持查询性能的同时减少数据冗余
主键策略自增INT或UUID分布式系统用UUID，单数据库场景用INT
软删除
deleted_at
时间戳字段		保留审计轨迹，支持数据恢复与合规要求
复合索引顺序选择性最高的字段优先针对常见查询模式优化索引使用率
Skill版本：2.0.0 最后更新：2026-01-08 维护团队：AI Agent Hub Team

Capability Details

功能详情

schema-design

schema-design

Keywords: schema, table, entity, relationship, erd Solves:
  • Design database schema
  • Model relationships
  • ERD creation
关键词： schema, table, entity, relationship, erd 适用场景：
  • 设计数据库Schema
  • 建模数据关系
  • 创建ER图

normalization

normalization

Keywords: normalize, 1nf, 2nf, 3nf, denormalize Solves:
  • Normalization levels
  • When to denormalize
  • Reduce redundancy
关键词： normalize, 1nf, 2nf, 3nf, denormalize 适用场景：
  • 规范化层级选择
  • 反规范化场景判断
  • 减少数据冗余

indexing

indexing

Keywords: index, b-tree, composite, query performance Solves:
  • Which columns to index
  • Optimize slow queries
  • Index types
关键词： index, b-tree, composite, query performance 适用场景：
  • 选择需索引的字段
  • 优化慢查询
  • 索引类型选择

migrations

migrations

Keywords: migration, alter table, zero downtime, backward compatible Solves:
  • Write safe migrations
  • Zero-downtime changes
  • Reversible migrations
关键词： migration, alter table, zero downtime, backward compatible 适用场景：
  • 编写安全的迁移脚本
  • 零停机变更
  • 可回滚的迁移