CODEWITHSUNDEEP
sql
user1042244
user1042244

Reputation:

Bit field in Mysql?

I have a simple question related to database schema, how can I check whether a student has borrowed a particular book, let say I have a table Borrowtable and I have Student_id,Book_id.... as attributes of that table, what else i should add in order to keep track whether the student_id has borrowed a book ???

I am thinkin to add another field like Status as INT field and values like 1 for borrowed and 0 not borrow. Is this good idea or there is another way of doing this?

Thankx

Lulzim.

Upvotes: 0

Views: 159

Answers (4)

Michael Berkowski
Michael Berkowski

Reputation: 270775

Edited for two scenarios

Scenario 1: not storing a borrowing history:

This applies if you have no need to keep track of the user's borrowing history. Borrowed books are one-off events, and are not remembered.

If you already have a Borrowtable containing a map between Student_id and Book_id, there is no need for another column to indicate borrowed or not borrowed.

Instead, you can simply rely on the presence of the row containing the student and book. If the row exists, the book has been borrowed. If the row has been deleted or does not exist, the book has not been borrowed.

To test if a book is available for borrowing by any user, simply check if the Book_id exists anywhere in the table. If it does, the book is unavailable.

Scenario 2: store a user's borrowing history:

If you need to keep track of what a user has borrowed, add two more columns, each of DATETIME type to track the borrowed date and the returned date for the book.

To test if a book is available for borrowing, use a query like:

SELECT Book_id FROM Borrowed WHERE Book_id=12345 AND NOW() BETWEEN Borrow_Date AND Return_Date;

If any row returns, then the book is currently borrowed and unavailable for use.

Upvotes: 2

Scott Ferguson
Scott Ferguson

Reputation: 7836

Perhaps you should have columns for DateBorrowed and DateReturned in the Borrowtable. You do not need a bit field to indicate whether a student has borrowed the book. The existance of the row for a given student and book should be enough.

Upvotes: 1

Akhil
Akhil

Reputation: 7610

Your Table seems to be a Many-to-many table (between Student & Book tables). You could assume a student borrowed the Book if its in this table (student either borrows the book or he doesnt). If there are more possible options, then you need to have one more column and have a StatusID.

Upvotes: 0

Dawood
Dawood

Reputation: 5316

You probably don't need additional columns. As the name BorrowTable implies, you can just store books that are borrowed in this table. If they're returned or not borrowed, you can remove them from this table.

Upvotes: 3

Related Questions