What is the best way to implement Polymorphic Association in SQL Server?

user_85144 0
Viewed 23415

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.

Here is the multiple column approach

Multiple Column approach

Here is the no foreign key approach

No Foreign Key Approach

And here is the base table approach

Base table approach

database-normalization polymorphic-associations associations sql-server
9 Answers

I used the following solution to solve a similar problem :

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.

First i create a relation table between an ObjectN and Something per Object and then i use the Something_ID column as the PK.

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)
)

More details and examples of other possible options in this ticket multiple-foreign-keys-for-the-same-business-rule

user_94235940
Related