Linking a third table to a bridge table in a many to many association

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:

    <li>teacher_id</li> <li>instrument_id</li> <li>level_id</li> </ul>

    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.


    Incidentally, the bridge table is also known as an intersect or intersection table.


    +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 TEACHER and INSTRUMENT and replace it with a complex intersection that includes teacher_id, instrument_id and 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 teacher_id and instrument_id (and 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 TEACHER and 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 teacher_id and 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 TEACHER and INSTRUMENT.

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

    人吐槽 人点赞



用户名: 密码:
验证码: 匿名发表


查看评论:Linking a third table to a bridge table in a many to many association