Help with Database structure

[rmiller]

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 detail work.

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)
FirstName, Text
MiddleName, Text
LastName, Text
DateJoined, Date
Phone, Text
Address, Text
City , Text
Zip, Text
EmailAddress
BirthDate, Date
Member, Boolean

CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs)
MemberId, FK
ChoirId, FK
FolderNo
RobeNo
Section
ChoirOfficeId, Fk

CHOIRS DB (This lists the various choirs in the Church)
ChoirId, pk
ChoirName, Text
DirectorId, FK (Pointing to Member DB, Person may not be member of any Choir)

MUSIC DB
CatalogId, PK
Title
Composer
Arranger
Type (Single Copy/octavo or book/collection)
PublisherId, FK
PublisherNumber
VoicingId, FK (From table with possible voicing)
NumCopies
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)

PUBLISHER DB
PublisherId, PK
PublisherName
PublisherAddress
PublisherPhone
PublisherWeb
PublisherContact

MUSIC CLASSIFICATION DB
ClassificationId, PK
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)
PerformanceId, PK
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)
Location
Speaker
Pianist
Organist
Introit
Invocation
Anthem
Meditation
Benediction
(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!!

Rmiller

 

[The_Doc_Man]

When one person can be in more than one choir, you have a one-to-many relationship. Invariably, this means you must have separate tables.

A SIMPLE-MINDED approach (not guaranteed to be perfect) is a linking table something like this:

tblChoir
ChoirID, PK, autonumber or manual entry (sounds like it is a small number)
Info about choir

tblChoirPers
ChoirID, FOREIGN KEY, link to Choir table
MemberID, FOREIGN KEY, link to Membership table

Now, if member 123 is a member of choir 1 and choir 3, you have two records...

123, 1
123, 3

Then, when you drive a report, drive it from the linking table joined to the membership table. To see people and the choirs in which they sing, group this by the MemberID. To see choirs and the people in them, group this by the ChoirID.

BTW, just a personal opinion - shorten your table names and eliminate spaces from the names. Down the road you will thank me for that suggestion.

 

[rmiller]

Help with database structure

Thank you very much. I have made the changes you suggested. I particularly appreciated the additional information on length of table names and how to link these tables to extract the information I will most certainly need.

One would have thought that there would have been a few examples of this type of database around but I have not been able to find any. The good thing is that I will have some experience doing this when I am done.

 

[rmiller]

Database structure

Have one more question. For the table above tblChoirPers that describes the Choir members, what is the best option for a key. Should I use an autonumber or a combination of foreign keys (Ie ChoirID an MemberID) Or can I get by without a key in this table?

What is the downside for not selecting a key here.

 

[The_Doc_Man]

There is an art to key selection. For instance, for your list of possible choirs to which a person could belong, I use this concept:

For small-sized sets (less than 10 distinct items), autonumber is a waste of time and space. Use single-character letters of the alphabet or single-digit byte integers and just ASSIGN the key.

For the many-to-many intermediate tables, which often are just pairs of foreign keys side-by-side, the COMPOUND KEY comprised of the two foreign keys is a perfectly good PRIME key for the intermediate tables. There is no need for an extra field to be a key here since you have two perfectly good candidate members - the person ID and the choir ID which, taken together, cannot meaningfully be duplicated. I.e. if person 31 is in choir 3, he/she is in choir 3 no matter HOW many times the record appears saying (31,3). So by making the combo a unique key, you let ACCESS prevent you from duplicating memberships.

Why was the comment about choir number relevant? Considering that your intermediate table could be no more than 5 bytes per record (1 byte for choir and no more than 4 bytes for person), you can do VERY fast searches and JOIN operations with such a small table - 'cause the smaller the record, the more records will fit in a single record buffer. And that makes a difference to Access.

 

[wazz]

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.

- you need a new table, tblRange with contents: Soprano, Alto, Tenor, Bass, Boy Soprano, Coloratura, etc.
- each choir member (one) may have multiple Ranges (many) - new intermediate table: tblMemberRange
- when you select a member for a choir you select from this table/query that shows the member AND which range they are singing

The table structure I have is as follows

CHURCH MEMBERSHIP DB:

You might want a DateLeft field

CHOIR MEMBERSHIP DB (How do I efficiently track someone in > 1 choirs)
MemberId, FK
ChoirId, FK
FolderNo
RobeNo
Section
ChoirOfficeId, Fk

this might be overkill but you could have a separate table for Folders (1-n), Robes (1-n) and Section (SATB)

MUSIC DB
CatalogId, PK
Title
Composer
Arranger
Type (Single Copy/octavo or book/collection)
PublisherId, FK
PublisherNumber
VoicingId, FK (From table with possible voicing)
NumCopies
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)

- new tables for Composer, Arranger, Type
- what is PublisherNumber (should this be in tblPublisher?)
- you should have an intermediate table tblCatlgClass (Catalogue/Classification) so you can classify a composition under more than one genre. this can be VERY useful if you think you could use some pieces for more than one occasion, which is usually the case. (CatalogID and ClassificationID become the combined PK; remove ClassID from this table).

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)
PerformanceId, PK
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)
Location
Speaker
Pianist
Organist
Introit
Invocation
Anthem
Meditation
Benediction
(etc)

could have new tables for Location, Speaker, Pianist, Organist.

 

[rmiller]

Help with Database Structure

Thanks for all the great responses. Just got back from out of town. I will incorporate these wonderful tips in my database structure and let you know how it turns out. You guys are great.

BTW: The publisher number is a number that appears on each piece of music that uniquely identifies that piece of music with each publisher. However not all pieces of music in our library have a publisher number. Thanks also to The_Doc_Man for the information about primairy key selection. I am learning a lot here.

 

[wazz]

One would have thought that there would have been a few examples of this type of database around but I have not been able to find any.

not really what you are looking for, i think, but there is an app called OPAS that many use. it's quite exhaustive. - w