What is the correct way to design this database?
Here is how I have my tables set up:
I have a many to many relationship between a table called teachers and a table called instruments. I then have a bridge table connecting the two. I would like to associate another table with the BRIDGE table. Meaning the instrument/teacher combination. That table would have 3 rows specifying what level of playing a teacher can teach (ie. Beginner, intermediate, advanced). Seems like I should set up the bridge table to have teacher_id, instrument_id AND level_id, but I don't know if this is the conventional way to do it.
I am using mysql and cakephp, and I haven't found anything in the documentation about the HABTM associations about having an extra field in the bridge table. Just want to make sure I'm doing it correctly.
If that's what your data calls for, and it sounds like it does, then go for it.
Basically, as you noted, you'd have a three-part key:
Which means that a teacher could teach guitars at beginner and advanced, piano at intermediate and beginner, and another teacher could teach piano at all three levels and oboe at beginner ... sounds good.<hr>
Incidentally, the bridge table is also known as an
+1 for Matt Fenwick. I would add that you want to be a little careful with your foreign key constraints. You essentially have two options, both of which could end up looking pretty similar, depending on your choice of primary keys.
<strong>Option one</strong> is: Forget the simple intersection between
INSTRUMENT and replace it with a complex intersection that includes
level_id. All three of these columns would be the (compound) primary key of this intersection table. In this option, you have foreign key constraints defined on
level_id if this is actually a foreign key to a
LEVEL table and not just an integer or string code).
<strong>Option two</strong> is: Keep the simple intersection between
INSTRUMENT (let's call it
TEACHER_INSTRUMENT even though that is unimaginative) and add a sub-child table that defines the levels that can be taught. This sub-child table (let's call it
SKILL) has a
level_id and a foreign key to
TEACHER_INSTRUMENT. If the primary key of
TEACHER_INSTRUMENT is the combination of
instrument_id then the
SKILL table is going to have the same three columns as in option one. What makes this option different? The foreign key constraint from
SKILL must be to the intersection table, not to
<strong>Why is this important?</strong> If you choose option one, you may need to have some extra query logic to get a fully populated grid of skills, since there is no referential integrity constraint you can define that will ensure that all skill levels are populated for each teacher/instrument combination.
On the other hand, if you choose option two, you have the advantage of separation of concerns between who can use what and how well they can teach it.
What you want to avoid is having one table that contains just the teacher/instrument relationship and then a second one which (independently) repeats that relationship but adds the skill level detail. If you do that, then you run the risk of these two things getting out of sync.