The first decision is whether you need to book rooms for *part* of a day,
e.g. for "late departure" or just an afternoon booking. If you need to do
that, things are complicated by the need to handle bookings for specific
times.
If you can avoid that, the minimum granularity for a booking is 1 day. You
can then create a record in a booking detail table for each day of a
booking, and it makes it very easy to see if there are clashes.
You could start with these tables:
Room table (one record for each room that can be booked):
RoomID Number primary key
Client table (one record for each person/company who will be invoiced):
ClientID AutoNumber primary key
IsCorporate Yes/No whether this is a person or a company
MainName Text company name or surname.
FirstName Text Christian name (persons only)
Address Text Number and street to show on invoice
City Text
Zip Text
...
Booking table (one record for each time a booking is made):
BookingID AutoNumber primary key
ClientID Number who pays for this booking
ContactInfo Text who to talk to about this booking
Notes Memo special requests, etc.
...
BookingDetail table (one record for each date in a booking):
BookingDetailID AutoNumber primary key
BookingID Number which booking this row belongs to.
RoomID Number which room is booked
BookedDate Date/Time which date this room is booked for.
ContactName Text the person responsible for this room.
PersonCount Number how many will occupy this room on this date.
This structure copes with things like:
- extended bookings (where a room is required over several nights);
- change of room during stay (since the RoomID is part of the detail table);
- group bookings (where a booking covers multiple rooms, even where some
people are staying different dates).
It also makes it very easy to see if a room is available on a date. For
example, room 99 is available on February 2 if this expression returns Null:
DLookup("BookingDetailID", "BookingDetail", _
"(RoomID = 99) AND (BookedDate = #2/2/2006#)")
If you cannot use the 1-day granularity, the BookingDetail table will have
fields:
StartDateTime Date/Time The date and time this room booking starts
EndDateTime Date/Time The date and time this room booking ends
instead of the BookedDate field.
To find out if there is a clash you will then need to use the logic that 2
events clash if:
A starts before B ends, and also
B starts before A ends.
And for finding existing clashes, see:
Clashing Events/Appointments'
at:
http://allenbrowne.com/appevent.html
There will be additional tables if you also need to track:
- the names of each person associated with each room on each date;
- the fees payable;
- the payments received;
- additional fees such as in-hotel restaurant, phone, minibar;
- tiered fee structures (e.g. for regular clients or extended stay);
- source of booking (such as responses to advertised specials)
and so on.
