DB设计03：写给开发者看的关系型数据库设计

1、写给开发者看的关系型数据库设计2013-03-28 10:22 meteorseed 博客园我要评论（0）字号:t | t收藏数据库设计，一个软件项日成功的基右。很多从业人员都认为，数据库设计其实不那么重要。现实中的情 景也相当雷同，开发人员的数量是数据库设计人员的数倍。多数人使用数据库中的一部分，所以也会把数 据库设计想的如此简单。其实不然，数据库设计也是门学问。ad：2013云计算架构师峰会超低价抢票中数据库设计，一个软件项h成功的基石。很多从业人员都认为，数据库设计其实不那么重要。现实中的情 景也相当雷同，开发人员的数量是数据库设计人员的数倍。多数人使用数据库中的一部分，所以也会把数 据

2、库设计想的如此简单。其实不然，数据库设计也是门学问。从笔者的经历看來，笔者更赞成在项目早期由开发者进行数据库设计（后期调优需要dba）。根据笔者的 项冃经验，一个将通oop和orm的开发者，设计的数据库往往更为合理，更能适应需求的变化，如果追其 原因，笔者个人猜测是因为数据库的规范化，与00的部分思想雷同（如内聚）。而dba,设计的数据库的 优势是能将dbms的能力发挥到极致，能够使用sql和dbms实现很多程序实现的逻辑，与开发者相比，dba 优化过的数据库更为髙效利稳定。如标题所示，木文旨在分享一名开发者的数据库设计经验,并不涉及复 杂的sql语句或dbms使用，因此也不会局限到某种dbm

3、s产品上。真切地希望这篇文章对开发者能冇所帮 助，也希望读者能帮助笔者査漏补缺。一 codd的rdbms 12法则rdbms的起源edgar frank codd （埃徳加弗兰克科徳）被誉为“关系数据库之父”，并因为在数据库管理系统的理 论和实践方面的杰出贡献于1981年获图灵奖。在1985年，codd廨士发布了 12条规则，这些规则简明的 定义出一个关系型数据库的理念，它们被作为所有关系数据库系统的设计指导性方针。1. 信息法则关系数据库中的所有信息都用唯一的-种方式表示表中的值。2. 保证访问法则依靠表名、宝键值和列名的组合，保证能访问每个数据项。3. 空值的系统化处理支持空值（null）

4、,以系统化的方式处理空值，空值不依赖于数据类型。4. 基于关系模型的动态联机日录数据库的描述应该是自描述的，在逻辑级别上和普通数据采用同样 的表示方式，即数据库必须含冇描述该数据库结构的系统表或者数据库描述信息应该包含在用户 可以访问的表中。5. 统-的数据子语言法则一个关系数据库系统可以支持儿种语言和多种终端使川方式，但必须至少 有一种语言，它的语句能够一某种定义良好的语法表示为字符串，并能全面地支持以下所有规则: 数据定义、视图定义、数据操作、约束、授权以及事务。（这种语言就是sql）6. 视图更新法则所冇理论上可以更新的视图也可以由系统更新。7. 爲级的插入、更新和删除操作把一个基础关系

5、或派生关系作为单个操作对象处理的能力不仅适应 丁-数据的检索，还适川丁-数据的插入、修改个删除，即在插入、修改和删除操作中数据行被视作 集合。8. 数据的物理独立性不管数据库的数据在存储表示或访问方式上怎么变化，应用程序和终端活动都 保持着逻辑上的不变性。9. 数据的逻辑独立性当对表做了理论上不会损害信息的改变时，应用程序和终端活动都会保持逻辑 上的不变性。10. 数据完整性的独立性专用于某个关系型数据库的完藥性约束必须可以用关系数据库子语言定义， 而口可以存储在数据目录中，而非程序中。11. 分布独立性不管数据在物理是否分布式存储，或者任何时候改变分布策略，rdbms的数据操纵子 语言必须能

6、使应用程序和终端活动保持逻辑上的不变性。12. 非破坏性法则如果一个关系数据库系统支持某种低级（一次处理单个记录）语言，那么这个低级 语言不能违反或绕过更鬲级语言（一次处理多个记录）规定的完整性法则或约束，即用户不能以 任何方式违反数据库的约束。二关系型数据库设计阶段（一）规划阶段规划阶段的主要工作是对数据席的必要性和可行性进行分析。确定是否需要使用数据库，使用哪种类型的 数据库，使用哪个数据库产品。（二）概念阶段概念阶段的主要工作是收集并分析需求。识别需求，主要是识别数据实体和业务规则。对于一个系统來说, 数据库的主要包括业务数据利非业务数据，而业务数据的定义，则依赖于在此阶段对用户需求的分

7、析。需 耍尽暈识别业务实体和业务规则，对系统的整体冇初步的认识，并理解数据的流动过程。理论上，该阶段 将参考或产出多种文档，比如“用例图”，“数据流图”以及其他-些项冃文档。如果能够在该阶段产出 这些成果，无疑将会对后期进行莫大的帮助。当然，很多文档已超出数据库设计者的考虑范围。而且，如 果你并不精通该领域以及用户的业务，那么诸放弃自己独立完成用户需求分析的想法。用户并不是技术专 家，而当你自身不能扮演“业务顾问”的角色时，请你选择与项日组的相关人员合作，或者将其视为风险 呈报给pm。再次强调，大多数悄况，用户只是行业从业者，而非职业技术人员，我们仅仅从用户那里收集 需求，而非依赖于用户的知识

8、。记录用户辭求时，可以使用一些技巧，当然这部分内容冇些可能会超出数据库设计人员的职责： 努力维护一系列包含了系统设计和规格说明信息的文档，如会议记录、访谈记录、关键用户期望、 功能规格、技术规格、测试规格等。 频繁与干系人沟通并收集反馈。 标记出你自c添加的，不属于客户要求的，耒决内容。 与所冇关键干系人尽快确认项目范围，并力求冻结緡求。此外，必须严谨处理业务规则,并详细记录。在之后的阶段，将会根据这些业务规则进行设计。当该阶段结束时，你应该能够回答以下问题： 需要哪些数据？ 数据该被怎样使用？ 哪些规则控制着数据的使用？ 谁会使用何种数据？ 客户想在核心功能界面或者报表上看到哪些内容？ 数据

9、现在在哪里？ 数拥是否与其他系统有交互、集成或同步？ 主题数据有哪些？ 核心数据价值几何，对可靠性的要求程度？并且得到如下信息： 实体和关系 属性和域 可以在数据库中强制执行的业务规则 需要使用数据库的业务过程（三）逻辑阶段逻辑阶段的主要工作是绘制e-r图，或者说是建模。建模工具很多，有不同的图形表示方法和软件。这些 工具和软件的使川并非关键，笔者也不建议读者花大量时间在建模方法的选择上。对于大多数应川来说， e-r图足以描述实体间的关系。建模关键是思想而不是工具，软件只是起到辅助作用，识别实体关系才是 木阶段的重点。除了实体关系，我们还应该考虑属性的域（值类型、范围、约束）（四）实现阶段实现

10、阶段主要针对选择的rdbms定义e-r图对应的表，考虑属性类型和范用以及约束。（五）物理阶段物理阶段是一个验证并调优的阶段，是在实际物理设备上部署数据库，并进行测试和调优。三设计原则（-）降低对数据库功能的依赖功能应该由程序实现,而非db实现。原因在于,如果功能由db实现时,一旦更换的dbms不如之前的系统 强人，不能实现某些功能，这时我们将不得不去修改代码。所以，为了杜绝此类情况的发生，功能应该冇 程序实现，数据库仅仅负责数据的存储，以达到放低的耦合。（二）定义实体关系的原则当定义一个实体与其他实体z间的关系时，需要考量如下： 牵涉到的实体识别出关系所涉及的所有实体。 所有权考虑一个实体“拥

11、有”另一个实体的情况。 基数考最一个实体的实例和另一个实体实例关联的数最。关系与表数杲 描述1:1关系最少需要1张表。 描述l：n关系最少需要2张表。 描述n:n关系最少需要3张衣。（三）列意味着唯一的值如果表示坐标（0,0）,应该使用两列表示，而不是将“0,0”放在1个列中。（四）列的顺序列的顺序对于表来说无关紧耍,但是从习惯上来说，采用“主键+外键+实体数据+非实体数据”这样的顺序 对列进行排序显然能得到比较好的可读性。（五）定义主键和外键数据表必须定义主键和外键（如果冇外键）。定义主键和外键不仅是rdbms的耍求，同时也是开发的要求。 儿乎所有的代码生成器都需要这些信息来生成常用方法的代

12、码（包括sql文和引用），所以，定义主键和 外键在开发阶段是必须的。之所以说在开发阶段是必须的是因为，有不少团队出于性能考虑会在进行大量 测试后，在保证参照完整性不会出现大的缺陷后，会删除掉db的所有外键，以达到最优性能。笔者认为， 在性能没冇出现问题时应该保留外键，而即便性能真的出现问题，也应该对sql文进行优化，而非放弃外 键约束。（六）选择键1人工键与自然键人工健一实体的非自然属性，根据需要山人强加的，如guid,其对实体毫无意义；自然健实体的自 然属性，如身份证编号。人工键的好处： 键值永远不变 永远是单列存储人工键的缺点： 因为人工键是没有实际意义的唯一值,所以不能通过人工键來避免重

13、复行。笔者建议全部使川人工键。原因如下： 在设计阶段我们无法预测到代码真正需要的值，所以干脆放弃猜测键，而使用人工键。 人工键复杂处理实体关系，而不负责任何属性描述，这样的设计使得实体关系与实体内容得到高 度解耦，这样做的设计思路更加清晰。笔者的另一个建议是一一每张表都需要冇一个对用户而言冇意义的自然键,在特殊悄况下也许找不到这样 一个项，此时可以使用复合键。这个键我在程序中并不会使用其作为唯一标识，但是却可以在对数据库直 接进行查询时使用。使用人工键的另一根弊端，主要源自对査询性能的考量，因此选择人工键的形式（列的类型）很重要： 自增值类型山于类熨轻巧查询效率更好，但取值有限。 gu1d查询

14、效率不如值类型，但是取值无限，且对开发人员更加亲切。2智能健与非智能键智能键一键值包含额外信息，其根据某种约定好的编码规范进行编码，从键值木身可以获取某些信息； 非智能键，单纯的无意义键值，如自增的数字或guid。智能键是一把双刃剑，开发人员偏爱这种包含信息的键值，程序盼瑕着其中潜在的数据；数据库管理员或 者设汁者则讨丿犬这种智能键，原因也是很显然的，钢能键对数据库是潜在的风险。前面捉到，数据库设汁 的原则z是不要把具冇独立意义的值的组合实现到一个单一的列中，应该使用多个独立的列。数据库设 计者，更希望开发人员通过拼接多个列来得到猪能键，即以复合主键的形式给开发人员使用，而不是将一 个列的值分

15、解后使川。开发人员应该接受这种数据库设计，但是很多开发打却想不明口两打的优略。笔者 认为，使用单一列实现智能键存在这样一个风险，就是我们可能在设计阶段无法预期到编码规则可能会在 后期发生变化。比如，构成钢能键的局部键的值用完而引起规则变化或者长度变化，这种编码规则的变化 对于程序的冇效性验证与猶能键解析是破坏性的，这是系统运维人员最不希望看到的。所以笔者建议如果 需耍彻能键，请在业务逻辑层封装（使用只读属性），不要再持久化层实现，以避免上述问题。（七）是否允许null关于null我们需要了解它的几个特性： 任何值和null拼接后都为nullo 所冇与null进行的数学操作都返回nullo 引入

16、nui丄后，逻辑不易处理。那么我们是否应该允许列为空呢？笔者认为这个问题的答案受到我们的开发语言的影响。以c#为例，因为 引入了可空类型來处理数据库值类型为null的情形，所以是否允许为空对开发者來说意义并不大。们有一 点必须注懣，就是验证非空必须要在程序集进行处理，而不该依赖于dbms的非空约束,必须确保完整数据 （所冇必须的属性均被赋值）到达db （所谓的“安全区”，我们必须定义在多层系统中那些区域得到的数 据是安全而纯净的）。（八）属性切割一种错课想法是，属性与列是1： 1的关系。对于开发者，我们公开属性而非字段。举个例子來说，对于实 体“员工”有“名字”这一属性，“名字”可以再被分解为

17、“姓”和“名”，对于开发人员來说，显然第 二种数据结构更受青睐（“姓”和“名”作为两个字段）。所以，在设计时我们也应该根据需要考虑是否 切割屈性。（九）规范化一范式 当笔者还在大学时，范式是学习关系型数据库时最头疼的问题。我想也许会有读者仍然不理解范式的价值, 简单來说一一范式将帮助我们來保证数据的有效性和完整性。规范化的目的如下： 消灭重复数据。 避免编写不必要的，用來使重复数据同步的代码。 保持表的瘦身，以及减从一张表中读取数据时需耍进行的读操作数量。 最大化聚集索引的使川，从而可以进行更优化的数据访问和联结。 减少毎张表使用的索引数量，因为维护索引的成木很高。规范化旨在一一挑出复杂的实体

18、，从中抽取出简单的实体。这个过程一直持续下去，直到数拥库中每个表 都只代表一件事物，并且表中每个描述的都是这件事物为止。1规范化实体和属性（去除冗余）1nf：每个属性都只应表示一个单一的值，而非多个值。需要考虑儿点： 属性是原子性的需耍考虑熟悉是否分解的足够彻底，使得每个屈性都表示一个单一的值。（和“（三）列意味着唯-的值”描述的原则相同。）分解原则为一当你需要分开处理每个部分时 才分解值，并且分解到足够用就行。（即使当前不需耍彻底分解丿屈性，也应该考虑未來可能的需 求变更。） 属性的所冇实例必须包含相同数屋的值实体冇固定数暈的属性（表冇固定数屋的列）。设计实体 时，要让每个属性只有固定数量的

19、值与其相关联。 实体中出现的所有实体类型都必须不同当前设计不符合1nf的“臭味”： 包含分隔符类字符的字符串数据。 名字尾端冇数字的属性。 没有定义键或键定义不好的表。2属性间的关系（去除冗余）2nf-实体必须符合1nf,每个属性描述的东西都必须针对整个键（町以理解为oop中类世属性的内聚性）。当前设计不符合2nf的“臭味”： 重复的键屈性名字前缀（设计z外的数据兀余）表明这些值可能描述了某些额外的实体。 有重复的数拥组（设计z外的数拥兀余）这标志着屈性间有函数依赖型。 没有外键的复合主键这标志着键中的键值可能标识了多种取物，而不是一种取物。3nf-实体必须符合2nf,非键属性不能描述其他非键

20、属性。（与2阡不同，3nf处理的是非键属性和非键属 性z间的关系，而不是和键属性z间的关系。当前设计不符合3nf的“臭味”: 多个属性有同样的前缀。 重复的数据组。 汇总的数抓，所引用的数据在一个完全不同的实体中。（有些人倾向于使用视图，我更倾向于使 用对彖集合，即由程序来完成。）bcnf-实体满足第一范式，所有属性完全依赖于某个键，如果所有的判定都是一个键，则实体满足bcnf。（bcnf简单地扩展了以前的范式，它说的是：一个实体可能冇若干个键，所冇属性都必须依赖于这些键中 的一个，也可以理解为“每个键必须唯-标识实体，每个非键熟悉必须描述实体。”3去除实体组合键中的冗余4nf-实体必须满足b

21、cmf,在一个屈性与实体的键z间，多值依赖（一条记录在幣个衣的唯一性由多个值纽 合起來决定的）不能超过一个。当前设计不符合4nf的“臭味”： 三元关系（实体:实体:实体）。 潜伏的多值属性。（如多个手机号。） 临时数据或历史值。（需要将历史数据的主体捉出，否则将存在大量冗余。）4尽量将所有关系分解为二元关系5吓-实体必须满足4nf,当分解的信息无损的时候，确保所冇关系都被分解为二元关系。5nf保证在第四范式中存在的任何可以分解为实体的三元关系都被分解。冇的三元关系可以在不丢失信息 的前提下被分解为二元关系，当分解为两个二元关系的过程耍丢失信息时，关系被宣称为处于第四范式中。 所以，第五范式建议

22、是，最好把现有的三元关系都分解为3个二元关系。需耍注意的是，规范化的结果可能是更多的表，更复杂的杳询。因此，处理到何种程度，取决丁性能和数 据架构的多方考量。建议规范化到第四范式，原因是5nf的判断太过隐晦。例如：表x （老师，学生，课 程）是一个三元关系，可以分解为表a （老师，学生），表b （学生，课程），表c （老师，课程）。表x 表示某个老师是上某个学生的某个课程的老师；表a表示老师教学生；表b表示学生上课；表c表示老师 教课。单独看是无法发现问题的，但是从数据出发,衣2衣a+衣b+表c"并不一定成立，叩不能通过连接 构建分解前的数据。因为可能有多种组合，丧失了表x反馈出的业

23、务规则。这种现彖，容易在设汁阶段被 忽略，但好在在开放阶段会被显现，而且并不经常发生。推荐做法： 尽可能地遵守上述规范化原则。 所有属性描述的都应该是体现被建模实体的木质的内容。 至少必须有一个键，它唯一地标识和描述了所建实体的本质。 主键要谨慎选择。 在逻辑阶段能做多少规范化就做多少（性能不是逻辑阶段考虑的范畴）。（十）选择数据类型（ms sql 2008）ms sql的常用类型:精确数字不会发生精度损失bit tinyint smallint int bigint decimal近似数字对于极值可能发生粘度 损失float(n) real日期和时间date time smalldatetimc datetime datctimc2 datetimeoffset二进制数据bingary(n) varbinary(n) varbinary (max)字符(串)数据char(n) varchar (n) varchar (max) nchar (n) nvarchar (n) n varchtir (max)存储任意数据sql_varici nt时间戳timestampguiduniqucidcntifierxml不要试图使用该类盘规 避1nfxml空间数据g