让非法状态无法表示：两个数据库建模案例
作者用两个生产案例说明如何把「让非法状态无法表示」落到数据建模上。案例一：把连续时间线表示为 List (Date, Date) 会留下空隙和重叠，改为只存切分日期的 Set Date，连续与不重叠约束就天然成立，再切一刀只需往集合里加一个日期。案例二：合约系统把 fixed 与 default 合约都塞进同一张表，结束日期是可空字段，加上按单份合约修改的 API 毫无防护，线上确实出现过合约空缺，排查耗费数小时工程时间；把 default 合约从表中移除、由「没有 fixed 合约」推断得出后，空缺与可选结束日期同时消失。作者认为根源是用 OO 思维把每个概念都物化成一行，把行当作序列化对象而非命题，这会反过来污染整个系统设计。文末给出禁止合约重叠的两种做法：数据库 excludes 约束，或写模型允许重叠、读模型投影时用下一份合约的起始日截断。适合做数据建模与后端 schema 设计的工程师阅读。
02 October 2020
Here are some real life cases of applying one of my favourite principles.
I'll try to update this as I come across good examples.
2020 年 10 月 2 日
以下是我最喜欢的原理之一在真实工程里的若干应用案例。
往后遇到好的例子，我会尽量补充进来。
Case 1: Contiguous Time Periods
A straightforward way to represent a period of time is by its start and end dates ((Date, Date)):
案例一：连续的时间段
表示一段时间，最直接的做法就是给出它的起止日期（即 (Date, Date)）：
If we need to represent a timeline split in to contiguous periods, it may be tempting to represent this as a sequence of periods (e.g. List (Date, Date)):
However, with this representation there can be both gaps in the timeline and overlapping periods:
如果要把一条时间线切成若干连续的时间段，很容易想到用一串时间段来表示（比如 List (Date, Date)）：
可这样一来，时间线上既可能出现空隙，也可能出现相互重叠的时间段：
Improved Representation
We can improve this representation so that the contiguous and non-overlapping constraints always hold, and we can do this in a way that may remind you of database normalisation – by removing redundancy.
In a well formed contiguous timeline the joint start/end of the adjacent periods are redundant. Contiguous, non-overlapping splits can simply be represented by a set of dates (Set Date):
改进后的表示法
我们可以改进这种表示，让“连续”和“不重叠”这两个约束始终成立。做法或许会让你联想到数据库规范化——去掉冗余。
在一条规整的连续时间线里，相邻时间段共享的那个起点/终点本就是冗余的。连续且不重叠的切分，完全可以用一个日期集合（Set Date）来表示：
You can begin to see how this representation simplifies the system when you consider how to make a further split in the timeline. In the list representation, splitting a period requires carefully modifying the data-structure and ensuring constraints aren't violated. In the ‘set of dates’ representation you simply add a date to the set.
It is sometimes still useful to represent the periods as a sequence of start and end dates. It is trivial to project the set of dates in to this form. As long as the canonical representation is the set, the constraints will still hold.
只要想一想如何在这条时间线上再切一刀，你就能体会到这种表示法如何简化整个系统。用列表表示时，切分一个时间段得小心翼翼地改动数据结构，还要确保约束不被破坏；而换成“日期集合”，你只需往集合里加一个日期。
有时候，把时间段表示成一串起止日期仍然有用。这很容易——把日期集合投影成这种形式即可。只要规范表示（canonical representation）仍是那个集合，约束就依然成立。
Case 2: Default Contracts
In this system a customer pays us a recurring rent based upon a contract. Contracts last for a fixed amount of time, and when they expire we fall back to a ‘default contract’. The customer can have many fixed contracts, and they can sign new contracts at any time.
案例二：默认合同
在这个系统里，客户按合同向我们支付周期性租金。合同有固定期限，一旦到期，就自动回落到一份“默认合同”。一个客户可以拥有多份固定合同，也可以随时签新合同。
This was represented as:
A ‘customers’ table storing
The customer start date.
An optional end date, should the customer leave.
A ‘contracts’ table storing
The contract start date.
An optional end date, for default contracts that don’t end.
If it was a ‘fixed’ or ‘default’ contract.
Customer and contract timelines
当时的表示方式是这样的：
一张 ‘customers’ 表，存有：
客户的开始日期。
可选的结束日期，用于客户离开时。
一张 ‘contracts’ 表，存有：
合同的开始日期。
可选的结束日期，用于不会结束的默认合同。
该合同是 ‘fixed’ 还是 ‘default’。
客户与合同的时间线
This representation allows for some undesirable states that are trivial to prevent:
The customer may have gaps in their contracts.
A fixed contract may not have an end date.
Contract gaps
这种表示法容忍了一些本可轻松避免的不良状态：
客户的合同之间可能出现空隙。
固定合同可能没有结束日期。
合同之间的空隙
To make matters worse, the API for these contracts allowed clients to modify each individual contract, fixed or default, without guarding against these states. This shows how a poor choice of representation propagates itself through the design of a system.
This poor choice was not just a theoretical problem - gaps in contracts were found on more than one occasion, requiring hours of engineering effort to hunt down and fix.
更糟的是，这些合同的 API 允许客户端逐个修改每一份合同——无论固定还是默认——却完全不对上述状态设防。可见，一个糟糕的表示选择会如何顺着系统设计一路渗透下去。
这个糟糕的选择并不只是理论问题——合同空隙在现实中不止一次出现，每次都要耗费数小时的工程精力去排查和修复。
Improved Representation
This is easily improved by removing the ‘default’ contracts from the contract table. If the customer doesn’t have a fixed contract, it is assumed they are on a default contract:
Inferred default contracts
Now there can no longer be any gaps, and the end date of a contract no longer needs to be optional as it only represents fixed contracts.
It’s worth reiterating that this representation can be projected in to the previous representation using a database view if that form is more convenient. What is important is that the underlying representation enforces these constraints; it is not important how that data is viewed.
A better representation makes the manipulation of the data structure simpler. Adding a new fixed contract has been greatly simplified. There is no need to create or modify default contracts, or ensure that the contracts are contiguous.
改进后的表示法
只要把“默认合同”从合同表里拿掉，问题就迎刃而解：客户如果没有固定合同，就默认他处于一份默认合同之下。
推导得出的默认合同
这样一来，空隙再也不可能出现，合同的结束日期也不必再是可选的——因为它只表示固定合同。
值得一提的是，如果前一种形式用起来更方便，完全可以用数据库视图把这种表示投影回去。关键在于底层表示本身要强制这些约束；数据以什么形式呈现并不重要。
更好的表示让数据结构的操作更简单。新增一份固定合同变得极为轻松：不必再创建或修改默认合同，也不必再操心合同是否连续。
The Influence of Object-Oriented Thinking
I think the original design happened because of atomistic, object-oriented thinking.
In this mindset, the fixed contracts are objects, the default contracts are objects, and each of these concepts must be reified as a row in a table and never inferred. Rows are seen as a serialised object instead of a true proposition.
Using an object-oriented toolbox to design a database is antithetical to quality relational design and the principle of making invalid states unrepresentable.
It may feel “simpler” on some level, as you don’t really need to think about how to map a database design to an in-memory representation; however, as we see here, this lack of forethought inevitably leads to complexity.
面向对象思维的影响
我认为当初之所以会那样设计，是原子式、面向对象的思维造成的。
在这种思维里，固定合同是对象，默认合同也是对象，每个概念都必须被实体化成表里的一行，绝不允许靠推导得出。行被当成了序列化后的对象，而不是一个真正意义上的命题。
用面向对象的工具箱去设计数据库，与高质量的关系型设计背道而驰，也与“让非法状态无法表示”这一原则背道而驰。
从某个层面看，这或许让人觉得“更简单”，因为你不用费心去想数据库设计如何映射到内存中的表示；但正如这个例子所示，缺少这份前瞻，最终必然带来复杂性。
Further Improvements
It was left unspecified if overlapping contracts are permitted, only that gaps aren’t permitted. As it happens, this was a desirable constraint for this application and it was omitted from the first version of the article because the best solution is not clear cut.
A simple solution is to enforce this by using an ‘excludes’ constraint in the database. This is perfectly acceptable.
A more interesting way of doing this is to allow for overlapping contracts in the write model, but flatten them in a projection for the read model. A fixed contract is terminated by the start date of its next overlapping fixed contract.
A nice side effect of this design is that information is never lost, and you don’t need to mutate existing contracts to add new overlapping contracts.
进一步的改进
原文没有说明是否允许合同重叠，只说了不允许出现空隙。事实上，禁止重叠对本应用而言是个值得追求的约束；之所以初版文章没写，是因为最佳方案并不明确。
一个简单的办法是在数据库里用 ‘excludes’ 约束来强制这一点。这完全可行。
更有意思的做法是：在写模型里允许合同重叠，但在读模型的投影中把它们展平。一份固定合同会在下一份与之重叠的固定合同的开始日期处终止。
这种设计还有一个好处：信息永不丢失，而且新增重叠合同时无需改动已有的合同。