类似于多态的SQL数据库表结构
我有一些表具有不同的结构，但是包含相同的数据(在示例中，它是名称和姓氏).
I have some tables that have different structures, but contains the same data (in the example it is name and surname).
演示结构小提琴- http://sqlfiddle.com/#!2/cce568
但是，现在我需要创建一些集合表，该表将存储此信息以及更多详细信息(例如，日期时间，公司名称，职位等).根据其余字段的上下文，该集合表可以包含多个实体.
However now I need to create some collective table which will store this information + some more details (eg datetime, company name, position etc). This collective table can contain some entity more than once according to the context of the remaining fields.
请问有什么模式可以存储这个集合表吗?根据Bill Carwin的帖子( https://stackoverflow.com/a/562030/1092627 )，我可以加入所有这些表合二为一，但是如果我需要直接向该表中添加一些信息，该怎么办?
Is there some pattern how to store this collective table, please? According to Bill Carwin's post (https://stackoverflow.com/a/562030/1092627) I can join all those tables into one, but what should I do if I need to add some information directly into this table?
预先感谢您的意见.
Craig Larman的书使用模式应用UML"描述了解决此问题的3种常见解决方案.
Craig Larman's book "Applying UML with Patterns" describes the 3 common solutions to this problem.
您的示例并不是特别有用-在逻辑上没有理由使用3种不同的方式来管理数据库中的人名(尽管由于数据导入/导出的怪异，这种情况经常发生).
Your examples are not particularly helpful - there's no logical reason to have 3 different ways of managing a person's name in your database (though this does regularly happen because of data import/export weirdness).
但是，通常会存在一个人"实体，该实体可能是雇员(带有employee_id)，联系人(带有指向潜在客户表的链接)或客户(带有customer_id并链接到客户的链接)订单表).
However, it's very common for there to be a "person" entity who might be an employee (with employee_id), a contact (with a link to the prospects table), or a customer (with a customer_id and link to the orders table).
在拉曼的书中，他给出了3个解决方案.
In Larman's book, he gives 3 solutions.
一个表将它们全部统治 在这里，您将创建一个包含所有已知列的表.这将创建一个凌乱的表，并将了解持久性每个子类的规则的责任推到应用程序层-数据库不会强制要求客户拥有customer_id.但是，这使联接变得更加容易-任何需要链接到人员的表都可以链接到人员表.
One table to rule them all Here, you create a single table with all known columns. This creates a messy table, and pushes the responsibility for knowing the rules of persisting each subclass to the application layer - the database won't enforce the need for customers to have a customer_id. However, it makes the joins much easier - any table that needs to link to a person can just, well, link to the person table.
超类表 通过将公共属性提取到单个表中来清理内容-例如"person"-并将特定于子类的字段推送到子类表.因此，您可能具有人"作为超类表，并且具有联系人"，员工"和客户"表以及特定的子类数据.子类表具有"person_id"列，以链接回超类表.这更复杂-在检索数据时通常需要附加联接-而且错误率也要低得多-您不能通过为员工"写入无效属性的错误意外破坏数据模型.
Superclass table This cleans things up by extracting the common attributes into a single table - e.g. "person" - and pushes the subclass-specific fields to subclass tables. So, you might have "person" as the superclass table, and "contact", "employee" and "customer" tables with the specific subclass data. The subclass tables have a "person_id" column to link back to the superclass table. This is more complex - it typically requires an additional join when retrieving data - but also far less error prone - you can't accidentally corrupt the data model with a bug that writes invalid attributes for "employee".
每个子类的表-这就是您所描述的.它在数据模型中引入了大量重复项，并且您经常有条件联接-如果人员类型= y，则在表x上联接"，这会使数据访问代码变得棘手.
Table per subclass - this is what you've described. It introduces a fair amount of duplication into the data model, and you often have conditional joins - "join on table x if person type = y", which can make the data access code tricky.