How to setup MySQL Database Relationship

Question

I have a database that has two tables "locations" and "classes". I am familiar setting up some sort of parent/child situation with my database tables, but this one has thrown me for a loop.

This relationship works exactly like you'd expect. The "locations" have multiple "classes". But some of the "classes" are at multiple "locations". So how do I set this up? Does there need to be a third table created to handle the relationships?

I am using MySQL/PHP (in case that matters)

Answer 1

Yes, you need a third table. This is called a many-to-many relationship. The table would (I recommend) have a primary key, a location ID column, and a class ID column.

Answer 2

You need a join table:

tbl_location
ID: int
Name: Varchar(20)

tbl_class
ID: int
Name: Varchar(20)


tbl_classlocation
ID: int
locationID: int
classID: int

Something along those lines, essentially the classlocation table joins the other two (both locationId and classID are foreign keys from the location and class tables)

Answer 3

   locations           location_classes           classes

+----+-------+    +-------------+----------+   +----+-------+
| id | title |    | location_id | class_id |   | id | title |
+----+-------+    +-------------+----------+   +----+-------+
|  1 |   foo |    |           1 |        1 |   |  1 | clas1 |
|  2 |   bar |    |           3 |        1 |   |  2 | clas2 |
|  3 |  test |    |           1 |        2 |   +----+-------+
+----+-------+    |           2 |        2 |   
                  |           3 |        2 |
                  +-------------+----------+

Answer 4

Yes, you need a third table due to this is a many to many relationship.

 --------         -----------------
|Class   |       | ClassLocation   |        ------------
|--------|       |-----------------|       | Location   |
|Id      | <---- |ClassId          |       |------------|
|Name    |       |LocationId       | ----> |Id          |
|...     |        -----------------        |Name        |
 --------                                  | ...        |
                                            ------------

It is even possible that you need two additional tables if the direction of the relation is important. If the meaning of "class belongs to location" is not the same as the meaning if "location belongs to class" you need two join tables because only one join table does not tell anything about the direction of the relationship. But I guess that is not the case for your situation.