CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
Jon Halliday
Jon Halliday

Reputation: 455

Multi level database foreign keys

I have the following structure in my database:

Area:
----------------
Id: (PK, bigint)
AreaName: (varchar)

Post:
----------------
Id: (PK, bigint)
AreaId: (FK, bigint)
Title: (varchar)
Text: (varchar)

Comment:
----------------
Id: (PK, bigint)
PostId: (FK, bigint)
Text: (varchar)

Since I will have many scenarios where I need to query comments by Area, my question is this -

Is it better to JOIN through my Post table to get to Area for Comments, or would it be better to modify my Comment table like so:

Comment:
----------------
Id: (PK, bigint)
AreaId: (FK, bigint)
PostId: (FK, bigint)
Text: (varchar)

What are the best practices with "cascading" foreign keys instead of joining for purposes of querying? My gut tells me that this is a bad idea, but I can't deny that it would make a lot of my queries easier.

Should I be constructing a View that does this? Also, is there a term for this "cascading" foreign key concept? I had a hard time coming up with information on this despite feeling like it would be a common question.

The following question asks something similar: Three level database - foreign keys

The answers point out that this is unnecessary (agreed), but not why (or if) it's a bad idea.

Upvotes: 4

Views: 1006

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

It is a very common and perfectly correct practice to cascade FKs through multiple levels of a hierarchy modeling a "containment" or "belongs to" type relationship. But you don't just add FK columns, you use compound PKs on the child tables:

Area:
----------------
AreaId: (PK, bigint)
AreaName: (varchar)

Post:
----------------
AreaId: (PK,FK, bigint)
PostId: (PK, bigint)
Title: (varchar)
Text: (varchar)

Comment:
----------------
AreaId: (PK,FK, bigint)
PostId: (PK,FK, bigint)
CommentId: (PK, bigint)
Text: (varchar)

And each table has one FK, not two. So Comment has a two-column FK referencing Post.

The only real complaint against this model is that you have to join on multiple columns, but that's just complaining about having to type. This is most efficient model, as related rows are stored together in the PK index, and the PK index supports efficient lookups and traversals of the relationships. In the single-column key model, you must have secondary indexes supporting the FKs.

Upvotes: 5

Arslan Farooq
Arslan Farooq

Reputation: 21

Modification is not necessary. Mostly people use multiple foreign keys to create easy queries and it is definitely not a bad idea according to me.

Upvotes: 0

Related Questions