4. 数据定义与模式设计实战

数据定义语言（DDL）是数据库模式设计的落地手段。概念模型再清晰，最终都要转化为可执行的 CREATE TABLE、ALTER TABLE、CREATE INDEX 等语句。本节以电商业务为背景，同时对照图书管理场景，完整走一遍从命名约定到建表、约束、索引、注释的实战过程。

一、命名规范：先定规则，再写DDL

命名规范不是形式主义，它直接决定后续维护成本。建议在项目初期就固定以下约定，并写入团队规范文档：

• 表名、列名、索引名统一使用小写字母加下划线，避免大小写敏感问题；
• 表名用单数名词，如 user、product、order，避免 tbl_ 之类无意义前缀；
• 主键统一命名为 id，外键命名为 引用表名_id，如 user_id、product_id；
• 布尔列用 is_ 开头，如 is_deleted、is_paid；时间列用 _at 结尾，如 created_at、paid_at；
• 索引命名采用 idx_表名_列名，唯一索引用 uk_表名_列名，外键约束用 fk_表名_引用表名。

需要特别提醒：order、user、group 等词在部分数据库中是保留字。MySQL 中可用反引号包裹，PostgreSQL 中需用双引号，但更稳妥的做法是改用 orders、users、user_group 等复数或变体名，从源头规避转义问题。

二、电商核心表结构设计

一个最小可用的电商模型通常包含用户、商品、订单、订单明细四张表。下面给出 MySQL 8.0 语法的建表示例，字段类型选择兼顾存储效率与业务语义。

CREATE TABLE users (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户主键',
  username    VARCHAR(32)  NOT NULL COMMENT '登录名，唯一',
  email       VARCHAR(128) NOT NULL COMMENT '邮箱，唯一',
  phone       VARCHAR(20)  DEFAULT NULL COMMENT '手机号',
  is_active   TINYINT(1)   NOT NULL DEFAULT 1 COMMENT '是否启用：1启用 0禁用',
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_users_username (username),
  UNIQUE KEY uk_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

CREATE TABLE products (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品主键',
  name        VARCHAR(128) NOT NULL COMMENT '商品名称',
  price       DECIMAL(10,2) NOT NULL COMMENT '单价，单位元',
  stock       INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存数量',
  category_id INT UNSIGNED DEFAULT NULL COMMENT '分类ID',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id),
  KEY idx_products_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单主键',
  user_id     BIGINT UNSIGNED NOT NULL COMMENT '下单用户',
  order_no    VARCHAR(32) NOT NULL COMMENT '订单编号，业务唯一',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  status      TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_orders_order_no (order_no),
  KEY idx_orders_user_created (user_id, created_at),
  CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE order_items (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '明细主键',
  order_id    BIGINT UNSIGNED NOT NULL COMMENT '所属订单',
  product_id  BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
  quantity    INT UNSIGNED NOT NULL COMMENT '购买数量',
  unit_price  DECIMAL(10,2) NOT NULL COMMENT '下单时单价快照',
  PRIMARY KEY (id),
  KEY idx_items_order (order_id),
  KEY idx_items_product (product_id),
  CONSTRAINT fk_items_orders FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_items_products FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

几个关键决策值得说明。金额一律用 DECIMAL 而非 FLOAT，避免浮点误差；order_items.unit_price 保存下单时的价格快照，因为商品价格会变动，订单必须固化历史价格；orders 表上的 idx_orders_user_created (user_id, created_at) 是联合索引，用于支撑“查询某用户最近订单”这类高频场景，遵循最左前缀原则。

三、主键、外键与索引的取舍

主键选择上，推荐使用自增 BIGINT 或雪花算法生成的分布式 ID。自增主键在 InnoDB 中顺序写入，页分裂少，性能好；但分库分表时需换成全局唯一 ID。业务主键（如 order_no）应通过唯一索引保证，而不是当作物理主键。

外键在工程实践中存在争议。它能保证引用完整性，但会带来插入顺序约束、级联操作风险和锁竞争。互联网高并发场景常选择“逻辑外键”——即不建物理外键，由应用层保证一致性，仅在需要时通过索引加速关联查询。教学与中小型系统建议保留物理外键，让数据库兜底。

索引不是越多越好。每个二级索引都会增加写入成本。经验法则是：为 WHERE、JOIN、ORDER BY 中高频出现的列建索引；区分度低的列（如性别、状态只有几个值）单独建索引收益有限，更适合放进联合索引的后缀位置。

四、注释与文档化

注释分两层：表注释和列注释。MySQL 中通过 COMMENT 关键字在 DDL 中直接声明，PostgreSQL 则使用 COMMENT ON COLUMN 单独语句。注释应说明业务含义而非重复列名，例如 status 列必须写清每个取值的含义，否则半年后没人记得 2 代表什么。

-- PostgreSQL 注释写法
COMMENT ON TABLE orders IS '订单主表';
COMMENT ON COLUMN orders.status IS '0待支付 1已支付 2已发货 3已完成 4已取消';

对于状态类字段，除了注释，更推荐单独建一张字典表或用枚举类型约束取值范围，让数据库层面就能拦截非法值。

五、图书管理场景的对照

图书管理模型与电商高度相似：books 对应 products，borrow_records 对应 orders，readers 对应 users。差异点在于：图书有 ISBN 唯一标识，适合做唯一索引；借阅记录需要 borrowed_at 和 returned_at 两个时间列，且 returned_at 允许为 NULL 表示未归还；库存概念变为“可借副本数”，需要防止超借，通常配合事务与行锁处理。

DDL 设计完成后，建议把建表脚本纳入版本控制，配合 Flyway 或 Liquibase 做迁移管理。模式变更和代码变更一样需要评审、需要回滚方案，这是工程化数据库实践的基本要求。