database-design

enoch-robinson's avatarfrom enoch-robinson

数据库设计指南。当用户需要设计数据库 Schema、优化索引、规划表结构、处理数据关系或进行数据库性能优化时使用此技能。

⭐0stars🔀0forks📁View on GitHub🕐Updated Jan 10, 2026
[SQL and Databases]

When & Why to Use This Skill

This Claude skill serves as a comprehensive Database Design Guide, helping developers and architects build efficient, scalable, and high-performance database schemas. It provides expert guidance on the entire lifecycle of database engineering, from conceptual ER modeling and logical table structure definition to physical optimization strategies like indexing and partitioning, ensuring all designs follow industry best practices and naming conventions.

Use Cases

  • Architecting new database schemas: Designing normalized table structures and defining complex relationships (One-to-Many, Many-to-Many) for new software projects.
  • Performance Tuning: Optimizing slow queries by implementing strategic B-Tree, composite, or partial indexes based on specific query patterns.
  • Schema Refactoring: Identifying and resolving common database anti-patterns such as over-normalization, 'God Tables', or missing foreign key indexes to improve maintainability.
  • SQL Boilerplate Generation: Automatically generating DDL scripts for tables, triggers, and constraints based on business requirements.
  • Scalability Planning: Developing physical design strategies including data partitioning and read-write splitting for high-traffic applications.
namedatabase-design
description数据库设计指南。当用户需要设计数据库 Schema、优化索引、规划表结构、处理数据关系或进行数据库性能优化时使用此技能。

Database Design

帮助开发者设计高效、可扩展的数据库结构，遵循最佳实践。

设计流程

  1. 需求分析：理解业务实体和关系
  2. 概念设计：绘制 ER 图
  3. 逻辑设计：定义表结构和关系
  4. 物理设计：索引、分区策略
  5. 优化迭代：根据查询模式调整

Schema 设计原则

命名规范

-- 表名：小写复数，下划线分隔
users, order_items, user_profiles

-- 字段名：小写，下划线分隔
created_at, user_id, is_active

-- 主键：id 或 表名单数_id
id, user_id

-- 外键：关联表单数_id
user_id, order_id

基础表模板

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 更新时间触发器
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

索引策略

何时创建索引

场景 建议
WHERE 条件字段 ✅ 创建索引
JOIN 关联字段 ✅ 创建索引
ORDER BY 字段 ✅ 考虑索引
高频更新字段 ⚠️ 谨慎索引
低基数字段 ❌ 避免索引

索引类型选择

-- B-Tree：默认，适合等值和范围查询
CREATE INDEX idx_users_email ON users(email);

-- 复合索引：多字段查询
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引：条件过滤
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- 唯一索引：保证唯一性
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

关系设计

一对多

-- 用户有多个订单
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

多对多

-- 用户和角色的多对多关系
CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role_id BIGINT REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

常见反模式

反模式 问题 解决方案
过度规范化 查询复杂，性能差 适度反规范化
万能表 难维护，浪费空间 拆分为专用表
无索引外键 JOIN 性能差 为外键添加索引
存储 JSON滥用 无法有效查询 结构化数据用列存储

性能优化

  1. EXPLAIN 分析：检查查询计划
  2. **避免 SELECT ***：只查询需要的字段
  3. 分页优化：使用游标分页替代 OFFSET
  4. 连接池：复用数据库连接
  5. 读写分离：主从架构分担负载

参考资源

database-design – AI Agent Skills | Claude Skills