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.


  • Has_and_belongs_to_many find if exists
  • CakeDC users plugin - how to add details field(i.e - first_name, company) in to registration form
  • How to call PDOStatement::nextRowset() in Cakephp 3
  • .htaccess is not working by my desires with CakePHP
  • Themes for CakePHP [closed]
  • List all tables in cakePHP 3.x
  • cakephp get schema for list of tables
  • How to: CakePHP logging in without password?
  • Find record, that has ALL associated records
  • Cakephp Form Helper
  • Mapping C# classes to Lua functions via dll
  • Associate git repo with existing fork network
  • Is it safe to accept URL parameters for populating the `url_for` method?
  • setContentView() is not enough to switch between layouts?
  • How can I see a list of all files that are different between two Hg repositories?
  • Ruby and class variables in inherit class
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • Receive mouse move even cursor is outside control
  • Request response issues in biztalk
  • Most efficient way to move table rows from one table to another
  • Overlapping controls in Windows XP
  • Unity3D & Android: Difference between “UnityMain” and “main” threads?
  • How to delay loading a property with linq to sql external mapping?
  • Why value captured by reference in lambda is broken? [duplicate]
  • What is Eclipse's Declaration View used for?
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Jquery - Jquery Wysiwyg return html as a string
  • SVN: Merging two branches together
  • PHP: When would you need the self:: keyword?
  • Why joiner is not used after Sequence generator or Update statergy
  • embed rChart in Markdown
  • How to get Windows thread pool to call class member function?
  • Append folder name and increment by 1 using batch script
  • Recursive/Hierarchical Query Using Postgres
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Reading document lines to the user (python)
  • How can I use `wmic` in a Windows PE script?
  • UserPrincipal.Current returns apppool on IIS
  • Unable to use reactive element in my shiny app