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
reservation
entity withstart_date
andend_date
attributes, so that I store only booked days. The reservation entity has relationship withbedroom
booked from which you can also know the relatedproperty
- Each
bedroom
has a (zero to many) relationship withavailability_day
that 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_day
is 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?