数据库的建立：从需求分析到表结构设计

建立数据库，不是把业务表单上的字段逐项复制成列。表结构会影响数据能否保持一致、查询是否可控、需求变化时改动有多大。设计得好，应用代码可以把精力放在业务规则上；设计得差，重复数据、隐含约定和相互冲突的更新会不断积累。

本文以 PostgreSQL 为例，介绍从需求分析到表结构设计的实用过程，并给出一组可以在 PostgreSQL 中执行的 SQL。示例使用一个简单的在线订单场景：客户可以有多个收货地址，订单包含多个商品，商品价格可能变化，而历史订单需要保留成交时的价格。它适合学习关系型数据库建模，不是适用于所有系统的固定模板。

一、先明确边界：数据库要保证什么

动手建表之前，先回答几个问题：系统记录哪些事实？哪些数据会变化？哪些操作必须同时成功或失败？数据需要保留多久？哪些用户或服务可以读写？这些答案比“将来可能会不会用到某个字段”更能影响设计。

以订单为例，可以先写出几条可验证的业务规则：

• 一个客户可以创建多个订单；每个订单只属于一个客户。
• 一个订单至少包含一条商品明细；同一商品在一个订单中最多出现一条明细。
• 商品当前价格可以改变，但订单需要保存下单时的价格。
• 订单中的商品数量必须是正数，单价不能为负数。
• 订单金额由明细决定，不能只靠页面传来的总金额作为可信数据。
• 订单创建时，订单记录与明细应当一并保存；其中任何一步失败，都不能留下半张订单。

这些规则决定了表之间的关系、约束和事务边界。要注意区分“数据库能可靠强制的规则”和“应用层更适合处理的规则”。非空、唯一、外键、数值范围等规则通常适合落在数据库约束中；复杂的促销资格、库存分配或多服务协作，可能需要应用逻辑、事务控制或专门的流程设计，不能仅靠几张表解决。

二、从需求中识别实体、关系和属性

可先找出业务中长期存在、需要单独查询或关联的对象。在示例中，客户（Customer）、地址（Address）、商品（Product）、订单（Order）和订单明细（Order Item）都是实体。

随后识别实体之间的关系。客户与订单是一对多；客户与地址也是一对多；订单与商品从业务角度看是多对多，因为一个订单可以有多个商品，一个商品也可以出现在多个订单中。关系型数据库通常把这种多对多关系拆成一个明细表：订单明细既连接订单和商品，也保存数量、成交单价等关系自身的属性。

字段应当表达一个明确事实。例如，订单明细中的 unit_price 是成交单价，不是商品当前的标价；两者含义不同，不能为了少建一列而混为一谈。字段名应体现含义和单位，像 weight_grams 通常比 weight 更清楚，金额则要明确采用何种币种或货币精度。

三、确定主键、唯一性和可空性

主键

每张表都应有稳定的行标识。示例使用 PostgreSQL 的 bigint GENERATED ALWAYS AS IDENTITY 生成整数主键。它适合单库或由数据库分配编号的常见业务系统。Identity 列生成值的具体方式由序列支持；序列值可能因回滚、并发等原因出现间隔，不应把它当成连续编号，也不适合单独承担发票号、对外订单号等有特殊编号规则的职责。

如果数据要在多个独立数据库间生成并合并，或需要不可预测、适合跨服务传递的标识，可以评估 UUID。UUID 会占用更多存储空间，索引特性也与整数不同；选择它并不自动带来访问控制或数据保密。主键的任务是标识一行，不是授权凭证。

唯一约束

需要区分主键和业务唯一性。客户邮箱若按业务规则不能重复，可以加唯一约束；但要先明确邮箱是否允许为空、大小写是否视为相同，以及是否允许一个邮箱被多个账户共享。数据库的普通文本唯一比较规则受类型、排序规则及数据库配置影响；不要假定它一定符合业务对大小写或 Unicode 的判断。需要特定比较规则时，应明确采用经过验证的规范化方式或适当的数据库表达式索引，并在应用中使用相同规则。

可空与默认值

NULL 不是空字符串，也不是零，而是“值缺失或未知”。若字段必须存在，应声明 NOT NULL；若缺失具有业务意义，才允许为空。默认值只在插入时没有提供该列值时生效，不能替代约束，也不能自动填补业务上未知的数据。

四、选择字段类型并表达约束

类型应匹配数据含义，而不是只看展示形式。时间点使用 timestamptz 通常比不带时区的时间戳更适合跨时区系统。PostgreSQL 会将带时区时间输入转换为内部时间点，并按会话时区显示；它不保留用户输入时所写的时区名称。如果业务还需记录用户选择的时区名称，应另存相应信息。

金额通常使用 numeric(p, s) 等精确十进制类型，而不是浮点类型。numeric(12, 2) 中，精度 p 表示总有效数字位数，标度 s 表示小数位数；此例最多有 12 位有效数字，其中小数点后 2 位。适用范围要按业务金额上限核算，且多币种场景应同时记录币种，不能默认所有货币都使用两位小数。

示例用 CHECK 表达数值范围，用外键表达引用关系，用唯一约束避免重复明细。约束使规则在所有写入入口下都成立，包括脚本、后台任务和其他服务，不只依赖某个网页表单。

五、可运行的 PostgreSQL 建表示例

以下脚本面向 PostgreSQL 15 及以上版本，可在一个空数据库中执行。它采用 CREATE TABLE IF NOT EXISTS，便于重复运行；但如果同名表已经存在，语句不会自动核对或修正旧表结构。生产环境应使用可审查、可回滚策略明确的数据库迁移，而不是依赖这项语法处理结构变更。

CREATE TABLE IF NOT EXISTS customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    full_name   text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS addresses (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id),
    label       text NOT NULL,
    line1       text NOT NULL,
    line2       text,
    city        text NOT NULL,
    postal_code text,
    country_code char(2) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    UNIQUE (customer_id, label)
);

CREATE TABLE IF NOT EXISTS products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         text NOT NULL UNIQUE,
    name        text NOT NULL,
    current_price numeric(12, 2) NOT NULL CHECK (current_price >= 0),
    active      boolean NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id),
    status      text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    currency    char(3) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS order_items (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  bigint NOT NULL REFERENCES products(id),
    quantity    integer NOT NULL CHECK (quantity > 0),
    unit_price  numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
    UNIQUE (order_id, product_id)
);

CREATE INDEX IF NOT EXISTS idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_order_items_product
    ON order_items (product_id);

这里的 ON DELETE CASCADE 只用于“删除订单时一并删除其明细”。客户、商品没有级联删除：删除仍被订单引用的客户或商品会因外键约束而失败。这是有意为之，避免删除基础记录时破坏历史订单。许多业务系统甚至不物理删除订单和商品，而是使用状态或归档标记；是否允许删除，应由保留政策和审计要求决定。

示例里的地址没有直接存入订单表。真实订单通常需要保存下单时的收货地址快照：如果订单只引用客户地址，客户之后修改地址，历史订单展示的地址也会随之改变。可在订单或独立的订单地址表中保存必要快照字段。快照不是“重复数据错误”，而是为了保留当时的业务事实。

同理，订单明细保存 unit_price，因此商品调价后历史订单仍能还原成交价。示例没有保存整单总金额，而是可以从明细计算；如果为了性能、对账或业务快照需要保存总额，就要明确它是派生值还是下单时锁定的值，并保证更新路径不会造成金额不一致。

六、原子操作要放在事务中

创建订单通常要插入订单行和多条明细。若订单创建成功而其中一条明细失败，数据就不完整。数据库事务可以让这些操作整体提交或整体回滚。下面是一个可执行的示意：先创建客户，再在事务中创建订单和明细。示例使用固定 ID 便于演示，实际程序应使用参数化查询，并获取数据库生成的 ID。

INSERT INTO customers (email, full_name)
VALUES ('[email protected]', '示例客户');

BEGIN;

INSERT INTO orders (customer_id, status, currency)
VALUES (1, 'pending', 'USD')
RETURNING id;

-- 假设上一条语句返回的 id 为 1
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (1, 10, 2, 19.90),
    (1, 11, 1, 5.50);

COMMIT;

若某条插入违反约束，应用应回滚事务，而不是继续提交不完整订单。在真实程序中，应通过驱动提供的事务接口管理事务，并将上一条 RETURNING id 返回的值绑定到后续语句。不要在多用户环境里假设新记录的主键一定是某个固定数字。

事务只保证其范围内数据库操作的原子性和相应隔离语义，不会自动让电子邮件、第三方支付或另一套数据库的操作一起回滚。涉及外部系统时，需要设计幂等处理、重试、补偿或消息发件箱等机制；不能把“开启事务”当成跨系统一致性的通用解法。

七、索引应从查询出发

索引能帮助数据库更快找到行，但会占用空间，也会增加插入、删除和更新的维护成本。索引不是越多越好，应根据真实查询、数据分布和执行计划逐步添加。

上例中的 idx_orders_customer_created 支持按客户查订单并按创建时间倒序排列；复合索引的列顺序有意义，通常要匹配查询的过滤条件和排序方式。idx_order_items_product 用于按商品查明细。PostgreSQL 会为主键和唯一约束创建相应索引，但创建外键时不会自动为引用表中的外键列创建索引。因此，是否给外键列加索引应按连接、删除检查和查询需求判断，而不是以为数据库已经替你全部建好。

索引可能被优化器选择，也可能因表很小、过滤条件不够有选择性或统计信息等原因不被使用。应使用代表性数据和 EXPLAIN 分析查询计划。执行计划显示的是规划器估算；必要时再结合实际执行分析工具评估耗时、行数和资源消耗，避免只凭索引名称判断效果。

八、规范化：避免更新异常，也不要机械拆表

数据库规范化的一个目标，是减少同一事实在多个位置重复保存。例如，若每条订单明细都重复保存商品名称和当前描述，商品改名时可能出现一部分行已更新、另一部分未更新的情况。把商品事实放在商品表中，可以避免这种更新异常。

但订单成交时的商品名称或地址快照可能是历史事实，语义上并非商品当前资料的重复副本。设计时要判断字段表示的是“现在的商品名称”还是“下单时显示的名称”。同一个外观相似的字段，业务语义不同，存储策略也不同。

可以从较清晰、可维护的关系模型开始，再根据实际需求引入缓存列、汇总表或反规范化结构。反规范化会带来同步责任：谁负责更新、何时更新、失败后如何修复，都必须有明确方案。为了尚未验证的性能猜测而预先复制大量字段，往往只会增加维护成本。

九、常见错误及修正方式

把所有内容塞进一张表

将客户、订单、商品和地址都放在一张宽表中，会重复保存客户和商品信息，也让更新、删除和查询条件变得含糊。按实体拆表，通过外键表达关系；关系自身有属性时，如数量和成交单价，使用明细表。

用逗号分隔字符串保存多个值

在 product_ids 中存储 10,11,12，会让外键、去重、范围查询和并发更新都难以正确处理。关系型数据应拆成多行。若某个字段确实是结构灵活且整体读写的数据，才考虑 JSON 等类型；JSON 并不能替代所有关系约束，也不自动解决字段校验问题。

把金额或时间存成含义不明的文本

"19.9元"、"明天下午" 这类字符串不利于排序、计算和验证。金额应使用适当的精确数值和币种；时间应选择能表达业务语义的时间类型。对“某日”与“某个时间点”要分别建模，不能混为一谈。

只在应用代码里校验

应用校验可以给用户更友好的错误提示，但数据还可能从定时任务、导入程序、后台管理工具或其他服务写入。应将适合数据库表达的规则用约束落地，并在应用层处理更高层的业务反馈。两者是分工关系，不是二选一。

把状态随意存成整数

诸如 status = 7 的写法，如果没有可靠、统一的映射，时间久了很难理解。可以使用带检查约束的文本、PostgreSQL 枚举类型或状态字典表。选择时考虑状态是否经常演进、是否需要附加说明，以及不同环境部署时的迁移难度。示例用文本加 CHECK，状态增加时需要通过迁移修改约束。

误用删除级联

级联删除很方便，也可能沿关系删除大量仍需保留的数据。对订单、账务、审计记录等尤其要谨慎。建立外键时先明确删除语义：限制删除、置空、级联，或者改用软删除。选择应来自业务保留要求，而不是追求少写几条 SQL。

把自增 ID 当业务编号

数据库生成的 ID 适合作为内部标识，不承诺连续、无空洞或不可猜测。需要对外展示的订单号，应另行设计生成规则和唯一约束；访问接口时也不能因为 ID 难猜就省略授权检查。

十、权限、安全和数据治理

表结构设计还应考虑谁能访问数据。应用连接账户通常不应使用数据库超级用户或对象所有者身份运行。应按服务职责授予所需的连接、读取和写入权限，生产环境限制迁移账户与日常运行账户的权限范围，并管理好密码和连接凭证。

SQL 中的值应使用数据库驱动的参数绑定，而不是字符串拼接。参数化查询可以避免把用户输入当作 SQL 语法解析，降低 SQL 注入风险；它不会替代身份验证、授权、字段校验或输出转义。表名、列名等 SQL 结构部分通常不能像普通值那样直接绑定，若需动态选择，应通过白名单映射到预先定义的标识符，不能把任意用户输入拼接进查询。

个人信息应按业务必要性收集、限制访问，并按适用法规和组织政策制定保留、删除、备份及审计策略。加密、脱敏和行级安全各有适用条件，也有配置和运维成本；它们不能单独代替正确的权限设计。本文示例没有加入租户隔离，因为单租户与多租户模型的约束、索引和授权策略不同，不能仅通过在每张表加一个 tenant_id 就保证隔离安全。

十一、从示例走向生产：迁移与验证

生产环境应把结构变化作为版本化迁移管理。迁移需要记录执行顺序，在开发、测试和生产环境以一致方式应用，并在数据量较大时评估锁表、回填时间、索引构建和回滚方案。增加一个非空列、重命名字段或改变类型，往往不只是执行一条 ALTER TABLE；旧版本应用是否仍能工作、回填期间如何读写，都需要纳入发布计划。

提交结构前，至少验证以下事项：

• 关键业务规则是否能用主键、外键、唯一约束、非空和检查约束表达。
• 删除、归档、历史保留和数据修改的行为是否明确。
• 金额、时区、字符编码、大小写和空值语义是否与业务一致。
• 常用查询是否有合理的过滤、排序和连接路径，索引是否经过执行计划验证。
• 并发下的事务、重试和唯一冲突是否经过测试。
• 备份能否恢复，迁移能否在接近生产的数据规模和环境中演练。
• 应用账户是否只拥有所需权限，所有用户输入是否通过安全的数据访问方式处理。

结语

数据库建模的核心不是选出一套“万能字段类型”，而是把业务事实、关系和不变量明确下来，再用合适的键、类型、约束、索引和事务表达它们。先从查询和数据生命周期出发，保留历史事实与当前状态之间的区别；先用简单、清晰的结构满足已知需求，再依据实际负载优化。这样的表结构更容易验证、演进，也更不容易在业务扩大后变成隐蔽的数据风险。