在 SQL Server 中实现多态关联的最佳方法是什么?
我有很多实例需要在我的数据库中实现某种多态关联.我总是浪费大量时间重新考虑所有选项.这是我能想到的3个.我希望有 SQL Server 的最佳实践.
I have tons of instances where I need to implement some sort of Polymorphic Association in my database. I always waste tons of time thinking through all the options all over again. Here are the 3 I can think of. I'm hoping there is a best practice for SQL Server.
这里是多列方法
这里是无外键方法
这是基表方法
推荐答案
我用下面的方案解决了类似的问题:
I used the following solution to solve a similar problem :
基于Many-Many的设计:即使ObjectN和Something之间的关系是1-Many，它也等同于关系表PK修改后的Many-Many关系.
Many-Many based design : Even though the relation is a 1-Many between an ObjectN and Something, it is equivalent to a Many-Many relationship with a modification of the PK of the relation table.
首先，我在 ObjectN 和Something per Object 之间创建一个关系表，然后我使用Something_ID 列作为PK.
First i create a relation table between an ObjectN and Something per Object and then i use the Something_ID column as the PK.
这是Something-Object1关系的DDL，对于Object2和Object3也是一样的:
This is the DDL of the Something-Object1 relationship which is the same for Object2 and Object3 as well :
CREATE TABLE Something
(
ID INT PRIMARY KEY,
.....
)
CREATE TABLE Object1
(
ID INT PRIMARY KEY,
.....
)
CREATE TABLE Something_Object1
(
Something_ID INT PRIMARY KEY,
Object1_ID INT NOT NULL,
......
FOREIGN KEY (Something_ID) REFERENCES Something(ID),
FOREIGN KEY (Object1_ID) REFERENCES Object1(ID)
)
此票中其他可能选项的更多详细信息和示例multiple-相同业务规则的外键
More details and examples of other possible options in this ticket multiple-foreign-keys-for-the-same-business-rule
相关文章