如何设计数据库表
关系型数据库理论可能是20世纪60年代和70年代存储系统先锋的救星，但是从那是开始它就成了许多数据开发⼈员的毒药，就是因为现代数据库系统发展得如此之好，以⾄于它将其关系型⽀柱对开发⼈员隐藏了。设计良好的关系型数据库很容易使⽤、很灵活，并且能够保护数据的有效性。⽽设计不良的数据相反仍然能够发挥相当的作⽤，但是最终可能会导致数据的⽆效、错误或者丢失。
开发⼈员有⼀些专⽤的规则，叫做范式（normal forms），他们根据这些规则来创建设计良好的数据库。在这⾥，我将通过创建⼀个⽤于保存书籍信息的简单数据库来探讨⼀下范式。
确定实体和元素
设计数据库的第⼀步是做你的家庭作业并确定你所需要的实体。实体是数据⼀种类型的概念集。通常只从⼀两个实体开始，再随着你数据的规范化⽽增加列表。对于我们的⽰例数据库，它看上去就好像我们只需要⼀个实体——书。
在确定了所需要实体的清单之后，你下⼀步就需要为每个实体创建数据元素（也就是说，你需要保存的信息）的清单。收集这样的信息有多种途径，但是最有效的可能就是依赖你的⽤户了。向你的⽤户询问他们⽇常⼯作的情况，要求查看当前完成他们⼯作所需要的各种表格和报告。例如，订单上可能会列出你创建销售应⽤程序所需要的许多数据元素。
我们的书籍实体没有书⾯表格和报告可⽤，但是下列元素清单将有助于我们开始设计这个数据库：
{Title, Author, ISBN, Price, Publisher, Category}
很重要的⼀点是，要注意，把我们这⾥要⽤的实体移动到元素的过程并不能适⽤于所有状况。你所需要的实体不会总是像我们书籍⽰例那样清楚，所以你可能要从数据元素的⼀长串清单开始，在后⾯你会根据实体来划分元素。
正规化的头⼏步
⼀旦有了实体清单（表格）和数据元素（字段），你就准备好让关系型数据库理论运作了。这个理论的主要推动⼒是规范化——删除任何重复的组和冗余的数据，并把它们放到两个或者更多相关表⾥的过程。你并不是⼀定需要拥有⼀个以上的表格，但是你的数据简单到只需要⼀个表格的机会并不多。
你应该⼩⼼地检查数据（这些数据会出现在多条记录⾥）和依赖性错误的实体和元素清单，并把已损坏的字段移动到不同的表格⾥。例如，你可能列出同⼀个作者的多本书，并在数据库⾥重复了作者的名字。当你认为会⼀次⼜⼀次地看到相同的数据值时，你就应该考虑把这个字段移动到另⼀个表格⾥了。
要记住，在这⼀点上，你只是在操作潜在表格的列表，⽽不应该真正地创建这个表格：现在还是要⽤笔和纸来列表。
范式简介
数据库规范化的过程⾮常著名，所以有正式的规则来保证规范化数据库的建设。这些规则有七条，叫做范式，⽽在⼤多数情况下头四条就够⽤了：
第⼀范式（1NF）——这条规则有⼏个要求，包括：⽆多值项⽬（multivalued item）和重复组（repeating group）；每个字段都是原⼦型的（atomic），也就是说每个字段必须包含可能的最⼩数据元素；以及表格含有关键字（key）。
第⼆范式（2NF）——表格必须按照1NF来规范化。所有的字段必须引⽤（或者描述）主键值。如果主键基于⼀个以上的字段，那么每个nonkey字段必须取决于复杂键（complex key），⽽不仅仅是⼀个没有键的字段。不⽀持主键的nonkey字段应该被移动到另⼀个表格⾥去。
第三范式（3NF）——表格必须符合1NF和2NF的要求。所有的字段都必须相互独⽴。任何描述nonkey字段的字段都必须被移动到另⼀个表格⾥。
Boyce-Codd范式（BCNF）——⼀定不能存在依赖于nonkey的字段。这条规则实际上是3NF的⼀个⼦规则，⽤于捕捉可能会通过进程的依赖性。这⼀点相当的抽象，⼀开始是很难应⽤的。
以上的规则很精确，但是技术定义以及规范化的规则能够被简化成下⾯⼏点：
每个字段必须尽量⼩。
每个字段只能包含⼀个数据项⽬。
每条记录都必须是唯⼀的。
注意重复的条⽬。
每个字段都必须完全⽀持主键，⽽且只⽀持主键。
下⼀步该做什么？
应⽤这些规范化规则，尤其是1NF的⼏个要求，将会是个很需要技巧的过程。正如你会在下⾯内容⾥看到的，我会开始真正地把范式应⽤到实⼒数据库上，在进⾏了其他规范化的步骤之后，你就会重新回到1NF。
关系型数据库的理论最早可以追溯到E. F. Codd博⼠1970年的论⽂《⼤型共享数据库的数据关系模型》，在这篇⽂章⾥，他总结出了七条抽象的规则，叫做范式（normal form），⽤来帮助创建设计良好的数据库。这七条规则的前四条——第⼀范式（First Normal Form，1NF）、第⼆范式（2NF）、第三范式（3NF）和Boyce-Codd范式（BCNF）——在⼤多数情况下已经够⽤了。
这些范式是⾮常抽象的，以⾄于有些开发⼈员在如何应⽤它们上存在问题。也许理解范式最好的⽅式是开始将它们应⽤于数据，因为规则在你确实有数据要划分的时候才更有作⽤。在本⽂中，我会对⼀个书⽬⽰例数据库应⽤1NF的规则，这些规则在⼀开始应⽤的时候是最复杂的。
你会回忆起，1NF的要求是：
多值字段（multivalued field）必须要被移动到另⼀个表格⾥。
每个字段必须是原⼦型的（atomic），或者说要尽量地⼩。
每个字段都必须有⼀个关键字（key）.
重复的值必须要被移动到另⼀个表格⾥。
我将要使⽤的简单表格是⽤来保存⼀些书⽬信息的。到⽬前为⽌，这个Books表格有下⾯这些字段：
{Title, Author, ISBN, Price, Publisher, Category}
将多值字段移动到另⼀个表格
应⽤1NF的第⼀步是确保表格没有包含多值字段，从定义可知它能够保存⼀个以上可能的条⽬。我们最
开始的清单有两个可能会违反这⼀规则的地⽅：Author（作者）和Category（分类）。许多书都有多个作者，所以Author字段就会出问题。类似的，⼀本书可以被归⼊多个类别。例如《⾦银岛（Treasure Island）》可以被归为⼉童读物、冒险类、经典类，以及其他类等等。
更正这个问题的唯⼀⽅法是把这些违反规则的字段转移到另⼀个表格⾥。你可能会发现字段在另⼀个已存在表格⾥⼯作得会更好，但是这样的情况⾮常少见。在⼤多数情况下，你需要为你所移动的每个字段都创建新的表格。为了满⾜1NF的要求，我为Author和Category这两个字段创建了两个新的表格，其他的字段都留在Books表格⾥没有动：{Title, ISBN, Price, Publisher}每个字段都必须是原⼦型的
1NF的下⼀项要求是说，每个字段都必须是原⼦型的，这就表⽰每个字段必须保存可能会有的最⼩数据元素。这条规则有助于搜索和排序。Author字段在这⾥再⼀次出现了问题，
因为⼀个名字可以被分成多条信息。我们需要⼀个字段⽤于姓，另⼀个字段⽤于名，这会让搜索作者姓名变得容易得多。
在这⼀点上，我会更进⼀步把Authors表格分成⾄少两个字段：FirstName（名）LastName（姓），那么我数据库的布局就是下⾯这样的：
Books: {Title, ISBN, Price, Publisher}
Authors: {FirstName, LastName}
Categories: {Category}
每个字段都必须有⼀个关键字
搜索有重复记录（duplicate record）的表格是很难的；事实上，关系模型不允许表格包含有重复记录。所以，⼀个表格⾥字段或者列的值必须是唯⼀的。唯⼀性可以通过检查key（关键字）来确定，关键字可以由⼀个单列或者列的组合构成，这样的列叫做composite key（复合关键字）。
关键字有很多不同的类型：
超关键字（Super key）：唯⼀辨别表格⾥记录的⼀个列或者⼀组列。
备选关键字（Candidate key）：包含有确定唯⼀性所需要的最少列的超关键字。
主关键字（Primary key）：⽤来唯⼀辨别表格⾥记录的备选关键字。
备⽤关键字（Alternate key）：没有被选为主关键字的备选键。
外来关键字（Foreign key）：表格内匹配同⼀表格或者另⼀表格⾥备选关键字的⼀个列或者⼀组列。外
来键允许你将⼀个表格⾥的记录和另⼀个表格⾥的数据相关联。
这⾥列出来的关键字的类型并不是相互排斥的；⼀个关键字可以同时被归⼊多个类。从定义上说，每个表格必须⾄少有⼀个主关键字。数据库设计的意义
要确定我们⽰例表格的主关键字，就让我们从到超关键字开始。Authors和Categories表格的超关键字很容易到，因为它们的字段⾮常少，但是Books表格的会稍稍困难⼀点。尽管两本书有同⼀个名字的机会⼏乎没有，但是这不是不可能的，所以我不能把Title（书名）作为Books表格的关键字。两本书来⾃同⼀个出版社具有同⼀个名字或者具有同⼀个ISBN的机会应该更⼩，所以我可以从这些可能性中创建⼀些超关键字。表A列出了这三个表格的所有超关键字：
表A
⽰例数据库的超关键字
Books表格事实上有更多的超关键字，例如Title、ISBN和Publisher，但是要包含所有这些关键字就⼜太多了。
到备选关键字
现在是缩短超关键字列表来到备选关键字的时候了，这些备选关键字包含有⼀个字段⾥满⾜唯⼀性所需的最少列。Categories表格在这⼀点上不存在问题，因为它只有⼀个字段。Authors表格只有⼀个超关键字，所以很明显它就是备选关键字。
但是，Books表格有点⿇烦，但是在最终的分析⾥，ISBN字段是备选关键字的最好选择。ISBN字段应该是唯⼀的，但是由于这些数字是由出版商来指定的，所以还是可能出现使⽤同⼀ISBN的两本书。实际情况是，只使⽤ISBN可能永远也不会碰到问题，但是⼀旦
出现这样的⼩错误，你的整个数据库可能都会崩溃。因此，我决定把Title和ISBN这两个复合超关键字作为Books表格的备选关键字。
确定主关键字
主关键字只不过是你最后⽤来唯⼀辨别表格⾥每条记录的备选关键字。在做完这些事之后，为每个表格分配主关键字就很容易了。现在我为每个⽰例数据库定义了下列表格（星号表⽰主关键字字段）：
Books: {*Title, *ISBN, Price, Publisher}
Authors: {*FirstName, *LastName}
Categories: {*Category, Description}
要注意，Categories包含有⼀个新的字段——Description。单字段的表格是可以接受的，但是加⼊了描述⽂本的字段将有助于更加完全地解释每个分类。
将计数字段（counter field）作为主关键字
⼤多数关系型数据库管理系统（RDBMS）都会提供⼀类计数或者⾃动编号的数据类型，它会为每条记录分配⼀个连续的数值。尽管这些计数字段会保证你得到⼀个备选关键字，但是这个关键字对于搜索是毫⽆疑义的。如果你把计数字段作为主关键字，你⾄少还需要另⼀个字段，这样才能以有意义的⽅式到记录。
那么Authors⼜会是什么样的呢？在Authors表格⾥出现重复的姓和名的机会好像不多，尤其当你的数据库很⼩的时候。但是不管怎么说这不是不可能的。你可以为这个表格分配⼀个计数字段，这肯定会解决唯⼀性的问题，但是这不会有助于你区别不同的作者。
在这⾥，解决我们问题最简单的⽅法是加⼊某种联系信息，例如电⼦邮件地址或者作者所居住的州或者地区等信息，但是这还是不够明确。你有可能碰到住在同⼀个州同名同姓的两个作者。这样的情况不多但不是没有可能，所以最好还是为出错做好准备。你可以考虑加⼊每个作者的邮件地址，但是为了保持例⼦的简单，我只加⼊了州名和，并扩展主关键字，让其能够同时包含两个新的字段：
Authors: {*FirstName, *LastName, *State, *ZIP}
在这⼀点上，这真的是保证Authors表格唯⼀性的唯⼀⽅法。
删除重复值
1NF需要我们满⾜的最后⼀条要求是在数据⾥不能有重复的组（group）。在没有检查真实数据之前，要确定你是否满⾜了这⼀要求是相当困难的，但是既然我们已经碰到了，不管怎么样就应该着⼿解决。之后，如果看到⼀个值重复了多次，我就会考虑把这个字段移动到⼀个新的表格⾥。如果我数据库⾥的表格通过别的途径被正确地规范化了，那么重新建模将不会是个⼤问题。事实上，许多数据库应⽤程序似乎总在不停地扩展
这最后⼀条规则最明显的违反者是Books表格⾥的Publisher（出版商）字段。尽管有很多出版商，但是我毫⽆疑问会发现⾃⼰⼀次⼜⼀次地碰到同⼀个出版商。为了让Books 满⾜1NF，我必须要把这个字段移动到另⼀个表格⾥。现在⽰例数据库看起来像下⾯这样：Books: {*Title, *ISBN, Price}
Authors: {*FirstName, *LastName, *State, *ZIP}
Categories: {*Category, Description}
Publishers: {*Publisher}
你可能不会碰到重复的出版商名，但是为了安全起见，你可以决定将⾃动编号字段作为主关键字。如果这样做的话，你的表格设计应该会像下⾯这样：
Publishers: {*PublisherID, Name}
最后，⽰例数据库⾥的表格看上去都符合1NF的要求了。每个字段都尽可能的⼩，没有重复的组和多值字段，每个表格都有⼀个关键字。你可能会问还差什么？毕竟，开始的
时候只有⼀个表格，
设计阶段，花在数据正规化上的时间可能⽐花上其他任何任务上的时间都要多。⽽且数据越多，这个过程所花的时间更长。根据以往的经验，你可能发现最困难的就是满⾜第⼀范式（1NF）的所有要求，因为将重复的值移动到另⼀个表时，经常会消除不恰当的依赖。
完成最困难的部分后，你可能选择在1NF之后就停⽌了，但不要这样做。请继续对数据进⾏正规化，尽可能地通过第⼆范式（2NF），第三范式（3NF），甚⾄通过Boyce-Codd 范式（BCNF）。这样就能出那些具有依赖性的数据元素，否则它们会在设计过程中悄悄地溜⾛，并在以后造成问题。最好在设计期间就发现这些问题——不要等到⽤户发现⾃⼰的⼯作⽆法完成，或者等到你开始损失⾦钱的时候。
在该系列的上⼀篇⽂章中，我们已经从⼀个表着⼿，并对其进⾏了处理，使其符合1NF的要求。该表最
终变成了4个表。现在，让我们通过应⽤2NF、3NF和BCNF来完成正规化过程。
继续未完的⼯作
我们的⽰范数据库在完⼯以后，将⽤来存储和书籍有关的数据；这是⼀个⾮常简单的⽬的，所以只需要⼀个简单的数据库。我们现在已经有4个表，⽽且全部正规化为1NF（记住，关键字段是⽤星号表⽰的）：
Books: {*Title, *ISBN, Price}
Authors: {*FirstName, *LastName, *State, *ZIP}
Categories: {*Category, Description}
Publishers: {*Publisher}
应⽤2NF
为了满⾜2NF的要求，表⾸先必须正规化成1NF，也就是其中没有多值项，没有重复的组，每个字段都只能包含原⼦值，⽽且每个表都必须包含⼀个键。迄今为⽌，似乎所有表都满⾜这个要求。2NF的第⼆
个要求是所有字段（在设计阶段通常称为“属性”）都必须依赖于主键，⽽且只能依赖于主键。就⽬前来看，似乎所有属性都满⾜2NF的要求，⽆需采取进⼀步的操作。
另⼀⽅⾯，假定Books表中还存储着⽤于描述借阅者的⼤量属性。有的属性不会违反2NF的要求，例如Books表中的⼀个lent date（借阅⽇期）属性。然⽽，其他数据（⽐如借阅者的姓名、地址等等）就会违反2NF，因为和借阅有关的信息不能完全地⽀持或描述书籍本⾝。
应⽤3NF
⼀个表在完成了2NF正规化后，可开始检查它是否违反3NF。3NF要求所有字段都相互独⽴。任何字段如果依赖于⼀个⾮关键字段，都必须转移到另⼀个表中。为了出违反3NF的地⽅，最简单的⽅式就是修改每个属性的值，看它是否⽴即使其他属性所包含的数据⽆效。这种简单测试虽然不能出违反3NF的所有地⽅，但却是⼀个不错的开端。
Authors表存在⼀个可能违反3NF的地⽅：如果更改State值，那么可能同时还要更新ZIP；反之亦然。例如，假如作者移居另外⼀个州，那么上述两个值都需要修改。为了避免这种形式的依赖性，你需要将State属性转移到⼀个新表中，如下所⽰：
Authors: {*FirstName, *LastName, *ZIP}
States: {*State}
上述修改的结果就是，每个作者都有了⼀个ZIP值，其中部分值可能重复，但在States表中，每个州只占⽤⼀条记录。如果某个作者移居到其他某个州，你虽然需要更新ZIP 值，但只需将记录与⼀个不同的州联系起来就可以了。如果是⼀个新出现的州，就可能需要输⼊⼀个新的州值，但⾄少州值不会重复。
就⽬前来说，感觉是在创建⼀个查表（lookup table）。以后，这些表会通过它们的主键和外键值相互联系，但在正式建⽴联系之前，按上述逻辑进⾏操作可能显得⽐较困难。不过，如果搞不清楚当前的状况，请不要担⼼。⽬前只需将注意⼒集中在规则上就可以了。
现在已经有了5个表，全部正规化成3NF：
Books: {*Title, *ISBN, Price}
Authors: {*FirstName, *LastName, *ZIP}
Categories: {*Category, Description}
Publishers: {*Publisher}
States: {*State}
有⼈会对此产⽣疑问，因为有⼀个属性似乎没有考虑到，也就是Authors表中的ZIP。前⾯说过，ZIP值是有可能重复的。在这个简单的应⽤程序中，将ZIP留在Authors 表中似乎是可以接受的；⽆论如何，数据库都应该能⾼效地运⾏。不过，这个表并没有充分地正规化，所以下⾯尝试将ZIP转移到⼀个新表中。在移动了ZIP之后，我们就有了6个表：Books: {*Title, *ISBN, Price}
Authors: {*FirstName, *LastName}
ZIPCodes: {*ZIP}
Categories: {*Category, Description}
Publishers: {*Publisher}
States: {*State}
正规化不⼀定能保证效率并⾮每个表都必须在完全正规化后才能获得⾼效率。换⾔之，如果你发现能使数据库变得更⾼效，那么完全可以对⼀个表进⾏反正规化处理。
表的数量虽然在快速增加，但请不要担⼼。事实上，我们尚未完⼯。在本系列的下⼀篇⽂章中，甚⾄可能出现更多的表。届时，我们将讨论主键和外键字段，并解释如何⽤它们在多个表之间建⽴关系。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。