数据库模式设计的最佳实践

引言

在设计应用程序的数据库时，无论应用程序规模大小，正确地构建架构并遵循商定的命名约定都非常重要。这有助于每个人凭直觉理解每个表和字段的架构和用途，即使他们不熟悉数据库的所有方面。第一次就正确设计架构还将减少（或消除）项目生命周期后期对数据访问代码所需的更改。

代码生成

如果使用代码生成器，例如 Iron Speed Designer，它会在创建前端图形用户界面以及底层数据访问和业务层代码的同时，正确设计数据库架构将为您节省大量时间，无论您的应用程序是基于 Web、SharePoint 还是 Windows 窗体应用程序。

Iron Speed Designer 依赖于表之间的关系，以确定如何提供最快速、最高效的体验。我的意思是，在创建绑定数据页面时，通常您会在页面上看到与某些其他数据相关的数据。例如，在创建 SalesOrder 页面时，您可能会想看到相关数据，最明显的是 SalesOrderDetails。不那么明显的是该订单所属的客户以及订单的状态。通过在数据库中链接这些数据，Iron Speed Designer 将知道如何通过向导为您创建页面布局，它将使用信息下拉列表，为您节省大量工作。如果您看到一个您认为是外键项（链接到另一个表）的内容，它显示为一个文本框而不是下拉列表，那么很可能您在数据库中没有设置关系。我们的建议是，在尝试继续使用 Iron Speed Designer 之前，请返回并创建关系。当然，您可以在代码生成器中创建虚拟外键关系，即仅存在于应用程序中的关系；对数据库架构不做任何更改。

设计实践

我们在 Dot Net Architect 遇到并使用的一些有用的设计实践是：

• 为表和字段使用定义明确的名称，例如 SalesOrder，而不是 order。
• 使用单数形式，例如 SalesOrder 而不是 SalesOrders。
• 使用首字母大写命名，例如 SalesOrder 而不是 salesorder。
• 避免使用空格和其他标点符号，例如 SalesOrder 而不是 [sales order] 或 [tblSales order]。这样可以避免始终需要将名称放在 [] 中。
• 使用单个字段作为主键（PK）。即使您可能还有其他字段可以组合起来作为 PK，我们发现最好不要这样做。
• 尽可能使用表名后跟 ID 作为 PK 字段的名称，例如 SalesOrderID。
• 使用位字段存储布尔值（是/否）并以 Is 开头命名，例如 IsEnabled。
• 对于存储类型信息的表，例如查找列表，请在末尾添加 Type，例如 CurrencyType 或 CountryType。
• 添加审计字段，以便您知道谁在何时创建、谁在何时编辑。
• 添加排序字段，以便您可以对项目进行排序，这在字母数字排序不够好时非常有用。
• 使用数据库图来可视化您的架构。这使得创建关系变得轻而易举。
• 使用索引来加速查询，并使用 Profile Analyser 提供建议。
• 使用唯一约束/索引。避免数据库中出现不应有的重复数据。
• 对键字段使用 GUID（唯一标识符）。这有很多原因，可以进行长时间的辩论。我们发现它们有效、全局唯一、比整数更安全，并且可以避免在所有其他 PK 都从 1 开始且递增 1 时出现的错误。
• 添加一个 DisplayText 字段，它是一个计算列，您可以根据需要进行定义。我们将它们指向表中的其他字段，并始终使用此字段作为下拉列表等的显示字段。如果将来需要更改它，只需在 SQL Server 的计算字段中更改公式即可。
• 并非总是可以删除记录，而是使用 IsDeleted 位字段将记录标记为已删除。确保所有数据都按此进行筛选。
• 我们还根据需要添加了几个额外的字段，IsHidden 和 IsEnabled，允许管理员和用户进行筛选，例如，作为管理员，我不想让用户再登录，我将 IsEnabled 设置为 false，如果以后想让他们登录，只需翻转标志即可。IsHidden 可用于过滤下拉列表中出现（但不删除）的项目。
• 添加一个自动增量字段，但不要将其用作 PK。例如，在创建发票时，您需要一个用户可见的编号，这时它会很有用。
• 使用 nvarchar 字段以确保支持所有语言。
• 在需要时使用 varchar(max)，除非真的需要，否则避免使用 ntext 或 image。
• 尝试将任何图像字段（您想存储在数据库中的文档或图像）卸载到单独的数据库或表中，并只保留一个指向它们的指针。这样，您可以减少服务器负载，因为在选择数据时不必总是检索图像数据。您还可以单独备份或图像化数据库。（我们有一个现有客户，其数据库中包含超过 40GB 的数据，其中 35GB 是 PDF 和 Word 文档。如果这些数据在一个单独的数据库中，下载主数据库只需要 10 分钟而不是 10 小时！）
• 仅在需要时使用触发器，并确保您真正理解它们。
• 使用视图，我们经常在只需要显示具有多个 JOIN 的只读数据时使用它们。
• 使用函数来处理重复代码，例如，我们有一套函数可以返回每月的第一天、每季度的第一天、每年的第一天等。
• 将用户权限保持在最低限度，很容易授予用户 dbo 权限，尽量不要这样做！
• 当您有一些需要速度的功能时，将其放在数据库中。将其移出应用程序到存储过程；您可以获得速度但会失去强类型环境。
• 编写您的存储过程，以便它们可以被测试和基准测试。使用 SQL Profile Analyser 来分析速度，并确保您使用多条记录和多个用户进行测试。
• 使用 Red Gate SQL Toolbelt 同步您的更改。