Q:

Benefits of a join table?

I am trying to explain the benefits of a join table to a colleague, and below is an explanation. Am I correct?
Currently he has the relationship between a pic and tag with two tables. A pic table and a tag table. The pic table has a tag_id this is a FK to an entry in the tag table. Here was my response:
First lets look at pics and tags tables. So in your current architecture lets imagine two pics (a & b). We tag pics a & b with the hashtag #wtf. We now have two entries in out tags table:
pic_id  title
------  -----
a       wtf
b       wtf

Do you see the issue? So imagine we have 1000 wtf tags on a 1000 different pics. With this same architecture we now have a bloated tags table with all this repeated data (and wasted space). This issue arises when we have a many to many relationship. In this case many pics can have many tags, and many tags can have many pics. How would we fix this? The answer is a join table. So we create a new table. Lets call it pic_tag. This table would have columns pic_id & tag_id. So now new tables would look like:
pic_tag
pic_id  tag_id
------  ------
a       1
b       1

tags
id   name
--   ----
1    wtf

pic
id  name
--  ----
a   pic1
b   pic2

So this does a couple of things for us. First it saves space. We only store the string 'wtf' one time. Second, to find all pics with the tag 'wtf' we first go to the tag table and find the id of 'wtf', then go to the pic_tag table and search for that id which is much more efficient that searching a bloated 'tags' table for a given text. Said another way, search for ints is much faster than searching for text.

A:

The main benefit is that there are relationships that can only be modeled with a join table.
Say you have two entities A and B. If the relationship A:B is 1:1, the two can be represented by a single table. If A:B is 1:N (like 1 customer can have N orders but each order comes from only one customer), then you could model this as a foreign key from orders table to customers table. But if A:B is N:M (your tags scenario is a good example) you need a way to represent 0, 1 or N tags for each picture. There is no sound relational representation for that other than a join table. 
Note you could represent multiple tags per picture while breaking some principles of relational design (like storing multiple tags or FK's to tags) in a single column. Whether to do that or not is a design decision.
Other benefits: you can change your mind whether the relationship is 0:1, 0:N, 1:(0-5), 1:N etc. without changing the core data model -- addressing that logic in triggers or application logic. You can create additional indexes to help with joins. You can introduce more uniqueness constraints to enforce data logic etc. 
But the main benefit is, join tables are the only relationally sound way to model certain types of relationships. 

