如何设计数据库表

1、关系型数据库理论可能是 20世纪60年代和70年代存储系统先锋的救星，但是从那是开始 它就成了许多数据开发人员的毒药，就是因为现代数据库系统发展得如此之好，以至于它将其关系型支柱对开发人员隐藏了。设计良好的关系型数据库很容易使用、很灵活，并且能够保护数据的有效性。而设计不良的数据相反仍然能够发挥相当的作用，但是最终可能会导致数据的无效、错误或者丢失。开发人员有一些专用的规则，叫做范式（normal forms），他们根据这些规则来创建设计良好的数据库。在这里，我将通过创建一个用于保存书籍信息的简单数据库来探讨一下范式。确定实体和元素设计数据库的第一步是做你的家庭作业并确定你所需要的实体。实体是

2、数据一种类型的概念集。通常只从一两个实体开始，再随着你数据的规范化而增加列表。 对于我们的示 例数据库，它看上去就好像我们只需要一个实体一一书。在确定了所需要实体的清单之后，你下一步就需要为每个实体创建数据元素（也就是说，你需要保存的信息）的清单。收集这样的信息有多种途径，但是最有效的可能就是依赖你的 用户了。向你的用户询问他们日常工作的情况，要求查看当前完成他们工作所需要的各种表格和报告。例如，订单上可能会列出你创建销售应用程序所需要的许多数据元素。我们的书籍实体没有书面表格和报告可用，但是下列元素清单将有助于我们开始设计这个数据库：Title, Author, ISBN, Price, P

3、ublisher, Category很重要的一点是，要注意，把我们这里要用的实体移动到元素的过程并不能适用于所有状况。你所需要的实体不会总是像我们书籍示例那样清楚，所以你可能要从数据元素的一长串清单开始，在后面你会根据实体来划分元素。正规化的头几步一旦有了实体清单（表格）和数据元素（字段），你就准备好让关系型数据库理论运作了。这个理论的主要推动力是规范化一一删除任何重复的组和冗余的数据，并把它们放到两个或者更多相关表里的过程。你并不是一定需要拥有一个以上的表格，但是你的数据简单到只需要一个表格的机会并不多。你应该小心地检查数据（这些数据会出现在多条记录里）和依赖性错误的实体和元素清单，并把已损

4、坏的字段移动到不同的表格里。例如，你可能列出同一个作者的多本书， 并在数据库里重复了作者的名字。当你认为会一次又一次地看到相同的数据值时，你就应该考虑把这个字段移动到另一个表格里了。要记住，在这一点上，你只是在操作潜在表格的列表，而不应该真正地创建这个表格：现在还是要用笔和纸来列表。范式简介数据库规范化的过程非常著名，所以有正式的规则来保证规范化数据库的建设。这些规则有七条，叫做范式，而在大多数情况下头四条就够用了：第一范式（1NF）这条规则有几个要求，包括：无多值项目（ multivalued item）和重复组（repeating group）；每个字段都是原子型的（atomic），也就是

5、说每个字段必须包 含可能的最小数据元素；以及表格含有关键字（ key）。第二范式（2NF）表格必须按照1NF来规范化。所有的字段必须引用（或者描述） 主键值。如果主键基于一个以上的字段，那么每个non key字段必须取决于复杂键（complexkey），而不仅仅是一个没有键的字段。不支持主键的non key字段应该被移动到另一个表格里去。第三范式（3NF）表格必须符合1NF和2NF的要求。所有的字段都必须相互独立。任何描述non key字段的字段都必须被移动到另一个表格里。Boyce-Codd 范式（ BCNF） 一定不能存在依赖于 nonkey 的字段。这条规则实际上是3NF的一个子规则，用

6、于捕捉可能会通过进程的依赖性。这一点相当的抽象，一开始是很难 应用的。以上的规则很精确，但是技术定义以及规范化的规则能够被简化成下面几点： 每个字段必须尽量小。每个字段只能包含一个数据项目。 每条记录都必须是唯一的。注意重复的条目。 每个字段都必须完全支持主键，而且只支持主键。下一步该做什么？应用这些规范化规则，尤其是 1NF 的几个要求，将会是个很需要技巧的过程。正 如你会在下面内容里看到的， 我会开始真正地把范式应用到实力数据库上， 在进行了其他规 范化的步骤之后，你就会重新回到1NF。关系型数据库的理论最早可以追溯到E. F. Codd博士 1970年的论文大型共享数据库的数据关系模型，

7、在这篇文章里，他总结出了七条抽象的规则，叫做范式（ normal form ），用来 帮助创建设计良好的数据库。这七条规则的前四条 第一范式（ First Normal Form， 1NF）、 第二范式（2NF）、第三范式（3NF）和 Boyce-Codd范式（BCNF）在大多数情况下已经 够用了。这些范式是非常抽象的， 以至于有些开发人员在如何应用它们上存在问题。 也许理解范 式最好的方式是开始将它们应用于数据，因为规则在你确实有数据要划分的时候才更有作 用。在本文中，我会对一个书目示例数据库应用 1NF 的规则，这些规则在一开始应用的时 候是最复杂的。你会回忆起， 1NF 的要求是：多值字

8、段（ multivalued field ）必须要被移动到另一个表格里。 每个字段必须是原子型的（ atomic ），或者说要尽量地小。 每个字段都必须有一个关键字（ key） .重复的值必须要被移动到另一个表格里。我将要使用的简单表格是用来保存一些书目信息的。 到目前为止， 这个 Books 表格 有下面这些字段：Title, Author, ISBN, Price, Publisher, Category将多值字段移动到另一个表格应用 1NF 的第一步是确保表格没有包含多值字段，从定义可知它能够保存一个以 上可能的条目。我们最开始的清单有两个可能会违反这一规则的地方：Author （作者）

9、和Category （分类）。许多书都有多个作者，所以Author字段就会出问题。类似的，一本书可以被归入多个类别。例如金银岛（Treasure Isla nd）可以被归为儿童读物、冒险类、经典类，以及其他类等等。更正这个问题的唯一方法是把这些违反规则的字段转移到另一个表格里。你可能会发现字段在另一个已存在表格里工作得会更好， 但是这样的情况非常少见。 在大多数情况下， 你需要为你所移动的每个字段都创建新的表格。为了满足1NF 的要求，我为 Author 和Category这两个字段创建了两个新的表格，其他的字段都留在Books表格里没有动：Title, ISBN, Price, Publis

10、her 每个字段都必须是原子型的1NF的下一项要求是说，每个字段都必须是原子型的，这就表示每个字段必须保存可能会有的最小数据元素。这条规则有助于搜索和排序。Author 字段在这里再一次出现了问题，因为一个名字可以被分成多条信息。我们需要一个字段用于姓，另一个字段用于名，这会让搜索作者姓名变得容易得多。在这一点上，我会更进一步把Authors表格分成至少两个字段：FirstName (名)LastName (姓)，那么我数据库的布局就是下面这样的：Books: Title, ISBN, Price, PublisherAuthors: FirstName, LastNameCategories

11、: Category每个字段都必须有一个关键字搜索有重复记录(duplicate record )的表格是很难的；事实上，关系模型不允许表格 包含有重复记录。所以，一个表格里字段或者列的值必须是唯一的。唯一性可以通过检查 key (关键字)来确定，关键字可以由一个单列或者列的组合构成，这样的列叫做compositekey (复合关键字)。关键字有很多不同的类型：超关键字(Super key):唯一辨别表格里记录的一个列或者一组列。备选关键字(Can didate key)：包含有确定唯一性所需要的最少列的超关键字。主关键字(Primary key):用来唯一辨别表格里记录的备选关键字。备用关键

12、字(Alternate key):没有被选为主关键字的备选键。外来关键字(Foreign key):表格内匹配同一表格或者另一表格里备选关键字的一个列或者一组列。外来键允许你将一个表格里的记录和另一个表格里的数据相关联。这里列出来的关键字的类型并不是相互排斥的；一个关键字可以同时被归入多个类。从定义上说，每个表格必须至少有一个主关键字。要确定我们示例表格的主关键字，就让我们从找到超关键字开始。Authors和Categories表格的超关键字很容易找到，因为它们的字段非常少，但是Books表格的会稍稍困难一点。尽管两本书有同一个名字的机会几乎没有，但是这不是不可能的，所以我不能把Title (

13、书名)作为Books表格的关键字。两本书来自同一个出版社具有同一个名字或者具有 同一个ISBN的机会应该更小，所以我可以从这些可能性中创建一些超关键字。表A列出了这三个表格的所有超关键字：表ABooksAuthorsCategoriesTitleFirstName, LastMame匚 ategoryTitle, ISBNISBN示例数据库的超关键字Books表格事实上有更多的超关键字，例如Title、ISBN和Publisher，但是要包含所有这些关键字就又太多了。找到备选关键字现在是缩短超关键字列表来找到备选关键字的时候了，这些备选关键字包含有一个字段里满足唯一性所需的最少列。Catego

14、ries表格在这一点上不存在问题，因为它只有一个字段。Authors表格只有一个超关键字，所以很明显它就是备选关键字。但是，Books表格有点麻烦，但是在最终的分析里，ISBN字段是备选关键字的最好选择。ISBN字段应该是唯一的，但是由于这些数字是由出版商来指定的，所以还是可能出 现使用同一 ISBN的两本书。实际情况是，只使用ISBN可能永远也不会碰到问题，但是一旦出现这样的小错误，你的整个数据库可能都会崩溃。因此，我决定把Title和ISBN这两个复合超关键字作为 Books表格的备选关键字。确定主关键字主关键字只不过是你最后用来唯一辨别表格里每条记录的备选关键字。在做完这些事之后，为每个

15、表格分配主关键字就很容易了。 现在我为每个示例数据库定义了下列表格 （星 号表示主关键字字段）：Books: *Title, *ISBN, Price, PublisherAuthors: *FirstName, *LastNameCategories: *Category, Descripti on要注意，Categories包含有一个新的字段Description。单字段的表格是可以接受的，但是加入了描述文本的字段将有助于更加完全地解释每个分类。将计数字段（counter field ）作为主关键字大多数关系型数据库管理系统（RDBMS）都会提供一类计数或者自动编号的数据类型，它会为每条记

16、录分配一个连续的数值。尽管这些计数字段会保证你得到一个备选关键字，但是这个关键字对于搜索是毫无疑义的。如果你把计数字段作为主关键字，你至少还需要另一个字段，这样才能以有意义的方式找到记录。那么Authors又会是什么样的呢？在 Authors表格里出现重复的姓和名的机会好像 不多，尤其当你的数据库很小的时候。但是不管怎么说这不是不可能的。你可以为这个表格分配一个计数字段，这肯定会解决唯一性的问题，但是这不会有助于你区别不同的作者。在这里，解决我们问题最简单的方法是加入某种联系信息，例如电子邮件地址或者作者所居住的州或者地区等信息，但是这还是不够明确。你有可能碰到住在同一个州同名同姓 的两个作者

17、。这样的情况不多但不是没有可能，所以最好还是为出错做好准备。你可以考虑加入每个作者的邮件地址，但是为了保持例子的简单，我只加入了州名和邮政编码，并扩展主关键字，让其能够同时包含两个新的字段：Authors: *FirstName, *LastName, *State, *ZIP在这一点上，这真的是保证Authors表格唯一性的唯一方法。删除重复值1NF需要我们满足的最后一条要求是在数据里不能有重复的组（group）。在没有检查真实数据之前，要确定你是否满足了这一要求是相当困难的，但是既然我们已经碰到了， 不管怎么样就应该着手解决。之后，如果看到一个值重复了多次，我就会考虑把这个字段移 动到一个

18、新的表格里。如果我数据库里的表格通过别的途径被正确地规范化了，那么重新建模将不会是个大问题。事实上，许多数据库应用程序似乎总在不停地扩展这最后一条规则最明显的违反者是 Books表格里的Publisher（出版商）字段。尽管 有很多出版商，但是我毫无疑问会发现自己一次又一次地碰到同一个出版商。为了让Books满足1NF，我必须要把这个字段移动到另一个表格里。现在示例数据库看起来像下面这样：Books: *Title, *ISBN, PriceAuthors: *FirstName, *LastName, *State, *ZIPCategories: *Category, Descripti

19、onPublishers: *Publisher你可能不会碰到重复的出版商名，但是为了安全起见，你可以决定将自动编号字段作为主关键字。如果这样做的话，你的表格设计应该会像下面这样：Publishers: *PublisherlD, Name最后，示例数据库里的表格看上去都符合1NF的要求了。每个字段都尽可能的小，没有重复的组和多值字段， 每个表格都有一个关键字。 你可能会问还差什么？毕竟，开始的 时候只有一个表格，设计阶段， 花在数据正规化上的时间可能比花上其他任何任务上的时间都要多。 而且数据越 多，这个过程所花的时间更长。根据以往的经验，你可能发现最困难的就是满足第一范式（1NF）的所有要

20、求，因为将重复的值移动到另一个表时，经常会消除不恰当的依赖。完成最困难的部分后，你可能选择在 1NF 之后就停止了，但不要这样做。请继续 对数据进行正规化， 尽可能地通过第二范式（2NF） ，第三范式 （3NF） ，甚至通过 Boyce-Codd范式（BCNF）。这样就能找出那些具有依赖性的数据元素，否则它们会在设计过程中悄悄地 溜走， 并在以后造成问题。 最好在设计期间就发现这些问题 不要等到用户发现自己的工 作无法完成，或者等到你开始损失金钱的时候。在该系列的上一篇文章中，我们已经从一个表着手，并对其进行了处理，使其符合1NF 的要求。该表最终变成了 4 个表。现在，让我们通过应用2NF、

21、3NF 和 BCNF 来完成正规化过程。继续未完的工作我们的示范数据库在完工以后， 将用来存储和书籍有关的数据； 这是一个非常简单的目 的，所以只需要一个简单的数据库。我们现在已经有4个表，而且全部正规化为1NF （记住， 关键字段是用星号表示的） ：Books: *Title, *ISBN, PriceAuthors: *FirstName, *LastName, *State, *ZIPCategories: *Category, DescriptionPublishers: *Publisher应用 2NF为了满足2NF的要求，表首先必须正规化成1NF，也就是其中没有多值项，没有重复的组

22、， 每个字段都只能包含原子值， 而且每个表都必须包含一个键。 迄今为止， 似乎所 有表都满足这个要求。 2NF 的第二个要求是所有字段 （在设计阶段通常称为 “属性”）都必须 依赖于主键，而且只能依赖于主键。就目前来看，似乎所有属性都满足2NF 的要求，无需采取进一步的操作。另一方面，假定 Books 表中还存储着用于描述借阅者的大量属性。有的属性不会违反2NF的要求，例如Books表中的一个lent date （借阅日期）属性。然而，其他数据（比如借 阅者的姓名、地址等等）就会违反2NF，因为和借阅有关的信息不能完全地支持或描述书籍 本身。应用 3NF一个表在完成了 2NF 正规化后，可开始

23、检查它是否违反 3NF。 3NF 要求所有字段都 相互独立。 任何字段如果依赖于一个非关键字段， 都必须转移到另一个表中。 为了找出违反 3NF 的地方， 最简单的方式就是修改每个属性的值， 看它是否立即使其他属性所包含的数据 无效。这种简单测试虽然不能找出违反 3NF 的所有地方，但却是一个不错的开端。Authors表存在一个可能违反 3NF的地方：如果更改 State值，那么可能同时还要更 新ZIP ;反之亦然。例如，假如作者移居另外一个州，那么上述两个值都需要修改。为了避 免这种形式的依赖性，你需要将State属性转移到一个新表中，如下所示：Authors: *FirstName, *LastName, *ZIPStates: *State上述修改的结果就是，每个作者都有了一个 ZIP 值，其中部分值可能重复，但在 States表中，每个州只占用一条记录。 如果某个作者移居到其他某个州，你虽然需要更新ZIP值，但只需将记录与一个不同的州联系起来就可以了。 如果是一个新出现的州， 就可能需要 输入一个新的州值，但至少州值不会重复。