财务会计应用程序的数据库 I：基本要求

引言

在过去的10多年里，我一直在为立陶宛的会计师开发一个开源的财务会计应用程序 Apskaita5。这曾经是，现在也仍然是我的爱好。它也是我第一个非玩具性质的（相对）大型应用程序。不出所料，我在开发数据库模型和应用程序架构时几乎犯了所有可能的错误。好在我从错误中学到了很多东西。以前，我受到向后兼容性要求的限制，因为这个应用程序正被数百甚至数千家立陶宛公司积极使用。因此，尽管会计师们对功能和可用性感到满意，但当前的应用版本从技术角度来看远非完美。

我打算从头开始重写相同的业务功能，而不考虑向后兼容性。一个很好的起点是开发一个新的数据库模型，它将以最佳的技术方式支持所有的业务功能。在这个系列的文章中，我将描述这样一个数据库模型的结构。尽管这个数据库模型将专门针对立陶宛的财务会计法律背景，但财务会计本身在不同司法管辖区之间非常统一。我相当有信心，针对特定司法管辖区所需的更改将是最小的。

本系列文章旨在防止典型的数据库设计错误——规划不周、缺乏一致性和缺乏文档。对我来说，这是一种思考可能的设计方案、权衡其利弊的方法。这些文章也将作为未来很好的数据库文档。

我也期待社区的反馈和建议

数据库模型的开发正在进行中 :)。非常欢迎任何讨论和提议。

在本文作为系列的第一篇中，我将讨论通用的数据库设计概念，并为整个数据库定义一个非常基本的路线图，例如：定义业务领域、要满足的基本要求、主键使用策略、命名约定。我们还将为可扩展性和基本查找代码设置基础设施。本文不会开始领域实体建模，因为在此之前还有很多决策要做。

数据库领域 – 会计

待开发的数据库模型将用于一个非常特定的目的——财务会计。

财务会计（或财务会计学）是会计学的一个领域，关注与企业相关的财务交易的汇总、分析和报告。（维基百科）财务会计受地方和国际会计准则的管辖。它包括会计师在记录和汇总以及编制财务报表时遵循的标准、惯例和规则。

从程序员的角度来看，财务会计是一套记录和查询公司财务数据的方法。记录财务（交易）数据的部分也称为簿记。查询已记录的财务（交易）数据的部分也称为会计（例如，参见 簿记员和会计师的区别）。

财务会计本质上与另外两种会计类型密切相关：税务会计和管理会计。

税务会计被定义为一种会计方法的结构，专注于税务而非公开财务报表的外观。从程序员的角度来看，税务会计意味着

1. 通过查询数据来准备税务报告；以及
2. 准备税务报告所需的关于财务交易的税务特定数据。

考虑到 (a) 会计应用程序的用户将是会计师，并且 (b) 会计师通常负责准备税务报告，应用程序数据库也应包括税务会计。这意味着在财务会计准则的基础上还需要一些辅助的财务交易数据，以便通过查询数据生成税务报告。否则，我们将无法满足用户的期望，而我们绝对不希望那样。

显然，税收根据定义是特定于司法管辖区的。另一方面——税收是会变化的（在立陶宛，这种情况发生得频繁且剧烈）。应用程序（数据库）模型应该能够承受不可避免的税收变化，因此税务会计的实现应该尽可能地通用。作为一个副作用，这个业务需求也使得为其他司法管辖区调整数据库模型变得相对简单。

管理会计被定义为向管理者提供财务和非财务决策信息。这里的关键部分是“非财务信息”。尽管会计师（会计应用程序）持有对管理会计非常重要的财务信息，但会计师并不持有太多背景信息，作为会计专业人士，她/他也不关心这些信息（例如，设备闲置时间、产能、风险评估、销售细节如确切的销售坐标等）。考虑到会计应用程序的用户将是会计师，会计师们不会乐于看到应用程序要求提供额外的、从会计师角度看非必需的数据。出于这个主要原因，管理会计数据和方法将不会被包含在应用程序（数据库）模型中，除了一个简单的成本中心关联。还有更多理由不将管理会计功能包含在财务会计应用程序中

• 针对多个不同用户类别的应用程序通常对任何一个用户类别都不舒适（例如，管理者对他们不理解也不想理解的会计数据组件感到不舒服，而会计师对“污染”了财务数据的管理数据组件感到不舒服）。
• 有多种管理会计方法。它们的选择相当主观。实现多种方法将极大地复杂化应用程序，并可能损害可用性。
• 管理会计方法可以通过扩展（插件）添加。

管理会计所需的大部分数据存储在各种专业信息系统中（POS、CRM等）。因此，无论如何，有效的管理会计都需要一个使用多个数据源的商业智能（BI）解决方案。

基本要求

对于数据库模型的开发，我选择了使用MySQL和SQLite（使用最广泛、调试最多、支持最好、开源且跨平台）。然而，这对于本系列文章来说并不重要，因为数据库模型将是相当通用的（见下文的简单技术要求）。

应用程序数据库模型的基本要求应为

1. 功能性：所有常见的原始凭证和会计操作都应在应用程序数据库模型中实现；这样，该应用程序可以有效地用于大多数小型和（可能）中型公司，而无需额外扩展。
2. 简单性：简单性要求有三个方面
   • 简单的设计：对于大型公司和中小型公司，不可能有通用的数据库模型。大型公司每年处理数百万次操作，仅因规模就需要特定的处理速度解决方案（大数据）。这样的解决方案对于中小型公司（每年数千次操作）是多余的，甚至会减慢处理速度。我不会去构建另一个Navision，也不会使用与大数据相关的解决方案。我假设每年处理超过10万次操作（财务交易、原始凭证）的公司不会使用这个应用程序。
   • 简单的功能：基本功能不应包括（相对）罕见使用的、行业特定的操作（财务交易、原始凭证、方法、数据字段等）。没有一家公司会利用所有特定功能，但每个会计师都会在应用程序GUI中面对不相关的功能。这会损害ISO 9241-12标准中为信息组织设定的几乎所有原则（清晰性、可辨别性、简洁性、一致性、可检测性、易读性和可理解性）。有人可能会争辩说该标准仅适用于GUI。然而，允许隐藏部分应用程序功能，尤其是在字段或方法级别上的GUI解决方案是复杂的，并且涉及到业务逻辑与视图的混合。这不是一个好主意。尤其是当你能实现插件模式时。如果会计师需要特定功能，他会安装相应的插件，该插件也可能覆盖基础业务实体（例如，开具的发票）并为它们提供GUI。
   • 简单的技术：应用程序应能既作为独立应用运行，也能与某些SQL服务器应用程序一起工作。对于小公司来说，安装SQL服务器应用程序的要求太复杂了。一个具有最少IT技能的会计师应该能够在他的台式机上安装和使用该应用程序。这就引出了基于文件的SQL解决方案，其中SQLite是最受欢迎的。这个要求的缺点是无法利用高级SQL服务器功能（例如，使用CHAR(32)代替GUID (UUID)，有限的数字、日期和其他函数集等）。但这个要求不能被放弃。
3. 可扩展性：应用程序数据库应支持轻松扩展，即外部应用程序扩展（插件）应能添加自己的数据库表，并使用现有的（基础）数据表，而不破坏基础功能。
4. 每个公司（租户）一个数据库：单个会计师（或会计师事务所）为多家公司提供会计服务并不罕见。该应用程序也可以使用SaaS模式提供。应用程序可以使用两种数据库模型来支持这些用例：每个租户一个数据库或单个数据库（也有混合模型，例如，微软的分片模型，但在这种情况下它们太复杂了）。对于单个数据库模型，你会在每个表中添加租户（公司）标识符。对于每个租户一个数据库模型，你将为每个租户（公司）拥有一个单独的数据库（对于支持每个数据库多个模式的RDBMS，则是一个模式）。你可以在这里阅读关于这两种模型的详细讨论：单租户与多租户SAAS；每个客户一个数据库，还是整个SaaS应用一个数据库？；多租户：每个租户一个数据库有什么好处？；为5万多家商店使用一个数据库是个好主意吗？；一个大数据库 vs. 几个小数据库。为该应用程序选择每个租户一个数据库模型的原因是
   • 出于财务会计的目的，公司的会计数据是隔离的。一家公司的会计数据（某个表中的一行）绝不能引用另一家公司的会计数据（某个表中的一行）。这是财务会计领域的要求。现实世界中有两个例外：大型公司集团的合并会计和科学研究（统计、人工智能等）。第一个用例与简单性要求不兼容。第二个仅涉及通常按需提取的统计数据。此外，如果有人要进行涉及多家公司会计数据的研究，他很可能会将数据库放在单个服务器上，而该服务器通常支持跨数据库查询（例如MySQL）。
   • 公司的会计数据是严格保密的。意外地（或出于恶意目的）获取非目标公司数据的机会应降至最低。
   • 单个公司的会计数据（潜在地）量很大。单个公司的某些表可能包含百万行或更多。将多家公司添加到单个数据库将需要处理数百万行，这把我们带到了大数据的领域。这将与上面描述的简单性要求相矛盾。此外，在这种情况下，每个租户一个数据库的模型允许水平和垂直扩展，而单个数据库模型只允许垂直扩展。
   • 有法律（以及实践上）要求能够尽可能大地在会计系统之间移动公司的会计数据（包括将数据传输到目标系统并从源系统删除数据）。例如，一家公司决定更换其会计师事务所，但新事务所将继续使用相同的应用程序。
   • 如果不同公司使用不同的扩展，它们的数据库模式可能会不同。你不会想为一个与保险无关的公司使用保险扩展。因此，每个应用程序实例一个扩展不是一个可接受的解决方案。
5. 乐观并发控制（见维基百科）：应用程序将在由典型用例决定的低并发环境中运行。即使是大型公司，处理财务会计应用程序的员工也很少。每个公司可能不会有超过10个直接的应用程序用户。应用程序也可以有程序化/虚拟用户：通过REST服务与应用程序交互的CRM、计费系统或电子商店。即使每个外部系统只有一个（程序化/虚拟）用户，由于REST服务的异步功能，这种情况下的并发性更高。然而，由于通过REST服务集成导致的更高并发性被此类集成的典型性质所缓解。这种集成极少做除了向会计应用程序提供已开发票数据之外的其他事情，即我们主要谈论的是不会产生任何并发问题的插入操作。
6. 外部身份验证和授权系统：使用每个公司一个数据库模型的原因之一是能够轻松地在应用程序实例之间移动公司数据，例如，从一个会计服务提供商的服务（服务器）移动到另一个。显然，在这种用例中，用户访问策略不是由公司定义，而是由服务提供商定义。因此，安全（身份验证和授权）策略和用户数据不应包含在公司的会计数据库中。这种方法不会在业务数据和安全解决方案之间创建紧密耦合，并且不会对用于访问控制的安全解决方案施加任何限制。
7. 简单的审计追踪：各种审计追踪方法提供不同的记录变更审计细节。一个很好的总结可以在这里找到：审计日志的数据库设计。审计追踪有三个目的：数据调试、回滚功能、疏忽会计和欺诈调查。审计追踪不应与数据版本控制混淆。后者是基于业务需求的特定解决方案。我们将实现某些表的版本控制。然而，这是本系列下一篇文章的主题。关于不同审计追踪解决方案的广泛讨论可以在这里找到：修订的数据库设计，变更日志/审计数据库表的最佳设计，关于捕获审计追踪的数据库设计想法，历史表的优缺点和陷阱——使用触发器、存储过程或在应用程序级别，维护数据库变更日志。在我们的情况下，理论上有四种可能的解决方案
   • 列变更日志（已拒绝） – 变更按列记录在单个表中，列（大致）为：table_name, field, user, new_value, deleted (布尔值), timestamp。这是可用的最详细的审计追踪解决方案，也正因如此，它完全是多余的。如果有人需要记录所有SQL事务细节，他/她只需打开SQL服务器日志记录，例如MySQL的二进制日志。因此，我们绝对不会实现这个解决方案。
   • 历史表（已拒绝） – 为所有业务表创建镜像表，所有待修改的行都移动到相应的历史镜像表中。这个解决方案与SQL服务器原生日志记录的唯一区别是能够更灵活地查询历史数据。这个解决方案的代价是双倍的表数量和由于外键关系导致的高复杂性。这个解决方案的例子只包括一些简单的情况，比如某个表（通常是常见的员工数据）没有任何外键关系。为了管理具有广泛外键关系的现实世界情况（例如，发票和供应商），人们要么需要使用高度非规范化的历史表，要么需要实现高度复杂的逻辑来进行外键索引转换，以便历史行的外键索引指向该行的历史版本而不是原始引用的行。因此，这个解决方案在现实世界场景中的复杂性非常高。然而，与原生SQL服务器日志记录相比，这个解决方案的好处并不大。因此，如果没有硬性的业务（或法律）要求来实现这个特定的解决方案——不要这样做。
   • 文档变更日志（作为可选功能考虑中） – 应用程序（而非数据库）在插入、更新或删除文档（再次强调——是应用程序定义的文档、父业务对象，而不是数据库实体）之前，添加一个日志条目：文档类型（可能是业务对象类型的全名，例如 Apskaita5.BusinessObjects.InvoiceMade）、文档ID、操作类型、序列化的文档（JSON或XML）、时间戳和用户。这个解决方案背后的基本原理是，数据库事务通常发生在由应用程序（在数据库之外）定义的图中，因此，记录图的变更是有意义的。这个解决方案的另一个好处是历史版本以应用程序的原生格式写入，这使得通过简单的反序列化在应用程序中查看历史文档变得微不足道，并因此可以通过插入一个具有相同业务字段的新文档来恢复已删除的文档。这个解决方案的缺点是无法查询历史文档字段。另一方面——为什么有人需要这样做呢？在数据库中，实现这个模型只需要创建一个如上所述的日志条目表（也许是两个表，以便将相对较大的序列化字符串移动到另一个表以提高性能）。在应用程序中，实现这个模型需要所有业务对象（类型）都具备序列化和反序列化自身以及在保存时添加日志条目的通用功能，这可以通过为所有业务对象使用某个超类来完成。实际上，实现它并不需要太多努力，因为序列化功能无论如何都是因为其他原因所必需的。目前，我将跳过这个功能，但会留作进一步考虑。
   • 简单审计追踪（已接受） – 只需在每个存储父实体的表中添加标准字段：inserted_at、inserted_by、updated_at、updated_by。我们不需要向子实体添加这些字段，因为它们始终是父实体的组成部分（当然，如果需要更多细节，子实体也可以用同样的方式进行跟踪）。这是一个简单、广泛使用的解决方案，既不需要太多工作，也不会对性能产生任何可观的影响。此外，(a) 我们无论如何都需要 updated_at 字段用于乐观并发控制，(b) 在多用户环境中，会计师喜欢看到是谁在何时插入和更新了某个文档。因此，我们将在应用程序数据库中实现这个模型。

业务逻辑

有一个永恒的宗教性问题：“数据库应该实现多少业务逻辑？”（相关讨论见：业务逻辑：数据库 vs 代码，数据库应该实现多少业务逻辑？）像任何宗教性问题一样，它没有确切的答案。当然，让数据库拒绝无效数据作为最后一道防线是很有诱惑力的。“最后一道防线”——因为实现可理解和可地区化的异常消息几乎是不可能的。然而，这不可避免地导致了数据库和应用程序层面的业务逻辑重复。

我处理这个问题的方法很简单——应用程序专门负责业务规则，而数据库专门负责数据一致性（引用完整性）。如果一个应用程序（业务实体）能够一致地获取其数据（即没有空引用异常和数据丢失），它们也能够修复任何业务错误。这可以（也应该）实现为一个审计方法或一个cron作业，并应通知会计师任何（潜在的）无效数据。另一方面，数据库专门负责数据一致性（引用完整性）。因此，它应该保证实体图永远不会被破坏，并且整个图总是可以加载到应用程序业务层，即没有孤立的记录，没有不完整的实体图。这可以通过使用外键和适当的 ON DELETE 约束来完成。

由于简单性要求，我们已经排除了触发器和存储过程。因此，我们不必处理这些业务逻辑的“容器”。然而，我们还需要小心字段类型。字段类型不应具有限制性，除非限制背后的业务逻辑像大陆一样稳固。例如，人们通常会期望税率没有小数部分，但税法制定者有相当丰富的想象力。因此，总是优先选择更宽泛的类型；只有当几个世纪以来都是如此时才使用 UNSIGNED 约束。

以货币为例。大多数国家有两位小数，但有些国家没有小数位（如日元），有些国家有三位小数（如阿尔及利亚、巴林、伊拉克、约旦、科威特、利比亚和突尼斯第纳尔）。除非你的应用程序目标是阿拉伯国家，否则在本位币金额上使用两位小数是正常的设计决定。据我所知，所有应用程序都是这样做的。然而，处理不同货币金额的应用程序字段应使用三位小数。这个寓言的教训是——永远要对字段类型的约束持怀疑态度。花些时间在谷歌上搜索，总比某个公司收到巴林公司的发票时发生事故要好。

尽可能少地使用业务逻辑的设计决策绝不意味着你应该忽略可能的数据错误。相反，这类情况应与数据库模式一起记录下来，以便业务层开发人员能够实现一个审计例程（方法、cron作业等），从而通知会计师潜在的差异。

计算值

有时你可能会想去掉那些可以用其他字段计算出来的值（字段），例如，可以通过单价乘以数量计算出的总金额。不要这样做。数据库的舍入算法不能保证与应用程序中的相同。由于舍入的不确定性，在数据库和应用程序端进行计算时可能会得到不同的值。这很糟糕，因为会计师是非常严谨的人，关心每一分钱。如果会计师发现发票本身的总金额与发票登记簿中的同一金额不同，他会非常不高兴。实际上，这是一个经验法则——如果计算涉及乘法或除法，并且可能在单个应用程序边界之外执行，切勿使用（依赖）计算出的小数值（十进制或浮点数）。

主键

主键是数据库表中一个特殊的列（或列的组合），用于唯一标识所有表记录。主键的主要特征是：(a) 它必须为每行数据包含一个唯一值；(b) 它不能包含空值。（Technopedia.com 定义）主键的主要功能目的是通过外键关联不同表的行（并快速完成）。没有主键，数据库就不再是关系型的（至少在某种程度上是这样）。为了保持我们的数据库表之间的关联（例如，关联发票及其明细行），我们需要在数据库的每个表中添加一个主键。

主索引有两种类型——自然索引和代理索引。自然键是表中已经存在的一列或一组列（例如，它们是数据模型中实体的属性），并且唯一标识表中的一行。由于这些列是实体的属性，它们显然具有业务含义。例如，社会安全号码可能是员工的自然主键。代理键是系统生成的值（可以是GUID、序列等），没有业务含义，用于唯一标识表中的一条记录。例如，

user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

如果你调查任意数量的SQL专业人士并问他们，“在定义主键时，使用代理键还是自然键更好？”，我敢打赌答案会非常接近50/50。以下是一些或多或少有建设性的讨论，以更深入地了解这个主题

• SQL Server中代理键与自然键的区别及何时使用
• 代理键 vs. 自然键
• 选择主键：自然键还是代理键？
• 代理键 vs. 自然/业务键

在很大程度上，关于自然键与代理键的辩论带有宗教性质。尽管如此，我们仍然需要为在我们的数据库模型中选择主键定义一个通用策略，而这些讨论让我们能够确定一些指导方针。关键因素是对主键值唯一性的绝对要求。在开发和支持财务会计应用程序的10多年里，我确定了一件事——行中几乎没有什么东西是唯一的（充其量是复杂和模糊的），例如

• 对于发票项目（行），有两条完全相同的行是完全可以的：相同的发票、相同的商品/服务、相同的数量、相同的价格等。发票是由我们无法控制的经理制作的。会计（应用程序）别无选择，只能按照经理创建的方式登记发票。
• 没有办法以非模糊的方式识别人，更不用说处理可能重复的业务规则的复杂性了：并非所有国家都为其居民（公司和自然人）提供唯一标识符。例如，在英国只有某些类型的公司有注册号。并非所有的人都是增值税纳税人。即使是同一国家的公司，同名也不意味着是同一个人，反之亦然。会计（应用程序）能做的最好的事情就是有根据的猜测。

如果一个实体的自然键在自然界中不存在，我们别无选择，只能引入代理键。这将适用于我们数据库模型中几乎所有的表。但仍有少数情况下存在自然键

• 查找表：自然键是会计中使用的各种代码：国家、货币、税收。它们是主键的绝佳候选，因为它们是唯一的、简短的（2-5个字符），并且只用于防止录入错误/拼写错误（参见下文对查找表的完整解释）。会计师（应用程序的主要用户）熟悉这些代码，因此对于大多数报告，除了代码本身之外不需要任何细节。这同样适用于业务对象，例如，发票持有货币代码就足够了，人类可读的货币名称可以由GUI中的查找控件提供。使用这些代码作为主键不会影响性能，并能避免不必要的连接。因此，我将使用这些代码作为主键。
• 会计科目ID：在过去的一千年里，公司会计科目表中的每个会计科目都需要一个唯一的数字代码。这使得科目ID成为主键的绝佳候选，因为它们是唯一的、简短的，并且是原生类型（BIGINT）。会计师（应用程序的主要用户）非常熟悉科目ID，因此对于大多数报告，除了ID本身之外不需要任何细节。这同样适用于业务对象，例如，发票持有应付账款科目ID就足够了，科目名称可以由GUI中的查找控件提供。使用这些ID作为主键不会影响性能，并能避免不必要的连接。实际上，在这种情况下，它能防止许多连接，因为业务对象可以引用多个科目，例如，每个发票行都引用某个成本科目和某个增值税科目。因此，我将使用科目ID作为主键。
• 应用程序的扩展ID：应用程序扩展是由第三方创建的（至少其中一些是）。然而，应用程序应该能够解析其扩展。因此，所有扩展以及由扩展实现的操作类型都应有唯一的标识符。可以由第三方安全生成的标准唯一标识符是GUID。在我们的情况下，这些扩展的GUID是自然键。每个操作（原始凭证和其他）表都应有一个扩展类型标识符。当应用程序用户请求检索某个操作时，应用程序应能够解析该操作所代表的业务实体类型，即，每次检索业务实体都需要操作的基础类型和扩展类型标识符。这使我们得出与前几种情况相同的结论——使用扩展操作类型标识符作为主键。尽管它们比查找或科目ID值大（32个字符而不是2-5个字符），但它们很少用于连接，并且经常需要自身。这种特定的用例通过大的键值有效地减轻了负面的性能影响。

如果我们因缺少自然键而选择使用代理键，我们仍然需要选择键的类型，可以是整数或GUID (UUID)。这里，我们面临另一个宗教性的争论

• 使用UUID还是不使用UUID？
• 标识与唯一标识符
• 主键：ID 与 GUID
• UUID还是GUID作为主键？
• MySQL InnoDB主键选择：GUID/UUID vs 整数插入性能
• MySQL中的UUID性能？
• 如果我在SQLite中使用blob字段作为主键，会遇到性能问题吗？

我从阅读的讨论中学到——GUID作为主键确实会降低性能（包括插入和连接）。降低多少以及是否可以通过特定的服务器配置在一定程度上缓解，这是有争议的，但它确实会降低性能。因此，只有在对应用程序用例至少有一些好处时，才应使用GUID作为主键。我们设计的应用程序不是分布式的，并且合理地不需要数据复制（低并发性，每个公司一个数据库，加上中小型公司）。这些是选择使用GUID作为主键的唯一好理由。因此，我不会在任何表中使用GUID作为主键（除了前面讨论的扩展操作类型表）。

另一方面，有一种情况下有一个额外的GUI列是有用的：当有（可能有）外部系统导入应用程序数据时。在这种情况下，外部系统只能通过应用程序提供的ID来解析已经导入的实体（文档、交易等）。如果实体ID是自增整数，可能会有某些情况下应用程序需要更改它们，这反过来又会破坏外部系统。幸运的是，这种情况极为罕见

• 第一种情况是公司数据库的合并。例如，如果我们要构建一个CRM应用程序，这很可能是一种场景，因为合并意味着接续公司将管理所有被合并公司的客户。然而，这不适用于财务会计，因为合并绝不意味着接续公司成为先前交易的所有者。接续公司只会继承被合并公司的余额。因此，由于公司合并，永远不会发生数据库合并。
• 第二种情况是应用程序本身的大版本升级。例如，我的应用程序Apskaita5就会发生这种情况，因为当前版本没有一个通用的原始凭证数据表（根据文档类型有很多表），而我们设计的应用程序将会有一个。尽管看起来不太可能，但这可能再次发生。另一方面，在10多年里，我没有遇到过需要将会计应用程序数据传输到某个外部系统的集成用例（除了不受此问题影响的服务/商品目录，因为有集成代码）。目前，我不会向表中添加GUID列，但会留作进一步考虑。

CHAR vs. VARCHAR

用于文本字段的数据类型实际上取决于所使用的具体RDBMS。对于SQLite，完全没有区别，因为 CHAR 和 VARCHAR 都具有 TEXT 亲和性。对于MySQL，存在一些对性能有影响的细微差异。然而，性能影响是基于权衡的，并且似乎不是非常显著（相关讨论见MySQL VARCHAR大小的性能影响，MySQL性能 - CHAR(64) vs VARCHAR(64)，在SQL中选择CHAR而非VARCHAR的用例是什么？，优化模式和数据类型）。因此，使用最能描述数据本身的类型是合理的

• 在处理本质上是固定长度的字符串时使用 CHAR，例如语言、国家代码、GUID等。
• 在处理可变/任意长度的字符串时使用 VARCHAR，例如名称、描述、注释等。

在处理 CHAR 类型字符串和MySQL服务器时，设置适当的字符集也很重要。例如，语言、国家代码和GUID只使用ASCII字符；因此使用UTF8字符集将是多余的，并会降低JOIN性能。同样，排序规则也很重要。虽然你可以对国家/货币/语言代码使用ci（不区分大小写）排序规则，但你应该对GUID使用bin（二进制）排序规则。

值得一提的是，MySQL的排序规则也适用于 ENUM 字段。不要对它们使用二进制排序规则，因为你会遇到大小写问题，应该使用ci（不区分大小写）排序规则。

在处理 VARCHAR 类型的字符串时，应以平衡的方式设置其最大长度。一方面，某些文本字段的最大长度应对会计师来说足够。另一方面，过长的字段即使实际字符串比最大允许长度短得多，也会增加内存使用。我检查了一个真实会计数据库中最常见文本字段的平均长度，发现了以下值

• 平均凭证号长度为6.22个字符，最大凭证号长度为27个字符；
• 平均凭证描述长度为37.54个字符，最大凭证描述长度为231个字符；
• 平均人名长度为21.19个字符，最大人名长度为82个字符；
• 平均非结构化地址长度为37.35个字符，最大非结构化地址长度为196个字符。

同样值得注意的是，一个普通的文本页面大约包含4000个字符（包括空格）。

实际上，在支持会计应用程序的10多年里，我只收到了几次关于字段长度的请求。而且除了一个特定的字段，我对所有字段都使用了255的最大长度。 

我相信，实际的统计数据会因地区/文化而异。然而，以下最大长度应足以满足需求，而不会对性能产生重大影响

• 凭证号 – 50个字符；
• 凭证描述 – 500个字符；
• 凭证备注 – 4,000个字符；
• 人名 – 255个字符；
• 非结构化地址 – 500个字符；
• 用于在查找中显示的各种名称 – 100个字符。

百分比

在数学中，百分比表示为分数。这同样适用于常见的数字格式化（例如 .NET decimal 到字符串格式 "P"）。将百分比存储为分数也简化了计算——你不需要将一个值除以100。因此，对于给定精度的百分比，首选的数据库类型是 DECIMAL(精度 + 3, 精度 + 2)，例如，如果要求的精度是零位小数（不推荐），数据库类型定义是 DECIMAL(3, 2)；如果要求的精度是两位小数（不推荐），数据库类型定义是 DECIMAL(5, 4)。

命名约定

数据库模型要求对象被命名。有各种命名约定的标准和关于哪个更好的永恒辩论。（见：数据库建模中的命名约定，数据库、表和列的命名约定？， 9个最常见的数据库设计错误）。然而，最终，一切都关乎清晰度（任何具有足够业务领域知识的人都应该理解表和字段名）和一致性（相同的概念应该以相同的方式命名）。其余的只是个人偏好的问题。

表名命名约定

• 最多28个字符 – 因为MySQL不支持更多（另请参见表和索引名称长度限制）
• 表应该以它所代表的实体来命名
• 在适用情况下，使用财务或税务会计术语——它们简短且其含义在应用程序领域内有明确定义，例如 accounts, bank_operations 等。
• 当财务和税务术语不适用时，使用通用应用程序中常见的术语，例如 company_profile, person_versions 等。
• 不要在表名中使用空格
• 不要使用缩写
• 使用存储在表中的业务实体的复数形式，例如 accounts, documents 等。
• 使用全小写的表名，单词之间用下划线 "_" 分隔 – 这对眼睛很方便，不需要太多额外的符号，并避免了大小写敏感性错误，例如 bank_accounts
• 不要使用任何前缀或后缀——应用程序不会有任何具有相似表的独立模块，而前缀在这些模块中可能是有益的（目前——相信我 :)）

字段名命名约定

• 最大长度 = 64个字符 – [表名长度] - 5个字符（参见索引名长度限制）
• 在适用情况下，使用财务或税务会计术语——它们简短且其含义在应用程序领域内有明确定义，例如 social_security_no 等。
• 当财务和税务术语不适用时，使用通用应用程序中常见的术语，例如 person_name, description 等。
• 不要在字段名中使用空格。
• 不要使用缩写。
• 使用全小写字段名，单词之间用下划线 "_" 分隔——这对眼睛很方便，不需要太多额外的符号，并避免了大小写敏感性错误，例如 official_code。
• 不要使用表名作为前缀，除非自然的字段名是保留字，例如 document_date 而不是 date。
• 对于标准的代理主键，使用名称 id (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)。
• 对于外键，使用被引用表的单数形式加上后缀 id，例如，对于表 persons，使用 person_id；当有多个字段引用同一个表时，（也为了清晰起见）外键字段名可以（可以）以某个业务名称为前缀，例如 bank_fee_account_id。在某些情况下，自然名称不遵循此约定，但包括被引用的表名（或者其含义在会计领域中是显而易见的），例如 accounts_payable_id。
• 对于布尔字段，使用前缀 is，例如 is_client。
• 对于存储用于快速填充功能的默认值的字段，使用前缀 default，例如 default_accounts_payable_id。
• 对于存储由某个外部系统（REST客户端等）分配的实体ID的字段，使用名称 external_id。
• 对于用于标记实体数据为已归档（过时，不再活跃使用）的字段，使用字段名 is_archived。
• 对于应用程序定义的枚举，使用后缀 type，例如 document_type。同时，使用 SMALLINT 类型而不是 ENUM，除非枚举的主题像大陆一样稳定（例如，会计中的借方和贷方）或者枚举定义了数据存储元数据，即仅作为技术字段来支持存储方法（例如，用于分层数据存储）。是否为此类枚举使用技术查找表正在考虑中：一方面，它有助于数据完整性；另一方面，这意味着每种类型（应用程序定义的枚举）都要增加一个额外的表，而这个表并没有用处。
• 对于审计追踪，使用以下字段名：inserted_at、inserted_by、updated_at、updated_by。

索引名命名约定

• 最大64个字符 – 因为MySQL不支持更多
• 外键索引的名称结构：[table_name]_[field_name]_fk
• 其他索引的名称结构：[table_name]_[field_name]_idx
• 不要在索引名中使用空格。

可扩展性基础设施

应用程序数据库的一个关键要求是能够由任何第三方开发人员进行扩展。当与每个租户（公司）一个数据库的要求相结合时，这意味着公司数据库应该持有关于所使用（已安装）扩展的（元）数据。技术上，某个外部数据库（解决方案）也可以做到这一点。然而，这将意味着在将公司数据库移动到另一个应用程序实例后，已安装的扩展数据将不会被移动到目标应用程序实例。因此，我们必须将扩展数据精确地保留在公司数据库中。

为了理解可扩展性数据需求，我们需要理解可扩展性的范围、方法和用例。主要的功能扩展原则是经典的SOLID，因为它也适用于数据库世界

1. 每个基础事实都应添加到基础表中。单一职责原则认为，每个类都应有一个单一的职责，并且应完全封装该职责。在数据库世界中，这基本上意味着数据规范化——每个关系必须包含一个超键以及功能上和直接依赖于该超键的值，即，如果一个扩展在其自己的表中持有某些基础事实字段，它就破坏了基础事实表的规范化。与此要求密切相关的是里氏替换原则。里氏替换原则认为，如果S是T的子类型，那么类型T的对象可以用类型S的对象替换（即，类型T的对象可以用其任何子类型S的对象替换），而不会改变程序的任何期望属性（正确性、执行的任务等）。也就是说，如果一个扩展对某个基础事实进行子类型化，但没有提供完整的基础事实描述，它就破坏了应用程序拥有基础事实统一视图的能力。简单来说，一个基础银行操作表包含构成基础银行操作的所有值，并允许在单个视图中查看所有这些值，即使特定的操作是由不同的源文档创建的。如果一个扩展处理某些特定类型的银行操作，但未能将其基础属性持久化到基础银行操作表中，它 (a) 违反了第三范式；(b) 违反了里氏替换原则并造成了数据异常——从基础视图中缺失的银行操作（简单地未包含在银行账户余额中）。由于简单的技术要求，我们不打算使用表继承，因此，当扩展继承表时没有特定问题。（参见构建SOLID数据库：单一职责和规范化，构建SOLID数据库：里氏替换的怪异之处）
2. 不允许修改基础表。开闭原则认为，实体（在这种情况下是数据库表）应该对扩展开放，但对修改关闭。从严格的角度来看，这意味着扩展不能修改任何基础表（添加或删除任何列）。在这种情况下，我们处理的是由多个独立开发者在不同时间开发的第三方扩展，这绝对意味着重叠的部署周期。因此——采取严格的观点（参见构建SOLID数据库：开闭原则）。否则会发生什么的一个实际例子是：扩展A向一个基础表添加了某个列，扩展B也向同一个基础表添加了某个列；两个扩展自然都需要它们拥有的列中的值，但由A扩展插入的行将没有B扩展列的值，反之亦然；因此——扩展会相互破坏。
3. 不允许使用用户定义函数。仅仅是因为简单的技术要求，例如SQLite不支持用户定义函数。因此，接口隔离原则和依赖倒置原则不会有问题。（参见构建SOLID数据库：接口隔离，或保持存储过程简单，构建SOLID数据库：依赖倒置和健壮的数据库接口）

考虑到所描述的功能扩展原则，应用程序扩展有以下几种不同的方法

1. 通过子类化数据进行扩展。一个扩展使用基础类型创建一个自定义业务实体类型，但带有更多扩展特定的数据。例如，一个自定义的银行操作数据仍将包含所有基础银行操作数据，但还会包含一些额外的自定义数据（字段）。为此，该扩展将需要一个自定义表，该表将包含自定义银行操作字段，并引用它所扩展的基础银行操作表中的一行。
2. 通过子类化方法进行扩展。一个扩展创建一个自定义业务实体类型，仅使用基础类型的数据（字段），但对这些字段应用不同的方法。例如，一个自定义的商品操作，以基础应用程序未实现的方式改变基础商品（余额）的状态。为此，（方法上）可扩展的基础操作表将包含一个用于扩展操作类型（GUID）的列，该列将被自定义操作类型用于指示需要使用自定义方法来编辑该操作。
3. 通过组合进行扩展。一个扩展包含一个自定义业务实体类型，该类型包含几个系统地安排和管理的基础类型。例如，一个（虚构的）现金操作，（内部）包含一个银行操作和一个现金基金操作，以指定从银行账户提取一些现金到现金基金。为此，该扩展要么使用基础文档基础设施，即子类化一个按设计可组合的基础源文档（通过子类化进行扩展）（参见后续文章的描述），要么添加（实现）自定义容器表（可能带有关联表），这些表将管理相关基础表行的引用。正如我们将在未来的文章中看到的，第一种方法将是一种常见的扩展方式，而第二种方法将受到与特定表（实体）相关的许多限制。
4. 独立扩展。一个扩展包含一个自定义业务实体类型，该类型不包含由基础功能表处理的数据。例如，一个自定义查找值，仅由某个其他扩展实体使用。为此，该扩展将需要一个自定义表，该表将包含自定义实体数据。

不涉及通过子类化方法进行扩展的扩展方法，对基础数据的完整性没有危险：人们可以删除扩展表（或不使用它们），只会丢失额外的数据；基础数据将保持完整。然而，通过子类化方法进行扩展（包括其在组合扩展中的使用）对基础数据的完整性构成危险：如果有人失去了对扩展的访问权限，他将不再能访问编辑扩展操作所需的特定方法，从而有效地使其变为只读。为了（至少在一定程度上）缓解这个问题，自定义操作应具有回退到某个基础操作类型的能力。这（在当前开发阶段）意味着我们还应该

• 拥有每个自定义操作类型的回退基础类型的数据；以及
• 保留已卸载扩展的数据，以了解（可能的）数据伪影的（可能）来源

总而言之，我们首先需要关于已安装扩展的以下基本信息

• 扩展的GUID，由扩展开发者分配（生成）
• 扩展的名称（简短描述）——用于（显而易见的）数据调试目的
• 扩展的版本——以免无意中降级扩展
• 扩展安装的时间戳
• 安装扩展的用户
• 扩展最后一次升级的时间戳
• 最后一次升级扩展的用户
• 扩展卸载的时间戳（可为空）
• 卸载扩展的用户（可为空）

一种可能的用例是，一个扩展可以被卸载，然后稍后重新安装。先前安装的信息对于数据调试和审计日志仍然有价值。因此，我们不能重用（覆盖）关于该扩展的现有条目，并保持GUID在整个表中是唯一的。为了允许这种用例，应添加一个代理键。

关于扩展安装、升级和卸载时间戳以及相应用户的信息将仅用于数据调试目的，也用于一致地实现简单的审计日志记录要求（除了卸载数据是扩展功能特有的）。

接下来，我们需要关于每个扩展操作类型的以下基本信息

• 扩展类型的GUID，由扩展开发者分配（生成）
• 扩展操作类型所属的扩展ID（外键）
• 扩展操作类型的名称（简短描述）——用于（显而易见的）数据调试目的
• 操作的回退基础类型

尽管每个可扩展操作的所有表都将相同，但屈服于诱惑，使用一个带有额外列“操作类型”的表将是非常不明智的。扩展操作类型的GUID将用作操作表中的外键。如果使用带有行类型的单个表方法，一个bug可能会导致分配了不适当的操作类型。这反过来会使数据不一致，并且手动修复可能会非常麻烦。

使用先前定义的命名约定（name – 保留字；GUID – 目前不是MySQL的保留字，但很可能成为一个）得到的数据库模式

有了这个模式之后

尽管我们必须为卸载的扩展删除扩展的操作类型（否则它们可能会被分配给一个可扩展的操作行），但可以考虑保留原始操作类型的一些历史痕迹，并在重新安装扩展时使用它们进行恢复（“撤销回退”）。例如，在一个可扩展的操作表中添加两个GUID字段而不是一个。我认为这是一个有争议的想法。存在一个相当大的风险，即相关操作可能会被编辑为它们回退后成为的基础类型的操作。更不用说同样的操作也可能被数据扩展了（无法撤销，因为扩展表已被删除）。在这种编辑发生后，重新安装时进行类型恢复（“撤销回退”）将破坏该扩展。因此，尽管试图做好事，结果可能会更糟。另一方面，在尝试撤销回退之前，我们可以检查操作的 updated_at 字段与扩展的卸载日期，并让扩展来做出最终决定是否可以进行回退撤销。目前，我将把这个可能性留作进一步考虑。

• 扩展的安装将通过以下方式完成
  1. 将所需的表添加到聚合数据库模式中（如果适用）
  2. 插入扩展的基础数据
  3. 插入扩展操作数据（如果适用）
• 数据库完整性检查将通过以下方式完成

1. 获取数据库中所有已安装扩展的列表
2. 创建所需的聚合数据库模式（基础表加上已安装扩展所需的表）
3. 检查实际数据库结构与所需的聚合模式
4. 检查过时的扩展

• 扩展的升级将通过以下方式完成
  1. 更新扩展基础数据
  2. 更新扩展操作数据，但有以下限制
     1. 类型的id不能更改，只能更改名称和回退类型
     2. 操作不能被移除，即扩展不能放弃它们自己的操作类型
  3. 更新扩展数据库表
• 扩展的卸载将通过以下方式完成
  1. 更新由扩展创建的所有可扩展操作，通过
     1. 将其基础类型从‘自定义’修改为扩展指定的回退类型
     2. 将扩展类型id设置为 null
  2. 删除该扩展的所有扩展操作类型（因为它们不应再在可扩展操作表中使用）
  3. 将基础扩展数据标记为已删除，即设置 uninstalled_at 和 uninstalled_by 的值
  4. 删除由扩展创建的表

查找列表

在继续建模领域（会计）实体之前，最后要考虑的是查找。有关于国家和货币代码的国际标准

• ISO 3166 标准，定义了国家代码（例如，GB代表英国）；以及
• ISO 4217 标准，定义了货币代码（例如，EUR代表欧元）。

当然，这些代码在会计数据中是必需的。可能会有不为这些代码设置查找表并允许直接输入的诱惑。然而，我们必须抵制这种诱惑。手动输入容易出错——要么是拼写错误，要么是（更可能的）查找控件选择错误。在我们的情况下，这种输入错误会产生非常不愉快的后果。有一次，一位会计师犯了一个“鼠标错误”，将供应商的国家代码选成了AG（安提瓜和巴布达），而不是AR（阿根廷）。这个错误在税务报告中未被发现。税务机关的逃税监控软件发出了红色警报，因为安提瓜和巴布达是一个离岸地区。这导致了税务机关对该公司的检查，这是不愉快的。教训是——永远不要给会计师提供选择一个公司没有任何关系国家的可能性。这同样适用于货币代码。

解决方案很简单——只需为这些代码添加查找表，这将同时提供数据完整性约束和一个方便（简短）的查找列表供会计师选择。虽然“鼠标错误”仍然可能发生，但错误选择一个公司本来就有业务往来的国家，其后果远没有错误选择一个公司毫无关系的国家严重。

我们将为每种代码类型添加单独的表，因为它们将用作外键，我们不希望货币代码出现在国家代码的位置。

如上文（主键部分）所述，我们将使用国家和货币代码作为主键。我们不会指定国家或货币名称，因为它们受应用程序地区化的影响。我们将添加（标准的）字段 is_archived，通过从查找列表中隐藏已归档的代码来进一步降低错误风险。这些代码是成为顶级（父）实体的良好候选。因此，我们还将添加标准的审计追踪字段。如果将来我们决定将它们作为公司资料的子项，移除它们也不会有太大麻烦。

使用先前定义的命名约定得到的数据库模式

结论

在本文中，我为未来的数据库设计定义了一个基本策略。一些要点留待将来考虑。一旦我下定决心（希望在你们的帮助下），我将更新本文。在做出了这些基石性的决定之后，我们现在准备好继续进行会计领域实体的建模。

下一篇文章将专门讨论会计基础设施的设计：总账、会计科目表、凭证和财务报表。

历史

• 2019年7月2日：初始版本
• 2019年7月21日：更改命名约定为更开发者友好
• 2019年7月28日： 添加 外键索引的命名约定 以区别于其他索引
• 2019年8月9日：在适当的地方切换到 VARCHAR，增加了关于在 CHAR 和 VARCHAR 之间选择的新段落
• 2019年9月16日： 增加了 关于业务逻辑、计算值和百分比存储的章节