如何在现有数据库中实现多态关联 [英] How to implement polymorphic associations in an existing database
问题描述
在这个问题 Mark做了一个很好的工作，显示了实施PA的三种常用方法。我想使用基准表方法，它在同样出色的 Bill Karwin回答中有更详细的描述。 p>
具体示例如下所示：
实体的主键指基基表和注释表中的相同键值指的是基准表，因此可以观察到参照完整性。这里的关键部分是实体表的主键具有不同的域。它们是通过在基表中创建一个新记录并将其生成的密钥复制到实体的主键来生成的。
现在我的问题：如果我想在具有生成自己的相互重叠的主键的实体的现有数据库中引入具有引用完整性的PA？
到目前为止，我看到两个选项： p>
选项1：
每个实体保留自己的主键，但也会获得一个备用键。
喜欢：
- 接近推荐的方法。
- 基表是稳定的。
不喜欢：
- 很难找到拥有实体的评论。
选项2：
每个实体在基表中都有自己的外键列。这看起来像马克的多列方法。
喜欢：
- 很容易找到一个评论的拥有实体。
不喜欢： p>
- 稀疏列
- 基本表不稳定：在引入PA的新实体时需要修改
-
我倾向于选项1，可能在基表中有一个字段EntityName用于双向查找。 哪个选项会更好。或者是另一种甚至更好的方法？
您可以使用选项1，但没有额外的代理替代键。
相反，使用 EntityType 列扩展现有的主键（每个实体） $ c> CHAR（1），这将是 E 的事件， P for
复合（EntityId，EntityType）将成为表 Entity 的主键以及其他3个子类型表中的相应化合物。
（ EntityType 只是一个辅助索引，参考表，有3行）：
Polymorphic assiociations (PA's) is quite a mouthful for a relatively simple database requirement: let various tables have child records in one shared table. The classic example is a single table with comment records that apply to different not necessarily kindred entities.
In this question Mark did an excellent job showing three common approaches to implement PA's. I want to use the base table approach, which is described in more detail in an equally excellent answer by Bill Karwin.
A concrete example would look like this:
The primary keys of the entities refer to identical key values in the base table and the Comment table refers to to the base table, so referential integrity is observed. The crucial part here is that the primary keys of the entity tables have distinct domains. They are generated by creating a new record in the base table and copying its generated key to the entity's primary key.
Now my question: what if I want to introduce PA's with referential integrity in an existing database having entities that generate their own, mutually overlapping primary keys?
So far, I see two options:
Option 1:
Each entity keeps its own primary key but also gets an alternate key.
Like:
- Close to the recommended approach.
- Base table is stable.
Dislike:
- Existing entities must be modified.
- Hard to find the owning entity of a comment.
Option 2:
Each entity has its own foreign key column in the base table. This looks like Mark's multiple column approach.
Like:
- Existing entities not affected.
- Easy to find the owning entity of a comment.
Dislike:
- Sparse columns
- Base table not stable: needs modification when a new entity with PA is introduced
I lean to option 1, possibly with a field "EntityName" in the Base table for bidirectional lookup. Which option would be better. Or is another, even better, approach?
You could use Option 1 but without an additional surrogate Alternate Key.
Instead, extend the existing Primary Key (of each entity), with an EntityType column (say CHAR(1), that would be E for Events, P for Persons, D for Products).
The compound (EntityId, EntityType) will become then the Primary Key of table Entity and the corresponding compounds in the other 3 subtype tables.
(The EntityType is just an auxilary, reference table, with 3 rows):
这篇关于如何在现有数据库中实现多态关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
