CODEWITHSUNDEEP
databasedatabase-designormpolymorphic-associations
James Tauber
James Tauber

Reputation: 3456

Set of Foreign Keys Where All But One Are NULL

What is the name for the technique of using a set of foreign keys in a table where all but one are NULL for a given row?

In other words, each row needs a foreign key to one (and only one) of n different possible tables so you actually have all the necessary foreign keys but all but one are NULL.

(users of Django may recognize this as an alternative to using a Generic Foreign Key)

Upvotes: 5

Views: 515

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

It would have been easier with a example, but a common way to untangle this is simply to find a common super-type for those tables. Suppose that we have tables Book, Article, Magazine and now a table has to have foreign key to these tables. Using a common super-type Publicationresolves this. See here for the model and the similar question/answer.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562721

The term for the design you're describing is Exclusive Arc.

Instead, I prefer to make one foreign key that references a common super-table of your n different parent tables.

See my other answers for polymorphic associations:

Upvotes: 6

Related Questions