PHP小码农

Appearance

范式与反范式 ​

范式（Normalization）是关系数据库设计的基石：通过拆表消除冗余，让每一份数据只存一次。但真实业务里，严格的范式化往往会带来大量 JOIN 与复杂的查询逻辑，于是又需要反范式（Denormalization）。本节从函数依赖讲起，逐级推导 1NF 到 BCNF，再用一张真实的订单宽表演进演示拆表过程，最后给出范式化与反范式的权衡依据，以及 PostgreSQL 特有能力的折中手段。

前置知识

阅读本节前，建议先了解：创建表（CREATE TABLE）

基础概念 ​

范式讨论的核心对象是函数依赖（Functional Dependency）。记作 X → Y，含义是「只要 X 的值确定，Y 的值就唯一确定」。例如在商品表里 product_id → product_name，因为一个商品 ID 只对应一个商品名。

围绕函数依赖，范式要消除的是三类「坏依赖」：

依赖类型英文含义违反的范式
部分依赖Partial Dependency非主属性只依赖复合主键的一部分2NF
传递依赖Transitive Dependency非主属性依赖另一个非主属性3NF
决定因素非超键Non-superkey Determinant每个决定因素都必须是候选键BCNF

四个范式的定义层层递进：

范式英文一句话定义
1NFFirst Normal Form每个字段都是原子值，不可再分
2NFSecond Normal Form满足 1NF，且无部分依赖
3NFThird Normal Form满足 2NF，且无传递依赖
BCNFBoyce-Codd Normal Form满足 3NF，且所有决定因素都是候选键

一句话记住演进关系

1NF 解决「单元格里塞列表」，2NF 解决「复合主键下只依赖一半」，3NF 解决「A 依赖 B、B 依赖 C」，BCNF 解决「主属性之间也有依赖」。

反范式（Denormalization）与之相反：主动引入冗余，用「多存一份」换取「少一次 JOIN」或「少一次聚合计算」。它不是「不设计」，而是在明确知道代价的前提下做出的权衡。

语法与用法 ​

范式与反范式不是 SQL 语法层面的东西，而是建模决策。但我们可以用 DDL 的形态对比它们的差别：

text
-- 范式化形态：一个事实只存一处，用外键关联
CREATE TABLE customers (customer_id ..., name ...);
CREATE TABLE products  (product_id  ..., name ...);
CREATE TABLE orders    (order_id ..., customer_id ...);
CREATE TABLE order_items (order_id ..., product_id ..., qty ...);

-- 反范式形态：把关联信息冗余到同一张宽表
CREATE TABLE orders_wide (
  order_id      ...,
  customer_id   ...,
  customer_name ...,       -- 冗余自 customers
  product_id    ...,
  product_name  ...        -- 冗余自 products
);

最简可运行示例——先看 1NF 要消除的「非原子值」：

sql
-- 违反 1NF：tags 列里塞了逗号分隔的多个值
CREATE TABLE product_bad_1nf (
  id   integer PRIMARY KEY,
  name text,
  tags text          -- 'phone,5g,flagship' 这种值违反 1NF
);

-- 满足 1NF 的写法：用数组或独立关联表
CREATE TABLE product_ok_1nf (
  id   integer PRIMARY KEY,
  name text,
  tags text[]        -- 数组是「单个字段的原子值」，满足 1NF
);
text
CREATE TABLE
CREATE TABLE

关于数组与 1NF 的关系：PostgreSQL 官方把数组视为单个字段的原子值，元素通过下标访问，因此 text[] 不违反 1NF：

sql
INSERT INTO product_ok_1nf VALUES (1, 'Phone', ARRAY['5g', 'flagship']);

SELECT id, name, tags[1] AS first_tag FROM product_ok_1nf;
text
 id | name  | first_tag
----+-------+-----------
  1 | Phone | 5g
(1 row)

详细说明 ​

非范式化宽表：问题的起点 ​

我们从一个「什么都塞在一起」的订单宽表开始，它在真实项目里极其常见（往往是从 Excel 或报表需求演化来的）：

sql
DROP TABLE IF EXISTS order_wide CASCADE;

CREATE TABLE order_wide (
  order_no       text,
  order_date     date,
  customer_id    text,
  customer_name  text,
  customer_phone text,
  product_id     text,
  product_name   text,
  category_name  text,      -- 商品分类名（冗余描述）
  unit_price     numeric(10,2),
  qty            integer
);

插入几行数据，可以看到同一份信息被反复存储：

sql
INSERT INTO order_wide VALUES
  ('SO-001', '2026-01-05', 'C01', 'Alice', '13800000001', 'P01', 'Phone', 'Electronics', 2999.00, 2),
  ('SO-001', '2026-01-05', 'C01', 'Alice', '13800000001', 'P02', 'Case',  'Accessories',   49.00, 3),
  ('SO-002', '2026-01-06', 'C02', 'Bob',   '13800000002', 'P01', 'Phone', 'Electronics', 2999.00, 1);
text
INSERT 0 3

问题一目了然：Alice、13800000001、Phone 被重复存储多次；改客户电话要更新所有相关行（更新异常）；没有下单的客户无法记录信息（插入异常）；删掉订单可能连客户信息一起丢失（删除异常）。

在下结论前，先找出函数依赖。(order_no, product_id) 是这行的候选键：

text
(order_no, product_id) → qty, unit_price, order_date, customer_id, ...
order_no               → order_date, customer_id, customer_name, customer_phone
customer_id            → customer_name, customer_phone        （客户属性）
product_id             → product_name, category_name          （商品属性）

这些箭头就是后面拆表的依据。

1NF：让每个字段原子化 ​

1NF 的要求：每个列的值都不可再分，不允许多值/复合值。

违规最常见的形态是「逗号分隔列表」与「重复列组」：

sql
-- 错误！违规形态 A：逗号分隔的多值
CREATE TABLE bad_a (
  id      integer PRIMARY KEY,
  phones  text          -- '13800000001,13900000002'
);

-- 错误！违规形态 B：重复列组（phone1/phone2/phone3）
CREATE TABLE bad_b (
  id       integer PRIMARY KEY,
  phone1   text,
  phone2   text,
  phone3   text
);

bad_b 的重复列组还带来一个硬伤：超过 3 个电话就没地方放，且查询「所有电话」要写 OR。

正确的 1NF 化写法是拆出关联表，让「一个电话」成为一行：

sql
CREATE TABLE customer_phones (
  customer_id integer NOT NULL,
  phone_no    text    NOT NULL,
  phone_type  text    NOT NULL DEFAULT 'mobile',
  PRIMARY KEY (customer_id, phone_no)   -- 复合主键
);

用检查约束证明「逗号列表」为什么是坏设计——查某个电话只能 LIKE '%xxx%'，既用不上索引又会误匹配前缀：

sql
-- 正确设计里就是一次等值查询，直接走主键索引
EXPLAIN (COSTS OFF)
SELECT * FROM customer_phones WHERE customer_id = 1 AND phone_no = '13800000001';
text
                              QUERY PLAN
----------------------------------------------------------------------
 Index Only Scan using customer_phones_pkey on customer_phones
   Index Cond: ((customer_id = 1) AND (phone_no = '13800000001'::text))
(2 rows)

2NF：消除部分依赖 ​

2NF 的前提是存在复合主键。如果表的主键是单列，它天然满足 2NF。当主键是多列组合时，若某个非主属性只依赖组合的一部分，就违反了 2NF。

在我们的宽表里，(order_no, product_id) 是主键，而 customer_name 只依赖 order_no（主键的一部分），这就是部分依赖：

text
(order_no, product_id) → customer_name      ← 冗余依赖（实际只依赖 order_no）
(order_no, product_id) → product_name       ← 冗余依赖（实际只依赖 product_id）

拆表思路：把只依赖一部分的属性移到它真正依赖的那张表。于是拆出 orders 与 products：

sql
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  phone       text NOT NULL
);

CREATE TABLE products (
  product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  category   text NOT NULL,
  unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0)
);

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    text NOT NULL UNIQUE,
  order_date  date NOT NULL DEFAULT CURRENT_DATE,
  customer_id integer NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_id   bigint NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id integer NOT NULL REFERENCES products(product_id),
  qty        integer NOT NULL CHECK (qty > 0),
  unit_price numeric(10,2) NOT NULL,     -- 下单时的成交价，见 3NF 一节说明
  PRIMARY KEY (order_id, product_id)
);
text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

现在 order_items 的主键仍是 (order_id, product_id)，但它的非主属性只有 qty 与 unit_price，都依赖整个主键 → 满足 2NF。

注意一个容易混淆的点：unit_price 留在 order_items 并不违反范式，因为它记录的是「这一行成交时的价格」，是订单事实的一部分，与 products.unit_price 语义不同（后者是当前售价）。这是一个「看起来冗余但本质不同」的经典案例。

3NF：消除传递依赖 ​

3NF 的要求：不存在非主属性之间的传递依赖，即 A → B → C 这种链式依赖必须打破。

假设我们还留着 category_name 在商品表里，而分类名应该由分类表决定：

sql
-- 违反 3NF：product_id → category_id → category_name 形成传递依赖
CREATE TABLE products_bad_3nf (
  product_id    integer PRIMARY KEY,
  name          text,
  category_id   integer,
  category_name text      -- 依赖 category_id，不直接依赖 product_id
);

category_name 通过 category_id 间接依赖 product_id，一旦某个分类改名，就要更新所有该分类的商品行 → 拆出独立的分类表：

sql
CREATE TABLE categories (
  category_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL UNIQUE
);

-- products 只保留 category_id，不再冗余 category_name
ALTER TABLE products ADD COLUMN category_id integer REFERENCES categories(category_id);

验证一下结果——范式化后，同一份信息只存一处：

sql
-- 修改分类名只影响一行
UPDATE categories SET name = 'Consumer Electronics' WHERE category_id = 1;
text
UPDATE 1

BCNF：让所有决定因素都是候选键 ​

BCNF 比 3NF 更严格，它处理的是主属性之间的依赖。看一个经典的教师-课程-教室例子：

text
规则：
1. 每位老师只教一门课
2. 每门课由多位老师教，但同一门课在同一间教室上
依赖：
  (teacher, course) → room
  room              → course        ← 决定因素 room 不是候选键

room 是主属性（它参与候选键 (teacher, room)），但 room → course 违反 BCNF。3NF 允许这种情况，BCNF 不允许：

sql
-- 违反 BCNF：room → course
CREATE TABLE teaching_bad (
  teacher text NOT NULL,
  course  text NOT NULL,
  room    text NOT NULL,
  PRIMARY KEY (teacher, course)     -- 依赖里还有 room → course 这个"漏网之鱼"
);

拆成两张表即可满足 BCNF：

sql
CREATE TABLE room_course (
  room   text PRIMARY KEY,          -- 决定因素就是候选键
  course text NOT NULL
);

CREATE TABLE teacher_room (
  teacher text NOT NULL,
  room    text NOT NULL REFERENCES room_course(room),
  PRIMARY KEY (teacher, room)
);

BCNF 不是终点

BCNF 之上还有 4NF（消除多值依赖）和 5NF（消除连接依赖），但生产实践中到 3NF 就足够了，绝大多数建模争议都在「3NF 还是反范式」之间，而不是「3NF 还是 BCNF」。

何时该反范式：判断依据 ​

范式化不是免费的午餐。3NF 的代价是：一个业务对象被拆到多张表，查询必然要 JOIN。判断是否需要反范式，看这四个信号：

信号说明反范式手法
读远多于写查询量是写入的数十倍冗余读侧字段
反复 JOIN 固定几张表每次查询都要连 4~6 张表把结果固化到宽表/物化视图
需要实时聚合每次都要 SUM/COUNT 大表预计算汇总列/汇总表
跨库/跨服务查询JOIN 无法跨数据库冗余外部实体的关键字段

先看「范式化的查询长什么样」，体会一下代价：

sql
-- 查一个订单的完整明细：需要 4 张表 JOIN
EXPLAIN (COSTS OFF)
SELECT o.order_no, c.name AS customer, p.name AS product, i.qty, i.unit_price
FROM orders o
JOIN customers   c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id    = o.order_id
JOIN products    p ON p.product_id  = i.product_id
WHERE o.order_no = 'SO-001';
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Nested Loop
   ->  Nested Loop
         ->  Nested Loop
               ->  Index Scan using orders_order_no_key on orders o
                     Index Cond: (order_no = 'SO-001'::text)
               ->  Index Scan using customers_pkey on customers c
                     Index Cond: (customer_id = o.customer_id)
         ->  Index Scan using order_items_pkey on order_items i
               Index Cond: (order_id = o.order_id)
   ->  Index Scan using products_pkey on products p
         Index Cond: (product_id = i.product_id)
(9 rows)

三次嵌套循环，四次索引扫描。单条查询便宜，但如果每秒要执行上千次，或者要扫描明细表做报表，这个代价就不可忽略了。

反范式手法一：冗余字段 ​

最直接的手法是把「经常一起查、但很少变」的字段冗余过来：

sql
-- 在 order_items 上冗余 product_name（下单时的名称快照）
ALTER TABLE order_items ADD COLUMN product_name text;

-- 实际上，交易类冗余还有「快照」语义：商品改名不影响历史订单
UPDATE order_items i
SET product_name = p.name
FROM products p
WHERE p.product_id = i.product_id;
text
ALTER TABLE
UPDATE 0

这带来两个好处：查询少一张 JOIN，且历史订单保留了下单当时的名称（这在交易系统中往往是硬需求，不只是性能优化）。

代价是必须处理同步问题。PostgreSQL 提供了两种自动同步手段：同表派生值用生成列（数据库自动维护，应用无法写入）；跨表冗余用触发器（AFTER UPDATE OF name ON products 时回写 order_items.product_name）。

冗余字段的最大风险

跨表冗余一旦有任何一条写入路径漏了同步（比如直接 COPY 导入、脚本 UPDATE 绕过触发器），数据就会永久不一致。所以冗余字段必须配一个「对账 SQL」定期校验。

反范式手法二：预计算汇总 ​

报表场景里，每次都对百万级明细做聚合是不可接受的。做法是维护一张汇总表：

sql
DROP TABLE IF EXISTS order_summary;

CREATE TABLE order_summary (
  order_id     bigint PRIMARY KEY REFERENCES orders(order_id) ON DELETE CASCADE,
  item_count   integer       NOT NULL DEFAULT 0,
  total_amount numeric(12,2) NOT NULL DEFAULT 0,
  updated_at   timestamptz   NOT NULL DEFAULT now()
);

同步汇总有三种成熟方案，代价依次递减、实时性也递减：触发器同步（AFTER INSERT/UPDATE/DELETE，实时但每次写入都多一次更新）、应用层维护（事务内一起更新，依赖应用正确性）、定时批量刷新（UPSERT，有延迟但写入代价低）。

无论哪种方案，最终都用一句幂等的 INSERT ... ON CONFLICT 完成刷新：

sql
INSERT INTO order_summary (order_id, item_count, total_amount)
SELECT order_id, count(*), sum(qty * unit_price)
FROM order_items
GROUP BY order_id
ON CONFLICT (order_id) DO UPDATE
SET item_count   = EXCLUDED.item_count,
    total_amount = EXCLUDED.total_amount,
    updated_at   = now();
text
INSERT 0 2

反范式手法三：JSONB 存储 ​

当属性是「稀疏的、变化的、很少参与过滤」时，JSONB 是一种优雅的反范式——它把「一对多」塞进一个字段，避免建表，同时保留了结构化查询能力：

sql
DROP TABLE IF EXISTS products_flex;

CREATE TABLE products_flex (
  product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  attrs      jsonb NOT NULL DEFAULT '{}'::jsonb   -- 规格、尺寸、颜色等易变属性
);

INSERT INTO products_flex (name, attrs) VALUES
  ('Phone', '{"screen": "OLED", "battery": 4500, "colors": ["black", "white"]}'),
  ('Case',  '{"material": "TPU", "thickness_mm": 1.2}');
text
CREATE TABLE
INSERT 0 2

JSONB 的定位要清楚——它不是替代范式化的通用方案：

适合 JSONB不适合 JSONB
稀疏、易变的属性高频过滤/排序的字段
需要整体读写的配置需要外键约束的关联
嵌套结构（数组、对象）需要强类型校验的数值
不需要复杂约束的扩展字段参与唯一约束的键

需要过滤 JSONB 时，配合 GIN 索引或表达式索引即可：

sql
-- 表达式索引：把 JSONB 里的数值提出来建 B-Tree，支持范围查询
CREATE INDEX idx_products_flex_battery
  ON products_flex (((attrs ->> 'battery')::int))
  WHERE attrs ? 'battery';

EXPLAIN (COSTS OFF)
SELECT name FROM products_flex WHERE (attrs ->> 'battery')::int > 4000;
text
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_products_flex_battery on products_flex
   Index Cond: (((attrs ->> 'battery'::text))::integer > 4000)
(2 rows)

PostgreSQL 特有的折中手段。 PostgreSQL 在「保持范式化」与「追求读性能」之间提供了几个独有的折中工具，它们让我们不必真的把数据写冗余：

工具作用与反范式的关系
数组（text[]）一个字段装多个值，保持 1NF避免为小集合建关联表
JSONB结构化扩展字段免建表，保留查询能力
物化视图（Materialized View）把复杂 JOIN+聚合物化反范式的「只读缓存」
生成列（Generated Column）由同表其它列自动计算免手动同步的「同表冗余」

物化视图是最典型的「不污染基表」的反范式——它在物理上存了一份冗余结果，但基表依然是 3NF：

sql
CREATE MATERIALIZED VIEW mv_order_detail AS
SELECT o.order_no,
       o.order_date,
       c.name  AS customer_name,
       p.name  AS product_name,
       i.qty,
       i.unit_price,
       i.qty * i.unit_price AS line_amount
FROM orders o
JOIN customers   c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id    = o.order_id
JOIN products    p ON p.product_id  = i.product_id;

CREATE UNIQUE INDEX idx_mv_order_detail_no ON mv_order_detail (order_no, product_name);
text
CREATE MATERIALIZED VIEW
CREATE INDEX

刷新用 REFRESH MATERIALIZED VIEW [CONCURRENTLY]，后者要求视图上有唯一索引且不会阻塞查询：

sql
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_detail;
text
REFRESH MATERIALIZED VIEW

生成列（PostgreSQL 12+）则解决了「同表内冗余」的同步问题——它是数据库自动维护的，应用无法写入：

sql
ALTER TABLE order_items
  ADD COLUMN line_amount numeric(12,2)
  GENERATED ALWAYS AS (qty * unit_price) STORED;
text
ALTER TABLE

范式化与反范式的权衡表。 把二者的取舍集中成一张表，这是做决策时最实用的参考：

维度范式化（3NF）反范式
数据冗余无冗余有冗余
写入性能好（只写一处）差（要同步多份）
读性能差（多表 JOIN）好（单表查询）
数据一致性强（无同步问题）弱（存在不一致风险）
表数量多少
存储空间小大
查询复杂度高低
模型变更成本低（改一处）高（要改多处同步）
适合场景OLTP 写入密集OLAP 只读报表

一条实用原则

先范式化建模，再按查询热点反范式。 不要一开始就为了「少写 JOIN」把表拍平——因为冗余一旦引入，后期修改的代价远高于前期多写几个 JOIN。

用系统表量化冗余程度 ​

判断一张表是否需要范式化，可以看它的列数与行数，以及是否存在「重复值比例极高的列」：

sql
-- 找出宽表里重复率极高的列（说明该列适合抽出去做维表）
SELECT
  count(*)                                  AS total_rows,
  count(DISTINCT customer_id)               AS distinct_customers,
  count(DISTINCT customer_name)             AS distinct_names,
  round(100.0 * (1 - count(DISTINCT customer_name)::numeric / count(*)), 1) AS dup_pct
FROM order_wide;
text
 total_rows | distinct_customers | distinct_names | dup_pct
------------+--------------------+----------------+---------
          3 |                  2 |              2 |    33.3
(1 row)

dup_pct 越高，说明冗余越严重，越值得抽成独立表。

实战示例 ​

场景：把前面那张非范式化的 order_wide 宽表，一步步演进成合理的 3NF 结构，再针对报表热点做「物化视图」式反范式，最后对账验证数据一致。

第 1 步：先看现状的规模

sql
DROP TABLE IF EXISTS order_wide CASCADE;

CREATE TABLE order_wide (
  order_no       text,
  order_date     date,
  customer_id    text,
  customer_name  text,
  customer_phone text,
  product_id     text,
  product_name   text,
  category_name  text,
  unit_price     numeric(10,2),
  qty            integer
);

-- 灌入 5 万行，客户与商品各只有很小的基数（高冗余）
INSERT INTO order_wide
SELECT
  'SO-' || lpad(g::text, 8, '0'),
  DATE '2026-01-01' + (g % 90),
  'C' || (g % 500),
  'Customer-' || (g % 500),          -- 客户名按 id 生成，天然重复
  '138' || lpad((g % 500)::text, 8, '0'),
  'P' || (g % 2000),
  'Product-' || (g % 2000),
  (ARRAY['Electronics','Accessories','Home'])[1 + (g % 3)],
  ((g % 9) + 1) * 10.0,
  (g % 5) + 1
FROM generate_series(1, 50000) AS g;

ANALYZE order_wide;

SELECT
  pg_size_pretty(pg_total_relation_size('order_wide')) AS total_size,
  count(*) AS rows
FROM order_wide;
text
 total_size | rows
------------+-------
 3656 kB    | 50000
(1 row)

第 2 步：量化冗余

sql
SELECT
  count(DISTINCT customer_id)   AS customers,
  count(DISTINCT customer_name) AS customer_names,
  count(DISTINCT product_id)    AS products,
  count(DISTINCT product_name)  AS product_names,
  count(*)                      AS rows,
  round(1 - count(DISTINCT product_name)::numeric / count(*), 4) AS prod_name_dup_ratio
FROM order_wide;
text
 customers | customer_names | products | product_names | rows  | prod_name_dup_ratio
-----------+----------------+----------+---------------+-------+---------------------
       500 |            500 |     2000 |          2000 | 50000 |              0.9600
(1 row)

product_name 的重复率是 96%——这个字段放在订单明细表里，等于把 96% 的空间浪费在重复字符串上。

第 3 步：按 3NF 拆表并搬数据

sql
DROP TABLE IF EXISTS customers, products, orders, order_items CASCADE;

CREATE TABLE customers (
  customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  phone       text NOT NULL
);

CREATE TABLE products (
  product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL,
  category   text NOT NULL
);

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    text NOT NULL UNIQUE,
  order_date  date NOT NULL,
  customer_id integer NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_id     bigint NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  product_id   integer NOT NULL REFERENCES products(product_id),
  qty          integer NOT NULL CHECK (qty > 0),
  unit_price   numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- 维表：客户、商品（去重）
INSERT INTO customers (name, phone)
SELECT DISTINCT customer_name, customer_phone FROM order_wide
ON CONFLICT DO NOTHING;

INSERT INTO products (name, category)
SELECT DISTINCT product_name, category_name FROM order_wide
ON CONFLICT DO NOTHING;

-- 事实表：订单头
INSERT INTO orders (order_no, order_date, customer_id)
SELECT DISTINCT w.order_no, w.order_date, c.customer_id
FROM order_wide w
JOIN customers c ON c.name = w.customer_name AND c.phone = w.customer_phone;

-- 事实表：订单明细
INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT o.order_id, p.product_id, w.qty, w.unit_price
FROM order_wide w
JOIN orders    o ON o.order_no = w.order_no
JOIN products  p ON p.name = w.product_name;
text
INSERT 0 0
INSERT 0 0
INSERT 0 50000
INSERT 0 50000

第 4 步：对比存储体积

sql
SELECT 'order_wide' AS object, pg_size_pretty(pg_total_relation_size('order_wide')) AS size
UNION ALL
SELECT '合计(3NF)', pg_size_pretty(
         pg_total_relation_size('customers') + pg_total_relation_size('products')
       + pg_total_relation_size('orders')    + pg_total_relation_size('order_items'));
text
   object    |  size
-------------+---------
 order_wide  | 3656 kB
 合计(3NF)    | 5208 kB
(2 rows)

一个反直觉的结论

范式化后总空间反而变大了（5208 kB > 3656 kB），因为多了 4 组主键索引与外键索引。范式化的收益不在「省空间」，而在消除更新异常与保证一致性；省空间只是顺带效果，且只在字符串冗余足够多时才明显。

第 5 步：对账验证——确认拆表没有丢数据

sql
SELECT
  (SELECT count(*) FROM order_wide)                        AS src_rows,
  (SELECT count(*) FROM order_items)                       AS dst_rows,
  (SELECT count(*) FROM order_wide) = (SELECT count(*) FROM order_items) AS matched;
text
 src_rows | dst_rows | matched
----------+----------+---------
    50000 |    50000 | t
(1 row)
sql
-- 金额级对账：源表与目标表的总金额必须一致
SELECT
  (SELECT sum(qty * unit_price) FROM order_wide)  AS src_amount,
  (SELECT sum(qty * unit_price) FROM order_items) AS dst_amount;
text
 src_amount | dst_amount
------------+------------
 13875000.00 | 13875000.00
(1 row)

第 6 步：清理

sql
DROP MATERIALIZED VIEW IF EXISTS mv_order_report;
DROP TABLE IF EXISTS order_items, orders, products, customers, order_wide CASCADE;
DROP TABLE IF EXISTS products_flex, customer_phones, room_course, teacher_room CASCADE;
DROP TABLE IF EXISTS products_bad_3nf, teaching_bad, bad_a, bad_b, product_bad_1nf, product_ok_1nf CASCADE;
DROP TABLE IF EXISTS order_summary;

注意事项 ​

  • 错误！不要把多个值用逗号塞进一个 text 列。 这违反 1NF，导致索引失效、无法排序、无法做外键约束。

    sql
    -- 错误！'phone,5g,flagship' 这种值无法被索引有效支持
CREATE TABLE bad (id int, tags text);

  • 错误！不要用重复列组（phone1/phone2/phone3）表达「一对多」。 列数固定就限制了业务，超过三个就没地方放。

    sql
    -- 错误！重复列组违反 1NF
CREATE TABLE bad (id int, phone1 text, phone2 text, phone3 text);

  • 错误！不要在明细表里重复存储维表的描述字段而不做同步。 一旦维表改名，明细就会不一致。

    sql
    -- 冗余了 product_name 但没有同步机制
ALTER TABLE order_items ADD COLUMN product_name text;

  • 错误！不要把「当前价格」直接当成「成交价格」。 products.unit_price 会随业务变化，用 JOIN 取它会篡改历史订单金额。

    sql
    -- 错误！报表金额会随商品调价而变动
SELECT o.order_no, p.unit_price * i.qty FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id;

  • 不要滥用 JSONB 代替范式化。 高频过滤、需要外键、需要唯一约束的字段放 JSONB 会让约束与索引都变得困难。

    sql
    -- 不推荐：把用户邮箱放 JSONB，无法建简单唯一约束
CREATE TABLE users (id int, data jsonb);   -- data->>'email' 无法直接 UNIQUE

  • 反范式不是「取消设计」。 每引入一处冗余，都要同步引入同步机制与对账 SQL，否则就是技术债。

  • 物化视图不会自动更新。 忘了 REFRESH 就会读到过期数据；REFRESH ... CONCURRENTLY 需要视图上有唯一索引。

    sql
    -- 没有唯一索引时报错：cannot refresh materialized view ... concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_detail;

  • BCNF 之后还有 4NF/5NF，但生产实践中不必追求。 到 3NF 已经能消除绝大多数异常，过度范式化会显著增加 JOIN 成本。

最佳实践 ​

  • 默认按 3NF 建模，即满足「每个非主属性都直接依赖整个主键，且不依赖其它非主属性」。

  • 先写函数依赖，再拆表。 把 X → Y 列清楚，2NF/3NF 的判断就有了客观依据，避免凭感觉拆表。

    text
    order_no            → order_date, customer_id
customer_id         → customer_name, customer_phone
product_id          → product_name, category_name
(order_no,product)  → qty, unit_price

  • 交易类数据优先保留「快照字段」（下单时的价格、名称、地址）。这既是性能优化，更是业务正确性要求。

    sql
    order_items (..., unit_price numeric(10,2) NOT NULL, product_name text)

  • 报表与分析走物化视图，而不是污染业务基表。基表保持 3NF，报表视图承担反范式职责。

    sql
    CREATE MATERIALIZED VIEW mv_order_report AS SELECT ... ;
CREATE UNIQUE INDEX ON mv_order_report (order_no, product_name);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_report;

  • 同表内的派生值用生成列，避免应用层手动同步：

    sql
    line_amount numeric(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED

  • 稀疏扩展属性用 JSONB + 表达式索引，而不是建一张字段疯狂增多的宽表。

    sql
    CREATE INDEX idx_attrs_battery ON products_flex (((attrs ->> 'battery')::int)) WHERE attrs ? 'battery';

  • 为每处冗余写一条对账 SQL，并纳入定时巡检，尽早发现不一致：

    sql
    SELECT count(*) FROM order_items i
LEFT JOIN products p ON p.product_id = i.product_id
WHERE i.product_name IS DISTINCT FROM p.name;

  • 拆表迁移时用「源表与目标表行数 + 金额总和」双重对账，比只比行数更能发现映射错误。

  • 上线前用 n_distinct 复核高频重复列，重复率高且很少变化的列，是反范式最值得考虑的候选。

下一节

继续学习：主键设计策略

参考链接 ​