SQL database issue

Question

I have a select statement showing the following results:

On_loan barcode
Y       12345
Y       12345
N       12345
N       12344
Y       12344

Each barcode for a book can have more than one copy. Users can place a book on hold. E.g user '1' has reserved book 12345 and 12344. The above results show: that the two books with barcode 12344- one is available, the other is unavailable. I want to be able to show two regions in PHP, the top showing books that are ready to take out(that were on hold) and the other showing books that are unavailable which have been placed on hold. From my select query i now want my select to check to see for each barcode 12345 and 12344 whether a book has been returned. If it has i will then use the hold_date to see if its the earliest Hold for the specific book.

I understand on_loan informs me whether a book has been returned, however how can i use 'N' from on_loan for each book. I believe distinct will not work.

How can i go about doing this.

My Hold table has the following fields:

user
isbn
hold_date

Answer 1

That's not quite a good database design, if you are asking this kind of questions. First of all, you should transform this to the third normal form databse. Then it will look like three tables: books (name, barcode, available_count), users(user_id, name) and a relationship table users_to_books(user_id, book_barcode, state), where state can be an enum whith values "on hold" and "on hands". After that you can do all kind of stuff with counting and checking.

Answer 2

Maybe you should have a bookTitle table with ID, Barcode, link to barcode tables and then you could do a query to return all copies of a bookTitle and use queries to return barcodes that are on loan and not.

The ID makes it unique.

Answer 3

I think you are asking for a way to check if a recently returned book is on hold for another customer, correct?

The book should actually have a unique barcode per each physical copy of a book in the library, as well as an ISBN for the book in general.

Holds would be placed by ISBN. When a book is checked in, enter that copies barcode, then pull its ISBN number and see if another customer is waiting for it. If so, set the status for that copy to 'hold', create a related library book to hold record relation. Otherwise, set the book status to checked in.

Assuming there is a table 'copy' that has a record for each physical copy with unique barcode and relates to a table called 'book' that has info about a book like ISBN and Author etc, and a table called 'hold' that has the hold info an ISBN (or better, book.id)

Here are the all the copies that are checked in and have a hold on them.

select * from copy left join book on book.id = copy.book_id where copy.status_id = get_book_status('in') and book.isbn in (select isbn from hold);