double bookings problem

[Mpatha]

I have an assignment for a B&B,I must prevent double bookings.The teacher
gave us an ERD with 4 tables - 1.Guests(guestId,name,address,tel),
2.Bookings(bookingId,arrival,departure) 3.Rooms(roomNo,descr,cost),
4.Booking_Details(roomNo,bookingId[pk&fk]). I have looked at prior posts on
this topic,tried quite a few of the solutions,but nothing works on displaying
error message to the user. I'm starting to think that Booking_Details should
not exist in this db, as I have noticed that most of these solution are based
on 2 or 3 tables and not 4 like mine. Should I drop table Booking_Details?
I'm using MS Access 2003.
Any advice will be greately appreciated.Thanx.

 

[Baz]

The Booking_Details table is there to allow a booking to be for more than
one room. However, the whole design looks a bit strange: for example, how
is a guest linked to a booking?

I'd have thought something like this would be better:

Guests: guestId, name, address, tel
Bookings: bookingId, guestId
Rooms: roomNo, descr, cost
Booking_Details: roomNo, bookingId, arrival, departure.

This assumes that you only need to record the details of the "lead" name on
the booking (which, in my experience, is how all B & B's, hotels etc work).
Your tutor might point out that moving the arrival and departure dates to
the Booking_Details table is a denormalisation leading to redundant data,
but you could counter that by pointing out (i) the difficulty of enforcing
data integrity in his/her design and (ii) that your design allows the
flexibility of having a single booking for multiple rooms for differing
dates (a rare eventuality, certainly, but nice to be able to support it just
in case).

n.b. you could probably make your given design work using check constraints,
but this is an obscure area of Access not accessible through the user
interface. Unless your teacher is an Access expert he/she will probably not
even know of the existence of this feature and, I would think, would
certainly not expect you to know about it.