高质量数据库建模系列课程<11> PPT & 讲义 -- NULL之别传2

浏览: 2330

时光荏苒,本课已经出了近半年,到今天(2016-6-16)为止,一共有1374名观众,非常感谢大家能有耐心听我的课。从本周开始,重新进入正常的轨道,这一课是本周发的第二课。

Clipboard Image.png

课程地址:http://www.hellobi.com/course/54

此外,希望大家听完课以后去https://www.surveymonkey.com/r/CYQLCTD 填写一下调查问卷,我也好有的放矢,对于我教的不好的地方做调整和改善。




大家好, 这节课我们接着上节课的内容继续, 讲一下在数据库设计中对于NULL的处理.

由于本系列课程的核心内容还是讲数据仓库, 那么我这边讲的大部分内容还是基于数据仓库中的一些设计技巧. 这一部分也将在后面的课程里被大量的引用。

 

这里我再强调一次,设计本身其实并没有放之四海皆准的金科玉律,而本人才疏学浅,也只是尽可能的按照自己的经验去讲解我曾经学习以及实践过的设计,给大家作为一个参考。大家在实际的设计过程中,还是要具体问题具体分析,切记不要生搬硬套。

 

首先,一般来说对于一个数据库的设计,对NULL值处理的是有统一的原则的,也就是我们常说的一致性,consistent。对于类似的场景,尽可能采用相同的设计模式来设计。举一个例子,对于NULL的处理,我们经常会采用默认值的方式,比如我们设计一个时间字段,表示项目实际结束时间,而创建这条记录的时间通常是项目刚刚开始,这个字段的内容当时我们是不知道的,因此我们可以默认的把该时间字段设置成9999-12-31,当最后项目结束的时候,再把这个值UPDATE项目结束的真实时间。这样为了保证整个数据库设计的一致性呢,当在同一个库里, 遇到类似的场景时,都是取同样的默认值,也就是说不能在这个字段你用9999-12-31,另一个类似的你用2099-12-31. 这样做的好处是使程序开发者以及其他数据库的使用人员更容易理解整个数据库的结构以及数据,并且也能保证所开发程序的一致性。

题外话,设计的一致性不仅仅体现在一个数据库上,其实整个企业也应该有相应的一致性设计规约,这也是信息标准化的重要课题。

 

第二,在建模过程中,对NULL的处理方式。有两种模式,如果对表的数据质量没有特别的要求,通常来说,都是对于主键以及一些重要的字段设成不可为空,其他都采用可为空的模式。如果对数据质量要求比较高,则采用相反的模式,默认所有的字段都不可为空,只针对有NULL需求的个别字段设置成可以为空。



上一节课我们介绍了一堆例子,那些容易出现SQL程序错误的坑。那我们设计的时候,为了减少编程错误的隐患. 让我们回顾一下,都有哪些隐患。一共有五种特点比较突出的隐患。

1. 数学运算:任何数字与NULL做数学运算均为NULL

2. Where子句:搞清NULL的含义,小心”<>”以及 “全选”

3. Join:NULL与NULLJoin没有结果

4. 聚合函数:若NULL设为0, AVG,MIN,MAX,COUNT均受影响

5. 子查询:当存在NULL时,使用not in时需要特别注意




那么我们罗列一下,在数据仓库应用中,常见的哪些的场景会遇到上面的麻烦。这个说明一下,因为应用到上面的场景可能是非常多的,我这不可能都列出来,我就选一些我认为比较重要的列出来。今天这节课讲的有点超前了,有些基础知识其实还没讲。以后大家在后面的学习中可以翻回来学这节课。

 

通常来说,数据仓库层面会分三层,Staging层也叫贴源层,这一层尽可能的和上游系统数据格式一致,因此基本上就是沿袭源表的数据定义,这里我就不讲了。接下来有的时候会有按照第三范式创建的数据仓库层,以及为了实现分析需求的星型模式构建的数据集市层。让我们来看看这两层都有哪些常见的场景,需要考虑到NULL值的设计。

 

我大概列了一下,应该还不仅限于此。

首先是第三范式模型层,也就是我们常说的数据仓库层。这一层通常来说都是为下游系统也包括下游打得数据集市提供数据源,或者支持很少量的adhoc查询。首先,这一层面向的使用者,相对来说还都是经验比较丰富的程序员以及下游系统的数据架构师,并且通常查询都有相对固定的模式,因此这一层相对而言,对于NULL值的设计原则,可以更遵从于需求以及数据的本质,不用过多考虑由于SQL水平问题而引起的错误。

而星型模式构建的数据集市层,数据将被大量的访问,这个时候的访问者,有可能是大量的报表设计工程师,最终的用户,面对的需求也非常难以预测。这种情形下,很多查询模式,你都是未知的,说白了你也不知道使用者的SQL什么水平,他对数据的了解程度也很难知道,SQL的随意性也很强。这个时候,设计的原则就要尽可能的注意SQL的隐患,也就是说要减少NULL的使用,并且采用一些其他的办法去规避。



那么关于NULL的设计有哪些建议呢?原则说起来其实也简单:

首先,是基于查询的需求。也就是说,所设计的表中,有哪些字段会在查询中有所应用。比如事实表中对度量值的查询获取,这个就相当于select后面对应的项目,再比如两个表关联查询,用于join的条件,再比如where子句的查询条件等等。

其次,基于数据的本质。也就是说某个字段的含义是什么?如果为NULL,是真未知,尚未知还是不适用?

然后,是不是可以用空字符串,0等代替NULL呢?如果代替了,是不是从逻辑上解释得通?以及有哪些利弊?最后还要告知DEVELOPER你的设计,提醒NULL的存在,或者默认值的设定设计。



那么, 我们来逐个看这些设计, 在第三范式下面有非常多的Pattern,我实在没有办法一一列举,因此只选择一些我认为比较常用的一些模式。今天的课程我们只讲第三范式的内容,星型表的结构因为需要更大的篇幅去介绍各种概念,暂时先不讲,大家有兴趣可以自己回去琢磨。

 

那么我们先来看第一个Pattern, Reference Data参照数据表。这种表我们前面讲过了,通常是数据量不大,改动不会很频繁,但做查询分析时又是经常使用的分类表。我这里给出一个例子,产品分类表,Product Category Reference。表结构很简单,有三个字段PROD_CATE_CD,PROD_CATE_NAME和PROD_CATE_DESC。通常来说,这种表最少都有两个字段,一个是CODE作为编码,一个是NAME作为显示的名称,有的时候还有DESCRIPTION作为更为详细的描述。首先我们看,要基于需求的话,最常见的需求就是两种,第一种情况,PROD_CATE_CD是一张Master表的外键,这里给出的例子是Product也就是产品表中有一个字段PROD_CATE_CD。那么我们经常有这样的需求,就是查询某一个产品分类中有哪些产品,这样就会应用到我前面提到的Join的隐患。 用Product去和ProductCategory做Join。这样在产品分类表中,ProductCategory Cd是不可以为空的,而基本上我们都把它设为主键。那么第二种需求就是,经常需要使用前台的查询程序,根据产品分类的名称进行产品的查询,因此就存在我们前面提到的where子句的隐患,所以Product Category Name尽可能不为空,而从数据的本质而言,设为不可为空也是可行的,因此在设计的时候这个字段设置为不可为空,而如果不可为空,我们就要给它设置一个Default值,这里用’’两个单引号表示的空字符串即可。这里需要注意的是,如果希望保证数据质量,也可以为该字段创建唯一约束。而对于product category description而言呢,如果存在查询,一般都是product category description like ‘%XXX%’的形式存在。这个字段如果为空也没有什么问题。另外其实也很常见的会用到子查询的,而Code和Name均不为空也就规避了子查询时的隐患。这种类型的表的NULL设置是非常简单的,考虑的无非就是编码,名字这两种情况。



那么我们来看第二种类型的表,主数据表。这类表我前面的课曾经介绍过,通常数据量较大,变化较为频繁,属性也比较多。比如产品,客户,员工等等,这些都是主数据表。

主数据表需要注意的几个地方,首先也是一样的,主数据表通常有编码,名称,然后和参照表比较大的区别之一就是它还会引用一些其他表的字段,尤其是参照表。我们来看这个例子,还是引用上一个例子,这回我们关注的是PRODUCT_MASTER这张表,这里我写的很简单,只有4个字段,Product Code,Product Name, Product Category Code以及Product Price。这里呢,Product Code和Product Name均不可为空,这个设计的理念是和Product Category Reference表是一致的。那么还有两个字段,一个是ProductCategory Code,一个是Product Price。ProductCategory Code引用于我们前面的例子产品分类表,通常是两个表做Join。这里我们设计的模式是,为了避免查询的隐患,设计成不可为空,那么如果因为数据质量等种种原因,上游提供比如空数据怎么办?

这里有个设计技巧,通常在主表Product Category Reference里面会手动增加几个值。我这里给出的例子是4个项目,‘未知’,‘不适用’,‘非法’,‘未分类‘。假如上游系统提供的字段为空,我们可以填未知或者未分类,这个酌情而定,如果该产品不适用用本产品分类,则填不适用;如果产品分类编码不合法,比如本来应该是char(4)的,提供的数据是char(5),我们就设为非法。我这里只是举个例子,帮助大家去理解,这个要根据项目的实际情况,实际应用中不要生搬硬套。这里的product cate code需要注意,不要用真实系统中未来可能存在的项目,像我这个例子给的就是假定真实环境中都是1, 2, 3, 4这类的CODE, 那么我手动添加-1, -2, -3, 4这类不可能出现的Code作为编码。

然后下面还有一个product price,这个字段有可能作为查询的where条件。也有人可能问,这个字段是不是会做数学计算。如果经验丰富的设计师会知道,因为通常会还会有单独的价格表,因此这个字段大部分的使用都是起到参照作用,一般不会用于数学计算。所以设置成可为空也无可厚非。设计成不可为空的话,就是要看是不是0可以作为默认值,当我们不知道产品的价格时,设置为0. 但如果担心未知设为0和产品0价格销售会产生歧义,那么可以增加一个说明字段用于描述产品是否是0价格产品,这也是一种设计技巧,主要的出发点还是要遵循实际的需求。我这里举产品价格这个例子的目的是想说明,在一张表里,可能有非常多的字段,有些字段未来可能会作为查询条件,这个难以预判,所以我们要分出优先主次来,前面几个字段都是必须要设成不可为空的,而像这个产品价格字段相对来说设计就没那么严谨。大家不要耗费同样的精力在这种字段的设计上,说白了就是怎么设计其实都可以的。



下面的这个场景就很简单了,这里是三张表,一张PROD MASTER表,还有下面这个新加的产品销售经理表,但因为产品表和销售经理表是多对多的关系,因此建一个XREF表,这张表主要用于表之间的JOIN。这张表是PROD_CD以及MANAGER_ID从逻辑上来讲是作为联合主键的,因此显然不可为空。因为在数据仓库设计中,有很多人不喜欢给这种表建主键,因此我这里单独说明一下。


下一个场景,事务表。这张表很简单,就是销售经理在某月的销售情况与销售计划的对比表。

首先我们看表中的字段,前三个是联合主键,因此显然必须不可为空。然后看PLAN_SALE_AMT, ACTUAL_SALE_AMT以及Quantity。我们按照步骤来,首先看需求,需求就是每个产品的销售经理会有销售配额,也就是PLAN_SALE_AMT,然后还有一个实际的销售情况,就是ACTUAL_SALE_AMT。很显然,需要经常去求完成比以及计划与实际的差额,因此这几个字段就需要经常做数学计算。然后还有这种需求,就是求每个经理的平均每个月的销售配额以及实际销售情况,也就是说还会用到聚合函数。这里我说的需求是非常重要的,就是还有一些特殊的场景我们要考虑到,就是有可能某个经理,其中某个产品他没有配额,但他也销售出去了,这样这个经理会有实际销售额,但没有对应的配额。这个时候呢,如果遵从数据的本质,那么就给PLAN_SALE_AMT置为NULL。也就是按照表PROD_SALES_PERFORMANCE2来设计,但如果担心NULL带来的数据计算的隐患,也就是说计划为NULL减去实际,而实际销售额是有值的,出现NULL的现象。也可以设计成右边的表的模式PROD_SALES_PERFORMANCE,这些项目均不可为空,然后增加一个PLAN_ASSIGN_IND用于标识,是不是这个经理有配额。但无论这两种那种情况,都需要告诉开发者,编程时需要注意的地方。这也是我前面提到的设计一致性的重要性,你有一个地方这么设计的,给开发者讲明白了,后面类似的设计他们就很容易理解了。

 

那么,有关NULL值的相关设计呢,我们就先讲到这,这个话题很大,应该还有很多场景没有覆盖到,并且星型表的NULL值设定我也没有讲。未来在讲维度建模的时候,还会提到。下节课我会讲第三范式的概念,我看了一下天善的课程,其实有人已经讲过了,不过实在对不起,我希望我的课程能够比较完整,因此还是会把这部分按我自己的理解重新讲一遍。谢谢大家!

推荐 5
本文由 BAO胖子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册