CODEWITHSUNDEEP
database-design
Refracted Paladin
Refracted Paladin

Reputation: 12216

Database Design: Where should my reference be, Parent or Child?

I have a table that can have a reference to any 1 of 5 tables. Should I have 5 Nullable FK Columns on my Primary Table or should I have a FK reference back to my Primary on each of the 5 potential Secondary tables?

I understand that either will "work" I simply would like some experienced based answers on which provides me with the better structure. For Instance, if I go the 5 Nullable FK's route I cannot use Cascading Delete.

Thanks to any and all who can spare a moment and provide some insight.

EDIT

My Parent table is NoticeOfAction, which is basically just a questionaire. It can have any 1 of 5 potential Child table's that are just different subset of questions based on the "Intended Action" chosen in the NOA. So, the NOA table entry has to happen first and can only reference 1 of the 5 child tables at a time. If the NOA entry is deleted I would remove the corresponding entry on the Child Action table as it cannot exist without the NOA itself.

I hope that helps.

Upvotes: 0

Views: 379

Answers (4)

vasek1
vasek1

Reputation: 14071

I am curious if you can combine the 5 child tables into one in any way, maybe by adding an extra column clarifying the type of the subset of questions. Then, the relationship would simplify to 1 child table referencing 1 parent table. But, of course, the possibility of this depends on your data.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96580

You put the id from the parent table in the child tables. Then cascading will work properly and you can properly enter the parent data first then the child data.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40319

One possible implementation: The parent table has (at least) a primary key and a “switch” or “type” column, indicating which of the 5 child tables--“subtables”, really—will contain the related data. Each subtable has the parent table ID as a foreign key.

When querying, you identify type of parent, and read the child entries from the appropriate table. Elaborate tricks could be done with views to simplify querying, and with foreign keys to ensure referential integrity.

Upvotes: 1

Jens Schauder
Jens Schauder

Reputation: 81950

There is no hard answer to this. But you should consider:

  • Is cascading delete of any use to you?

  • Do you have constraints between the fks? Like 'if there is a reference to table A you must not reference table B' If so you could implement that using a check constraint on the single table, if it also contains the fk

  • Which table is more natural to create first? The id might be better on the other table.

Upvotes: 1

Related Questions