Since seat can be reserved(taken) everyday, when a user want to see all available seat or in a general case for airbnb or hotel.com when a user search for available hotel rooms with a given date range, how can we efficiently fetch those?
The current design: we will fetch all seats for a movie and for each seat do a join query check if it’s booked and return the noon books ones. This will do range query and join and if we want to check a lot of the available space(for airbnb or hotel.com) case, the join is really slow
My approach: we can pre -generate available space for a year for a user to take, and when a booking is finalized, the space will be taken from the table. And when a user query for any space given a time range, it would query the available space table and and we index those by date to speedup. Then we don’t need to do join to check if available?
what’s the best way to handle this?