数据库设计：规范化、反规范化与何时打破规则

数据库设计原则

良好的模式设计是规范化（数据完整性）与反规范化（查询性能）之间的平衡。

规范化范式

1NF：原子值

-- 错误：重复组
CREATE TABLE orders (
  id INT,
  items TEXT  -- 'apple,banana,cherry' — 违反1NF
);

-- 正确：原子值
CREATE TABLE order_items (
  order_id INT,
  item_name VARCHAR(100),
  quantity INT
);

3NF：无传递依赖

-- 错误：城市决定邮编，邮编决定州（传递依赖）
CREATE TABLE users (
  id INT,
  name VARCHAR,
  zip_code VARCHAR(10),
  city VARCHAR(100),  -- 由邮编决定，而非id
  state VARCHAR(2)    -- 由邮编决定，而非id
);

-- 正确：提取到zip_codes表
CREATE TABLE zip_codes (
  zip VARCHAR(10) PRIMARY KEY,
  city VARCHAR(100),
  state VARCHAR(2)
);

策略性反规范化

缓存计数

-- 替代每次页面加载时执行COUNT(*)：
ALTER TABLE posts ADD COLUMN comment_count INT DEFAULT 0;
ALTER TABLE users ADD COLUMN follower_count INT DEFAULT 0;

-- 使用触发器保持同步
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER AS $
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
  END IF;
  RETURN NULL;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_comment_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();

JSONB灵活属性（EAV替代方案）

-- 错误：实体-属性-值（性能极差）
CREATE TABLE product_attributes (
  product_id INT,
  attr_name VARCHAR,
  attr_value TEXT  -- 丢失类型信息！
);

-- 正确：JSONB列
ALTER TABLE products ADD COLUMN attributes JSONB DEFAULT '{}';

-- 索引特定JSONB路径
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
CREATE INDEX idx_products_attrs ON products USING gin(attributes);

-- 查询
SELECT * FROM products
WHERE attributes->>'color' = 'red'
  AND (attributes->>'weight')::float < 5.0;

多租户模式

行级（共享模式）

-- 在所有表中添加tenant_id
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100),
  plan VARCHAR(50)
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID REFERENCES tenants(id) NOT NULL,
  email VARCHAR(255),
  UNIQUE (tenant_id, email)
);

-- RLS实现自动隔离
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- 在应用中设置
SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';

每个租户独立模式

-- 每个租户拥有自己的PostgreSQL模式
CREATE SCHEMA tenant_abc;
CREATE TABLE tenant_abc.users (...);
CREATE TABLE tenant_abc.orders (...);

审计追踪模式

CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  table_name VARCHAR(100) NOT NULL,
  record_id TEXT NOT NULL,
  operation CHAR(1) CHECK (operation IN ('I','U','D')),
  old_values JSONB,
  new_values JSONB,
  changed_by UUID REFERENCES users(id),
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_audit_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_changed_at ON audit_log (changed_at DESC);

-- 通用审计触发器
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $
BEGIN
  INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_by)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id::TEXT, OLD.id::TEXT),
    LEFT(TG_OP, 1),
    CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD)::JSONB END,
    CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW)::JSONB END,
    current_setting('app.user_id', true)::UUID
  );
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

软删除 vs 硬删除

-- 软删除：标记为已删除，保留数据
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
ALTER TABLE users ADD COLUMN is_deleted BOOLEAN GENERATED ALWAYS AS (deleted_at IS NOT NULL) STORED;

CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;

-- 未删除记录的视图
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;