2

That was a hard title to digest even for me so I'll break it down.

I'm very new to SQL, and I just designed my first database. I also am not very good at JOINS which might be the answer to my problem. But to get back on track I currently have three tables of interest:

comments

   id    |      author       |     category    | date_submitted 
---------+-------------------+-----------------+----------------

replies

   id    | date_submitted | comment_id 
---------+----------------+------------

images

           image_url            | reply_id |                    original_id                    
--------------------------------+----------+---------------------------------------------------


So given this, the question came to mind when I was trying get a table with the columns comments.id and images.original_id, an operation that I would like to do often.

Currently to do this I can't directly JOIN comments and images because they have nothing in common, meaning that the middleman has to be the replies table. So I have to JOIN replies and images and then JOIN that with comments.

So my question is this. Would it be okay to add a foreign key column referencing comments to the images table so that I can directly JOIN the two tables instead of going images -> replies -> comments, or would it be bad practice to add a foreign key that I already have to access to through the replies table?

5
  • Best to omit the transitive closures. They bring no benefit but incur additional overhead. – Pieter Geerkens May 29 '15 at 6:00
  • 1
    I'm sorry, but I'm unfamiliar with the term transitive closures could you maybe explain in simpler terms? – m0meni May 29 '15 at 6:02
  • "a foreign key column referencing comments to the images table" is a transitive closure of the relationships " images -> replies " and "replies -> comment". Also lookup up transitive closure in GOOGLE. – Pieter Geerkens May 29 '15 at 6:05
  • I did google it, but your explanation was much much clearer and more specific than wikipedia. One last question: Is there no benefit gained in having to write one fewer JOIN? – m0meni May 29 '15 at 6:07
  • @TemporaryName All of that is correct except that the relationship is one to one between the comment and reply. This is because I'm not keeping track of all replies, but 1 very specific reply to the comment. – m0meni May 29 '15 at 16:56
1

This answer expands on the comment given by Pieter Geerkens.

The definition of third normal form states that it rules out what are called transitive dependencies. The description given of 3NF in Wikipedia (see article) has an example of a transitive dependency.

Your description of the foreign keys in your Q strongly suggests, but does not absolutely prove, a transitive dependency. The only way you can truly get the dependencies in the subject matter is to study the subject matter.

So, assuming that your example does have a transitive dependency, then your Q boils down to "what advantage do you get by conforming to third normal form"?

The answer to this is that violations of 3NF result in harmful redundancy. Harmful redundancy is a situation where the same assertion is stored multiple times in different places in the database (in this case multiple rows of the table in question). If your design permits harmful redundancy, it will also permit mutually contradictory assertions, which you will have to avoid by careful programming.

It's better, all other things being equal, to conform to 3NF and prevent the contradictions that way.

All other things are seldom equal. There are reasons for adopting a design that doesn't conform to 3NF, in spite of the risk. One design that comes to mind is star schema, but don't worry about star schema until you've got 3NF under your belt.

1
  • Okay thank you I'm going to read up on 3NF. – m0meni May 29 '15 at 16:59
3

It is not a good practice for many reasons. The most important one, design principles, you should never mix entities just because it is easier for the programmer/dba to do queries. Entities should be as clear as they can and must exclude any other information not related to them.

For your scenario, to make it easier for future querying, you can create a view that does the join and instead query this view.

Regarding any benefit that could be possible while cutting a join, there are other costs maintaining the foreign keys, so there is not really a huge advantage either.

If you are in SQL Server, you can create an index for the view, which materializes the view (creates an underlying table with the data) and you will have performance as querying a single table.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.