2

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)
.       | .          | .         |
user99992934
  • 21
  • 1
  • 3

2 Answers2

4

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • Why do I need a primary key and an index? Doesn't a primary key imply an index? – user99992934 Apr 08 '15 at 21:48
  • 2
    Yes, a PRIMARY KEY is an index. In a compound index (multiple columns) the order is vital. Think about searching a list of names when you have a first name, but the list is sorted by last name. (case_id, tag_id) is used for "ind all the tags for a given case". (tag_id, case_id) is for "find all the cases with a given tag". So, they are different, and you need both. – Rick James Apr 08 '15 at 21:53
  • Nice analogy about the phone directory sorted by last name but you want to search by first name - good example for future reference. +1 – Vérace Apr 08 '15 at 23:11
  • You may like my blog on compound indexes and index cookbook. The latter has a section devoted to 'Many-to-Many Mapping table'. – Rick James Apr 08 '15 at 23:20
0

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.

Vérace
  • 29,825
  • 9
  • 70
  • 84