PostgreSQL / 系统课时
设计业务数据模型
设计业务数据模型
本课把订单系统的需求清单转换为四张核心表,并解释主键、外键、快照字段和关系基数。完成后,你将得到可继续细化为 PostgreSQL DDL 的模型草案。
学习目标
- 将业务实体转换为表、字段和关联关系
- 正确判断一对多关系及外键方向
- 区分实时属性与必须保留的业务快照
确定表和主键
客户、商品、订单、订单明细分别对应 customer、product、orders、order_item。每张表使用 bigint 代理主键,业务编号单独设置唯一约束。
代理主键负责稳定关联,业务编号负责展示和外部交互。不要把可能变化的手机号、商品编码或订单展示号直接作为所有关联的主键。
设计实体关系
一个客户可以拥有多个订单,一个订单包含多条明细,一个商品也可以出现在多条订单明细中。因此,订单明细是订单与商品之间的关联实体,同时保存数量和成交价格。
下面是模型草案的片段示例,依赖本章统一采用的订单业务场景:
-- 片段示例:只表达表之间的关系,完整约束将在下一课实现
CREATE TABLE customer (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_no varchar(32) NOT NULL
);
CREATE TABLE product (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_code varchar(32) NOT NULL,
name varchar(100) NOT NULL
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_no varchar(32) NOT NULL,
customer_id bigint NOT NULL
);
CREATE TABLE order_item (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL,
product_id bigint NOT NULL,
quantity integer NOT NULL,
unit_price numeric(12, 2) NOT NULL
);预期结果
PostgreSQL 成功创建 customer、product、orders、order_item 四张表。
当前版本仅建立字段和主键,尚未加入唯一、检查和外键约束。选择字段类型
金额使用 numeric(12, 2),避免浮点误差;数量使用 integer;时间使用 timestamptz,统一记录带时区的业务时间;状态使用长度受控的 varchar,再配合检查约束限制取值。
字段长度应来自业务边界。例如订单号上限为 32 个字符,就不必直接使用无限长度文本类型。长度不是越大越安全,而是越明确越容易发现异常输入。
保留业务快照
商品名称和价格会变化,但历史订单必须保持下单时的事实。因此,order_item 至少应保存成交单价;若业务要求历史页面展示原始商品名称,还应增加 product_name 快照字段。
快照不是重复设计,而是对历史事实的保存。实时商品表回答“现在是什么”,订单明细回答“交易发生时是什么”。
控制冗余和派生字段
订单总金额可以由明细计算,但频繁查询时可在 orders 中保存 total_amount。保存后必须由同一事务写入,并通过测试保证它等于明细金额之和。
不要同时保存大量可随时计算、又缺少一致性维护机制的字段。冗余字段必须有明确用途、维护位置和校验方式。