Multiple nullable foreign keys in one table

Question

I am in the process of designing a table for a promotion. I can promote three types of entities: article, photo and forum thread.

Initially I thought about creating one table with three foreign keys where only one would be set.

promotion: 
- id
- id_article
- id_photo
- id_thread
- promotion_type
- ... other properties

 article:
 - id
 - .. other properties

 photo:
 - id
 - .. other properties

 thread:
 - id
 - .. other properties

Is t a good approach? After initial thoughts it does look like there could be something "prettier" done here. promotion_type indicates which foreign keys tables should be joined.

by design it's there to save some joining time, because we don't have to join all tables, only those which are needed. — SirKometa, Mar 21 '16 at 21:06
that can also be determined by which id_ has a value correct? — JamieD77, Mar 21 '16 at 21:08
Seems like a good point, sql server won't try to join null value even if I request it in the query. — SirKometa, Mar 21 '16 at 21:17
Possible duplicate of [Defining multiple foreign keys in one table to many tables](http://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables) — philipxy, Mar 21 '16 at 23:00

Erick Kamamba · Answer 1 · 2016-03-21T21:24:09.387

0

Instead of having 3 columns (id_article, id_photo, id_thread), just add one column.. Call it PromotionID for example, then you can still use PromotionType column to determine which table to tie.

The Promotion table would be as follows:-promotion: - id - PromotionID(either id_article, id_photo or id_thread) - promotion_type - ... other properties

edited Mar 21 '16 at 21:24

answered Mar 21 '16 at 21:15

Erick Kamamba

268
1
4

2

I wouldn't like to weaken data integrity. With FK, sql server will check whether this id actually exist in the other table. – SirKometa Mar 21 '16 at 21:17
I have a similar usecase and if I follow this approach then I'll have to make sure the id exist manually and I'll have to fetch the data from the related table manually, any simpler way of doing this? – nick May 10 '22 at 08:06

Multiple nullable foreign keys in one table

1 Answers1