I'm designing a sample database for a venue where members can create bookings for venues. I'm looking for a method to prevent double bookings (possibly by creating a button on a form that checks availability). I'm aware of the method where times are stored in a separate table (found this by searching on Google), but that doesn't work for me because members should be able to choose what time they'd like to start their booking.
The values that I want to be unique are: the date, the time and the venue room number. For example,
21/12/14, 16:45, Room 4
should not be compatible with
21/12/14, 16:45, Room 4
because all data is the same, but it should be compatible with
21/12/14, 16:45, Room 3
because the room number is different.
Also, I need to find a way where a person can't book the same room while it is being used for someone else. I've thought about using a start and end time for this, but I'm not sure how I could implement it.
If it helps, then I am using Microsoft Access to create this database.
I'd appreciate any help I can get to either problem, and thanks!
Best Answer
Lets say that we have two venues
AandBwith both a start date/time and a end date/time. We need to check whether their time spans overlap. They overlap if: