关系数据库设计之(双外键 )

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

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

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 值进行关联,而不是自增的无意义的数据进行关联,这样的好处是,数据无论产生任何变更后,关联关系依然有效,而且数据进入数仓的先后顺序可以随机,对采集系统的设计可以非常灵活。

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

guest
0 评论
Inline Feedbacks
View all comments
0
如果你有话对我说,请点击x
()
x