I'm designing an house rental application database and I'd like to model the concept of availability for rooms. I'm still in the early phases of development so I'm thinking in terms of entity-relationship diagrams and such but you can also make examples with tables.
In particular, what I'd like to understand better here is how to model (in er diagram) a system where a
user can view a
property page in which is possible to select a
bedroom to book for a certain amount of days.
From my understanding there are two main options:
- Use a
end_dateattributes, so that I store only booked days. The reservation entity has relationship with
bedroombooked from which you can also know the related
bedroomhas a (zero to many) relationship with
availability_daythat is an entity to model a day that can be booked from user. So the property host can register in advance the days that are available for booking for a specific
bedroom. When a day is booked an
availability_dayis removed from the database.
The problem I have with the first option is that I wouldn't know how to make possible for the property host to decide in advance that certain room in certain days can't be booked (are unavailable). Is second option too heavy on memory/bad idea because I store different "calendars" for each room for each property?
Or I could store only reservations (with start/end dates) and just make hosts "hide" their bedrooms when they are unavailable for a certain period?