I have two tables for hotel booking. One is rooms table where I save room type, hotelId and total number of rooms that the hotel has for that type.
I have Bookings table where I keep record of all the reservations.
|1001||2||100||3||2023-03-17 14:00||2023-03-20 11:00|
|1002||2||100||2||2023-03-17 14:00||2023-03-20 11:00|
|1003||3||100||1||2023-03-17 14:00||2023-03-20 11:00|
When a user tries to book a room of a specific type(say premium) in a hotel. I do below two things:
- Using sql query, get the sum of number of all the premium rooms booked in the hotel from Bookings table and subtract it from the TotalRooms of the premium room for that hotel from Rooms table. This gives me the number of available rooms.
- If available rooms > 0, I insert a new booking record in the Bookings table.
How can I prevent double booking in this scenario? For example, when there is only one premium room left and two users try to book it for the same DateTime, step 1 may get that available rooms is 1 for both users and then step 2 may insert duplicate record in Bookings table.