Re: Database design

[alicejwz]

Re: Database design

Hi,

I have a database problem if someone can provide some input
on my database design if it confirms w/ normalization rules at least the three normal forms.

Here is info/requirements of db:
An orchestra has four broad classes if instruments(strings, woodwinds, brass, and percussion). Each class contains musicians who play different instruments. For example, the string section of a full symphonic orchestra contains 2 harps, 16 - 18 first violins, 14 -16 second violins, 12 violas, 10 cellos, and 8 double basses. I need to develop a database to store details of the musicians in its three orchestras. All the musicians are specialists and play only one instrument for the orchestra.

I have five tables:
tblOrchestra: orchestra_id(PK), Name
1 A
2 B
3 C

tblClass: class_id(PK), description
1 strings
2 woodwinds
3 brass
4 percussion

tblinstruments: instru_id(PK), description,
1 harp
2 first violin
: :

tblmuscians: mus_id(PK), Name
1 Jack
2 John
3 Ann

tblmuscians_details:
mus_detail_id(PK), mus_id, orchestra_id, class_id, instru_id
1 1 1 1 1
2 2 1 1 2
: : : : :

Thanks for your input.

 

[wazz]

I have five tables:
tblOrchestra: orchestra_id(PK), Name
1 A
2 B
3 C

fine

tblClass: class_id(PK), description
1 strings
2 woodwinds
3 brass
4 percussion

i would call this tblSection with SectionID, Name, etc. just a name change, so, up to you. you could add Conductor if you track conductors.

tblinstruments: instru_id(PK), description,
1 harp
2 first violin
: :

are you tracking sections or instruments here? first violin is a section. violin is an instrument. i would say violin and add a memo field to tblMusician to add specifics. you can search for all violinists this way in case you are in a pinch and need somebody - anybody - to move over to firsts or vice-versa. again, up to you.

add SectionID to add a section for each instrument

tblmuscians: mus_id(PK), Name
1 Jack
2 John
3 Ann

add InstrumentID to select an instrument for each musician. since each instrument has a section assigned to it (above) you will know which section a player is in by the instrument.

tblmuscians_details:
mus_detail_id(PK), mus_id, orchestra_id, class_id, instru_id
1 1 1 1 1
2 2 1 1 2
: : : : :

this doesn't work. "instrumental" details go with the musician and orchestra details go with the orchestra. if a musician can be in more than one orchestra you need another table:

tblOrchMusician
OrchID (FK)
MusicianID (FK)

you can add an orchestra and a musician as many times as necessary to this table. if a musician can only be in one orchestra, add OrchID to tblMusician.

- w

 

[alicejwz]

Re: database design

Hi Wazz,

Thanks for your reply.

I would like to track the sections. Do I have to modify the tblSection?
Are you saying I don’t need the tblmuscians_details and link the tables to get all details on each musician in all three orchestras?

Thanks much!

 

[wazz]

I would like to track the sections. Do I have to modify the tblSection?

no, its fine. you could add a few more "sections" though. although they are not usually considered 'sections' you could include Conductor, Harp, Keyboard and Timpani. (you can add a conductor's name to tblMusician and then select 'Conductor' as its 'section'. that way you can search for all conductors (or keyboardists, or harpists) who have worked with you).

Are you saying I don’t need the tblmuscians_details and link the tables to get all details on each musician in all three orchestras?

put the table aside for now. i don't think you'll need it.
Q. can players play in more than one orchestra? that's the main thing to find out. if yes, then you need tblOrchMusician as described. if not, then you need OrchID as a FK in tblMusician to select an Orchestra for each musician.
Q. many players play more than one instrument. you should have another table tblMusicianInstrument where you can select as many instruments as you want for each player.

- w

 

[alicejwz]

Re: database design

Thanks Wazz,

You are a great help. Thanks for helping me to understand this. Nice to know I'm not too off. Thanks!