为什么要从列名开始定

在 LabHub 中继续学习

一句话总结

先确定列名不是为了美观，而是因为没有标准时，同一含义的列会出现三套不同的名称和长度，代价最终会在三年后的迁移中支付。

概念图: 三年后的迁移中 · 合并时 · 悄无声息地被截断 · 做关联查询、构建统一查询，或三年后迁移数据时

为什么这是个问题

没有标准，开发也能顺利进行，因为各团队都会各自合理地命名。问题只有在把三套系统合并时才暴露出来——统一查询页面、结算对账，以及新一代系统迁移。

最棘手的是长度。如果一个地方把客户姓名定义为 50 个字符，另一个地方只留 30 个字符，那么长姓名出现时就会悄无声息地被截断。由于不会报错，没人会察觉，几个月后才以“这个客户姓名为什么变成这样”的咨询暴露出来。

三年后会发生什么

如果项目初期没有制定数据标准，就会变成这样。

고객관리 팀:  CUSTOMER   (CUST_NAME    VARCHAR(50))
주문 팀:      ORDERS     (CUSTOMER_NM  VARCHAR(100))
정산 팀:      SETTLEMENT (CUST_NM      VARCHAR(30))

同样是客户姓名，却有三种名称、三种长度。最初看不出任何问题，直到做关联查询、构建统一查询，或三年后迁移数据时才会爆发。

构建统一搜索页面时，每张表都要使用不同的列名
把 50 个字符的姓名写入结算团队的表时，会被截断，而且是静默截断
新一代项目迁移时，只能由人工手工制作映射表

因此，SI 项目会在开发开始前确定数据标准。公共项目还会按照相关数据库标准化指南，把标准词、标准域、标准术语和标准代码定义书作为交付物。

标准的四个层级

1. 표준단어사전   의미의 최소 단위와 그 약어
                  고객→CUST, 주문→ORD, 상품→PROD, 명칭→NM, 일자→DT,
                  금액→AMT, 수량→QTY, 번호→NO, 여부→YN, 코드→CD

2. 표준도메인     같은 성격의 값이 갖는 타입·길이 규칙
                  금액 → NUMBER(15,2)   일자 → CHAR(8)
                  여부 → CHAR(1) Y/N    코드 → VARCHAR(10)
                  명칭 → VARCHAR(100)   번호(내부키) → NUMBER(18)

3. 표준용어       단어를 조합한 논리명과 물리명
                  고객명 → CUST_NM (도메인: 명칭)
                  주문금액 → ORD_AMT (도메인: 금액)
                  주문일자 → ORD_DT (도메인: 일자)

4. 컬럼 정의      실제 DDL
                  ORD_AMT NUMBER(15,2) NOT NULL DEFAULT 0

遵循这个顺序，无论谁来设计，都会得到相同的名称和类型。 新增列时也只需判断“它属于哪个域”，其余部分即可自动确定。

坦率看待行业惯例

国内 SI 中有些惯例可能并不理想。

日期使用 CHAR(8) YYYYMMDD——DATE 类型更好，确实如此。
布尔值使用 CHAR(1) Y/N——BOOLEAN 更好，也确实如此。
所有表都带 REG_DT、REG_ID、UPD_DT、UPD_ID——这些审计列非常实用。
逻辑删除（DEL_YN）——不做物理删除，以便恢复与审计。

前两项主要是为了兼容遗留系统。如果只有新系统采用不同方式，每个集成与迁移节点都会产生转换代码；只要漏掉一处，错误数据就会悄悄积累。 其成本可能高于改进类型带来的收益。

标准不是“最佳方案”，而是“共同约定”。 理解这一点后，问题就会从“为什么要用这么落后的方式”，转变为“什么时候、用什么方式可以改变这项标准”。

不过，逻辑删除需要格外谨慎。只要有一个查询漏掉 DEL_YN='N' 条件，已删除数据就会重新出现在页面上。因此，应设计为查询一律通过视图进行，至少也要把它加入代码评审清单。

约束不是文档，而是代码

如果表定义书写着“必填”，DDL 中却没有 NOT NULL，这条规则就不会得到执行。约束必须设置在数据库中，才能被强制遵守。

约束	防止什么	SI 现场的现实
PRIMARY KEY	重复记录	必须设置
NOT NULL	必填值缺失	必须设置
CHECK	值范围或代码值违规	经常省略，但应该设置
UNIQUE	业务键重复	经常省略，是重复数据事故的主因
FOREIGN KEY	引用完整性	存在争议
DEFAULT	意料之外的 NULL	设置后可简化代码

FK 之所以存在争议，是因为大批量作业中的 FK 检查会影响性能，迁移时会强制装载顺序，并且与逻辑删除冲突。因此，有些组织规定“FK 只在开发环境启用，生产环境移除”。

无论选择哪一种，都要明确决定并形成文档。 最糟糕的是有些表有、有些表没有。那样就没人知道完整性究竟由应用还是数据库保证。

索引是设计交付物

如果把索引当成“慢了以后再加”的东西，上线后必然吃苦。应在设计阶段梳理查询模式，并同步设计索引。

화면 SCR-021 주문조회: WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?
  → IX_ORDERS_01 (CUST_ID, ORD_DT)

배치 BAT-005 일마감:  WHERE ORD_DT = ? AND ORD_STS_CD = '완료'
  → IX_ORDERS_02 (ORD_DT, ORD_STS_CD)

复合索引的关键是列顺序，规则如下。

用于等值条件（=）的列放在前面
用于范围条件（BETWEEN、>、<）的列放在后面
选择度更高（值种类更多）的列尽量靠前

(CUST_ID, ORD_DT) 与 (ORD_DT, CUST_ID) 是完全不同的索引。对 WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ? 而言，前者才正确；后者必须扫描整个日期范围，再从中筛选客户。

而且，索引不是免费的。 每次 INSERT、UPDATE、DELETE 都必须同步更新索引，因此在有 10 个索引的表中批量装载数据会非常慢。这也是迁移时先删除索引、装载完成后再重建的原因。

代码表——与硬编码作战

假设订单状态保存为 '01'、'02'、'03'，这些值的含义放在哪里？

应用常量——容易在不同页面中被分别硬编码
公共代码表——集中管理，运行期间也可新增

SI 项目几乎总是选择后者，结构通常如下。

CREATE TABLE COMMON_CODE (
  GRP_CD  VARCHAR(20) NOT NULL,   -- 'ORD_STS'
  CD      VARCHAR(20) NOT NULL,   -- '01'
  CD_NM   VARCHAR(100) NOT NULL,  -- '접수'
  SORT_NO INTEGER NOT NULL,
  USE_YN  CHAR(1) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (GRP_CD, CD)
);

需要注意一点：不能因为公共代码表中存在某个值，就允许它进入任意字段。应给状态列设置 CHECK，至少也要明确代码值由哪一层验证。“已经有公共代码表，所以没问题”很多时候其实意味着没有任何地方在验证。

表定义书应从数据库生成

表定义书是设计交付物，但开发结束时，文档与实际 schema 几乎总会出现偏差。

因此，实务上的办法是：最终表定义书从真实数据库的元数据生成。 列清单、类型、NULL 与否、默认值和约束都可以通过查询提取，只有**说明（comment）**需要人工填写。

如果把说明写入数据库的 COMMENT 功能，文档与 schema 就能始终同步。若所用 DBMS 不支持这一点，至少也应把生成脚本纳入版本管理，并由脚本生成文档。一旦靠人工维护，文档很快就会变成谎言。

在实际项目中

比没有标准更常见的失败，是制定了标准却无人遵守。定义书虽然存在，表中却混入不符合标准的名称，而且没有任何检查。

因此，标准不能只存在于文档里，必须采用可以自动检查的形式。不手写表定义书，而是从 pragma_table_info 或系统目录中提取，也是同一个道理——手写定义书必然与实际 schema 出现偏差，而有偏差的定义书甚至比没有更糟，因为阅读者会相信它。

约束也一样。如果“数量必须大于等于 1”只写在文档里，迟早会写入 0。用 CHECK 强制后，这句话就成为代码，也就没有不被遵守的余地。