翻译的数据库设计资料.docx
《翻译的数据库设计资料.docx》由会员分享,可在线阅读,更多相关《翻译的数据库设计资料.docx(11页珍藏版)》请在冰豆网上搜索。
翻译的数据库设计资料
数据库设计
作者
PhilippK.Janert
IEEE.org
翻译
王保荣
Coship
校对
耿春霞
Coship
1.键和数据类型
在该系列讲座中,我将介绍一些数据库设计的最实用的技巧。
在第一部分,我要讨论简单数据类型、复合数据类型以及主键和外键等等。
1.1键及分类
关系数据库储存两种信息——数据和通道。
数据包括客户名、库存号、注释等业务信息。
通道指主键和外键,它们帮助查询数据记录并把数据表关联起来构成关系数据库。
v基本管道
管道应该透明以便建模。
许多人在建立数据库时不分数据和管道的区别。
增加一些
管道信息会使数据库便于管理和维护,同时也能够提高数据库的性能。
v主键
能够唯一确定记录的字段或字段组合。
每一个表都应有一个主键,可以是一个字段也可以是多个字段的组合,它们的值能唯一地标识表中的每行,以其构成和意义不同,介绍以下术语。
v代理键
在表中添加的可以唯一确定每条记录的字段作为主键。
在业务上,这个字段没有任何实际意义。
主键必需具备的条件:
⏹不为空
⏹唯一
⏹不能更改
引进代理键的三个原因:
⏹使每一实体具有唯一的表识字段,以区别其他实体。
⏹有利于提高效率和便于数据库维护。
⏹代理键通常是简单的数字字段,简化了主键和外键的构成。
v外键
一个表中的字段(属性),是其他表中的主键字。
外键的命名遵循
_fk。
v候选键(candidatekey)
在选择采用代理键时,不排除选择业务字段组合,使其能够唯一确定每条记录。
这样的业务字段组合就叫候选键。
候选键其值都不为空,并且每一个组合在表中都是唯一的。
候选键具有逻辑意义,有利于建模。
然而,并不是每一个表都具有候选键。
比如,一个表中只有人的姓和名两个字段,若有两个人姓和名都相同,这种情况下,就没有候选键。
因为字段组合不能区别两个人。
这里的关键问题是,你强调的是字段组合的唯一性还是关键实体的唯一性,当讨论实体的唯一性时,代理键是正确的选择。
v选择键
选择键:
可见的标识符,代理键在业务上没有任何逻辑意义,故对数据库外的用户不可见。
这样便于DBA在必要时变换代理键。
如果因业务需求,应提供给用户一个对某些字段的唯一性标志。
该标志应该属于真正的业务数据,应与管道数据区别对待。
如多加一个字段VisibleAccountNumber,使之成为可见的标志字段(不为空,唯一),这样就构成了一个选择键。
这个字段应便于生成和修改,对用户友好。
如,当用户打电话给售后服务人员时,便于记录和对话。
主键是否对应用程序可见还是对用户可见,取决于具体的项目。
利用数字型别将数据库记录引入应用程序,应尽量避免耦合。
因为数字易于在程序中更改,从而改变所代表的记录。
在小型系统中,可考虑用字符串型别。
一个较好的选择是用一个简单的包装实体,虽较负责,但不会与其他界面数据混淆。
对业务字段组成的键,需要将其串行化(serializing)。
v全局标志(UniversallyUniqueIdentifier,UUID)
关系数据库并不需要全局标志。
管道对于每一个表都应是唯一的,这一目标可通过串行
化数据类型来实现。
UUID可能会有一些技术问题,为了确保唯一性,所有的UUID必须经由一个中心服务产生,这样导致了可测性(scalability)问题。
另一方面,如果不用UUID,用字符串或数字组合代表UUID,会使数据库运行变慢。
此外,当用于外键时,复合键增加了数据库的复杂度。
尽管面向对象特性要求每一业务实体具有一个键,但没有必要将数据库内部运行器建立于此。
总而言之,我建议将业务数据和管道数据分离。
利用UUID违反了这一要求,因为UUID是业务数据。
1.2数据类型
SQL标准定义了一系列的标准数据类型,绝大部分DB供应商支持这些数据类型的同时,增加了自己独有的类型。
没有特别的理由,应回避利用这些非标准的数据类型。
v字符串和数字
数字类型:
考虑选择足够大的类型以支持业务数据。
字符:
在定义字符型字段时,没有必要找到最佳的宽度来容纳所储存的信息。
只要大致适合信息宽度即可。
定义界个固定宽度,同时赋予别名,如:
32bytes("Label"),256bytes("Note"),
和4k("Text")。
尽管有的业务要求限定某些字段宽度的某一具体值,但数据库schema不是最好的地方来体现这一规则。
当非法输入或请求达到数据库时已是太晚了,除了拒绝没有别的办法。
用户的数据交换和有效性验证应在业务逻辑层实现,而不是在资源层实现。
此外,如果将字符串限定于几个有限的宽度,可以大大简化数据库Schema(DBSchema)。
注:
Schema可以理解为表格的表头。
v时间和货币
SQL提供了时间类型,但没有一个满意的数据类型来储存货币类型。
用小数表示货币经常会引起数字超限错误。
以货币的最小单位为单位,并用整数对货币进行表示,经常会引起数字过大现象。
许多数字小数点后的位数多于货币单位所需。
通常用5到9位十进制数表示货币较合适。
只考虑货币数量而不考虑币种,使得货币实体失去实际意义。
建议建立币种表,用外键将其,这样有利于国际货币的处理。
v布尔类型和系统类型
布尔(Boolean)类型有三个值:
真、假、未知(unknown),并非两个值:
真(true)、假(false)。
在许多数据库中,字段表示记录的类型,如GenderType,ItemType,PaymentType等等。
对每种类型,可用其他信息描述这种类型的特点。
一条具有类型字段的记录含有一列类型字段(Type),这个字段是一个外键,指向类型表。
一个类型表可能具有以下字段:
ØtypeCode_pk
Ølabel(uniquemnemonic,suchasvarchar(32))
Ødescription(varchar(256)shouldbesufficient)
Øuri(pointingtoadditionalresouces,wherenecessary)
ØcodeGroup_fk
codeGroup_fk字段将有关类型构成一个组,如所有的定购者构成一个组。
codeGroup_fk
应是一个外键,指向另一个组表。
然而组又包括类型,codeGroup_fk指向typeCode_fk。
这样的递归关系导致了任意深的结构。
在设计种,最好使类型系统简单、明了。
v复杂数据类型
在许多数据库schema中,会遇到一个复杂的数据类型,如电话号码、邮政地址、联系地址、信用卡号等等,这些记录需要在许多表中访问。
如联系地址应包括在用户、供应商、仓库和管理员的记录中。
将联系地址单独建一个表,其他有关表,如用户、供应商表,用它们的外键指向地址表,这样至少可以得到下列的好处:
1.便于改变表之间的关联关系(如一对一、多对多关系)
2.使地址变更仅局限于一个表
预测每种复杂类型的字段是一种艺术。
我建议一开始就考虑包括所有可能的字段,而
不是在后来改变数据库schema,以下给出几种复杂数据类型的例子:
邮政地址:
Ø部门
Ø公司
Ø邮区
Ø地址行1
Ø地址行2
Ø地址行3
Ø城市
Ø洲
Ø邮编
Ø国家
联系地址:
Ø称呼
Ø首名
Ø中间名
Ø姓
Ø后缀(如sr.pro)
Ø家庭地址
Ø工作地址
Ø家里电话
Ø单位电话
Ø手机
Ø传真
Ø传呼机
Ø电子邮件
电话号码:
Ø国家码
Ø地区码
Ø变换码(前缀)
Ø行号(后缀)
Ø扩展码
如在987-1234中,前缀是987,后缀是1234,扩展码是可有可无的。
总结:
上面我们讨论了关系数据库设计的常用技巧:
Ø代理键的好处。
Ø不要将数据库内部关联建立在UUID上。
Ø利用统一类型表,便于表述有限的、预先确定的字段
Ø复杂数据类型的定义
下一节将讨论数据库标准化及在其在历史记录和日志中的应用
2.规范化、历史表和事件日志
本节讨论规范化、历史、事件日志。
2.1规范化
通过对数据规范化,使插入、更改、删除等数据库基本操作不会产生不规则记录,保证数据的完整性。
不规则记录往往是由冗余或不同实体建不恰当的分离导致。
规范化有利于实体辨别和建模,有利于改善对业务领域的理解。
规范化是将原有数据库转换或分解成一种对等的数据库,转换过程中不会有信息的丢失,转换后的设计方案跟原来的相当。
规范化的过程也是一系列影射的过程,分解得到的表通过合并会得到原有的表。
我们说这样的影射是无丢失影射。
通过无丢失分解原有表可变成下列几种形式:
Ø第一范式(1NF)处理独立的表
Ø第二(2NF)和第三范式(3NF)处理一对一和一对多的关系
Ø第四范式(4NF)处理多对多的关系
一个处于高级范式的表自动满足所有低级范式的一切标准。
第五范式式通过映射和合并所能达到的最高范式。
不包括由映射所消除的任何异常。
在以下的讨论中,键指的是概念上的键,由业务数据形成,并非代理键(surrogatekeys)。
v第一范式
表中任意一行中每一列数据都是一个简单的标量,并非矢量。
下面的方案违反1NF,因为SupplierID有三个值,形成了一个重复组。
要达到1NF,去掉重复组,对每一组相关的数据单独建立表。
考虑下面的表格:
1NF所带来的问题:
1.插入:
如果一个用户(CustomerID)从来没有发放订单,就不可能增加一条关于他的记录,因为该客户没有(OrderID)。
2.更改:
改变用户的地址,需要改变用户所有订单中的相关记录。
3.删除:
删除用户最后一个订单,同时会删除有关这个客户所有的信息。
功能依赖
2NF和3NF处理字段间的功能依赖,尤其是主键字段和非主键字段间的功能依赖。
主键唯一地决定表中的记录,所以,一旦主键给定,则同一行中其他字段也相应地被确定。
在一给定关系中,假设x、y是两个(或两组)字段,如果两条记录中x相同,y也相同,我们说y依赖于x,x是决定因子,y是依赖因子。
同时,对任何x,只有一个y与之对应。
我们把这种依赖关系叫单值依赖。
在以上的依赖关系中,如果x是一个组,去掉其中任意一个字段,x不再决定y,我们说x是最小决定因子,依赖关系是不可削减的。
可见功能依赖属于业务逻辑,由字段所代表的逻辑关系决定。
v第二范式(2NF)
若表是1NF,且表中所有非主键字段功能依赖整个主键,即主键是最小键,那么这个表是2NF。
显然2NF只是针对于主键是复合键的情形。
下面的表违反2NF:
因为WarehouseAddress只依赖于WarehouseID,而不是PartID。
要达到2NF,建立一个新表储存具有重复值的字段,然后用外键与原有表关联起来,原有表的决定因子成为新表的主键。
v第三范式(3NF)
在2NF的表中,没有任何字段依赖于非主键字段(依赖传递)。
下面的表违反了3NF:
要达到3NF,取消不依赖于主键的字段,将它们放置于一个新表中,新表的决定因子作为主键。
以上三种范式可以概括为:
表中每个字段必须依赖于主键(1NF),整个主键(2NF),只依赖于主键(3NF)。
vBoyce-Codd范式(BCNF)
BCNF是3NF的扩充。
让我们重新回忆决定因子的概念,在一个表中,一个字段,不论它是主键还是非主键,都决定另一个字段,我们把前一个字段叫决定因子。
在一个表中,①有两个或多个候选键为复合键,②且候选键之间有重叠的字段,如果决定因子是候选键,我们说该表是BCNF。
如果条件①和②不成立,则3NF和BCNF等同。
在下面的表中:
{SupplierID,PartID}和{SupplierName,PartID}都是候选键,但决定因子SupplierID和SupplierName不是候选键,所以改表不是BCNF。
注意,SupplierID和SupplierName是决定因子,因为它们相互决定。
下面两个表均为BCNF:
或
要达到BCNF,去掉不是候选键的决定因子。
v多对多关系和高级范式
4NF和5NF适用于多对多的关系,在关系数据库中,多对多的关系由关联表表达。
例如,在一个课程注册问题中,每个学生可以注册一门或多门课程,而每门课可以有一个或多个学生。
显然这是一个关于学生和课程之间的多对多的例子。
这种关系可以用学生和课程的关联表表示:
{StudentID,ClassID}
这个表的主键是StudentID和ClassID的组合,为了避免违反2NF,其他信息都储存在Student和Class表中。
注意每一个StudentID决定的不是唯一的ClassID,而是一组ClassID。
这种依赖关系叫多值依赖。
v第四范式(4NF)
如果一个表满足3NF,并且它不包含两个或多个独立的多对多的关系,那么这个表满足4NF。
考虑两个多对多关系的例子:
学生和课程、课程和老师,这里暗示了学生和老师也是一个多对多的关系。
然而,业务规则并为对此关系作任何限制。
除了由学生/课程和课程/老师关系所暗示的信息外,学生和老师之间的关系并没给出任何信息。
结果,学生/课程和课程/老师之间的关系相互独立,这些关系没有多余的限制。
所以,下面的表违反了4NF:
不能添加一门由多个老师教的课程,除非这门课程已经有多个学生注册。
要实现4NF,将每一个独立的多对多由它们的关联表取代。
v第五范式(5NF)
如果一个表满足4NF,且它的信息内容不能由几个较小的表重组而成,我们说这个表处于5NF。
让我们继续考虑学生/课程/老师的例子,现在假设还有另外一个关系:
学生和老师。
这样,上面的例子就满足4NF,因为所有关系都互相关联。
但它不是5NF,因为这个表可以由三个关联表重新组成,每个关联表都表示多对多的关系。
要实现5NF,分离多对多关系,增加新的表以表达所有业务限制。
v具体业务规范化
实际上并非所有数据库都被彻底规范化,数据库或多或少地处于不完全规范化状态。
因为非完全规范化数据库要求较少的合并,因此,有利于提高查询速度。
在这里必须权衡考虑彻底规范化是否影响数据库性能,如果是,还要决定非规范化的程度。
此外,一个非完全规范化的数据库schema很难升级。
附加的完整性检验可能会抵消非完全规范化带来的优良性能。
最后,多对多关系带来许多由规范化不能解决的问题。
2.2历史表和事件日志
除了存储业务数据外,数据库还用来存储有关内部技术信息。
如系统的管理和维护。
v历史表
在一个实用的系统中,你也许想保留对现行数据库修改的历史,利用备份表以及有关操作的插入、删除、更改触发器可以达到这一目的。
数据库中的每张表都应以一个备份表,用来反映原表的变化历史。
如果要对原表进行更改,则初始记录在更改前先被复制到历史表中。
删除记录也是如此。
历史表命名一般遵循:
原表名+_hist。
插入历史表的记录一般附在表的最后,随着时间的延长,历史表会变的越来越大。
故应定期将历史表的记录刻录到磁带以存档。
历史表除具有与原表完全相同的字段外,还有两个字段:
1.日期:
对原表进行操作的时间
2.类型:
对原表进行操作的类型,包括创建、修改和删除。
对原表结构的改变会影响到历史表。
当对原表添加一个字段时,这个字段也被加入到
历史表中;当删除原表的一个字段时,相应的字段并未从历史表中删除,其值在以后的记录中为空。
因此,历史表的长度和宽度都会随时间的迁移增长。
v事件日志
数据库可用作记录事件日志。
事件包括的内容很广,从纠错和系统运行信息到具体的业务范畴。
一般需记录日志的事件有:
Ø常用数据库的修改操作
Ø交叉组件边界的处理
Ø错误和异常
Ø发布消息
Ø有关收费记录的操作
Ø业务组件的状态改变
一个日志表至少包括以下字段:
Ø时间
Ø事件类型
Ø描述
有的事件表还包括事件触发器,如用户或系统的其他模块。
在有关费用的事件表中,还应增加付款人和收款人字段。
v系统配置表
另外,数据库还可以作为存储系统的设置信息的中心。
这些信息通常保留在文本文件、性能文件等中。
而数据库可以提供一个唯一的易于管理的地方。
最后,数据可以用来存储系统文件。
对一些以表格形式存储的文件,尤其适合用数据库存储。
如用户端口号记录,共享内存主键、数据字典等。
在任何情况下,数据以简单的键/值对存储。
另外加上注释、指针等字段。
利用数据库存储信息的最大好处是集中管理所有相关信息。
总结:
本文主要讨论了数据库五种范式和规范化过程。
在规范化过程中,原数据库变成一个等价的数据库,但消除了原有数据库在插入、修改、删除时带来的弊端。
合理地规范化也有助于正确地确认实体。
同时,我也讨论了用数据作为日志和配置信息的存储中心。