Hotel booking system: optimal SQL db structure for queries
I'm going to create a hotel booking system.
Each hotel has several RoomTypes.
RoomType: id | name | hotel_id
Each hotel offers several types of RoomTypes for a specific period (subject to date_from
and date_to
). Also each client has the opportunity to make a reservation of certain amounts of certain RoomTypes for the period ( date_from
and date_to
).
I need to be able to find and display available Offers for a given hotel, know the number of free (offered) rooms for each RoomType for each day, request the minimum number of available rooms, etc.
I would like to ask for advice on how I store the data . What is the best solution? I know that some queries (for example, displaying the number of free rooms of a given type for each day in a given range) cannot be achieved with a simple SQL query unless I use stored procedures. However, I would like to do this as quickly and simply as possible.
so far i have considered:
-
keep
RoomOffer: hotel_id | date_from | date_to | quantity | room_type_id
the same with redundancy -
have
RoomOffer: hotel_id | date | quantity | room_type_id
the same with redundancy, i.e. when creating RoomOffer / Reservation, create a single record for each day within the specified range.
any advice?
source to share
I am assuming that it RoomType
refers to the same room and also that the primary key for each room is a tuple (hotel_id, room_type_id)
, since you are using both fields for RoomOffer
.
However, I do not recommend that you use the RoomOffer
and approach Reservation
. First of all, because you store a lot of unnecessary information: when a room has not yet been booked, you need a room offer to say that it is available (or worse, a lot of them, because you divide it by time ranges) that you already know.
Instead, I would suggest something more like this:
I know from your question that you are concerned about system performance, but usually this kind of design optimization is not a good idea . This will likely lead to a lot of redundant data and related classes. However, you can improve the performance of your database queries with indexes or even without SQL approaches. This is something that you can better appreciate at a later stage in the project.
source to share