I am designing a reservation system booking system. I want to put as much logic into the database as possible so it is independent from the application. One of the constraints is that a room can not be overbooked. I have tried some different ideas of how to do it.
The idea is that you have rooms that can be either reserved or can be booked in with a guest. But, the rule is that no room can be booked by either a reservation or a paid booking more than once.
One idea, break the bookings into reservations and paid guests. The issues I had with that idea is that is was difficult to join the views and the double-book functions were duplicated for two tables.
Two, add a booking table which must have either a reservation dependency id or a booking dependency id. This seems to me the best way to handle the data because it seems that bookings should be a table to themselves. But, the triggers to enforce it get much more complex in this scheme.
Three, denormalize the tables into one. The triggers become very simple but what the data means becomes convoluted and a new field has to be added to denote what kind of booking it is. The field paid_from, paid_to is somewhat of a lie if it is reservation.
Any thoughts one what would be the 'best' way to handle this situation would be appreciated.