Database Schema for the Library

Question

I am developing a library management system for a department at my university, and I wanted to enlist your plan. This post is primarily about how we keep several copies of each book; something about what I developed is rubbing me wrong, and I hope you all can point out the best ways to solve problems.

To work with users browsing books, I developed three tables: a book , a client, and book_copy . The relationships between these tables are as follows:

• Each book has many book_copies (to avoid duplication of information about the book while maintaining the fact that we have several copies of this book).
• Each user has many book_copies (the other end of the relationship)

The tables themselves are created like this:

------------------------------------------------ book ------------------------------------------------ + id + title + author + isbn + etc. ------------------------------------------------ ------------------------------------------------ customer ------------------------------------------------ + id + first_name + first_name + email + address + city + state + zip + etc. ------------------------------------------------ ------------------------------------------------ book_copy ------------------------------------------------ + id + book_id (FK to book) + customer_id (FK to customer) + checked_out + due_date + etc. ------------------------------------------------ 

Something about this seems wrong (or at least ineffective for me) - the perfectionist in me feels that I am not normalizing this data correctly. What are you talking about? Is there a better, more efficient way to develop this circuit?

Thanks!

Answer 1

This circuit is OK. However, he does not model the possibility that there may be several different presentations in the work, that is, the book can have many issues (both translations and formats).

How you cut it - the granularity that you use depends, as always, on data modeling, on your use. In other words, is it "true", for you, for your use, that the German translation of "Alice in Wonderland" is "different" from the English orginal? (Yes, maybe). Is the paperback version "different" from the hardcover?

The simple answer to this is simply to use ISBN as the key - letting the publishing industry make these decisions for you. Then everything, with the same ISBN, is equal and interchangeable.

You may also want to model something like “an acceptable substitute,” “this ISBN is an acceptable replacement for this because the only difference is mandatory” or “this ISBN (Darwin Origins 6th edition) is the sixth version of this one (original Darwin “Origin of Species”) or “this ISBN is a translation of this” or even “this ISBN (KJV Bible) is like this (Bible NIV).” It penetrates into subtle gradations.

Another, more fundamental problem is that copies of the same book are combined with checks of these copies. If you unfortunately ordered 10 copies of, say, Herb Schildt The Annotated ANSI C Standard, but they are not graciously tested, because your Uni students read Pete Seebach an excellent overview of this scary book, what is the customer_id for these copies in book_copy

You want (at least) tables for the book (work, isbn); copy; user; and the user-checksout-copy relation.

Answer 2

Few things.

I think the only obvious normalization error is one of the tpdi noted: you should not combine the book_copy object with the checkout record. The layout should be written in a separate table that cross-references the client to book_copy:

---

Photo

• customer_id (FK: customer.id)
• book_copy_id (FK: book_copy.id)
• date_checked_out

The due_date function is not strictly required in this table, since you can always calculate the payment date by looking at check_date_date and adding, however, how many days the name can be checked. This raises the topic of having different types of media and various restrictions on the statement. For example, DVDs may have a limit of 1 week, while books have a limit of two weeks. To track this information, you will have another table:

---

checkout_limit

• media_type
• checkout_limit_days

And if you have several types of media, then of course you should think about whether you want more tables (one for each type of media) or want all types of media in one table (then you will have to rename it from " books "into something in common). In the latter case, you will have redundancy, as some types of media will not have attributes that are of other types (for example, books have ISBNs but no DVDs).

Answer 3

What about the main titles table for books that were supposed to be offered by tpdi on ISBN numbers that would have a title, author, and other details. The book sub-table can be a list of actual inventory where you have a primary key for each copy (10 entries if you have 10 copies of the standard annotated ANSI C) and a foreign key in the titles table, which links these copies to the corresponding title and ISBN number. Finally, the checkout table will be your many-to-many relationship in which the user ID is associated with the book ID (not the title).

The main difference between the way you have done this so far and the way I suggest is that you moved customer_id from the book table and used the new check table. You still have time for each book, showing whether it is or not, but to find out to whom it is provided, you should consult the extract table.

Answer 4

I think he is trying to get him to request any available copy of the book.
Say 10 copies of Herb Schildt Annotated ANSI C standard, there will be 10 entries in the book_copy table.
I'm right? therefore, when the student checks the book, in particular copy No. 3, he can simply put the student_id on the student.
The remaining 9 entries will still have an empty client_id.
This will certainly simplify your queries, but this is not a good practice.
What others have indicated is correct, you need some kind of transaction table, a check table to keep track of any books released.