database-design

数据库设计

根据需求设计数据库架构。

工作流

1. 识别实体

从需求中提取核心实体（名词）：

• 用户、团队、项目、任务、评论等
• 每个实体对应一张表

2. 定义关系

关系	实现方式
一对一	使用带唯一约束的外键，或嵌入同一张表
一对多	在“多”的一侧添加外键
多对多	使用关联表
自引用	使用指向同一张表的外键（例如 parent_id）

3. 设计架构

每张表：

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. 遵循最佳实践

主键：

• 分布式系统或面向公众的 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
• 对自然键（邮箱、slug、外部 ID）添加 UNIQUE
• 使用 REFERENCES 并设置 ON DELETE 行为（CASCADE、SET NULL、RESTRICT）
• 对枚举值或取值范围使用 CHECK 约束

5. 添加索引

-- For columns you filter/sort by frequently
CREATE INDEX idx_projects_owner_id ON projects(owner_id);

-- For unique lookups
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Composite for common query patterns
CREATE INDEX idx_tasks_project_status ON tasks(project_id, status);

适合添加索引的情况：

• 外键（几乎总是）
• WHERE 子句中的列
• ORDER BY 中的列
• JOIN 条件中的列

不适合添加索引的情况：

• 小表（<1000 行）
• 基数较低的列（布尔值、仅有 3 个值的状态字段）
• 很少用于查询的列

6. ORM 设置

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(),
});

常见模式

软删除： 添加 deleted_at TIMESTAMPTZ，而不是实际删除行 审计日志： 使用单独的 audit_events 表，包含 entity_type、entity_id、action、actor_id、payload 标签： 使用关联表（task_tags），包含 task_id 和 tag_id 树形结构/层级结构： 使用 parent_id 自引用，或使用物化路径（/1/4/7/） 多态关联： 使用 entity_type 和 entity_id 列（尽量避免，优先使用单独的外键）

提示

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