冒险作品解说
我一直在查看 Microsoft 的 Adventure Works 2012 数据库.如果有任何信息解释为什么按原样创建表，我会非常感兴趣.我猜是某种架构概述.
I've been looking at Microsoft's Adventure Works 2012 database. I'd be very interested if there's any information explaining why the tables were created as they are. Some sort of schema overview I guess.
例如:
为什么他们选择创建一个 BusinessEntity 表作为 Person、Employee 等的一种基类.
Why they chose to create a BusinessEntity table as a sort of base class for Person, Employee, etc.
大多数数据都已规范化，所以他们选择将 CountryRegionCode 字段放入 StateProvince 表中，而不是将 ID 放入单独的表中.
Most of the data is normalized so why they chose to put the CountryRegionCode field into the StateProvince table instead of an ID to a separate table.
无论如何，我很想了解更多有关数据库设计中的决策的信息.有谁知道涉及这种事情的资源吗?
Anyway I'm very interested in learning more about the decisions that went into the databases design. Anyone know a resource that goes into this sort of thing?
推荐答案
我不知道 AdventureWorks 的任何官方设计文档，但我曾经是一名培训师并且广泛使用 AdventureWorks 数据库进行演示和实验室，所以我很漂亮熟悉它.
I am not aware of any official design documentation for AdventureWorks, but I use to be a trainer and used AdventureWorks databases extensively for demos and labs, so I am pretty familiar with it.
BusinessEntity 表是 SuperType/SubType 设计的经典案例，它减少了数据冗余，因为客户也可以成为供应商，员工也可以成为客户，以及其他所有组合.此外，这意味着您不会在单独的表中重复存储与所有实体相关的详细信息，从而最大限度地减少代码更改时的工作量.
The BusinessEntity table is a classic case of a SuperType/SubType design, which reduces data redundancy, because customers could also become vendors, employees could become customers, and every other combination. Also, it means that you are not storing details related to all entities repeatedly, in separate tables, minimising effort in the event of code changes.
我对 CountryRegionCode 并不乐观，但我怀疑以下三个原因之一:
The CountryRegionCode I am not positive about, but I would suspect one of three reasons:
- 没有足够的不同组合来保证以报告性能为代价的额外表格(这可以通过一些简单的 COUNT(*) GROUP BY 语句进行验证)
- 他们希望它在同一个表中，以便将来他们可以灵活地使用层次结构对层次结构进行建模(这是最不可能的选项)
- 这是一个标准化错误！(我的钱就在这个选项上！)
相关文章