关系数据库设计之（双外键 ）

本文的设计方法主要应用于大型综合数据分析系统，由于其接入数据源种类较多且数据不稳定。所谓不稳定是指数据进入数据仓库后，外部数会发生变化，关键是这些变化会影响整体的数据分析。一般的数据仓库中采集的各种数据聚合策略，聚合后的数据能够提升整体的分析效率，但聚合后的数据更新的成本极高，会产生链条式的反应，影响一波又一波的数据。双外键的设计主要是应对这类不稳定的数据源，针对数据来源多样化、数据源无法受到自身约束的数据分析系统。

关系型数库中有所谓的主键，外键，这些都是数据库基本的特性，也统称为关系键。通常，关系键用来表示领域模型中的关联关系，这也是最常见的使用方法，而本文讨论的关系键的使用与之不同，是在特定的场景下对关系键使用，这样的场景也会在不同的应用系统中出现，所以本设计方法应该具有普适性，适用于各数仓的模型设计。

1 应用场景描述

我们先简化零售系统中的领域模型，只存在三个模型：门店、导购和订单，如下图所示：

1. 上图中每张表有logic_id，可以是MySQL 自增ID，也可以是Oracle Sequence，它们可以唯一标识一领域实例，但缺点是不包含何业务信息，无法准确的描述一个含有业务意义的实例，无法根据实际的实例匹配到已经存在的实例，往往需要多个字段组合后才标识一个实例，从数据库性能的角度观察，匹配的属性越多，性能越低，尤其在数据量过大时，性能的感知越明显。
2. 上图中三张表中分别包含code，code 是领域中的概念，能够表示不同实例之间的逻辑关系，但缺点是它们的数据是不稳定的，不稳定的原因有两种，第一种是门店的编码和导购的编码会发生变化，这些变化有可能是商户自身的错误导致或者属于正常的变化；第二种是商户在切换ERP 时，相关code 不得不发生变化。既然code 在实际的场景必定会发生变，数仓建设时就无法正常的使用code 来作为主键。
3. 按上图的表设计，我们抛开性能的因素，也可用logic_id 进行数据关联，这样可以确保code 发生变化时，只需要修改相应的code ，事实数据与维度数据以内部的logic_id 进行关联，在一般的应用系统中可以解决业务外键变更的问题，但在数仓的实际场景却不一样，数仓的数据通常会定时从应用系统同步数据，业务外键被定义为唯一标识一个实例的属性，当发生变化时，数仓为认为是一个新的实例，后期的事实数据统一与新的实例进行关联，这样就会存在逻辑上的一个实例，而在数仓中却存在了两个实例。由于应用系统不一定完全受控，也就无法提前感知变化，作为数仓只能被动响应变化。
4. 还有一个方案就是用logic_id 作为数仓中唯一标识实例的属性，真实情况下，同一个商户维度，当ERP 的发生更换，此时的logic_id 就有可能重复，无法持久地唯一标识一个实例。

综上所述，传统的关联外键的设计是无法解决数据在数仓中的唯一性，同时也无法确保业务主键发生变化后，数据统计和检索的一致性。我们需要一个新的设计方法，既可以保证数据的一致性，同时对数据变更量达到最小。

2 双外键概念的阐述

2.1 主要概念

首先，我们先理解一下事实数据的概念，事实就是事实 ，永远都不会改变，所有的事实数据关联的都是一个稳定的维度数据。而且需要真实的描述事实，体现出数据的变化，此时，针对真实的描述和稳定的关联，本文引出了两个重要的概念：

1. 逻辑模型实例：所谓逻辑实例不同于物理实例，它是一个被抽象出的概念，业务实例中code 的变化，或者ERP发生更换后，产生的实例在应用系统中只会存储最终的实例，也就是变更后的实例，针对历史数据，应用系统中可以完全忽略；但数仓中则不同，数仓需要存储有历以来的所有数据，此时，所有的变化都会以逻辑实例的形式存储，也就是说，每一次变化都会产生一个新的实例，应用系统中的一个门店、导购或其它维度数据发生变化后，在数仓中会存储多个实例，体现数据的变化过程
2. 关联键：这里的关联键不同与一般意义上的业务主键，它是一组唯一标识一个业务实例的属性的组合，并且将组合进行FarmHash，产生一个极低碰撞率的64 位整形值，例如：导购的关联键为farmHash(shopCode + guiderCode)，维度数据基于该键与对应的事实数据保证稳定的关联关系，不受外部数据的变化而变化。
3. 可变关联键：可变关联键是主要是响应变化，数据维度数据在应用系统中发生变化，数仓是无法避免去变更数据，但需要将数据的变更降到最低，所以可变关联键的设计就至关重要，它有两重作用，一是外部应用系统进行关联，主要用于检索与分组，二是与数仓中逻辑实例进行关联

文字型的描述不够直观，但仔细阅读能够理解的更透彻，下面我们通过真实案例分析双关联键的原理：

注释：

上图中门店表shops 中存在两个code，分别为code 与assoc_code，当数据不发生变化时，两个字段的值保持一致，一旦发生code 变更后，旧记录的 assoc_code 字段值则与新记录的code 保持一致，主要用于数据分组及过滤； 1）hashed_id 与shop_id 两个字段的生成规则一致，均为farmHash(shop_id)，如果存在多个主键，则拼接后再hash 2）上图描述了门店表shops 与订单表orders 之间的关联关系，以hashed_id 与shop_id 为主外键关系进行关联； 3）门店表下方的0003 的门店为数仓表中的新增记录，而应用系统中则是门店 0002 的code 变更为0003； 4）订单表orders 表下方的数据为门店编码变更后产生的新的订单数据，所以shop_id 与新门店的shop_id 进行关联；

2.2 主要概念

基于上述表结构的设计，维度表shops 中分别存在两个业务主键code，而维度表shops 与事实表orders 关联则用hash 后的数值进行关联，而该hash 值由业务数据生成，具有业务特征，即使误操作后，导致数据丢失也不会影响数据的关系。实际的查询示例如下：

SELECT t1.assoc_code, t1.name, COUNT(DISTINCT t2.no), SUM(t2.amount) FROM shops t1
LEFT JOIN orders t2 On t1.hashed_id = t2.shop_id
GROUP BY t1.assoc_code, t1.name

上述查询按门店进行分组，统计订单数量和销售金额，在这样的设计，可以确保应用系统的数据变更后，数仓中的数据变化最小，维度数据的变化，相比事实数据的变化容易的多，这样的设计也可以应用到聚合模型的设计中，可以对历史数据按天，按月的形式聚合，但需要注意的是hash_id 的设计需要增加相应的维度，否则会产生笛卡尔积内的数据重复，导购统计结果的错误。

3 总结

双外键的设计有两个关键的特点：1）同一个外键存储了两份，一份用于记录历史，而另一份用于响应变化，并提供对外检索和分组的能力；2）维度表与事实表之间的关联以带有业务特征的hash 值进行关联，而不是自增的无意义的数据进行关联，这样的好处是，数据无论产生任何变更后，关联关系依然有效，而且数据进入数仓的先后顺序可以随机，对采集系统的设计可以非常灵活。

这样的模型设计充分解决数仓数据的灵活性，也降低外部系统的变化对数仓数据的影响。尤其对数据聚合模型产生的影响比较深远，通常大数据存储的数据聚合后是无法进行修改，但实际场景中维度数据的变化是无法避免的，这就要求数仓的模型设计能够兼容这样的场景，当前很多的大数据系统基本是通过重新计算的方式来解决，或者人肉的方式解决，因此，双外键的设计将有效提升数据系统的灵活性和可用性，降低对外部系统的要求，从而提升开发效率。