Do I need a diamond relationship to make this problem work?

user_15581203 0
Viewed 67

I've trawled the internet trying to find an answer for this - I should preface this question with my being a total amateur.

EDIT: The Database type is mySQL
EDIT: The band sizes aren't dynamic; they're for showbands ie a 5 piece will always be Vocals | Guitar | Keys | Bass | Drums
EDIT: I've included some sample data at the bottom

So, the problem is thus:

I have a table of musicians (musiciansID)
I have a table roles (rolesID), which is their role in a band (vocals, guitar etc)

I have a table of musicians_roles (musiciansID, rolesID). Musicians can play many instruments.

I have a table of specific bands (bandID) (8 piece, 6 piece) etc

I have a table of band_roles (bandID, rolesID).
Each band has a specific format eg 3 piece | Vocals, Guitar, Drums

I'm banging my head against the wall trying to enforce something like the following:

BandID = 1 (3 piece, say)

BandID has RolesID = 1,2,3 (Vocals, Bass, Drums)

and this is the bit where I fall down

BandID has MusiciansID = 1,2,3 (John, Paul, Ringo), but I'd like the constraint to fail if I tried to put Ringo as the bass player for example.

I'm SO sorry if this is super simple and I just don't have the vocabulary to search for it. I have a feeling the answer may be something similar to the link below, but I can't get it to work.

https://dba.stackexchange.com/questions/34040/many-to-many-and-weak-entities

Some sample data

Musicians:

Joe Bloggs
Sarah Samsonite
Sam Stalactite
Don Bike
Tracy Shiney

Roles:

Vocals
Trumpet
Saxophone
Guitar
Bass
Drums
Keyboards

Musicians_roles:

Joe Bloggs, Saxophone
Joe Bloggs, Drums
Sarah Samsonite, Vocals
Sam Stalactite, Drums
Don Bike, Guitar
Tracy Shiney, Bass

Band_Roles:

5 piece, Vocals
5 piece, Keyboards
5 piece, Guitar
5 piece, Drums
5 piece, Bass
6 piece, Vocals
6 piece, Keyboards
6 piece, Guitar
6 piece, Drums
6 piece, Bass
6 piece, Sax

Musicians in band:

5 piece, Vocals, Sarah Samsonite
5 piece, Keyboards, NULL
5 piece, Guitar, NULL
5 piece, Drums, Sam Stalactite
5 piece, Bass, NULL

Then, say for example I tried to put Sarah Shiney on guitar, it would fail.

Thank you so much for your time!

database-design mysql
1 Answers
-- Role ROL exists.
--
role {ROL}
  PK {ROL}

-- Musician MUS exists.
--
musician {MUS}
      PK {MUS}

-- Musician MUS can play role ROL.
--
musician_role {MUS, ROL}
           PK {MUS, ROL}

FK1 {MUS} REFERENCES musician {MUS}
FK2 {ROL} REFERENCES role     {ROL}

-- Band BND exists.
--
band {BND}
  PK {BND}

-- Band BND has role ROL.
--
band_role {BND, ROL}
       PK {BND, ROL}

FK1 {BND} REFERENCES band {BND}
FK2 {ROL} REFERENCES role {ROL}

The constraints, as I understood them from the example:

  • Each musician can play in at most one band; for each band: more than one musician can play in that band.

  • For each band and role: exactly one musician plays in that band in that role.

-- Musician MUS plays in band BND in role ROL.
--
musician_band_role {MUS, BND, ROL}
                PK {MUS}
                AK {BND, ROL}

FK1 {MUS, ROL} REFERENCES musician_role {MUS, ROL}
FK2 {BND, ROL} REFERENCES band_role     {BND, ROL}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
FK = Foreign Key
user_1967130
Related