数据库架构设计Skill database-schema-design

数据库架构设计

概览

设计可扩展的、规范化的数据库架构，包含适当的关系、约束和数据类型。包括规范化技术、关系模式和约束策略。

何时使用

• 新数据库架构设计
• 数据模型规划
• 表结构定义
• 关系设计（1:1, 1:N, N:N）
• 规范化分析
• 约束和触发器规划
• 架构层面的性能优化

规范化策略

第一范式（1NF）

PostgreSQL - 消除重复组：

-- 非1NF：单个列中的重复组
CREATE TABLE orders_bad (
  id UUID PRIMARY KEY,
  customer_name VARCHAR(255),
  product_ids VARCHAR(255)  -- "1,2,3" - 重复组
);

-- 1NF：重复数据的独立表
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_name VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id UUID PRIMARY KEY,
  order_id UUID NOT NULL,
  product_id UUID NOT NULL,
  quantity INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

第二范式（2NF）

PostgreSQL - 移除部分依赖：

-- 非2NF：非键属性依赖于复合键的一部分
CREATE TABLE enrollment_bad (
  student_id UUID,
  course_id UUID,
  professor_name VARCHAR(255),  -- 仅依赖于course_id
  PRIMARY KEY (student_id, course_id)
);

-- 2NF：独立表
CREATE TABLE enrollments (
  id UUID PRIMARY KEY,
  student_id UUID NOT NULL,
  course_id UUID NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id),
  UNIQUE(student_id, course_id)
);

CREATE TABLE courses (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  professor_id UUID NOT NULL,
  FOREIGN KEY (professor_id) REFERENCES professors(id)
);

第三范式（3NF）

PostgreSQL - 移除传递依赖：

-- 非3NF：传递依赖（customer_city依赖于customer_state）
CREATE TABLE orders_bad (
  id UUID PRIMARY KEY,
  customer_city VARCHAR(100),
  customer_state VARCHAR(50),
  state_tax_rate DECIMAL(5,3)  -- 依赖于customer_state
);

-- 3NF：独立表
CREATE TABLE states (
  id UUID PRIMARY KEY,
  code VARCHAR(2) UNIQUE,
  name VARCHAR(100),
  tax_rate DECIMAL(5,3)
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_city VARCHAR(100),
  state_id UUID NOT NULL,
  FOREIGN KEY (state_id) REFERENCES states(id)
);

表设计模式

实体-关系模式

PostgreSQL - 一对多：

-- 一个用户有多个订单
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  order_date TIMESTAMP DEFAULT NOW(),
  total DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_id (user_id)
);

PostgreSQL - 一对一：

-- 一个用户有一个档案
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID UNIQUE NOT NULL,
  bio TEXT,
  avatar_url VARCHAR(500),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

PostgreSQL - 多对多：

-- 学生和课程（多对多）
CREATE TABLE students (
  id UUID PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE courses (
  id UUID PRIMARY KEY,
  title VARCHAR(255)
);

-- 连接表
CREATE TABLE course_enrollments (
  id UUID PRIMARY KEY,
  student_id UUID NOT NULL,
  course_id UUID NOT NULL,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  UNIQUE(student_id, course_id)
);

约束策略

PostgreSQL - 数据完整性：

-- NOT NULL约束
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  sku VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

-- 唯一性约束
ALTER TABLE products
ADD CONSTRAINT unique_sku UNIQUE(sku);

-- 检查约束
ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

ALTER TABLE orders
ADD CONSTRAINT valid_status
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- 默认值
CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name VARCHAR(100) NOT NULL,
  operation VARCHAR(10) NOT NULL,
  user_id UUID,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

数据类型选择

PostgreSQL - 优化数据类型：

CREATE TABLE users (
  -- 标识符
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- 文本字段
  email VARCHAR(255),          -- 电子邮件固定长度
  name TEXT,                   -- 无界文本
  bio TEXT,

  -- 数值数据
  age SMALLINT,                -- 0-32767
  balance DECIMAL(15,2),       -- 财务数据（精确）
  rating NUMERIC(3,1),         -- 范围0.0-9.9

  -- 布尔值
  is_active BOOLEAN DEFAULT true,
  email_verified BOOLEAN,

  -- 日期和时间
  birth_date DATE,
  last_login TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- JSON/二进制
  metadata JSONB,
  profile_image BYTEA,

  -- 数组（PostgreSQL特定）
  tags TEXT[] DEFAULT ARRAY[]::TEXT[]
);

MySQL - 兼容数据类型：

CREATE TABLE users (
  id CHAR(36) PRIMARY KEY,       -- UUID作为CHAR

  email VARCHAR(255),
  name VARCHAR(255),

  age TINYINT UNSIGNED,
  balance DECIMAL(15,2),

  is_active BOOLEAN DEFAULT true,

  birth_date DATE,
  last_login TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  metadata JSON,

  KEY idx_email (email)
);

架构演进

PostgreSQL - 向后兼容的更改：

-- 添加带有默认值的列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加新特性的列
ALTER TABLE orders
ADD COLUMN notes TEXT DEFAULT '';

-- 在新列上添加约束
ALTER TABLE orders
ADD CONSTRAINT check_notes CHECK (LENGTH(notes) <= 500);

-- 安全地弃用列
ALTER TABLE users RENAME COLUMN old_field TO old_field_deprecated;

MySQL - 架构更改：

-- 添加带有默认值的列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT '';

-- 添加多个列
ALTER TABLE orders
ADD COLUMN notes TEXT DEFAULT '',
ADD COLUMN internal_status VARCHAR(50);

-- 修改列
ALTER TABLE users MODIFY COLUMN bio TEXT;

性能考虑

PostgreSQL - 分区大表：

-- 按日期范围分区时间序列数据
CREATE TABLE events (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  event_type VARCHAR(100),
  created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (DATE_TRUNC('month', created_at));

CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

架构设计清单

• 确定实体和关系
• 应用规范化规则（1NF, 2NF, 3NF）
• 为所有表定义主键
• 为关系创建外键
• 添加约束以确保数据完整性
• 选择合适的数据类型
• 为常见查询规划索引
• 为可扩展性设计（如有必要，去规范化）
• 记录表的用途和关系
• 规划架构演进

常见陷阱

❌ 不要为了方便而跳过规范化 ❌ 不要对所有文本字段使用VARCHAR(MAX) ❌ 不要忘记添加外键约束 ❌ 不要使用自然键作为主键 ❌ 不要在基础表中存储计算值

✅ 要使用UUID或序列作为主键 ✅ 要适当规范化数据 ✅ 要为数据有效性添加CHECK约束 ✅ 要在外部键上创建索引 ✅ 要使用TIMESTAMP进行审计跟踪

资源

• PostgreSQL 数据类型
• MySQL 数据类型
• 数据库规范化指南
• Draw.io - 架构图工具