At my library, if an item is reserved, it can only be checked out by the person who reserved it. Also, the reservation expires if it hasn't been claimed in a set number of days. Also, multiple people can place a reservation for a given item.
Can you be more detailed about how you want your system to work?
Hi. Thanks for the response. We are using the system to check out science kits to teachers in our district and it works fine. However, when they attempt to reserve a kit we have an issue. When we use the check out button and set the date for the reservation the item is checked out to them at that point. The database doesn't allow us to check the item out in the meanwhile before reservation date. We want to allow people to check items out as well as reserve them, but we don't want reservations to conflict with item check out if the dates are not conflicting.
Does that make any sense?
I see, so teachers will reserve an item for specific future dates. I assume then that there might be multiple reservations for an item? If so, then one part that will be tricky is to prevent overlapping reservations, and reservations that start before the current due date.
The immediate problem is not quite as tough, since you'll only need to check that the current check out's due date is not greater than the next upcoming reservation date.
One approach would be to make a separate table for reservations, you could start by duplicating the History table and then remove fields you don't need. You'd want to design a way to move a reservation to a check out, an in that process I would delete the reservation. That will make it easier to "see" the next upcoming reservation date via a relationship.
Is that enough to get you started?