Room availability

All we need is an easy explanation of the problem, so here it is.

I was wondering what would the best way to manage availability for rooms or beds like in a hostel (Saas).
I was thinking about using MySQL and Elasticsearch.
What would be the best schema for the rooms/bed table and availability table?

Thanks a lot.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

MariaDB-10.5 has a feature Application Time Periods – WITHOUT OVERLAPS who’s example even lists availability for rooms as an example.

The example gives:

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout),
 PRIMARY KEY (room_number, p WITHOUT OVERLAPS)
 );

As an example syntax.

Attempting to overlap a booking will result in an error:

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');
ERROR 1062 (23000): Duplicate entry '2-2020-10-06-2020-10-04' for key 'room_number'

So in your application you need to catch the duplicate key error as an exception and treat that as already booked.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply