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.