CODEWITHSUNDEEP
mysqldatabasedatabase-design
Mike McLin
Mike McLin

Reputation: 3637

How to setup MySQL Database Relationship

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)

Upvotes: 1

Views: 2068

Answers (4)

Daniel Brückner
Daniel Brückner

Reputation: 59645

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.

Upvotes: 3

cherouvim
cherouvim

Reputation: 31903

   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 |
                  +-------------+----------+

Upvotes: 2

Steven Evers
Steven Evers

Reputation: 17196

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)

Upvotes: 1

chaos
chaos

Reputation: 124257

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.

Upvotes: 3

Related Questions