Prevent Double Bookings

[kingofspades]

Hello,

I am supposed to make a database for a hotel system, I was wondering how I could prevent double bookings. I have searched plenty of forums but I haven't really got the idea.

My Reservation Table has the following fields:
Reservation No. (PK)
Room No. (FK)
Customer ID (FK)
Payment ID(FK)
Reservation In Date
Reservation Out Date



Together with preventing double bookings can someone also tell me if there is a way automatically that can mark in the Room Table, the status as "available" or "booked" automatically by looking at the date today?

Thanks In Advance

[ranman256]

Thats kinda tough...normally ROOM#, INDATE keys would stop this, but it must include the dates between.

So I would key the fields on my reservation table: ROOM#, DATE
this would prevent any room getting booked on the same date BUT at the point-of-entry when the user clicks BOOK ROOM...
the program would have to append every date between start and end date for this to work.

book: cust#12 ,R123, 5/1/14 to 5/4/14

the app must append to table
cust#12 ,R123, 5/1/14
cust#12 ,R123, 5/2/14
cust#12 ,R123, 5/3/14
cust#12 ,R123, 5/4/14

thus NO dbl bookings.

[kingofspades]

is there any way i can do this? sorry im a beginner :/

[recyan]

Check, if below gives some guidelines :

http://www.baldyweb.com/OverLap.htm

Thanks