# Librarians--DB schema for storing book info?
**URL:** https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712
**Category:** Cafe Society
**Created:** [August 25, 2009, 11:56pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712 "2009-08-25T23:56:40Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@NoCoolUserName](https://boards.straightdope.com/u/NoCoolUserName)
#### Post date: [August 25, 2009, 11:56pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/1 "2009-08-25T23:56:40Z")
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?
---
### Author:  [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)
#### Post date: [August 26, 2009, 12:03am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/2 "2009-08-26T00:03:14Z")
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.
---
### Author:  [@Athena](https://boards.straightdope.com/u/Athena)
#### Post date: [August 26, 2009, 12:20am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/3 "2009-08-26T00:20:12Z")
> [@KneadToKnow](#):
>
> 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.
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.
---
### Author:  [@Discipline](https://boards.straightdope.com/u/Discipline)
#### Post date: [August 26, 2009, 12:21am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/4 "2009-08-26T00:21:26Z")
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.
---
### Author:  [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)
#### Post date: [August 26, 2009, 12:22am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/5 "2009-08-26T00:22:09Z")
> [@Athena](#):
>
> 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.
An excellent point.
---
### Author:  [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)
#### Post date: [August 26, 2009, 12:27am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/6 "2009-08-26T00:27:21Z")
> [@Discipline](#):
>
> Book. Publisher. Story. Author. No plurals, no Hungarian. Please.
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’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’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.
---
### Author:  [@Giles](https://boards.straightdope.com/u/Giles)
#### Post date: [August 26, 2009, 12:34am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/7 "2009-08-26T00:34:16Z")
> [@KneadToKnow](#):
>
> 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.
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.
---
### Author:  [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)
#### Post date: [August 26, 2009, 12:39am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/8 "2009-08-26T00:39:40Z")
> [@Giles](#):
>
> 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).
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.
---
### Author:  [@Discipline](https://boards.straightdope.com/u/Discipline)
#### Post date: [August 26, 2009, 12:52am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/9 "2009-08-26T00:52:49Z")
> [@KneadToKnow](#):
>
> Sorry, I don’t understand your Hungarian comment.
[Hungarian notation](http://en.wikipedia.org/wiki/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.
> [@KneadToKnow](#):
>
> 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.
That makes much more sense to me - linking a Story record back to one particular Book.
---
### Author:  [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)
#### Post date: [August 26, 2009, 12:55am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/10 "2009-08-26T00:55:03Z")
> [@Discipline](#):
>
> [Hungarian notation](http://en.wikipedia.org/wiki/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.
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.
---
### Author:  [@well\_he\_s\_back](https://boards.straightdope.com/u/well_he_s_back)
#### Post date: [August 26, 2009, 1:54am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/11 "2009-08-26T01:54:43Z")
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/](http://www.librarything.com/)
---
### Author:  [@Lok](https://boards.straightdope.com/u/Lok)
#### Post date: [August 26, 2009, 2:03am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/12 "2009-08-26T02:03:13Z")
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](http://www.collectorz.com/book/), but there is also [Goodreads](http://www.goodreads.com/) 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](http://Collectorz.com)) you don’t even have to type in the number.
---
### Author:  [@Giles](https://boards.straightdope.com/u/Giles)
#### Post date: [August 26, 2009, 8:30am UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/13 "2009-08-26T08:30:28Z")
> [@KneadToKnow](#):
>
> Publishers are not always 2-digit numbers. The hyphens are not always in those places.
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.
---
### Author:  [@LurkMeister](https://boards.straightdope.com/u/LurkMeister)
#### Post date: [August 26, 2009, 3:04pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/14 "2009-08-26T15:04:44Z")
> [@Lok](#):
>
> 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](http://www.collectorz.com/book/), but there is also [Goodreads](http://www.goodreads.com/) 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](http://Collectorz.com)) you don’t even have to type in the number.
You also might want to check out [ReaderWare](http://www.readerware.com/); 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.
---
### Author:  [@NoCoolUserName](https://boards.straightdope.com/u/NoCoolUserName)
#### Post date: [August 26, 2009, 3:13pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/15 "2009-08-26T15:13:28Z")
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.
---
### Author:  [@Zakalwe](https://boards.straightdope.com/u/Zakalwe)
#### Post date: [August 26, 2009, 3:42pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/16 "2009-08-26T15:42:44Z")
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.
---
### Author:  [@FalconFinder](https://boards.straightdope.com/u/FalconFinder)
#### Post date: [August 26, 2009, 4:14pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/17 "2009-08-26T16:14:01Z")
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):
> **[MARC STANDARDS (Network Development and MARC Standards Office, Library of...](https://www.loc.gov/marc/)**
>
> The MARC formats are standards for the representation
> and communication of bibliographic and related information in machine-readable form.
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:
> **[MARC Records, Systems, and Tools (Network Development and
MARC Standards...](https://www.loc.gov/marc/marctools.html)**
>
> This page provides links to MARC tools that supply MARC systems Services for the MARC 21 formats. (Network Development and MARC Standards Office, Library of Congress)
Here’s where you can look up, and download MARC records:
> **[LC Catalog](https://catalog.loc.gov/)**
>
> Find material in the Library's collections of books, periodicals, manuscripts, maps, music, recordings, images, and electronic resources. Search by keyword or browse for authors/creators, subjects, names/titles, uniform titles, and call numbers.
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…
---
### Author:  [@Lok](https://boards.straightdope.com/u/Lok)
#### Post date: [August 26, 2009, 4:32pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/18 "2009-08-26T16:32:39Z")
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.
---
### Author:  [@FalconFinder](https://boards.straightdope.com/u/FalconFinder)
#### Post date: [August 26, 2009, 4:55pm UTC](https://boards.straightdope.com/t/librarians-db-schema-for-storing-book-info/507712/19 "2009-08-26T16:55:17Z")
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!