database-design

Database Design 技能用于从需求出发设计数据库架构（Schema），覆盖关系型设计、规范化以及常见模式，并包含 ORM 配置方式。

一、用途与工作流该技能的核心目标是根据需求设计数据库 Schema。其工作流分为若干步骤。

识别实体（Identify Entities）：从需求中提取核心实体（名词），例如 Users、Teams、Projects、Tasks、Comments 等。每个实体对应一张表。
定义关系（Define Relationships）：针对不同关系类型给出实现方式。一对一（One-to-one）使用带唯一约束的外键，或嵌入同一张表；一对多（One-to-many）在“多”的一侧放置外键；多对多（Many-to-many）使用连接表（junction/join table）；自引用（Self-referential）使用指向同一张表的外键（例如 parent_id）。
设计 Schema（Design the Schema）：为每张表编写建表语句。资料给出的示例如下：

CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, avatar_url TEXT, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() );

CREATE TABLE projects ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, created_at TIMESTAMPTZ NOT NULL DEFAULT now() );

二、主要能力与最佳实践 4. 应用最佳实践（Apply Best Practices）：主键方面，分布式系统或面向公众的 ID 使用 UUID，仅内部使用的 ID 使用 SERIAL/BIGSERIAL（连接更快）。时间戳方面，始终添加 created_at 和 updated_at，并使用 TIMESTAMPTZ（带时区），绝不使用 TIMESTAMP。命名方面，表名使用复数 snake_case（如 users、project_members）；列名使用单数 snake_case（如 user_id、created_at）；索引命名为 idx_<table>_<columns>（如 idx_users_email）。约束方面，除非确实可选，否则一律加 NOT NULL；对自然键（email、slug、外部 ID）加 UNIQUE；使用 REFERENCES 并指定 ON DELETE 行为（CASCADE、SET NULL、RESTRICT）；对枚举或值范围使用 CHECK 约束。

添加索引（Add Indexes）：资料给出如下示例：

-- 对频繁筛选/排序的列 CREATE INDEX idx_projects_owner_id ON projects(owner_id);

-- 唯一查询 CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 常见查询模式的复合索引 CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);

何时建索引：外键（几乎总是）、WHERE 子句中的列、ORDER BY 中的列、JOIN 条件中的列。何时不建索引：小表（少于 1000 行）、低基数列（布尔值、只有 3 个值的 status）、很少被查询的列。

ORM 设置（ORM Setup）：资料覆盖 Prisma 与 Drizzle 两种方式。

Prisma 示例：

model User { id String @id @default(uuid()) email String @unique name String projects Project[] createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at") @@map("users") }

Drizzle 示例：

export const users = pgTable('users', { id: uuid('id').primaryKey().defaultRandom(), email: text('email').notNull().unique(), name: text('name').notNull(), createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(), updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(), });

三、常见模式（Common Patterns）软删除（Soft deletes）：添加 deleted_at TIMESTAMPTZ，而不是实际删除行。审计日志（Audit log）：单独的 audit_events 表，包含 entity_type、entity_id、action、actor_id、payload。标签（Tags/labels）：使用连接表（task_tags），包含 task_id + tag_id。树形/层级（Tree/hierarchy）：parent_id 自引用，或物化路径（materialized path，如 /1/4/7/）。多态关联（Polymorphic associations）：使用 entity_type + entity_id 列（尽可能避免，优先采用独立外键）。

四、提示（Tips）从规范化（3NF）开始，只有在有实测性能问题时才反规范化。除非有缓存/失效策略，否则不要存储派生数据。状态字段使用数据库枚举或 CHECK 约束，而非自由文本。始终考虑删除父记录时会发生什么。

五、适用场景与限制本技能适用于关系型数据库的建模与规范化设计，尤其是需要从需求推导实体关系、并落地为 SQL Schema 与 ORM 模型的场景。资料未提供具体的平台限制、收费信息、价格或效果保证；相关内容暂未提供。

技能介绍