Why to have a join table for 1:m relation in SQL

Answer 1

Its usually due to duplication of data.

As for your reasoning, yes you can enforce this in the business layer, but if you make a mistake, it could break a significant amount of code. The issue you have is your data model may have only a few tables. Lucky you. When your data model grows, if you can't make sense of the structure and you have to put all the logic to maintain denormalised tables in your GUI layer you could very easily run into problems. Note that it is hard to make things threadsafe on a GUI for your SQL Database without using locking which will destroy your performance.

DBMS are very very good at dealing with these problems. You can keep your data model clean and use indexing to provide you with the speed you need. Your goal should be to get it right first, and only denormalise your tables when you can see a clear need to do so (for performance etc.)

Believe it or not, there are many situations where having normalised data makes your life easier, not harder when it comes to your application. For instance, if you have one big table with questions and answers, you have to write code to check if it is unique. If you have a table with a primary key, you simply write

insert into table (col1, col2) values (@id, @value) --NOTE: You would probably 
--make the id column an autonumber so you dont have to worry about this

The database will prevent you from inserting if you have a non unique value there OR if you are placing in an answer with no question. All you need to do is check whether the insertion worked, nothing more. Which one do you think is less code?

Answer 2

I agree that the join table for a one-to-many in this situation doesn't seem to add much benefit, and as @chaos says, you actually end up implementing many-to-many support. But Joe Celko is a smart guy - is this really the exact answer he gives?

One other possible reason for implementing a join table on a one-to-many is that it completely separates questions/answers from a dependence on users.

For example, say you added a Dogs tables and an Deities table. We all know that dogs can't register as users because they don't have email addresses, and gods don't register as users because, well, it's beneath them. Maybe dogs and gods still ask questions though, but to do that you might want to implement a dogs-questions table and a deities-questions table. In theory this is still many-to-many, but in practice you do it so that you can have multiple one-to-manys.