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
reservationentity withstart_dateandend_dateattributes, so that I store only booked days. The reservation entity has relationship withbedroombooked from which you can also know the relatedproperty - Each
bedroomhas a (zero to many) relationship withavailability_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 specificbedroom. When a day is booked anavailability_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?
