[RESOLVED] Library Management System

[Nitesh]

Hi Guys,

I am creating a Library Management system. I've got the basic tables like members, books etc. I'm wondering how I should handle borrowing and returning of books. Should I use one table or two? Please advise.

[rjv_rnjn]

I would say two tables.
1.) to maintain transactions with fields, BookId, MemberId, BorrowedDate, ReturnDate, IsCheckedOut (or IsBorrowed, whatever makes sense).
2.) To maintain active check outs. This table will have a row inserted every time there is an insert in transactions table above and a delete every time there's an update to the table above (to set IsCheckedOut to false).

[FunkyDexter]

I would disagree with the second table, that's a denormalisation and just means you have to maintain checked out books in two places instead of one.

Instead you just need the transactions table with BookID, MemberID, CheckedOutDate and CheckedInDate.

To see checked out books you just look for transactions with a checked out date but no checked in date.

[rjv_rnjn]

I would disagree with the second table, that's a denormalisation and just means you have to maintain checked out books in two places instead of one.

Right. For a moment I guess I forgot about views.

[techgnome]

a table for books, a table for borrowers, and one table for check outs & ins..... the checkout table would have an ID, the book ID, the borrower id, the checkout date, and a due date... the check-in date would be left null, until the book is checked in.

-tg

[Nitesh]

Thanks guys. Will go with tg's answer

[Nitesh]

hey techgnome,

a table for books, a table for borrowers, and one table for check outs & ins..... the checkout table would have an ID, the book ID, the borrower id, the checkout date, and a due date... the check-in date would be left null, until the book is checked in.

I'm using the table for borrowers as you suggested. I need to be able to reserve a book as well if it is out. How should I handle this?

[techgnome]

Expand the check out table ... add a Reserved field, then when a book is reserved... set the flag, and don't set a checkout date...

-tg

[Nitesh]

thanks techgnome