MySQL – Creating an associative table

I have the following table structures:

Cases:

(PK)
case_id | case_notes | time
1       | blah       | timestamp
2       | blah       | timestamp
3       | blah       | timestamp
.       | .          | .

There will be an “infinite” amount of cases so each has it’s own unique ID.

I now have a “tags” table. There can be an “infinite” amount of tags so each tag has it’s own unique ID:

(PK)
tag_id | tag
1      | tag-1
2      | tag-2
3      | tag-3
.      | .

Now I would like each case to be associated with n’-many tags. Due to this I need an “association” table (is that the right word?) to link cases to tags.

(PK??? not sure)    (FK)      (FK)
id | case_id | tag_id
1  | 1       | 1
2  | 1       | 2
3  | 2       | 2
4  | 3       | 1
5  | 3       | 2
6  | 3       | 3
.  | .       | .

In the above example case 1 is associated with tag-1 and tag-2, case 2 is associated with just tag-2, and case 3 is associated with tags 1,2, and 3.

I want to be able to do the following SELECTS:

SELECT all cases (including their notes) which are associated with tag-1
SELECT all tags associated with a particular case.

On a high level you can imagine the system as the following giant table:

case_id | case_notes | time      | tags
1       | blah       | timestamp | (tag-1, tag-2)
2       | blah       | timestamp | (tag-2)
3       | blah       | timestamp | (tag-1, tag-2, tag-3)
.       | .          | .         |

For that many-to-many ‘relation’ table, don’t bother to have an id. Simply have

PRIMARY KEY(case_id, tag_id),
INDEX(tag_id, case_id)

Note that one of those works perfectly for one of your SELECTs, the other works perfectly for the other. (Use ENGINE=InnoDB.)

To get (tag-1, tag-2, tag-3), use GROUP_CONCAT().

FOREIGN KEYs are optional; I would not bother.

What you describe is frequently called a “joining table” – a many to many connection. Athough the word “associative” is readily understandable in the context, I would use the term “joining” table.

You can put either a PRIMARY KEY or a UNIQUE KEY on the fields (case_id, tag_id) – no need for a PRIMARY KEY with id – it’s superfluous.