Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Question...
Database Question...
#1
Peggy and I are in the beginning stages of setting up a multimedia library database under SQL Server to catalog our books, DVDs, CDs, etc.  We did this once for just our library about 15 years ago, when our collection was only about three or four thousand books, using DataEase.  Now we want to catalog everything -- and use it as a chance for me to get a good amount of SQL Server experience.
The question I have for the community at large is this:  Although I'm currently designing a db to handle all our different media, it would make things much faster if we were to use a pre-existing design.  Does anyone know of an open/free library database schema capable of handling multiple media types that we could make use of?
Thanks!
-- Bob
---------
Then the horns kicked in...
...and my shoes began to squeak.
Reply
Re: Database Question...
#2
It depends on how complex you want it to be.  I've been looking into using Koha 3 http://koha.org/ as I currently have over 5,000 books and 600 DVDs.  I'm running the demo version under vmware server http://kylehall.info/index.php/projects ... appliance/.  I just deleted all the sample data and started to put in my books.  They do have a copy for VirtualBox if you want that instead.
Reply
 
#3
If you have any version of Microsoft Office that includes Access, one of the sample databases that comes with it is a media library.  While I can't vouch for the naming scheme or methods Microsoft chose, it's something you can set up and look at the structure of.
Frankly, a schema for a media library doesn't have to be very complex.  What I've found is a fast, easy way to approach creating a schema is to treat it as a classic object-oriented programming problem, and boil every instance down to a base class and derived classes.  Once you have that on paper, you can translate that to a relational database very easily.
Just off the top of my head, a Media base table (with a unique ID value) would hold the basic information about any form of media that you wish to track, the stuff that's common to all forms.  Title (indexed for fast searching), possibly author, that sort of thing.
A second table (MediaDetails, perhaps) would reference the Media table by ID, and contain a type identifier of some sort (which should itself be a reference to another table -- VHS, CD, DVD, talking fish, whatever), plus perhaps notes about that particular work.
Finally, third-level tables would be defined for each media type, holding all the 'class-specific' details about each one.  DVDs, for example, might have a flag about bonus tracks (if you want to get to that level of detail), while an entry for a Book type might include page count or publication date or whatever.  You'd likely want to write a view that collapses all this in the background into something you can work with in the UI, but that's hardly difficult.
The advantage to this method is that it allows you to define a particular mixed-media work (say, a boxset of anime on DVD that includes a CD soundtrack and physical art book) and still index each item contained within separately according to its particular type, while counting it as one item in your overall collection.  Also, it allows you to tune your data storage to what you actually have, rather than having to either include lots of columns that are rarely used, or possibly worse, insert free-form data and have to ignore the power of the DB system (storing all your fields as varchar, for example, while flexible, means you're wasting space AND ignoring data typing, which can bite you down the road).
That's just off the top of my head, mind you.  There are probably better schemas (and this is a very bare-bones one, at that), but I don't know of any directly and you -did- specify getting SQL experience... Big Grin

--sofaspud
--"Listening to your kid is the audio equivalent of a Salvador Dali painting, Spud." --OpMegs
Reply
 
#4
Thanks, Spud -- that's very similar to what I was starting to sketch out myself, but I didn't think of setting it up to include multiple media in the same work. I'll have to revise what I've got now.

And BMull, thanks, too. I took a quick look at Koha, and while it's probably far more powerful than we need (and doesn't give me the coding experience) it's certainly worth studying, if only to find features I want to implement.
-- Bob
---------
Then the horns kicked in...
...and my shoes began to squeak.
Reply
 
#5
I was going to suggest looking at what Tellico does in its built-in default "getting you started with X collection type" options, but it looks like you might not need that, and it might even be off down the wrong road...
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)