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

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.

Answer 2

someoneinomaha Maybe you need 4th model, which will cover and union all of her children entities, e.g. called like 'Mus Model'(or whatever you want) and have some methods like:

• get_bands()
• get_instruments()
• get_musicians()
• get_instruments_by_musician()
• get_musicians_by_band()
• get_instruments_by_band()
• get band_by_musician() and so on...It'll provide you needed data and will not brake entities relationships, imho.

Answer 3

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 4

I might be a little late to the party here and I am no database expert but I have found that drawing out your DB schema helps immensely. Just make boxes and fill in your table names and columns then draw arrows to define your relationships and it should be a lot clearer as to how you should structure things and whether you need to add a table to join two other tables.

If all else fails, just copy a schema from databaseanswers.org. I'm sure there is one there that would probably help you.