Struggling with model relationships

Question

I'm having a hard time designing a relationship with a few models in my project.

The models are: band, musician, instrument

Bands have multiple musicians

Musicians have multiple bands and multiple instruments

That’s all pretty straightforward, but I also need to keep track of what instruments a musician has for a particular band. So in a sense, I guess, bands have multiple instruments via the musicians.

In the tables, I was going to add instrument_id to the bands_musicians linking table, but I need a musician to be able to have multiple instruments for a band, so I was thinking it would need to go in the musicians_instruments table.

What's the best way to set up the relationships with these models?

Thanks for your time!

Answer 1

Musicians would have a one-to-many relationship with both bands and instruments. So create your musicians table and add all of the information relavent to the musicians themselves into that table.

Create an instruments table to hold information about instruments, and do the same for the bands. That will take care of all of your individual items.

Then create something like 'band_assignments' table that just has the id of a band and the id of a musician and links the two together. Create an 'instrument_assignment' table to do the same thing.

Now when you query a musician you can left join all of these tables together to get the data that you need or selectively join on just instruments, just bands, or sort by 'join date' and limit to get the last band they joined or the last instrument they learned.

Basically 5 tables should cover it all.

musicians  (musician_id, first_name, last_name)
bands  (band_id, name)
instruments  (instrument_id, name)
band_instument_assignments  (musician_id, band_id, instrument_id, date_played)

As you can see in the edited version above you will have multiple rows in the 'band_instrument_assignments' table--one for each instrument that each user played in each band. You will need to use some GROUP BY and LIMIT clauses to get the data you want, but it should work for you.

Answer 2

See:

How to handle a Many-to-Many relationship with PHP and MySQL

That should give you an idea on how to go about designing your database structure.