I am working on a booking system for an hotel where owners can edit Room schedules.

For storing the dates when a Room is not available, my first intuition was a table Occupancy:

Occupancy.create from: "01-04-2022", to: "31-05-2022", room: @room
Occupancy.create from: "15-04-2022", to: "15-05-2022", room: @room

I will end up with overlapping Occupancies, but thats ok.

But now, I would like the owner to be able to choose to make the Room vacant during the first weekend of April.

And I don't see how to do that with this schema, unless creating another table, or adding a column to Occupancy.

So, I was thinking of storing an array of occupied dates in a column of the Room table, using maybe json format.

But I feel I might struggle when I will need to query the available rooms for a given period.

It doesn't sound the right tool for the job.

CodePudding user response：

I worked on a booking system in the past that was modeled similarly to your current approach, and it worked fine. We had an equivalent to Occupancies called Bookings and had a second table of InternalHolds that were used for the usecase you're describing, as well as for other tasks like cleanings or maintenance.

This worked perfectly fine for a long time. Eventually, we ended up extracting out a denormalized table of bookable calendar days, but it was derived from the bookings and holds.

At larger scale and on a longer time horizon, you may end up with some dedicated search infrastructure outside of Postgres (think geographic search, amenity filtering, and full-text search), which will require this kind of denormalization anyway.

I would hesitate to jump straight to a JSON column approach, as the querying would just be more tricky. You'd either be stuck writing tricky non-portable SQL for a lot of queries; or loading every occupancy, for all rooms, for all time to be able to answer a query like "What rooms are available for a 3 night stay starting Tuesday".