[RandomlyKnighted]

Hello everyone,

I'm currently starting a new project that lets the user add books to a database to create their own miniature library. I've got a rough draft design for the database and would like some opinions. I tried to think of everything with the database that way the program could be used by both an home user or by a business. Here's what I have currently:

book table
bookID - int, autoincrement, PK, FK -> user.bookID & booksCheckOut.bookID
title - varchar(100), FK - > user.title(plus number which is determined programmatically) & booksCheckedOut.title
author - varchar(50), FK -> booksCheckedOut.author
subject - int
isbn10 - varchar(10), FK -> user.isbn10 (plus number)
isbn13 - varchar(13), FK -> user.isbn13 (plus number)
quantity - int
quantityInLibrary - int
quantityCheckOut - int
summary - varchar(1000)

user table
userID - int, autoincrement, PK
name - varchar(50)
bookID1 - int, FK
bookTitle1 - varchar(100), FK
isbn10_1 - varchar(10), FK
isbn13_1 - varchar(13), FK
bookID2 - int, FK
bookTitle2 - varchar(100), FK
isbn10_2 - varchar(10), FK
isbn13_2 - varchar(13), FK
bookID_3 - int, FK
bookTitle3 - varchar(100), FK
isbn10_3 - varchar(10), FK
isbn13_3 - varchar(13), FK

booksCheckedOut table
checkedOutID - int, autoincrement, PK
userID - int, FK
name - varchar(50), FK
bookID - int, FK
title - varchar(100), FK
author - varchar(50), FK
isbn10 - varchar(10), FK
isbn13 - varchar(13), FK

Opinions?

P.S. The book information in the user table is so if someone wants to lend a book out to someone else when they view the persons information it will display the 3 most recent books removed from the library.

This post has been edited by RandomlyKnighted: 19 June 2012 - 11:42 PM

[macosxnerd101]

Why do you have three books listed under the users table, if the booksCheckedOut table links users to the books they checked out?

Edit- I just saw your edit. If you are looking to see the three most recent books a user checked out, then you can use SQL to query the booksCheckedOut table based on dates. You can use the LIMIT keyword to limit results, with the ORDER BY keyword on the dates.

[RandomlyKnighted]

The booksCheckedOut table is just a way to keep up a running list of when a book is checked out and by who. Books are never removed from this list. When someone checks out a book it is added automatically added to this table. The books listed in the user table was designed a way to have a list of what they currently have checked out. When books are checked in, they are removed from this table. So the book info would have to be able to be null as well.

Hope that helps explain it a bit.

Edit: Ha, just saw your edit. So would I need to add a timestamp to the booksCheckedOut table?

This post has been edited by RandomlyKnighted: 19 June 2012 - 11:51 PM

[macosxnerd101]

Your booksCheckedOut table should really only associate the user_id, book_id, time_out, time_due, and time_in. The other tables have the remaining information. If you need to gather additional information, you may need to query multiple tables and possibly use a join.

The whole idea with relational database design is to minimize redundancy. If the information exists in one table, it doesn't need to exist in another.

[RandomlyKnighted]

So just use a join to get all the information that you want to display?

Next question, you suggested doing the books checked out by date. That's fine for just keeping a running list, but what if you wanted to view their "profile" and wanted to see what they currently have checked out? If you go by date then you risk leaving something out. For example, let's ay I have book that is 5 months overdue, and 1 book that I checked out last week and you set it for the last 30 days. Well then the overdue book wouldn't be visible. So what if I had something like isCheckedOut with a boolean value in the booksCheckedOut table. That way you could just use a WHERE to only get the list of what is checked out. What do you think?

It's been 2 years since I created a database from scratch. I can't believe how much I have forgotten.

[macosxnerd101]

If the book hasn't been checked in, then the time_in field would be null. You can use an IS NULL check when querying that field.

[RandomlyKnighted]

Ah, true. Works for me.

Here's an updated version of the design:

book table
bookID - int, autoincrement, PK, FK -> booksCheckOut.bookID
title - varchar(100)
author - varchar(50)
subject - int
isbn10 - varchar(10)
isbn13 - varchar(13)
quantity - int
quantityInLibrary - int
quantityCheckOut - int
summary - varchar(1000)

user table
userID - int, autoincrement, PK, FK -> booksCheckedOut.userID
name - varchar(50)

booksCheckedOut table
userID - int, FK
bookID - int, FK
time_out - varchar(50) - variable liable to change depending on the format of the timestamp, PK
time_in - varchar(50)
time_due - varchar(50)


That certainly looks simpler. Did I leave anything out?

This post has been edited by RandomlyKnighted: 20 June 2012 - 12:36 AM

[macosxnerd101]

I wouldn't make the time_out the PK. What if two clerks are checking the same book out at the same time? A composite key would be better here, using the book_id and user_id.

[RandomlyKnighted]

I assumed that the time would go down to seconds or milliseconds. I thought about book_id and user_id but would it not cause problems once you check out multiple books? I mean you'd have multiple entries with the same primary key. Wouldn't it conflict?

[e_i_pi]

Well, your design has come a fair way since your first post, but I think it can be normalised out a fair deal. A lot of the information you're storing in tables isn't normalised enough, and will end up being a chokepoint or a roadblock in your design. If you approach DB design in the same way you approach OOP, it becomes easier to conceptualise.

Let's start with the basic unit - books.

Books

ID (int PK)
Title (varchar)
AuthorID (int FK->Authors.ID)
ISBN10 (varchar)
ISBN13 (varchar)
Summary (text)

Notice how I've made AuthorID a FK? This means we need an Authors table:

Authors

ID (int PK)
FirstName (varchar)
MiddleName (varchar)
LastName (varchar)

This allows us to quickly search the DB for specific authors' books. But where have categories gone? Shouldn't they be in the Books table? Well, can a book not have multiple categories? I believe it can, so what we need is a categories table, and then a "bridging table" to connect categories to books:

Categories

ID
Description

BookCategories

BookID (FK->Books.ID)
CategoryID (FK->Categories.ID)

Now we can create one-to-one, one-to-many, many-to-one and many-to-many relationships between books and categories.

Next you're probably wondering why I haven't put quantities in the books table. Well, that's because the books tables is storing information on the abstract concept of a book. If we want to store information on an actual real-life in-the-flesh book, we create a seocnd table. Why? Have you ever been to a library that has had multiple copies of the one book? I know I have...

BookCopies

ID (int PK)
BookID (int FK->Books.ID)
DeweyDecimal (varchar) {in a real library system, you'd want a better method than storing as a varchar, since the dewey decimal system is a robust normalised schema of it's own}
DatePurchased (date)

You could add more attributes here, such as the condition the book is in, for instance, or even whether it is a hardcover or softcover. I'm not sure about books, but if hardcover and softcover share the same ISBN, I would put this info in the BookCopies table - if they have differing ISBNs I'd put it in the Books table.

Well, that's books done, at least the basics of books. Now on to users. The users table should be pretty simple at it's most basic:

Users

ID (int PK)
FirstName (varchar)
MiddleName (varchar)
LastName (varchar)
DOB (date)
{any other info you'd want to store}

Now things start falling into place easily. Why? Because now we can assign a specific book to a person, like this:

Checkouts

ID (int PK)
UserID (int FK->Users.ID)
BookCopyID (int FK->BookCopies.ID)
DateLoaned (datetime)
DateDue (datetime)
DateReturned (datetime NULL)

Now there's a little trick of the trade in there. Notice how I've set DateReturned to be nullable? That's how we track whether a book is currently available for loan or not, and here's how we can get a working inventory of our library:

-- All books owned by the library
SELECT DISTINCT
    Books.Title
FROM Books
INNER JOIN BookCopies ON BookCopies.BookID = Books.ID

EXCEPT

-- Books currently loaned out
SELECT DISTINCT
    Books.Title
FROM Checkouts
INNER JOIN BookCopies ON BookCopies.ID = Checkouts.BookCopyID
INNER JOIN Books ON Books.ID = BookCopies.BookID
WHERE Checkouts.DateReturned IS NULL

ORDER BY Books.Title

This schema is far more robust, and is much more easily queried when it comes time to ask the hard questions to the DB.

This post has been edited by e_i_pi: 20 June 2012 - 04:09 AM

[RandomlyKnighted]

Sorry, due to school and work I was just now able to take a look at your post.

Wow, seems like you definitely simplified it. You even mentioned the things that I forgot about.

[RandomlyKnighted]

Hello again, I finally found some time to start implementing the database and I noticed some things in the PHPMyAdmin that I didn't remember from when I orginally learned about databases. Could someone explain these to me?

Collation
Index

[e_i_pi]

Collation is the character set used on the column/table. An index helps speed up queries if it is applied correctly. Indexes are automatically in place on Primary keys, and you should generally always place indexes on foreign keys (i.e. - relations to other tables). Going overboard with indexes, though, can lead to a lot of rebuilding of indexes, which will slow down your DB.

This post has been edited by e_i_pi: 06 July 2012 - 12:17 AM

[RandomlyKnighted]

Just now got a chance to start implement the database. Does this look right to you?

[e_i_pi]

At a glance that looks right. You probably want to index up the BookCategories bridging table, I can't see why (bookID, categoryID) can't be a Primary Key, which would help when querying against books/categories. Also you could probably put some unique constraints on some things (ISBN10 for example) though this is really more about data maintenance than modelling or efficiency.