数据库设计和命名约定

引言

我查阅了许多资料，发现大多数数据库“标准”都没有意义，所以我想记录下我的标准。

背景

在网上查阅标准时，我看到有一些建议，比如表名使用前缀 `tbl`，列名使用 `col`，主键列名使用表名，这些都很愚蠢。当人们开始给列名添加前缀时，情况会变得更糟。我见过最糟糕的是，在客户表中，用 `cusCustomerNotes` 来表示一个字符串。所以，我将提出我的理念并解释每一点。

自然连接是邪恶的

我从这里开始，因为如果我不说清楚，我的下一点就会引起无休止的争论。我听到的最荒谬的论点之一是，列应该被唯一命名，这样你的工具“知道”正确的连接，而你无需进行显式连接。因此，像 Enterprise Manager 或 Oracle 中的 SQL 执行引擎这样的工具，将显式或隐式地为你创建连接。

如果你不知道什么是自然连接，简单的描述就是“你可以偷懒而不写连接，因为数据库知道你的意思”。所以像下面这样的 SQL 实际上是有效的（甚至在 Oracle 中也不报错）。

SELECT person.*,card.* from person, card

这里显而易见的问题是，你完全不知道社保号、人员、谁知道呢……正在连接什么。此外，你不知道它应该是内连接（正如自然连接一样），还是交叉连接。交叉连接连接了两个表中的所有行。

第二个问题是，不同的数据库系统会以不同的方式处理该语句，有些将其视为交叉连接，有些则将其视为自然连接。

根据我的经验，所有连接都应该显式地列出。以下内容清楚地表明了意图

SELECT person.*, card.* 
FROM person 
INNER JOIN card 
    ON card.person_id = person.person_id

如果我设计了这些表，它会是下面的样子。（请注意，Person 表中的 `id` 列现在只是 `id` 而不是 `person_id`），所以自然连接将不起作用。然而，像 Enterprise Manager 这样的工具会识别外键关系。这是假设存在外键关系，如果你想确保数据完整性，这是绝对必要的。

SELECT person.*, card.* 
FROM person 
INNER JOIN card 
    ON card.person_id = person.id

我之所以不喜欢 `where` 子句中的隐式连接，原因很简单：你可能会漏掉连接，从而导致自然连接和交叉连接。在像下面这样复杂的查询中，你很容易出错。

select country.countryname, state.statename, county.countyname, city.cityname
FROM country, state, county, city
WHERE state.country=country.country and city.county=county.county

至少在这个例子中，州和县是一个自然连接或交叉连接，取决于是否存在匹配的列名，但我们不知道，所以谁知道这会返回什么数据。

在我看来，最好具体说明，让编译器知道你想要什么，而不是让它猜测。

重用列名

我为什么要为每个表都创建唯一的名称呢？对于简单的事情，如果一个列是唯一的行 ID，那么它可能应该被称为 `ID`；如果它是用于描述，它可能应该被称为 `Description`。这样，在访问表或将其用于下拉列表之类的东西时，它将始终是相同的 SQL。一致性是关键；如果你总是将表示名称的列命名为“Name”，那么处理起来会比 `colcustblCustomerNameString` 容易得多。如果我们停下来思考一下，我们已经知道了表名，那么为什么要将其包含在列名中呢？此外，数据库不仅仅是用来存储数据的，还有一些程序在使用它，所以更现代的程序甚至可以使用称为对象关系映射器（ORM）的映射工具，而命名一致性有助于一切。

每个表都有一个行标识符

没有什么比在父行具有复合键时，为单个实体将表连接到多个列来强制执行数据库约束更糟糕的了。以下是我们的州、县和市的例子，当事情变得疯狂时……

SELECT Country.CountryName, State.StateName, County.CountyName
    FROM Country
    INNER JOIN State 
        ON State.CountryName = Country.CountryName
    INNER JOIN County 
        ON County.StateName = State.StateName 
        AND County.CountryName = State.CountryName
        AND County.CountryName = Country.CountryName

正如你所看到的，这很快就会失控，而且不可否认，这对于 `Select` 语句来说是一个糟糕的例子，因为所有信息都在县表中。然而，许多数据库不支持多列外键约束，所以想象一下当有人在输入县名时输入了错误的县名会发生什么。通过每个表都有一个唯一的行标识符，它允许你使用外键引用该行以确保完整性，并简化 SQL 中的连接。

SELECT Country.Name as Country, State.Name as State, County.Name  as County
    FROM Country
    INNER JOIN State 
        ON State.Country_ID = Country.ID
    INNER JOIN County 
        ON County.State_ID = State.ID

如你所见，它也简化了 SQL。在我离开这个话题之前，我想谈谈 GUID 和其他键。

唯一标识符和主键

关于 GUID 是否能成为有用的主键，有很多争论。我认为重点被忽略了，GUID 应该是唯一标识符，这并不是说它们应该用作主键，因为那样聚类（实际文件系统中行的组织）将变得毫无意义，从而导致性能严重下降。这些 GUID 应该用作唯一的行标识符，在某些情况下，同步技术需要它们。

一个关于 GUID 非常普遍的抱怨是它们很难记住。是的，这是事实，这就是为什么它们永远不应该用作主键的原因；使行唯一的东西应该是主键。理想情况下，是人类有意义的数据，这样人类就可以查看它。但是当在数据库级别强制执行约束时，尤其是在关系中，很高兴知道你不能将错误的数据放入列中。看看下面的实体关系图

看到这个，我们发现表上已经设置了外键关系，因此就数据库而言，只要父表中存在具有正确编号的行，该行就是有效的。但是如果我们意识到它们只是整数，程序中的一个 bug，一个复制粘贴错误，或者存储过程中的一个拼写错误都可能颠倒数据。将人员表中的数字插入到“car_id”列中，将汽车表中的数字插入到“person_id”列中。根据每个表中的行数，可能需要很长时间才能发现错误，然后所有数据都必须重新输入。因此，仅仅因为你有一个外键，这本身并不能保证数据的有效性。然而，如果 ID 列确实是 GUID，则此类错误的可能性将降至几乎不存在的程度。

换句话说，这条 SQL 更容易出错并且不会被发现

INSERT INTO Car_Owner (person_id, car_id) VALUES (3, 5)

比这条更容易

INSERT INTO Car_Owner (person_id, car_id) 

VALUES ('{E1D56D43-4FCA-44B1-8D16-BF7F106D0A6D}',

'{BCFB7934-6FB2-4AC1-96BC-1D8D46C7067D}')

主键应该基于行的唯一数据

这里有两个想法，首先，GUID 对人类来说没有意义，你不应该在日常使用数据时需要知道 GUID。它们是为了数据完整性，而不是供人们使用。其次，在查找数据时，数据库需要能够轻松找到数据。所以你不应该将人员表的唯一键设为 GUID，你应该将其设为与他们相关的唯一信息，也许是他们的社会安全号，并在他们的姓、中间名和名上设置一个辅助的非唯一索引。使用姓名作为主键的问题是像 John Smith 这样的姓名并不唯一，但它很可能是一个常见的搜索项，因此需要非唯一索引。

引用其他表的列

当一个表需要引用另一个表中已存在的行时，它应该遵循以下几个规则

• 此类列应仅引用行 ID 列
• 引用应通过外键约束强制执行
• 它应该包含表名

我将逐一讨论每一点，首先是第一点。一个表应该包含唯一的数据，这是它存在的原因。如果表中有两条完全相同的数据，你就有一个问题，但是该行可能正在关联另外两个表，比如我上面的 `Car_Owner` 表。如果我有一个表现在需要引用该表，我不想将该关系保存在另一个地方，我想确保该关系存在。因此，与其在新表中保留两列，我宁愿只保留 `Car_Owner` 中的 `ID` 列，或者举另一个例子，我不想在我的 City 表中保留 `County`、`State` 和 `Country` 列，而我可以在 City 表中保留单个 `County_ID` 列。

第二点很简单，我们告诉数据库确保我们引用的数据确实存在。

最后，包含表名可确保一致性。我知道如果我有一个名为 `Person_ID` 的列，它将是 Person 表中的 `ID` 列。

关系表应仅连接两个表

人们经常试图将多个关系强制放入一个表中。例如，请看下图

从这个关系中，你无法判断这个关系应该如何，也无法判断其他关系，比如用户属于一个或多个组是否确实存在。如果关系是每次定义两个，你可以判断这个表是关联 `media` 到 `user` 的 `group`s，还是关联 `media` 的 `group`s 到 `user`s，或者其他什么。在建立下一个关联之前，也会保证这些关联的存在。例如，在公司的汽车租赁合同中，你需要确保合同和公司存在，然后才能开始分配汽车。

使用模式/命名空间分离同名对象

在过去，或者在使用弱数据库时，你不得不通过命名不同的对象来区分 `State`（一块土地）和 `State`（流程在工作流中的位置）。所以你最终会把一个命名为 `WorkFlowState`，另一个命名为 `State`，这没问题，但现代数据库可以将这两个对象分割成单独的块。有些数据库称这些块为“模式”，有些称它们为“命名空间”，而 MySQL 似乎认为它们是独立的数据库。在大多数情况下，将这些对象分区到命名空间在概念上与让程序中的对象使用不同命名空间没有区别。你的查询必须是具体的，但这并不会阻止对象被使用。

对关键字的恐惧

正如你所知，我并不害怕在列名和表名中使用可能是关键字的词语。大多数现代数据库都提供了一些方法来处理转义关键字，这样你就可以将它们用作列名之类的东西。SQL92（现在应该被所有数据库遵守）规定使用双引号（"）字符来执行此操作。Microsoft SQL Server 使用方括号（[]）来执行此操作，如果指定了 `QUOTED_IDENTIFIER ON`，则允许使用双引号。MySQL 使用的是 重音符 （`）。

对于所有的 .NET 程序员，如果你知道 在哪里查找，DataProviders 会提供字符。

充分利用你的数据库

强类型化你的数据

就像编程一样，有不同的类型可以做不同的事情，为工作使用正确的类型。换句话说，强类型化你的数据。

• 使用 `boolean` 或 `bit` 存储真/假值
• 不要使用字符字段存储数字
• 确保数字字段具有有效值的约束
• 仅当数据确实是固定长度时才使用 `char/nchar` 字段

就像在编程中一样，这些类型不仅能帮助你高效地保存数据，还能帮助强制执行数据完整性。使用固定长度字符字段存储可变长度数据是我个人的一个痛点。因为如果数据不像字段那么长，数据库会填充它，这要求编程在所有地方都去除填充。

使用级联、触发器和约束

级联确保当你删除或更新一行时，依赖于该行的行也会被删除或更改。

触发器可以帮助确保数据更新时运行计算。

字段上的约束可以确保诸如“此字段必须是正数”之类的东西。但需要注意的是，使用它来强制执行“魔术值”，即“此字段必须是‘P’、‘Q’或‘Z’”：这表明它实际上应该是一个指向表的外部键，以便你可以将其显示给用户。

避免字段过载

不要在一种情况下用一个字段表示一件事（例如价格），而在另一种情况下表示另一件事（例如折扣百分比）。这使得编写 SQL 和针对数据库的代码都成为一场噩梦。

整合

正如你所看到的，当我创建表示多个表之间关系的表时，我最好的想法是使用表名，并用下划线分隔。即使在仅支持大小写区分的数据库（或数据库设置）中，我也不希望多词表名和关系表名之间发生混淆；例如，我不想将 `User_Groups` 与 `UserGroups` 混淆。

这些指导原则意味着你可以以有意义的方式规范化数据，确保数据完整性。

此时，有人会抱怨规范化数据库与非规范化数据库的性能，另一些人会加入讨论 GUID 庞大而缓慢。但请思考一下，数据库中的数据可能至关重要，你是否愿意牺牲一点速度来保证数据的完整性，还是愿意走超高性能的路线，并可能导致数据被破坏？如果你真的停下来思考一下，规范化可以通过简单地更改组记录来增强对组中每个用户的更新性能，而无需像在非规范化数据库中那样更改每个用户行。

使用一致的命名意味着我可以节省编写 SQL 的时间，因为我知道事物将如何命名，并且连接将变得容易且始终快速。此外，如果使用编写良好的对象关系映射器，我可能能够利用编程模型中的继承。

所以，作为最后一个例子，这是一个使用国家、州、县和市以及关系表的规范化数据库示例。它可能过度规范化了，但这意味着每个名称只有一个记录，人口、邮政编码等信息可以添加到有意义的关系记录中。然而，这是一个很好的学术例子，但对于商业例子来说不值得；因为 合并的城市-县，像密苏里州圣路易斯这样的城市，它不存在于任何县中，以及华盛顿特区，它不存在于任何州中。然而，它确实考虑了每个州都有一个名为斯普林菲尔德的城市，没有数据重复。

SELECT   
        Country.Name as Country, State.Name AS State, County.Name AS County, City.Name AS City
    FROM   Country
    INNER JOIN Country_State 
        ON Country.ID = Country_State.Country_ID 
    INNER JOIN State 
        ON Country_State.State_ID = State.ID 
    INNER JOIN  State_County 
        ON Country_State.ID = State_County.Country_State_ID 
    INNER JOIN  County 
        ON State_County.County_ID = County.ID 
    INNER JOIN County_City 
        ON State_County.ID = County_City.State_County_ID 
    INNER JOIN City 
        ON County_City.City_ID = City.ID

注意事项

像任何其他数据库设计思想一样，有些事情可能会导致问题。人们依赖自然连接是一个大问题，我已经在上面解决了这个问题。我遇到的唯一真正的问题是尝试将 Entity Framework 与此设计一起使用。虽然我没有尝试过版本 4 与此设计，但版本 3.5 即使在使用 SQL Server 时也存在一个非常严重的问题。EntityFramework 似乎不会拾取外键的关系，除非它们是针对主键的；这个错误迫使我在 `Name` 列上创建了一个聚簇唯一约束，并将我的 `ID` 列设为主键。

下次再说

我还没有涉及几个方面，比如视图或存储过程的命名。我也没怎么涉及 SQL 的编写，我尽量只使用解释我所做选择所必需的 SQL。记得通过投票告诉我你对我的文章的看法。