数据库课程设计测试部分_实用的数据库设计，第1部分

数据库课程设计测试部分

在本系列文章中，我将讨论一些特别有用的常规最佳实践。 其中没有任何内容是特定于任何供应商的产品的，因此，无论使用哪种数据库实现，所有内容都应适用。 在本文的第一部分中，我想讨论简单和复杂的数据类型，以及主 键和外键 ，它们是将整个数据库保持在一起的管道。

主键及相关事项

关系数据库（DB）存储两种信息-数据和管道。 数据包括应用程序使用的客户名称，库存编号，项目描述等。 管道是指DB查找数据库记录并将它们彼此关联所需的主键和外键。

基本水暖

出于数据建模的目的，管道应在很大程度上透明。 实际上，纯粹的DB知识不会区分数据和管道。 但是，您会发现，具有一些其他字段作为数据库密钥对于管理和维护以及运行时性能而言，效率更高。

每个表都必须有一个主键：保证是唯一且不为空的一个属性或属性组合。 引入代理键通常是很有帮助的，它是一个表属性，它没有业务意义，而只是充当表中每个记录的唯一标识符。 这就是我一直在指的管道。

对主键的要求非常严格。 必须 ：

• 存在
• 独一无二
• 随时间不变

代理键有助于缓解实际业务数据永远无法可靠地满足这些要求这一事实。 并非每个人都有一个社会安全号码（想想美国以外的人），人们会更改其姓名和其他重要信息。

商业数据可能也很糟糕-社会保障管理局系统的故障可能导致不同的人拥有相同的社会保障号。 代理密钥有助于将系统与此类问题隔离开。

代理密钥偏爱的第二个原因与效率和易于维护有关，因为您可以为代理密钥选择最有效的数据类型。 此外，代理键通常是单个字段（不是复合键），从而简化了架构（尤其是当该键在其他表中用作外键时）。

每个表都应有一个专用列作为该表的主键。 此列可以称为id或pk （或可能称为<table_name>_id或<table_name>_pk ）。 大多数数据库都针对整数查询进行了优化，因此使用此数据类型作为主键很有意义。 许多数据库，包括Postgres和Oracle，也提供特殊的serial或sequence整数类型，该类型会生成唯一整数序列。 将列声明为这种类型可确保为每个插入的行生成唯一的键。

外键是表属性，其值是另一个表的主键。 例如，通过采用诸如<other_table_name>_fk类的命名约定，显式标记外键列通常<other_table_name>_fk 。 创建表时，应将引用完整性约束（ references ）声明为CREATE语句的一部分。

值得重复的是， 前面讨论的代理密钥只是管道的一部分-它们的存在并没有消除建模要求，即仅能够从业务数据中形成主键。 这样的业务数据候选键是所有属性的子集，这些属性的值永远不会为空，并且值的每种组合都是唯一的。 为了检查正确的数据建模，必须存在这样的候选键，并且应该为每个表记录下来。

严格来说，您可能并不总是在业务数据中找到候选关键字。 想象一下一个表，该表记录了每个用户的名字和姓氏，但没有其他属性。 现在假设有两个不同的人，两个人的名字均为“ Joe”，姓氏为“ Blow”。 在这种情况下，不存在可以形成合适的候选键的表属性的组合。

这里的根本问题是，您是在谈论数据集的唯一性，还是在谈论基础实体（在此示例中为用户）的唯一性。 通常，对于习惯于面向对象分析的开发人员而言，建模基础实体的唯一性通常更为直观。 如前所述，代理键可以帮助实现这一目标。

备用键和可见标识符

作为管道的一部分，代理键永远不需要在数据库外部可见。 特别是，永远不要向用户透露它。 这使数据库管理员可以根据需要随意更改键的表示。 如果需要为用户提供特定数据集的唯一标识符的业务需求，则该标识符应视为真实业务数据，并应与管道分开。 例如，可以引入称为VisibleAccountNumber等的附加列。 当然，此属性应为非null且唯一，以便它形成替代的候选关键字（ 替代关键字 ）。 具有用于可见标识符的单独的列还使得可以以用户友好的方式生成和格式化该属性的值，从而使得例如通过电话容易地将其读取给客户支持人员。

边界情况是标识符不直接可见，但用户仍可访问。 示例包括网页中的隐藏字段，在这些字段中，标识符被传递到客户端以用作以下请求中的参数。 尽管不需要用户处理该标识符，但恶意用户可能会读取并尝试对其进行欺骗。 原则上，直接使用主键的数值，任何攻击者都可以遍及整个表！

针对此问题的防御措施包括加密和解密主键的值，或者通过附加消息验证码（MAC）来保护该键。 另一种选择是为表使用难以欺骗的可见标识符属性，例如记录的主键的哈希或创建时间戳。 （当然，必须确保此属性的唯一性。）

密钥对应用程序（相对于最终用户）是否可见取决于项目的细节。 使用数字类型可直接将密钥的数据库表示形式直接带入应用程序代码中，应避免使用这种类型，以防止耦合。 在小型开发中，可以接受键值的String表示形式（可以存储在DB中的所有数据类型都必须能够序列化）。

但是更好的解决方案是使用简单的包装对象，该对象几乎没有增加复杂性，但是可以将数据库键的表示与其接口进行强大的分离。 使包装器对象过于智能存在危险。 代理键的目的是使它们简单有效地供数据库处理。 从数据库值（可能从String ）进行设置，与另一个键对象进行比较以及可能进行序列化是所有必需的方法。 Smarts （例如根据校验和计算来验证内容的能力）表明，该对象可能属于业务数据域（如之前介绍的可见记录标识符）。

通用唯一标识符的问题

最后要考虑的问题是可能需要通用唯一标识符（UUID）。 简短的答案是关系数据库根本不需要UUID。 实际上，整个UUID概念在某种程度上与关系数据库管理无关。 关系数据库键（管道）仅在每个表中唯一，这可以通过使用自动递增的数据类型（例如前面提到的serial类型）来实现。

UUID可能会有一些技术困难。 为了确保唯一性，所有UUID必须由集中式服务生成-这会导致可伸缩性问题，并且可能成为单点故障。 （可通过分层方法来缓解可伸缩性问题，在该方法中，使用中央主控器将种子分发给多个从属器，从属器又成批生成最终标识符，依此类推。）要在数据库中表示UUID，请使用字符串属性或包含多个整数列的复合键。 两种方法都比基于由长整数组成的键的操作要慢得多。 当用作外键时，复合键还会增加数据库架构的复杂性。

最后，数据库中的记录是否需要具有真正的全局唯一ID是由业务规则而不是数据库体系结构决定的。 一些记录可能已经包含某种形式的UUID（例如，商品通常具有通用产品代码作为条形码）。 可能对应于主要业务实体的某些其他记录可能已经包含唯一标识符作为其业务数据的一部分（例如分类帐条目的时间戳和帐户名的组合）。 如果不是这种情况，则可以为需要UUID的那些记录生成并与业务数据一起存储。 无论如何，应该将UUID视为业务数据的一部分，而不是管道的一部分。

即使（这是个很大的可能 ）选择的对象关系映射方法要求每个业务对象有一个持久的，唯一的ID，也没有必要在此基础上的事实基础关系数据库引擎的内部工作。

总而言之，我主张将业务数据与数据库的内部管道分开。 围绕UUID建立关系数据库通过使用属性（最终将其实际上是业务数据的一部分）作为内部基础结构来打破这一原理。 （关于这个问题，并参与在一个可伸缩的方式生成的UUID的问题的认真讨论一个完全不同的观点，参见Scott Ambler的论文“对象映射到关系数据库，”在相关主题 。）

资料类型

SQL标准定义了许多标准数据类型，大多数数据库供应商都支持一些特定于其自己产品的附加数据类型。 在没有真正令人信服的相反的理由的情况下，为便于携带而避免此类扩展。

字符串和数字

通常，数字类型不会带来什么问题-只需选择足够大的数字类型即可支持必要的值范围。

寻找字符串列的最佳宽度的尝试通常是不值得的。 通过将所有文本消息都设为varchar(n)并将自己限制为一些标准字符串长度并为其引入别名，可以避免很多混乱，例如：32字节（“标签”），256字节（“注意”）和4k（“文本”）。

即使其他业务需求将某些字段的最大长度限制为特定值，也可以说DB模式并不是实施这些规则的最佳位置。 到数据到达数据库时，对它做任何事情都为时已晚（除了拒绝它）。 源于业务规则和要求的个人限制应由业务逻辑层实施，该逻辑处理用户交互和输入验证。 另一方面，如果将数据库模式的维护限于少数几个不同的字符串属性，则可以大大简化数据库模式的维护。

将固定宽度字符串的使用限制为各种代码（与实文本的可变长度字符串相反）。 但是请记住，随着时间的推移，许多看似固定长度的代码实际上确实变得越来越宽。 审慎的数据库设计人员尝试避免在新开发工作中出现与Y2K问题类似的问题。

时间和金钱

记录时间戳（日期/时间组合）的类型始终是必需的，幸运的是，该类型已被SQL标准覆盖。 但是，没有一种完全令人满意的方式来记录货币价值。

保存货币值并将其在程序代码中视为浮点值总是会导致舍入错误。 将值记录为最小货币细分的精确整数（例如美元，欧元和其他适当货币的“分”）也可能不够。 许多值在小数点后的位数要多于实际硬币存在的两位数（只需访问您当地的加油站）。 不过，可以选择5到9位数字的十进制。

毋庸置疑，任何货币价值都应在不记录货币的情况下进行记录-即使您认为您的应用程序只能处理美元之外的其他任何事务。 考虑设置货币表，并使用外键将其与货币值相关联，而不是直接嵌入货币信息。 这有助于国际化（不同的货币名称和符号）以及格式问题。

布尔值和系统类型表

在设计中的任何地方使用bool类型都暗示着重新考虑这个特定模块。 很少有属性真正地仅限于两个值-甚至性别列都有一种恶意的趋势，会趋向（至少）三个状态-男性，女性和未知状态。 允许空值只会掩盖真正的问题。 需要一种更灵活的类型代码方法。

在数据库的许多地方，属性以某种方式确定记录的类型。 上面提到的GenderType就是这样的一个实例。 其他示例可能包括ItemType （例如商品，货运，包装，保险）， PaymentType （现金，支票，MoneyOrder，CreditCard，Barter），以及诸如StoreType ， MembershipType ， DeviceType等等。 这还包括您要在某些适用的对象模型中存储对象类型的实例。

对于每种类型，您都需要某种形式的文档，不仅可以告诉您类型的名称，还可以告诉您与之相关的特性。 例如，您可能想知道每个UserType拥有哪些权限。 与数据库本身相比，在哪个地方保留此信息更好？

任何与某种形式的类型信息相关联的记录都应包含一个类型代码列，该列本身就是引用类型代码表的外键。 外键约束确保不存在无效类型的记录。 类型代码表可能具有以下属性：

• typeCode_pk
• label （唯一的助记符，例如varchar(32) ）
• description （ varchar(256)应该足够）
• uri （在必要时指向其他资源）
• codeGroup_fk

当然，其他属性也是可以想到的，例如三个字母的代码或可见的数字代码。

codeGroup_fk属性用于组织相关的类型代码。 例如，所有订户类型都可以组成一个组。 codeGroup_fk属性是单独代码组表中的外键。 但是，由于意识到代码组不过是类型代码本身，所以可以使该关系递归，以便codeGroup_fk引用typeCode_pk 。 这不仅使附加类型代码表变得不必要，而且还使得可以按任意深度的层次结构对组进行排序。 最好保持代码系统的类型相对简单明了。

复杂的数据类型

最后，几乎每个数据库模式中都存在一些常见但复杂的数据类型，例如电话号码，邮政地址，联系信息和信用卡。 通常，需要从数据库中的许多表访问此类记录。 例如，在典型的电子商务系统中，可能有必要存储用户，供应商，仓库和管理员的联系信息。

而不是在相应的用户，供应商或其他记录中包括这些属性。 （从而在整个数据库中重复这些列），有必要为所有其他表通过外键引用的联系信息建立一个表。 这有两个直接的好处：

• 以后更改关系的基数更容易。
• 它将将来对复杂数据类型的任何更改本地化。

预期每种复杂类型可能需要的属性是一种艺术。 我的建议是尝试从一开始就争取完整性，而不是在每次需要附加字段时都被迫更改架构。

邮寄地址的可能属性示例包括：

• 部门
• 公司
• 邮件停止
• 地址第一行
• 地址行2
• 地址行3
• 市
• 州
• 邮编/ Zip”）
• 国家

完整的联系信息可能包括以下属性：

• 标题
• 名字
• 中间名（或缩写）
• 姓
• 后缀（例如jr。或sr。）
• 家庭地址
• 工作地址
• 家庭电话
• 工作电话
• 手机
• 传真
• 传呼机
• 电子邮件

最后，电话号码绝对不应视为固定电话号码。 实际上，它们分为以下几个字段：

• 国家代码
• 区号（号码计划区）
• ExchangeCode（前缀）
• LineNumber（后缀）
• 延期

在诸如987-1234的电话号码中，前缀是987 ，后缀是1234 。 该分机号是电话号码中唯一可选的部分。 对所有列使用char(4)可能就足够了，但是人们可能认为char(6)是安全的。 请注意，美国的区号限制为三位数字，但其他国家/地区则不是如此。

敏感数据应以加密形式保存。 即使数据库系统本身受到威胁，仍可以防止数据被滥用。 这种数据管理的最著名示例是Unix密码系统，该系统仅存储用户密码的哈希值，而不存储密码本身。 某些数据（例如信用卡号）需要以可恢复的方式进行加密； 但是，单向加密（对于Unix密码文件）将无效。 这导致了加密密钥管理的问题-显然，它不应与机密一起存储在数据库中，而应在引导时提供。

摘要

在本文中，我讨论了设计关系数据库时的一些常规最佳实践，包括：

• 在没有任何业务意义的情况下，保持其他表属性作为代理键的好处。
• 建议不要将数据库的内部工作基于通用唯一标识符。
• 使用集中式类型代码工具来表达具有有限的预定值范围的属性。
• 设计要在整个数据库模式中使用的复杂数据类型的一些注意事项。

在本文的下半部分，我将介绍数据库规范化以及项目中数据库的一些其他用途，例如历史记录表和事件日志的使用。

---

翻译自: https://www.ibm.com/developerworks/web/library/wa-dbdsgn1/index.html

数据库课程设计测试部分