Use a child table for multiple sub-groups?

[ktalley]

Hello,

I am in process of building a database that will cover most of our university's music department activities and I need some assistance with one area.

I have tables for:

Contacts

Schools

Events

Contact-Event Join Table

The problem I'm having is scheduling the bands for our annual jazz festival. Most schools bring only one band and have only one director, which makes everything simple - I add the director to the Contact-Event Join table and get a list of only the directors participating in this year's festival (and can keep track of that year to year). However, some schools bring multiple bands with the same or multiple directors. Would it be best to add a child table related to the Schools Table where each band would have it's own record related to their respective school. Then each band would be linked to a particular director. I would then add a band to the event instead of the director (and need a band-event join table instead). Is that the best way of going about it?

For example:

Enid High School

Jazz Band I - Class 6A - Director Eric P.

Jazz Band II - Class 6A-E - Director Bob S.

Jazz Band III - Class 6A-EE - Director Robert A.

Mustang High School

Jazz Band I - Class 6A Steven S.

Jazz Band II - Class 6A-E Steven S.

Enid High School and Mustang would still get the total bill for entry fees (itemized by band) and I would still be able to have a list of all the directors.

Other parts of the database are more director focused instead of bands, so this is the only case where multiple groups from the same school are an issue (that I've run into so far).

It seems like this would work, but I wanted to run it by the experts first. Thanks for your assistance

kt

[comment]

I think it depends on how many things are common to entries from the same school, and how many are different. For example, you could have the join table linking Schools to Events, and make Bands a child table of the join.

[ktalley]

Thanks for your reply. I think that would work. Some schools have multiple directors and not all directors from each school would be directing bands at the jazz festival. But if I had a child table from a Schools-Events join as you suggest, I could still get a set of participating directors by assigning them to a band from a school.

Thanks for your suggestion