Help needed on small hotel database

[StuckInLiverpool]

Hope someone can help.

I need to create (for college) a small database for a hotel. It must prevent double bookings.

I have the following tables:

Customer
BookingStaff
Rooms
Bookings (with composite key: BookingDate and RoomID. It also has CustomerID as a foreign key.)

This works well and prevents double bookings.

However, I want to add on Room Service. I have an Item table with ID, Item and Price. I have a Quantity table with ID, Quantity, ItemID and I need to add an ID from the Booking table. I really need the date here. However, if I add the BookingDate as a Date field and try to link it to BookingDate from the Booking table, I get an error message.

I can't have a straightforward number ID on the Booking table as I need Date and RoomID as a composite key. If I include a BookingID (number field) in there, I can't have a composite key.

Does anyone know how I can have Room Service and Bookings in the same database?

Thanks.

 

[RuralGuy]

Allen's link may help here: http://allenbrowne.com/appevent.html

 

[StuckInLiverpool]

Thanks, RuralGuy. The actual double booking bit is sorted, it's the problem of trying to also charge for room service items.

 

[RuralGuy]

"I need Date and RoomID as a composite key"...is that a course requirement?

 

[StuckInLiverpool]

No, that was to prevent the double booking, so that if someone chose the same date and room twice, it would be rejected.

 

[RuralGuy]

The link I posted was for multi-day stays that might overlap somewhere. You do know you can have multi-field indexes without it being the PrimaryKey, right? Using a date as a key is problematic because of the time portion of a DateTime field. An AutoNumber field as the PrimaryKey would simplify tagging RoomService items to a unique Booking as the PK would be a Booking Number as well. Just my $0.02.

 

[StuckInLiverpool]

Thanks for your reply. I'm not too experienced with Access and don't know what you mean by multi field indexes without it being the primary key. You're right - I'd be much better off with an autonumber ID for the booking, but I don't know how to do that and still prevent them from booking the same room twice. I'll have another look through the attachment you sent above. Thanks again.

 

[RuralGuy]

MultiField Indexes are easy: http://www.btabdevelopment.com/ts/mfi