Librarians--DB schema for storing book info?

Cafe Society
1

I’m attempting to organize my personal library. I would like to catalog all my books and be able to search for a particular story. I’m sure I want a table with ISBN (or made-up index number) that includes title, publish date, etc. Then another table linked to that with author, since there could be more than one. If it’s a collection, I suppose the author table could include the name of the story which that author contributed. Will that be enough?

table: Book
ISBN, Book Title, Publisher, Date

table: Author
ISBN, First Name, Last Name, Story (same as book if a novel), Function (editor, author, intro, etc.)

They link on ISBN, of course. Now I can just search the “Story” field if I know that (or Author) and follow the link to the Book Title. Then I can find it on the shelf.

What else should I be thinking about storing? And will I need more tables?

2

Don’t make ISBN your key unless you plan to store it with the hyphens. Though it’s rare, it does happen that the numerals of ISBNs can be duplicated, with only the differently-placed hyphens to show that they refer to different books.

That is to say that it is possible to have two books, one with ISBN* 0123456789 and another with ISBN 0123456789. But the first might be 01-23-45678-9 and the other might be 01-234567-89.

From a database point of view, other changes I’d make would be to break Publishers out into their own table and to keep story titles separate from the Author table.

So:

tblBooks (includes Key, BookTitle, ISBN, PublisherID, PubDate, StoryID)
tblPublishers (includes PublisherID, PublisherName)
tblStories (includes StoryID, StoryTitle, AuthorID)
tblAuthors (includes AuthorID, AuthorFName, AuthorLName)

*ISBN-10, obviously.

3

Plus not all books have ISBN numbers - I found this out the hard way when trying to catalog my own library. All my older books are ISBN-less.

4

Book. Publisher. Story. Author. No plurals, no Hungarian. Please.

I’m not sure what function the Story table is supposed to have, but including StoryId as a column in the Book table means that a Book can only have one Story (and I assume this restriction defeats the point of having a Story table in the first place). If a Book can be made of many Stories, you need a join table between the two.

I imagine this schema would end up being annoying to work with, as most of your Book records will likely only have one Story - meaning extra rows in tables that you need to enter but serve no real purpose. What about Books with co-authors? One story, written by several people.

I would probably make a BookAuthor join table, to track that info exclusively. Use the Story table for further detail breakdown if required (and Story can have an AuthorId as well) but tie Book to Author through it.

5

An excellent point.

6

It’s English. A table that keeps track of Books can very correctly be referred to a Table of Books and abbreviated tblBooks. Likewise with the others. Regardless, the OP is free to name his tables WTFever he prefers. I chose the names I would use.

Sorry, I don’t understand your Hungarian comment.

I’ll take your word for it. Perhaps I meant to say that tblStories should have a field to link the book it appears in.

I’ve done all my database work in Access, and I believe Access is completely capable of doing what you require a Join Table for with a Query. I am willing to be shown that I am wrong on this point.

7

ISBNs can be duplicated (usually by mistake), but hyphens won’t fix it. If the digits are the same, the hyphens should be in the same position – and if that were a valid ISBN, the hypens would go thus: 0-12-345678-9 (0 is the language/country group (English); 12 is the publisher (which would be Academic Press); 345678 is the number of the title; and 9 is a check digit).

New ISBNs are 13-digit numbers starting with 978; the next nine digits are the same as the fist nine digits of the old ISBNs; and the check digit is usually different. Many books have both the 10-digit and the 13-digit ISBN; and many older books have neither.

8

Publishers are not always 2-digit numbers. The hyphens are not always in those places. The example I gave of how numerals can be duplicated is based on having seen it happen personally. The numbers were changed to protect the innocent.

Examples from my bookshelf:

Twain, Mark. A Connecticut Yankee in King Arthur’s Court. University of California Press. 0-520-05089-4
Munsterberg, Hugo. The Arts of Japan: An Illustrated History. Tuttle. 0-8048-0042-1.
Shaw, Bernard. The Perfect Wagnerite: A Commentary on the Niblung’s Ring. Dover. 0-486-21707-8.

Didn’t even cherry-pick. These are literally the first three books I pulled off the shelf.

9

Hungarian notation is what I was talking about. Prefixing every table with “tbl” does absolutely nothing for you other than ensure that you press the keys t, b and l much more often. I work in databases a lot, there really is never any need to clarify that a table is, in fact, a table. It’s a peeve.

You’re right though, the OP can name them however he pleases.

That makes much more sense to me - linking a Story record back to one particular Book.

10

Gotcha. Like so many things, one does as one was taught until one has a good reason to change. Didn’t even know that way of doing it had a name.

11

As with everything else these days, there are also web sites that will catalog your library for you. Here is one
http://www.librarything.com/

12

I was just going to post, you really don’t need to re-invent the wheel. There are plenty of programs out there that can take care of this stuff for you. I personally use Book Collector, but there is also Goodreads and Librarything, as well he’s back pointed out.

I don’t know about the 2 websites, but the Book Collector software will search online for an ISBN or book title or author, and give you assorted choices that fit what you search for, so you don’t have to enter as much information. And if you spring for a bar code scanner (You don’t have to use the ones they sell at Collectorz.com) you don’t even have to type in the number.

13

That’s true, but with a 10-digit ISBN, if the first two digits are 01, then the hyphens are in those places. The position of the hyphens depends on the initial digits.

14

You also might want to check out ReaderWare; a few years I picked up their bundled set that included programs for filing music and video collections, along with a free CueCat barcode scanner. The programs also have an online searcher.

15

I used Library Thing to get listings for everything–it’s a pretty easy interface–and then exported to my spreadsheet. LT limits you to 200 books before charging and just my F&SF collection is over 525. Book Collector and ReaderWare are also payware. Goodreads appears to be free.

I’m going to start with Access before I decide I need to buy something. Hence my question re: schema.

16

Here’s the one I use (which is probably more complicated than you want). It’s in Access with a (now sadly out of date) VB front-end.

Category (Sci-Fi, Fantasy, etc - Probably should have called this Genre)
Author (First Middle Last Notes)
Series (Used to collate books in a particular series - Thieves’ World, Amber, etc)

Category and Series are straight linked to book (ie, the CAT_ID is field in the Book table).

Author is linked somewhat more complicatedly. I have four different fields for capturing Author_ID (for mult. author books). I also have an indicator for each Author_ID to determine whether that person is the editor (no check indicates author, checked indicates editor).

For each book, I have Main Title, Sub Title, an Anthology indicator, Number within Series (for sorting purposes), a “Need” indicator (for pre-entering books to develop a shopping list), and, Notes.

The last time I messed with it I was in the process of adding an Edition table (I collect a couple of authors and so have multiple copies of some books) that had Edition, Printing, Impression, Type (Hardback, Paper, etc), Copyright and bunch of other stuff.

To do what you want, all you need to do is add the Story table and link it to Book (ie, BOOK_ID appears as a column in Story). You will also need to link Author to Story (but only use it if it’s an anthology with multiple authors, otherwise the author can be implied from the Book.

If you’ll PM me, I’ll be glad to send you the stripped down database including the queries and such that I used for reporting. Unfortunately, I can’t send you the code as it uses some custom controls that are no longer licensed and thus I can’t get the damn thing to open.

17

I’m curious about these book programs. I used to work for a large, well-known Library Automation Software company and am curious if anyone here knows if these other programs use MARC records?

To the OP:
You can get free MARC records from the Library of Congress web site (God, I can’t believe I remembered that – I haven’t worked in the business for 4 years) and it was pretty easy. You just need to be able to import the files once downloaded.

LCCN’s are the only true unique identifier, but not all books will have them. I forget what year they became mainstream, but most newer books will have them. ISBN’s are notorious for being either duplicated, or wrong. They are not assigned by the Library of Congress, but instead were created by the book vendor companies. You can look up a book in these databases and quite possibly never find your actual book! It was a huge problem back when I was in support. I would say I had several calls/emails a week about why a book wasn’t being found or was being updated incorrectly because of the ISBN. Once the customer found and entered the LCCN, the problem went away.

If you want to learn more about MARC records, you can find data here (might help you create your database):

The above site has TONS of data that might help you. I strongly recommend reading the Understanding MARC booklet as it breaks down MARC records into very easy to understand language.

Here’s a link to a page with tools to help you utilize MARC records:

Here’s where you can look up, and download MARC records:

PM me if you want more info about MARC records and their use. I used to be pretty good at these. I’m sure it will come back to me very quickly…

18

The version of Book Collector does download directly from the Library of Congress, but I don’t know how they do it. The newer version has a different method, but I haven’t used it and am not sure how it works, because when it came out it was not as good at finding things. That was late last year, so I don’t know how it works now.

19

Interesting. Thanks! Seeing this thread has renewed my interest in getting away from Access to manage my own media database and make a real library database so I can find stuff more easily in my own home!