在 SQL Server 中实现多态关联的最佳方法是什么? [英] What is the best way to implement Polymorphic Association in 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

这篇关于在 SQL Server 中实现多态关联的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！