Logic behind a booking system?

I'm really new to Oracle Apex and I'm making a booking app for a hotel for my final project and i cant wrap my head around the Booking process for rooms. Lets say i have 2 tables:

1. table named ROOMS that has ROOM_ID, ROOM_NUMBER,PERSONS_PER_ROOM, ROOM_STATUS, ROOM_DATE attributes.(ROOM_STATUS can only be set to 'FREE' or 'TAKEN'
2. table named RESERVATIONS that has ID_RES, DATE_OF_ARRIVAL, DATE_OF_DEPARTURE, PERSON_NUMBER, ROOM_ID_FK

Now my train of thought was to set ROOM_STATUS to 'TAKEN'and DATE_OF_DEPARTURE=ROOM_DATE once the form for RESERVATIONS is submitted. Also to make an automation to set ROOM_STATUS = 'FREE' once ROOM_DATE > CURRENT_DATE And then if i want to make another reservation for the same ROOM_ID but on a later date i could just set a where clause on my LOV in my RESERVATIONS form to show only ROOM_ID-s where ROOM_STATUS = 'FREE' OR DATE_OF_ARRIVAL >= ROOM_DATE. I thought i was smart as hell for that but then i realized that if someone makes a Room reservation from 04.July till 15.July and another reservation from 15.august to 25.august for the same room i wouldn't be able to make a reservation on that ROOM_ID in between 15. July and 15.august.

How do i handle this situation? I looked all over the internet for info on this and yes there are threads on Oracle-s sites and on stack overflow with all kinds of links but none of those links lead to anywhere or the stuff has been removed.

2 Answers

How about yet another table, a calendar? It would have the following columns:

create table reservation_calendar (id_cal number constraint pk_cal primary key, datum date, id_res number constraint fk_cal_res references reservations (id_res) ); 

You'd pre-populate it with e.g. one year's dates for all rooms (so, if this year has 365 days and there are 10 rooms, you'd create 365 * 10 = 3650 rows).

Then, when someone makes reservation, you'd UPDATE that table and set ID_RES column to reservation ID - it'll tell you that room (which can be found via another foreign key from reservations to rooms and room_id_fk column) is taken. If you want to de-normalize the model, that new table could contain the room_id (or even room number) column as well; it would make queries simpler, but - I wouldn't do that, if I were you - I'd rather create a view that joins all 3 tables.

During reservation process, you'd be able to display room statuses between date_of_arrival and date_of_departure, day-by-day; guests would then visually be able to make up their mind and make a reservation for that, or some other period - but you'd actually see what's going on.

I don't understand what is room_date supposed to represent so I can't follow what you wrote about it, nor I have any comment.

Hi you are going about it the wrong way. What you need to do is

1. get all rooms not present in (reservations table with constraints new arrival/departure dates overlaps with existing reservations)
2. So your query should look something like below

SELECT ROOM_ID FROM ROOMS WHERE ROOM_ID NOT IN ( SELECT ROOM_ID_FK FROM RESERVATIONS WHERE (DATE_OF_ARRIVAL< {NEWBOOKING_STARTDATE} AND DATE_OF_DEPARTURE>{NEWBOOKING_STARTDATE}) OR (DATE_OF_ARRIVAL< {NEWBOOKING_ENDDATE} AND DATE_OF_DEPARTURE>{NEWBOOKING_ENDDATE}) ); 

Please let me know if I have missed some corner case here. Also Note that DATE_OF_ARRIVAL and DATE_OF_DEPARTURE in the where clause is for existing bookings. Just saying so that there is no confusion.