Small library circulation system?

May 22, 2009 3:59 PM

What's the best way to create a circulation database for a small-medium music score library?

I direct a high school vocal music program.

We have a music library of about 700 choral pieces in box files based on ID number. For most titles, we have between 20-50 copies. We also have several music collection books containing several pieces each. The database is stored in an old FileMaker file that was imported from a CSV from some proprietary library program we used previously. None of this is connected to our school's media circulation system in any way.

Currently it's pretty much a simple approach, one record per title; composer, arranger, voicing, etc. indicated. However I would like to go further and have full circulation capability, which seems like it would necessitate individual records for each single copy. I would like a system where our student librarian can easily check-out music to the singers, as well as check it back in... barcodes may be used... as well providing lists of checked out materials... and essentially keeping library patron records. should be easy to determine at the end of the year what music was not returned, and how much money students owe for it.

I looked at the more common GPLed integrated library systems like Koha and Evergreen but they both seem to be overkill for what I'm doing. Plus I don't have MARC data on individual scores or anything like that. I also don't know if I'm skilled enough to create the db relationships or anything like that. I'm a teacher, not a DBA! But I don't mind putting a little effort into it.

I also looked at OpenBiblio, but it seems like it's barely maintained(?) or at least, I don't know, slightly unsettling.

posted by those are my balloons to computers & internet (8 comments total)

Can you get someone in a CS class at your school to build you a system? It really isn't that complicated, but it does take someone that knows a bit of programming. The solution I would take would be to use C# for a simple GUI & a MS SQL Express database for storing the stuff. The barcode part is probably the hardest & most expensive part. Barcode scanners run probably around $100-$200 for a cheap-ish one that wouldn't be too hard to interface with, but barcode printers are likely to be $400+. Interfacing with the scanner shouldn't be too bad, but it's the part that requires the most systems programming (the scanner should come with some sort of API). A smart high school student should be able to figure it out, though. If you're willing to ditch the barcode system in favor of label printing & hand-entering, the whole system gets a lot easier, and a student should have no problem putting something together over a quarter.
posted by devilsbrigade at 4:41 PM on May 22

I can generate barcodes on the fly already and stick 'em on any old label. That's the easiest part IMO.
posted by those are my balloons at 4:49 PM on May 22

Well, then pick up a barcode scanner & talk to the CS teachers to see if they have anyone in mind for this. This is the kind of stuff that I would have loved to do in high school.
posted by devilsbrigade at 4:53 PM on May 22

Agree that you could do this yourself with minimal fuss.

First thing I would do is think about the data. What does your system need to know? As for database structures, it wouldn't be too hard to figure out a schema. You've already got your tables: as you say— "per title; composer, arranger, voicing, etc". Then you've got your inventory table with foreign keys to each of those "meta-data" tables that you think you might need. That table has an ID. That ID is a number. That number can be translated into a bar code with a bunch of different programs. Print ID (now barcode) on label, stick label on inventory. Now just do the last two steps 699 more times.

To link up students, you'll need a students table and a checkout table with foreign keys to student and inventory. Add a payments table and link to student and checkout.

Now, the front-end. Easiest thing to do would be to hook that database up to a web server and use any front-end language you like to put it on a website. PHP. Java. .NET. Flash. (please don't use Flash)

Get any 'ol barcode scanner... it'll probably work like a keyboard. So, student brings item to librarian, librarian scans label, which enters an ID into the input field of a form on a website that's being served on the school's local intranet that's connected to a database. Librarian presses "enter", record gets written to database, done.
posted by Civil_Disobedient at 6:01 PM on May 22

Thanks Civil_Disobedient.

I would still need some way to extrapolate/create records for every single copy of a title. I.e. I need to know that student Johnny has copy #17 of song #101, when we can't find copy #17.

So if I have 25 copies of piece #101, is there an easy way to generate and update records for that piece?

Like, say I put a typo in the song title. Would I have to edit 25 records? Library systems tend to associate copies of an item with the item itself.
posted by those are my balloons at 6:09 PM on May 22

Relational databases deal with this quite well. You'd have a table for 'titles' and a table for 'items' or 'copies'. Each row in 'items' would have a foreign key (basically an id) in 'titles' for which song title it actually referred back to. Users would check out items, but they'd be tied to the titles, so you wouldn't lose any information, and you wouldn't have to duplicate any data. Any information that related to the song title itself would be stored in the titles table, and any information about the copy in particular would be stored in the 'items' table. Relational databases are set up so you should never have to do grunt work of updating multiple records when you 'shouldn't' have to.
posted by devilsbrigade at 6:34 PM on May 22

Ok, well then assuming I stick with FileMaker, is there a way to automatically generate n copy records & pattern-based barcode IDs given a number of copies owned?
posted by those are my balloons at 7:12 PM on May 22

I would still need some way to extrapolate/create records for every single copy of a title

That's what the inventory table is for. Each item in the inventory has foreign keys to each of the relevant meta-info tables (composer, title, etc.) Every item in the table has a unique ID, so there's no problem with multiple items that have the same foreign keys.
posted by Civil_Disobedient at 9:12 AM on May 23