What's the right way to create this database?

This is how I've my tables setup:

I've got a many to a lot of relationship from a table known as instructors along with a table known as instruments. Then i possess a bridge table hooking up the 2. I must connect another table using the BRIDGE table. Meaning the instrument/teacher combination. That table might have 3 rows indicating what degree of playing an instructor can train (ie. Beginner, intermediate, advanced). Appears like I ought to setup the bridge table to possess teacher_id, instrument_id AND level_id, but I'm not sure if this sounds like the traditional method of doing it.

I'm using mysql and cakephp, and that i haven't found anything within the documentation concerning the HABTM associations about getting an additional area within the bridge table. Would like to make certain I am doing the work properly.

If that is what your computer data requires, also it seems like it will, go for this.

Essentially, while you noted, you'd possess a three-part key:

  • teacher_id
  • instrument_id
  • level_id

Meaning an instructor could train guitars at beginner and advanced, piano at intermediate and beginner, and the other teacher could train piano whatsoever three levels and oboe at beginner ... sounds good.

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

+1 for Matt Fenwick. I'd add that you would like to become a little careful together with your foreign key constraints. You basically have two options, each of which could finish up searching pretty similar, based on the selection of primary secrets.

Option one is: Your investment simple intersection between TEACHER and INSTRUMENT and change it having a complex intersection which includes teacher_id, instrument_id and level_id. All of these posts will be the (compound) primary key of the intersection table. Within this option, you've foreign key constraints defined on teacher_id and instrument_id (and level_id if this sounds like really an overseas answer to a LEVEL table and not simply an integer or string code).

Option two is: Keep your simple intersection between TEACHER and INSTRUMENT (let us refer to it as TEACHER_INSTRUMENT despite the fact that that's unimaginative) and give a sub-child table that defines the amount that may be trained. This sub-child table (let us refer to it as SKILL) includes a level_id along with a foreign answer to TEACHER_INSTRUMENT. When the primary key of TEACHER_INSTRUMENT may be the mixture of teacher_id and instrument_id then your SKILL table will have exactly the same three posts as with option one. Why is this method different? The foreign key constraint from SKILL is always to the intersection table, to not TEACHER and INSTRUMENT.

Why so much interest? When you purchase option one, you may want to possess some extra query logic to obtain a fully populated power grid of abilities, since there's no referential integrity constraint you are able to define which will make sure that all abilities are populated for every teacher/instrument combination.

However, when you purchase option two, you will find the benefit of separation of concerns between who are able to use what and just how well they are able to train it.

What you would like to prevent is getting one table that consists of only the teacher/instrument relationship after which another the one that (individually) repeats that relationship but adds the level of skill detail. Should you choose that, then you definitely risk both of these things escaping . of sync.