Database normalization - how deep should I link tables together?

Question

I have three tables: Post, Attachment, and Media.

Posts have Attachments, and Attachments have Media.

Currently, the Post and Attachment tables are linked by foreign keys, and so are the Attachment and Media tables. My question is, for the sake of proper database design and normalization, should I setup a foreign key relationship between Post and Media? I'm unsure as to how deep I should link these tables together.

Thanks

Answer 1

Link tables as deep as makes sense. Denormalize for reporting and after seeing performance issues.

Answer 2

I think that you are OK.

It seems possible that a POST can have several ATTACHMENTs and that an ATTACHMENT can have several POSTs, if so you will need a link entity for third normal form:

  Post

    |
    |
  -----
  | | |

Post_Attachment

  | | |
  -----
    | 
    |

Attachment

    |
    |
  -----
  | | |

  Media

But from your description there seems to be no key relationship between POST and MEDIA.

Answer 3

No, for normalization as deep as 3NF is concerned your, which is the usual normalization level, the structure is ok.

For the record normalization has it costs as well as it benefits, specially for data insertion and deletion against a major level of control over precisely how and what can be inserted.

One normalized and denormalizes at his own risk :)

Answer 4

for the sake of proper database design and normalization, should I setup a foreign key relationship between Post and Media?

For "proper normalization" you have to assure that there are no "update anomalies".

If someone updates a Post, what happens to Attachments and Media? Will renaming a post disconnect the attachments and/or the media? If so then your FK is wrong. [Hint, you must use surrogate keys not the name of the post to make your FK's work.]

If someone wants to "move" an Attachment from one Post to another [i.e., update the Attachment's FK reference], what happens to the Media? Does it stay with the Attachment and move to the new Post?

Could you wind up with Post having Attachments and Media, as well as Attachments having Media? Could the Post and the Attachments disagree about the Media because the Attachment was "moved" but the Post wasn't also updated?

If you can have contradictions, you have broken 2nd Normal Form and you have repeated key relationships you should not have repeated.

Proper Normalization is easy.

Data Depends on the Key and Nothing But the Key.

Do not copy or repeat dependencies anywhere. What you're calling "deep linking" appears to be a repetition of dependencies.

Answer 5

The only reason for adding a FK from Media to Post would be if you need to filter or select media for a particular post without regard for the attachments. Even if you need to display media (perhaps by type) and the post it belongs to, I wouldn't add a direct association; the overhead for adding a second join (via the attachment table) is likely to be minimal, so you're unlikely to see any significant improvement.

Answer 6

First off, you don't need to use surrogate keys. A proper database will cascade updates if you desire. When normalizing a database, you're usually trying to achieve 3rd normal form or even BCNF. 2nd normal form will not always protect your data from update anomalies. Determining the functional dependencies in your schema should be simple once you produce an ER diagram and decide what data is part of the entities (Posts, Attachments, Media) and what data is part of the relationships. Depending on the cardinality of your relations, you may or may not need join tables. The best thing to do is to model your data in a diagram, then deal with the implementation issues.