Database Table Design

  • (2 Pages)
  • +
  • 1
  • 2

21 Replies - 1724 Views - Last Post: 25 July 2012 - 01:39 PM Rate Topic: -----

#1 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Database Table Design

Posted 19 June 2012 - 11:40 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Database Table Design

#2 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10568
  • View blog
  • Posts: 39,127
  • Joined: 27-December 08

Re: Database Table Design

Posted 19 June 2012 - 11:42 PM

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.
Was This Post Helpful? 1
  • +
  • -

#3 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 19 June 2012 - 11:50 PM

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. :P 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

Was This Post Helpful? 0
  • +
  • -

#4 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10568
  • View blog
  • Posts: 39,127
  • Joined: 27-December 08

Re: Database Table Design

Posted 20 June 2012 - 12:08 AM

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.
Was This Post Helpful? 0
  • +
  • -

#5 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 20 June 2012 - 12:23 AM

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.
Was This Post Helpful? 0
  • +
  • -

#6 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10568
  • View blog
  • Posts: 39,127
  • Joined: 27-December 08

Re: Database Table Design

Posted 20 June 2012 - 12:28 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 20 June 2012 - 12:34 AM

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

Was This Post Helpful? 0
  • +
  • -

#8 macosxnerd101  Icon User is offline

  • Self-Trained Economist
  • member icon




Reputation: 10568
  • View blog
  • Posts: 39,127
  • Joined: 27-December 08

Re: Database Table Design

Posted 20 June 2012 - 12:40 AM

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.
Was This Post Helpful? 1
  • +
  • -

#9 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 20 June 2012 - 12:43 AM

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?
Was This Post Helpful? 0
  • +
  • -

#10 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Database Table Design

Posted 20 June 2012 - 04:05 AM

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

Was This Post Helpful? 4
  • +
  • -

#11 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 25 June 2012 - 10:41 PM

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.
Was This Post Helpful? 0
  • +
  • -

#12 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 05 July 2012 - 08:14 AM

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
Was This Post Helpful? 0
  • +
  • -

#13 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Database Table Design

Posted 06 July 2012 - 12:07 AM

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

Was This Post Helpful? 0
  • +
  • -

#14 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 23 July 2012 - 11:48 AM

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

Posted Image
Was This Post Helpful? 0
  • +
  • -

#15 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Database Table Design

Posted 23 July 2012 - 04:35 PM

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.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2