I am designing an application that tracks information on Choir membership and sheet music that we have on file. I am starting
with a database of church members. There are four different choirs and choirs share some members. Some members of some of the
choirs are also not members of our church so I will have to place non members in the member table. I am new to database
design and would like the collective wisdom of this list to tell me of any problems I may encounter before I start doing any
What is the best way to deal with someone who is a member of more than one choir and may belong to a different section in
this other choir (Tenor in one and Bass in another). An individual may also hold different offices in various choirs.
The table structure I have is as follows
CHURCH MEMBERSHIP DB:
MemberId Autonumber (pk)
City , Text
CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs)
CHOIRS DB (This lists the various choirs in the Church)
DirectorId, FK (Pointing to Member DB, Person may not be member of any Choir)
Type (Single Copy/octavo or book/collection)
VoicingId, FK (From table with possible voicing)
UsageId, FK (Where in the service is it appropriate
Location, Text (Where in the filing system, or off site)
ClassificationId, FK (List of classification/genre in table so can update)
MUSIC CLASSIFICATION DB
Classification, Text (Christmas, Easter, general anthem etc)
To be able to track performances and plan services and performances I have the following table.
PERFORMANCE DB (This is to keep track of and plan the regular service)
Pdate, date (Date of Past/Planned performance. Possibly more than one per day)
ServiceTypeId, FK (From table of types of performances – morning service, evening, etc)
I would also like to be able to prepare mailing labels for the various choirs as well as the general membership from this DB.
My primary focus will be on the music. I would like to have an efficient music DB that I may find out what music I do have
and when I last performed them, what options for performance (usage and classification)
Would be grateful for your comments, Thanks!!