代理键和自然键

在数据仓库和数据库设计中，代理键和自然键是两种常见的唯一标识符设计方式，它们在数据建模中各有优缺点和适用场景。以下是详细解释和示例：

1. 自然键（Natural Key）

定义

自然键是业务中天然存在的唯一标识符，通常具有实际业务意义，直接来源于业务数据本身。 特点：

• 业务相关性：与业务实体直接绑定（如身份证号、订单号）。

• 稳定性要求：需要长期保持唯一性，但可能因业务规则变化而失效。

• 可读性：通常包含业务信息（如订单号中的日期或分类代码）。

示例

• 用户表：身份证号（id_card）、手机号（phone）。

• 订单表：订单编号（order_no，如20231015-001）。

• 商品表：商品编码（sku_code，如ELEC-1001）。

优缺点

适用场景

• 业务标识天然唯一且稳定（如国家代码CN/US）。

• 需要直接通过键值理解业务含义的查询（如按订单号查物流）。

2. 代理键（Surrogate Key）

定义

代理键是数据库内部生成的、无业务意义的唯一标识符，仅用于技术层面标识记录。 特点：

• 无业务含义：通常为自增整数（如1, 2, 3）或UUID。

• 稳定性强：不受业务规则变化影响，终身唯一。

• 高效性：整型键比字符串键更节省存储和提升查询性能。

示例

• 用户表：自增ID（user_id = 1001）。

• 订单表：UUID（order_sk = 'a1b2c3d4'）。

• 时间维度表：日期代理键（date_key = 20231015）。

优缺点

适用场景

• 自然键不稳定或过于复杂（如用户表用手机号或邮箱作为键易变）。

• 需要高效关联多表（如事实表通过代理键关联维度表）。

• 处理缓慢变化维（SCD Type 2需生成新代理键记录历史版本）。

对比与选择建议

设计原则

1. 优先代理键：数仓中90%的维度表推荐使用代理键，尤其是需要处理历史变化的场景。

2. 保留自然键：代理键生成后，仍需在表中保留自然键供业务查询使用（如user_sk为主键，id_card为普通字段）。

3. 混合使用：事实表可同时存储代理键（高效关联）和自然键（业务校验），例如：

   CREATE TABLE fact_order (
     order_sk BIGINT PRIMARY KEY,  -- 代理键（关联维度表）
     order_no VARCHAR(20),          -- 自然键（业务查询使用）
     user_sk  INT,                  -- 用户维度代理键
     amount   DECIMAL(10,2)
   );

实战案例

场景：电商订单分析

• 维度表（商品）：

  CREATE TABLE dim_product (
    product_sk INT PRIMARY KEY,  -- 代理键（自增）
    sku_code   VARCHAR(20),      -- 自然键（业务唯一编码）
    product_name VARCHAR(100),
    category    VARCHAR(50)
  );

• 事实表（订单）：

  CREATE TABLE fact_order (
    order_sk    BIGINT PRIMARY KEY,  -- 代理键（UUID）
    order_no    VARCHAR(20),         -- 自然键（业务订单号）
    product_sk  INT,                  -- 关联商品代理键
    user_sk     INT,                  -- 关联用户代理键
    order_date  DATE,
    amount      DECIMAL(10,2)
  );

• 查询示例：

  -- 通过代理键高效关联
  SELECT 
    o.order_no,
    p.product_name,
    u.user_name,
    o.amount
  FROM fact_order o
  JOIN dim_product p ON o.product_sk = p.product_sk
  JOIN dim_user u ON o.user_sk = u.user_sk;

总结

• 自然键是业务属性的直接体现，适合简单、稳定的标识场景。

• 代理键是技术驱动的唯一标识，适合复杂关联、历史追踪和高性能需求的场景。

• 实际设计中，二者通常共存：代理键用于技术关联，自然键用于业务查询，共同支撑数据仓库的稳定性和灵活性。