数据库设计笔记_数据库设计记入-CSDN博客

本文链接：https://blog.csdn.net/qq_34067504/article/details/104896447

1 简介

这份文档并非规范性的文档，只是作为设计时的建议，当然某些建议不是绝对的，这主要看具体的应用，根据实际权衡，做出适当的决策。

2 设计数据库之前

2.1 定义标准的对象命名规范

一定要定义数据库对象的命名规范。对数据库表来说，从项目一开始就要确定表名是采用复数还是单数形式。比如，如果键是数字类型，你可以用_NO 作为后缀；如果是字符类型则可以采用 _CODE 后缀。对列名应该采用标准的前缀和后缀。再如，假如你的表里有好多“money”字段，你不妨给每个列增加一个_AMT 后缀。还有，日期列最好以DATE_作为名字打头。

检查表名、报表名和查询名之间的命名规范。你可能会很快就被这些不同的数据库要素的名称搞糊涂了。假如你坚持统一地命名这些数据库的不同组成部分，至少你应该在这些对象名字的开头用table、query 或者report 等前缀加以区别。

同时，数据对象的命名建议不要超过30个字符，便于跨数据库。

2.2 创建ER图表

一定要花点时间创建ER 图表。创建ER 图表确实有点费时但对其他开发人员要了解整个设计却是完全必要的。越早创建越能有助于避免今后面临的可能混乱，从而可以让任何了解数据库的人都明确如何从数据库中获得数据。

2.3 报表技巧

要了解用户通常是如何报告数据的：批处理还是在线提交报表？时间间隔是每天、每周、每月、每个季度还是每年？如果需要的话还可以考虑创建总结表。系统生成的主键在报表中很难管理。用户在具有系统生成主键的表内用副键进行检索往往会返回许多重复数据。这样的检索性能比较低而且容易引起混乱。

3 设计表和字段

3.1 采用有意义的字段名

3.2 标准化和数据驱动

数据的标准化不仅方便了自己而且也方便了其他人。比方说，假如你的用户界面要访问外部数据源（文件、XML 文档、其他数据库等），你不妨把相应的连接和路径信息存储在用户界面支持表里。还有，如果用户界面执行工作流之类的任务（发送邮件、打印信笺、修改记录状态等），那么产生工作流的数据也可以存放在数据库里。预先安排总需要付出努力，但如果这些过程采用数据驱动而非硬编码的方式，那么策略变更和维护都会方便得多。事实上，如果过程是数据驱动的，你就可以把相当大的责任推给用户，由用户来维护自己的工作流过程。

3.3 范化不能过头

对那些不熟悉范化一词（normalization ）的人而言，范化可以保证表内的字段都是最基础的要素，而这一措施有助于消除数据库中的数据冗余。范化有好几种形式，但Third NormalForm（3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好平衡。简单来说，3NF 规定：

• 表内的每一个值都只能被表达一次。

• 表内的每一行都应该被唯一的标识（有唯一键）。

• 表内不应该存储依赖于其他键的非键信息。

遵守3NF 标准的数据库具有以下特点：有一组表专门存放通过键连接起来的关联数据。比方说，某个存放客户及其有关定单的3NF 数据库就可能有两个表：Customer 和Order。Order 表不包含定单关联客户的任何信息，但表内会存放一个键值，该键指向Customer 表里包含该客户信息的那一行。更高层次的标准化也有，但更标准是否就一定更好呢？答案是不一定。事实上，对某些项目来说，甚至就连3NF 都可能给数据库引入太高的复杂性。

为了效率的缘故，对表不进行标准化有时也是必要的，但是绝不把数据表的非标准化当作当然的设计理念。而具体的操作不过是一种派生。所以如果表出了问题重新产生非标准化的表是完全可能的。

3.4 不活跃或者不采用的指示符

增加一个字段表示所在记录是否在业务中不再活跃挺有用的。不管是客户、员工还是其他什么人，这样做都能有助于再运行查询的时候过滤活跃或者不活跃状态。同时还消除了新用户在采用数据时所面临的一些问题，比如，某些记录可能不再为他们所用，再删除的时候可以起到一定的防范作用。

3.5 使用角色实体定义属于某类别的列

在需要对属于特定类别或者具有特定角色的事物做定义时，可以用角色实体来创建特定的时间关联关系，从而可以实现自我文档化。这里的含义不是让PERSON 实体带有Title 字段，而是说，为什么不用PERSON 实体和PERSON_TYPE 实体来描述人员呢？然后，比方说，当 John Smith, Engineer 提升为JohnSmith, Director 乃至最后爬到John Smith, CIO 的高位，而所有你要做的不过是改变两个表PERSON 和PERSON_TYPE 之间关系的键值，同时增加一个日期/时间字段来知道变化是何时发生的。这样，你的PERSON_TYPE 表就包含了所有PERSON 的可能类型，比如Associate、Engineer、Director、CIO 或者CEO 等。还有个替代办法就是改变PERSON 记录来反映新头衔的变化，不过这样一来在时间上无法跟踪个人所处位置的具体时间。

3.6 保持字段名和类型的一致性

在命名字段并为其指定数据类型的时候一定要保证一致性。假如字段在某个表中叫做“agreement_number”，你就别在另一个表里把名字改成“ref1”。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。记住，你干完自己的活了，其他人还要用你的数据库呢。

3.7 仔细选择数据类型

在SQL 中使用smallint 和tinyint 类型要特别小心，比如，假如你想看看月销售总额，你的总额字段类型是smallint，那么，如果总额超过了$32,767 你就不能进行计算操作了。所以要合理使用数据类型，不要让数据越界。

下面以DB2数据类型为例来说明。

对于整数类型DB2提供了SMALLINT、INTEGER和BIGINT三种，SMALLINT型占用2个字节的存储空间，数值范围从-32768－32767；INTEGER型占用4个字节的存储空间，数值范围从-2147483648－2147483647；BIGINT型占8个字节，表示数值范围更大，在应用中如果字段不超过32767那就应该选SMALLINT型，不超过2147483647就选INTEGER，否则就选BIGINT，当然如果你都用BIGINT也可以，就是可能会浪费空间，从性能方面来讲SMALLINT当然最优，因为其最短。

对于字符型字段这里只讨论定长（CHAR）变长（VARCHAR）两种，定长型在存储时如果实际长度小于254个字符，系统也用空字符补足254个，所以会有空间浪费，但这种类型查询速度比较快，变长型是实际多长就占多长的磁盘空间，不会有空间浪费，但和定长相比性能稍差一些，因为系统要花费额外的开销来检查字段数据的长度。一般GUID用定长型表示，而职员名称一般用变长型表示。

如果很关心性能，那么少于50个字符的字段建议采用CHAR类型，如果字段长度小于32K就选择VARCHAR而不使用LONG VARCHAR，虽然二者长度差不多，但LONG VARCHAR不能被缓冲。

十进制数包括Decimal和Numeric，必须提供精度和小数位定义，否则按缺省值定义，这两种类型按实际值存放。

浮点数值类型包括了REAL、DOUBLE和FLOAT，均是数的近似值，也就是说说不准确的，这些类型是 IEEE 标准 754 二进制浮点数算术的一般实现，加上下层处理器、操作系统和编译器对它的支持。REAL类型定义一个单精度数，需要32位（4个字节），长度在1-24之间，DOUBLE和FLOAT型也是表示数的近似值，但需要64位（8字节）的存储空间，长度一般在25-53之间，浮点数值类型不能准确地转换成内部格式并且是以近似的形式存储的，因此存储然后把数据再打印出来可能显示一些丢失。

十进制和浮点数相比虽然表示数据准确，但速度要慢，这两类在实际应用中要根据需要选择使用。

对字段都尽量统一使用主要的数据类型，如字符型用Nvarchar等

3.8 删除标记

在表中包含一个“删除标记”字段，这样就可以把行标记为删除。在关系数据库里不要单独删除某一行；最好采用清除数据程序而且要仔细维护索引整体性。

3.9 避免使用触发器和存储过程

触发器的功能通常可以用其他方式实现。在调试程序时触发器可能成为干扰。假如你确实需要采用触发器，你最好集中对它文档化。当然，这里也存在我们的应用程序夸数据库的问题。

3.10 包含版本机制

建议你在数据库中引入版本控制机制来确定使用中的数据库的版本。无论如何你都要实现这一要求。时间一长，用户的需求总是会改变的。最终可能会要求修改数据库结构。虽然你可以通过检查新字段或者索引来确定数据库结构的版本，但我发现把版本信息直接存放到数据库中不更为方便吗？。

3.11 建立适当的表以提高性能

大家应该有一个概念，在关系型数据库中，应该尽量避免出现大表（记录达几十万条以上）。如果一个系统中都是在十、百、千量级记录的表，那无论怎么设计，怎么写sql，怎么建索引甚至不建索引，都不会慢；反之，如果有几个百万记录的大表，而且又被频繁使用，甚至在sql中用了关联的话，那无论如何做优化，系统都很难快得起来。所以，很好的划分表是非常关键的。这里举一个例子：

凭证表分为已过帐和未过帐两个表，分别存储过帐前后的凭证数据。这样当查询帐表选中“包含未过帐凭证”时，就只需要关联未过帐凭证这张表。一般系统使用几年后，已过帐表的记录一般在百万级，而未过帐表的记录不过万级，对未过帐表做关联查询的速度当然很快。而已过帐的凭证数据在汇总表中已经有汇总数据，一般不需要直接使用明细数据。这样，通过增加一张表，巧妙地避开了对大表的频繁操作，系统性能自然大幅度提升。有以下几点建议：

1．从业务出发，降低数据量。

2．严格控制数据行的尺度，每行数据量越小，数据库性能越高。

3．严格控制索引数量。

4．拆表，将一个表结合业务拆分为两个或者多个表。

5．考虑采用工作表和历史表的方式，工作表中记录当前需要处理的数据，历史表存放使用较少的历史数据。

6．与大表相关SQL脚本，不要轻易使用“*”。

3.12 SQL脚本符合SQL99规范

写符合SQL99规范的SQL脚本，不要写难以阅读的SQL脚本，在该加入换行的地方加入换行。

4 选择键和索引

4.1 使用系统生成的主键

假如你总是在设计数据库的时候采用系统生成的键作为主键，那么你实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问。采用系统生成键作为主键还有一个优点：当你拥有一致的键结构时，找到逻辑缺陷很容易。

4.2 分解字段用于索引

为了分离命名字段和包含字段以支持用户定义的报表，请考虑分解其他字段（甚至主键）为其组成要素以便用户可以对其进行索引。索引将加快SQL 和报表生成器脚本的执行速度。假如年度和类型字段可以分解为索引字段那么这些报表运行起来就会快多了。

4.3 键设计4 原则

• 为关联字段创建外键。

• 所有的键都必须唯一。

• 避免使用复合键。

• 外键总是关联唯一的键字段

4.4 建立索引

索引是从数据库中获取数据的最高效方式之一。95%的数据库性能问题都可以采用索引技术得到解决。作为一条规则，我通常对逻辑主键使用唯一的成组索引，对系统键（作为存储过程）采用唯一的非成组索引，对任何外键列采用非成组索引。不过，索引就象是盐，太多了菜就咸了。你得考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用作读写。大多数数据库都索引自动创建的主键字段，但是可别忘了索引外键，它们也是经常使用的键，比如运行查询显示主表和所有关联表的某条记录就用得上。还有，不要索引memo/note 字段，不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间。

关注簇索引

说到索引还要再强调一下簇索引，簇索引也叫聚集索引，就是物理上的存储和逻辑上一致的索引类型，这种索引一个表只能建一个而且更新记录时可能会移动数据在磁盘上的位置，所以要慎重使用。簇索引一般使用在有连续相同记录的字段上（即有些记录的值是相同的），比如职员表“职称”字段就比较适合，多个职员具有相同的职称，如查找“高级”职称时，找到第一个“高级”记录时，下面的记录就不用再通过索引去相应磁盘块上读取，而是直接取下一个连续的物理块，直到下一个不是“高级”的为止，这样显然速度很快。

在以前的系统中发现有些表将簇索引建在了主键上，这样是不恰当的，因为主键的值是唯一的，失去了簇索引的意义，在查询速度上和普通索引相同，另外更新主键还会带来更新物理存储的资源消耗。如果在ＵUID值的主键上建簇索引，则基本每次插入都会导致磁盘数据移动，因为ＵUID字符串的随即产生的，系统要为其进行物理排序。

一些建立索引的原则：

1.对于需要频繁更新的表，原则上索引数不要超过5个

2.对于主要用于查询的表，则索引可以尽量多建

3.理论上对于所有出现在where子句后的字段都要建立对应的索引

4.以上三条实际上是有相互矛盾和冲突的地方，需要在实际应用中灵活掌握，实际就是一个不断权衡的过程

另外，关于索引，请参见附录，对于建立合适的索引有指导意义。

4.5 不要索引常用的小型表

不要为小型数据表设置任何键，假如它们经常有插入和删除操作就更别这样作了。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。

4.6 不要用用户的键

在确定采用什么字段作为表的键的时候，可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。

4.7 可选键有时可做主键

记住，查询数据的不是机器而是人。

假如你有可选键，你可能进一步把它用做主键。那样的话，你就拥有了建立强大索引的能力。这样可以阻止使用数据库的人不得不连接数据库从而恰当的过滤数据。在严格控制域表的数据库上，这种负载是比较醒目的。如果可选键真正有用，那就是达到了主键的水准。我的看法是，假如你有可选键，比如国家表内的state_code，你不要在现有不能变动的唯一键上创建后续的键。你要做的无非是创建毫无价值的数据。比如以下的例子：

Select count(*)

from address, state_ref

where address.state_id = state_ref.state_id

and state_ref.state_code = 'TN'

另一种做法：

Select count(*)

from address

where state_code = 'TN'

如你因为过度使用表的后续键建立这种表的关联，操作负载真得需要考虑一下了。