MariaDB 教程 / 通过规范化减少重复数据并设计订单数据库

规范化通过分析“哪些键决定哪些事实”,拆分数据以减少重复的当前信息与更新异常。把客户或商品的当前属性与购买时的事实区分开,可以让现用姓名只在一处修改,同时保留旧订单的成交单价。

找出重复保存同一事实造成的异常

把客户当前姓名复制到每条订单明细,部分更新后可能让同一客户同时拥有多个现用姓名。删除最后一条明细,还可能删掉仅存的客户信息;新客户尚未下单时,也可能无处存放资料。这些分别是更新、删除与插入异常,原因是不同事实被绑在同一种记录中。

事实 决定它的键 保存位置
客户当前姓名与城市 customer_id customers
商品当前名称与售价 product_id products
订单客户、时间与状态 order_id orders
购买数量与成交单价 order_id+product_id items

分析不是看到重复文本就一律删列,而是确定它是否真是同一个事实。把商品清单塞进逗号分隔字符串,在需要分别查询数量时不合适;但这也不表示任何文本或 JSON 都必须无条件拆散,应根据数据领域与用途判断。

依赖关系的基础可参阅官方 第二范式说明与 第三范式说明。下面通过订单样本观察具体问题,不把“表越多越规范”当作设计标准。

创建规范化样本与用于对照的平铺副本

以 course_user 连接学习服务器,检查 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。本节独立使用 c37_customers、c37_products、c37_orders、c37_items,以及稍后创建的 c37_flat。已有同名对象时,先查看定义和记录,不要覆盖未知用途的数据。

在 MariaDB 客户端执行完整代码。也可按 SQL 文件与 SOURCE 教程保存 UTF-8 文件,用实际路径加载。以下四表不依赖上一节的对象,按父记录先于子记录的顺序建立。

USE shopcourse;
CREATE TABLE c37_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c37_order_customer FOREIGN KEY (customer_id)
  REFERENCES c37_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c37_item_order FOREIGN KEY (order_id)
  REFERENCES c37_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c37_item_product FOREIGN KEY (product_id)
  REFERENCES c37_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c37_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c37_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c37_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c37_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

姓名、城市与商品字符串保留原始韩文。商品 1~4 分别表示笔记本、圆珠笔、马克杯和台灯;客户 1、3 位于首尔,2 位于釜山,4 位于大田。用编号核对关系与结果,保持 UTF-8 字面量一致。

确认 c37_flat 尚不存在,再执行下面的对照代码。CREATE TABLE AS SELECT 建立独立的平铺副本,用于观察重复,不会自动复制所有源表主键、外键与约束,也不是完整备份或会跟随原表变化的视图。

CREATE TABLE c37_flat AS
SELECT o.order_id,i.product_id,c.customer_id,c.customer_name,
 p.product_name,i.quantity,i.unit_price
FROM c37_orders o JOIN c37_customers c ON c.customer_id=o.customer_id
JOIN c37_items i ON i.order_id=o.order_id
JOIN c37_products p ON p.product_id=i.product_id;
SELECT customer_id,customer_name,COUNT(*) AS repeated_rows
FROM c37_flat GROUP BY customer_id,customer_name ORDER BY customer_id;
START TRANSACTION;
UPDATE c37_flat SET customer_name='김민수 변경' WHERE order_id=101;
SELECT DISTINCT customer_id,customer_name FROM c37_flat
WHERE customer_id=1 ORDER BY customer_name;
ROLLBACK;

客户 1 的姓名在三条明细行重复出现:订单 101 两条、102 一条。只修改订单 101 的副本姓名后,同一个 customer_id 同时出现原名与“姓名已更改”的韩文值。ROLLBACK 撤销这次副本修改,原来的规范化客户表没有被改变。

平铺副本还只包含有匹配订单与明细的客户,客户 4 没有订单,因此不在其中。这提醒我们:一份适合明细报表的查询结果,并不自动适合作为全部客户事实的唯一存储位置。

追踪部分依赖与传递依赖

明细的复合键是 order_id 与 product_id,而下单时间只由 order_id 决定。如果把它放进每条商品明细,就让只依赖复合键一部分的订单事实重复出现。把下单时间留在 orders,可以把订单属性与明细数量、成交单价分开管理,这就是检查部分依赖的实际意义。

订单编号决定客户编号,客户编号又决定客户当前姓名。如果把当前姓名复制到每张订单,同一次姓名修改就要分散更新许多记录。用 customer_id 引用客户表,可以让当前姓名有一个明确的权威来源;检查这种跨非键属性的依赖,是理解传递依赖的重要一步。

不要仅背“第二范式”“第三范式”名称。先问每个字段描述谁、由什么键确定、什么时候会改变,再检查它是否被放在合适的表。关系键与实际约束可以结合 表关系和外键设计教程核对。

把历史成交价保留为另一种事实

items.unit_price 目前可能等于 products.price,但不能因为数值相同就删除它。前者是该次购买的单价,后者是商品当前售价,描述的是不同时间的事实。目录日后调价,旧订单金额仍应按成交值重现。

例如商品 1 的当前价改为 4000,订单 101 中原来的两件商品仍按 3500 的成交单价计算为 7000,而不是变成 8000。可以在独立练习事务中只改目录价格,读取现价、成交价和明细金额,再回滚;不要修改历史明细去配合当前目录。

收货地址、订单上显示的姓名等,也可能需要购买时快照。应明确它们是历史证据还是当前资料,并规定捕获时点和更新政策。保留必要历史,与不加定义地重复一份“当前姓名”,并不是同一类设计。

用真实约束落实键与业务条件

创建四张表本身不会阻止错误引用。应定义 PRIMARY KEY、UNIQUE、NOT NULL、CHECK 与外键,再验证重复键、缺失父记录和无效数量是否被拒绝。约束行为可对照官方 外键文档,不能仅靠字段名称看起来相似。

本节模型把同一订单里的同一种商品合在一条明细,复合键因此拒绝重复的订单—商品组合。若需要不同规格、批次、分批发货或独立折扣行,应重新评估明细编号与其他唯一性条件。样本是分析起点,不是所有订单系统的完整答案。

客户可以在没有订单前独立存在,删除某单最后一条明细也不应抹掉客户身份。用小样本观察这些插入和删除边界,比只检查一份连接报表是否显示正确,更能发现存储模型的问题。

有依据地引入反规范化并清理练习

规范化提供的是更新一致性的基础,查询速度还取决于语句、索引和数据量。确实需要报表汇总表时,应明确源数据、刷新时机、异常恢复与核对规则;JOIN 多并不是无维护政策地复制当前信息的充分理由。

本节平铺副本适合做异常对照,但不能把它当作自动维护的当前资料来源。实际设计中应先证明重复数据解决了什么问题,再确定怎样防止更新后不一致,而不是只比较字段数量。

重练前检查五张练习表的定义与记录,备份需要保留的内容。下面仅删除确认可丢弃的本节对象,先删对照副本,再按子表到父表的顺序清理。DROP TABLE 会移除结构与全部数据,普通 ROLLBACK 无法恢复,应与数据回滚实验分开。

DROP TABLE shopcourse.c37_flat;
DROP TABLE shopcourse.c37_items;
DROP TABLE shopcourse.c37_orders;
DROP TABLE shopcourse.c37_products;
DROP TABLE shopcourse.c37_customers;