2022年数据库建模经验总结汇编 .pdf

《2022年数据库建模经验总结汇编 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库建模经验总结汇编 .pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库如何建模笔者从 98 年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多， 创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。提起数据建模来， 有一点是首先要强调的， 数据建模师和 DBA有着较大的不同，对数据建模师来说， 对业务的深刻理解是第一位的，不同的建模方法和技巧是为业务需求来服务的。 而本文则暂时抛开业务不谈， 主要关注于建模方法和技巧的经验总结。从目前的数据库及数据仓库建模方法来说，主要分为四类。第一类是大家最为熟悉的关系数据库的三范式建模，通常我们将三范式建模方法用于建立各种操作型数据库系统。第二类是 Inmon 提倡的三范式

2、数据仓库建模， 它和操作型数据库系统的三范式建模在侧重点上有些不同。Inmon 的数据仓库建模方法分为三层，第一层是实体关系层，也即企业的业务数据模型层， 在这一层上和企业的操作型数据库系统建模方法是相同的 ; 第二层是数据项集层，在这一层的建模方法根据数据的产生频率及访问频率等因素与企业的操作型数据库系统的建模方法产生了不同; 第三层物理层是第二层的具体实现。第三类是 Kimball 提倡的数据仓库的维度建模， 我们一般也称之为星型结构建模，有时也加入一些雪花模型在里面。维度建模是一种面向用户需求的、容易理解的、访问效率高的建模方法，也是笔者比较喜欢的一种建模方式。第四类是更为灵活的一种建模

3、方式，通常用于后台的数据准备区， 建模的方式不拘一格，以能满足需要为目的，建好的表不对用户提供接口，多为临时表。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - 下面简单谈谈第四类建模方法的一些的经验。数据准备区有一个最大的特点， 就是不会直接面对用户， 所以对数据准备区中的表进行操作的人只有ETL工程师。ETL工程师可以自己来决定表中数据的范围和数据的生命周期。下面举两个例子：1)数据范围小的临时表当需要整合或清洗的数据量过大时

4、，我们可以建立同样结构的临时表，在临时表中只保留我们需要处理的部分数据。这样，不论是更新还是对表中某些项的计算都会效率提高很多。 处理好的数据发送入准备加载到数据仓库中的表中，最后一次性加载入数据仓库。2)带有冗余字段的临时表由于数据准备区中的表只有自己使用，所以建立冗余字段可以起到很好的作用而不用承担风险。举例来说，笔者在项目中曾遇到这样的需求， 客户表 客户 ID， 客户净扣值 ，债项表 债项 ID，客户 ID，债项余额，债项净扣值 ，即客户和债项是一对多的关系。其中，客户净扣值和债项余额已知，需要计算债项净扣值。计算的规则是按债项余额的比例分配客户的净扣值。这时，我们可以给两个表增加几个

5、冗余字段，如客户表 客户 ID，客户净扣值，客户余额 ，债项表 债项 ID，客户 ID，债项余额，债项净扣值，客户余额，客户净扣值 。这样通过三条 SQL就可以直接完成整个计算过程。 将债项余额汇总到客户余额， 将客户余额和客户净扣值冗余到债项表中，在债项表中通过(债项余额客户净扣值 / 客户余额 ) 公式即可直接计算处债项净扣值。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 另外还有很多大家可以发挥的建表方式，如不需要主键的

6、临时表等等。总结来说， 正因为数据准备区是不对用户提供接口的，所以我们一定要利用好这一点，以给我们的数据处理工作带来最大的便利为目的来进行数据准备区的表设计。行业借鉴经验：数据仓库架构经验谈对于数据仓库的架构方法， 不同的架构师有不同的原则和方法，笔者在这里来总结一下当前常采用的架构方式及其优缺点。这些架构方式不限于某个行业，可以供各个行业借鉴使用。首先需要说明的一点是， 目前在数据仓库领域比较一致的意见是在数据仓库中需要保留企业范围内一致的原子层数据。而独立的数据集市架构 (Independent data marts)没有企业范围内一致的数据，很可能会导致信息孤岛的产生，除非在很小的企业内

7、或只针对固定主题，否则不建议建立这样的架构方式。联邦式的数据仓库架构 (Federated Data Warehouse Architecture)不管是在地域上的联邦还是功能上的联邦都需要先在不同平台上建立各自的数据仓库，再通过参考(reference)数据来实现整合，而这样很容易造成整合的不彻底，除非联邦式的数据仓库架构也采用Kimball 的总线架构 (Bus Architecture)中类似的功能， 即在数据准备区保留一致性维度(Conformed Table) 并不断更新它。所以，这两种架构方式不在讨论范围之内。下面主要讨论剩下的三种架构方式。1)三范式 (3NF)的原子层 +数据集

8、市这样的数据仓库架构最大的倡导者就是数据仓库之父Inmon，而他的企业信息工厂 (Corporate Information System)就是典型的代表。这样的架构也称之为企业数据仓库 (Enterprise Data Warehouse ，EDW) 。企业信息工厂的实现方式是，名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - 首先进行全企业的数据整合，建立企业信息模型，即EDW 。对于各种分析需求再建立相应的数据集市或者探索仓

9、库，其数据来源于EDW 。三范式的原子层给建立OLAP 带来一定的复杂性，但是对于建立更复杂的应用，如挖掘仓库、探索仓库提供了更好的支持。这类架构的建设周期比较长，相应的成本也比较高。2)星型结构 (Star Schema) 的原子层 +HOLAP 星型结构最大的倡导者是Kimall ，他的总线架构是该类架构的典型代表。总线架构实现方式是， 首先在数据准备区中建立一致性维度、建立一致性事实的计算方法 ; 其次在一致性维度、一致性事实的基础上逐步建立数据集市。每次增加数据集市， 都会在数据准备区整合一致性维度，并将整合好的一致性维度同步更新到所有的数据集市。 这样，建立的所有数据集市合在一起就是

10、一个整合好的数据仓库。正是因为总线架构这个可以逐步建立的特点，它的开发周期比其他架构方式的开发周期要短， 相应的成本也要低。 在星型结构的原子层上可以直接建立聚集，也可以建立 HOLAP 。 笔者比较倾向于 Kimball 的星型结构的原子层架构，在这种架构中的经验也比较多。3)三范式 (3NF)的原子层 +ROLAP 这样的数据仓库架构也称为集中式架构(Centralized Architecture)，思路是在三范式的原子层上直接建立ROLAP ，做的比较出色的就是MicroStrategy 。在三范式的原子层上定义ROLAP 比在星型结构的原子层上定义ROLAP 要复杂很多。采用这种架构

11、需要在定义ROLAP 是多下些功夫， 而且 ROLAP 的元数据不一定是通用的格式， 所以对 ROLAP 做展现很可能会受到工具的局限。这类架构和第一类很相似，只是少了原子层上的数据集市。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - 总结来说，这三种数据仓库的架构方式都是不错的选择。对于需要见效快、成本低的项目可以考虑采用第二种总线架构，对于资金充足并有成熟业务数据模型的企业可以考虑采用第一种架构或第三种架构。应用难点技巧：变

12、化数据捕获经验谈在数据仓库系统中， 一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获 (Change Data Capture，CDC) 就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1)文件或者表的全扫描对比，2)DBMS 日志获取， 3)在源系统中增加触发器获取， 4)基于源系统的时间戳获取， 5)基于复制技术的获取，6)DBMS 提供的变化数据捕获方法等。其中，由DBMS 提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS 来完成。像银行、电信等很多行业的操作记录生成后就不会改变，只有像客户、 产品等信息会随时间发生缓慢的变化，所以通常的变化数据捕获是针对

13、维度表而言的。Kimball 对缓慢变化维的分析及应对策略基本上可以处理维度表的各种变化。而对于一些零售行业， 像合同表中的合同金额类似的数值在录入后是有可能会发生改变的， 也就是说事实表的数据也有可能发生变化。通常对于事实表数据的修改属于勘误的范畴，可以采用类似缓慢变化维TYPE 1的处理方式直接更新事实表。笔者不太赞同对事实表的变化采用快照的方式插入一条新的事实勘误记录，这样会给后续的展现、分析程序带来太多的麻烦。接下来要讨论的是笔者曾经遇到的一个颇为棘手的事实表数据改变的问题，该事实表的主键随表中某些数据的变化发生改变。以其中的一个合同表为例， 该名师资料总结 - - -精品资料欢迎下载

14、 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - 合同表的主键是由“供货单位编号”+“合同号”生成的智能主键，当其中的“供货单位编号”和“合同号”中任何一个发生变化时，该合同表的主键都会发生变化，给变化数据捕获带来了很大的麻烦。项目中，笔者的处理方式是采用触发器的办法来实现变化数据捕获。具体的实现方式是：1)建立一个新表作为保存捕获的数据表使用，其中字段有“原主键”、 “修改后主键”、及其他需要的字段，称为“合同捕获表”。2)在原合同表 Delete 和 Update

15、时分别建立触发器， 当删除操作发生时， 建在 Delete 上的触发器会插入一条记录到“合同捕获表”，其中“修改后主键”字段为空，表示该记录是删除的记录; 当发生更新时，将“原主键”、“修改后主键”及其他需要记录的字段都保存入“合同捕获表”中，表示该记录被修改过，如果“原主键”和“修改后主键”不同，则表示主键被修改，如果相同，则表示主键没有被修改。3)由于操作系统中的主键通常会成为数据仓库中事实表的退化维度，可能仍会起主键的作用。 所以在数据加载时， 需要分情况判断“合同捕获表”的数据来决定是否更新事实表中的退化维度。可以说，这样的基于触发器的变化数据捕获方法并不是一个很好的选择。首先这需要对

16、源系统有较大的权限; 其次，触发器会给源系统的性能带来很大的影响。所以除非是没有别的选择，否则不建议采用这种方法。而对于这样的情况， 我们在建立操作型数据库系统时完全可以避免。下面是对操作型数据库系统建立者的几点建议：1) 操作型系统的主键不要建立成智能型的，至少不要建立成会变化的。 2)操作型系统的表中需要加入操作人和操作时间名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - 字段，或者直接加入时间戳。 3)操作型系统中操作型数据最好不要直接在原值上修改，可以采用“冲红”的方式加入新的记录。这样后续建立数据仓库时就不需要考虑事实表数据的变化问题。最后，期待各大数据库管理系统的厂商能尽快在DBMS 层提供功能强大、简单好用的变化数据捕获功能，目前Oracle 已经有了这个功能。毕竟技术方面复杂的事情留给厂商做是一个趋势，而我们做应用的则更关注于业务。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -