Small Library Project - Design Advice?

[michaeljohannes]

Hello Access Architects!

I am in the process of creating a small-scale database (suitable in Access) that tracks my client's music collection. This is not a CD collection but rather a sheet music collection for a community choir with 20 - 25 singers.

To make things really simple, I will have a table that has the following criteria:

Composer_name
Composer_dates (birth and death dates)
Title
Season (when it's appropriate to perform)
comments

etc... (perhaps more fields)

What I'm curious about is how to set up the database so the main user of the database (likely only going to be two people - the conductor and the librarian) can 'check out' pieces of music for the 20 - 25 people.

Example: Conductor wants to sign out 20 copies of Title_of_music. The conductor knows that 5 are not checked out by looking at the record of the individual piece of music.

Another form will be created so he can see which pieces in his database are checked out, and how many of each are not currently in the library.

I liken this project to a video store, or a community library with many copies of each book.

He has approximately 2000 records (pieces of music) to track.

Can anyone suggest what sort of procedure I can use to track the signing out of each piece of music? I hope my example is clear!

Thank you for all your great ideas in advance

Mike

 

[The_Doc_Man]

You were doing fine until you started the check-in, check-out part. The problem this engenders is simple. You need an inventory.

To make life simple, you could try something like a child table depending on the sheet music title OR on a synthesized inventory number. You would include the number of copies in the parent record. Add this ID number. It can be arbitary but must be unique per sheet music title.

Now have a child table where you have fields:

Checkout:

SMID - sheet music ID
WhoCkOut - name / initials of who checked out
CkOutCount - number of copies checked out.
CkOutInAgain - Y/N field saying - OK, they are back now.
CkOutNotes - any special comments.

Then, when you go looking to check out that ID, you look for Checkout records not yet checked back in for that ID and for which the number available (as shown in the parent table) minus the number checked out (in the checkout records) is such that you cannot check out what was requested.

This is not the excruciatingly technically precise way to do it, but you are keeping it small.

If you bought more sheet music matching something you already had, you could easily edit that count field that shows the total number owned. In a "true" inventory system you would do it another way.

 

[Jurjen]

track each copy and normalize everything

From my 20 year experience i have learned to always fully normalize the datamodel. It will result in a more solid system, that is easily expandable.

You are dealing with the following entities:
Music Title
Composer
Season
Copy
Person

My first advice would be to make separate tables for Composer and Season, and relate them to the Music Title table. It will enable you to quickly (and correctly!) list all music titles of a specific composer and/or a specific season. It might not be a functional requirement right now, but in my opinion it could be very handy. If you store the composer and the season in text fields your model is not fully normalized and if the spelling is not consistent, you would have a problem in filtering specific music titles.

So we start with three tables:

TITL (Music Title):
titl_id (long) = primary key
titl_text (text)
comp_id (long) = foreign key to COMP
seas_id (long) = foreign key to SEAS

COMP (Composer):
comp_id (long) = primary key
comp_name (text)

SEAS (Season):
seas_id (long) = primary key
seas_name (text)

Now lets look at the copies to be checked in or out. I do not fully agree with the following approach, advised in the previous post:

Checkout:

SMID - sheet music ID
WhoCkOut - name / initials of who checked out
CkOutCount - number of copies checked out.
CkOutInAgain - Y/N field saying - OK, they are back now.
CkOutNotes - any special comments.

Then, when you go looking to check out that ID, you look for Checkout records not yet checked back in for that ID and for which the number available (as shown in the parent table) minus the number checked out (in the checkout records) is such that you cannot check out what was requested.

First of all this Checkout entity describes actually an individual checkout event of a certain number of copies. If i want to know if the number of available copies minus the checked out copies is sufficient, i will have to summarize the CkOutCount field of all the checkouts where CkOutInAgain = False. Since the number of checkout event records will increase over time, this is not optimally efficient. To avoid this calculation it would be better to maintain an extra field in the Music Title table that is updated whenever copies are checked out or returned.
Secondly, suppose the conductor hands out 4 copies of a specific title on monday, and 5 on tuesday, this would create two checkout events. If 3 copies are returned on saturday, there is no way to administer this, since the CkOutInAgain flag supposes that all copies that were checked out were returned.

So my following advice would be: add two fields to the TITL table in which you store the original total number of copies and the available number of copies. Whenever a certain amount is checked out or returned you update the available number:
titl_copies (byte)
titl_avail (byte)

Finally, to administer to checkout and return of copies, i would design a separate entity for Copy and Person. So you can track who gave which copy to whom. Translated into tables:

COPY (Copy):
copy_id (long) = primary key
titl_id (long) = foreign key to TITL
pers_idF (long) = optional foreign key to PERS, the person who handed out the copy
pers_idT (long) = optional foreign key to PERS, the person to whom the copy was handed out

PERS (Person):
pers_id (long) = primary key
pers_name (text)

When a copy is checked out, the pers_idF and pers_idT fields will get a value, when it is returned their value will be Null. In this way you can also easily obtain a list of copies that were handed out to a person, and not yet returned.