Is it common practice to maintain a 'Available Accommodations' table in a reservation system?

Question

I'm maintaining a reservation's system for a holiday park. I was wondering if it is common practice to keep a Available Accommodations table which could look like this:

Acc    AvailableFrom    AvailableTo
1      7/1/2010         7/31/2010
2      7/8/2010         7/15/2010
3      7/15/2010        7/20/2010

Because I think it is easier to find an available accommodation this way. Everytime a reservation is made or changed, the Available Accommodations table is updated.

Or is this cumbersome and inefficient?

Answer 1

I don't think this is common; availability is usally inferred from the fact that there is no reservation at a specific time. That way, you're always sure that the availability information is up-to-date with the actual bookings.

To make things easier for yourself, you could create a view "Availability" which renders this information.

Now you're about to duplicate information, which is exactly what you shouldn't want in a database, unless you're doing it for very specific, well thought-out reasons.

Answer 2

If possible, try to avoid those tables. You have a table containing your rooms and a related table containing the reservations. Try to use those tables to determine free rooms.

"Available Accommodations" would be a duplication of data and those are always awkward to maintain.

Greets Flo

Answer 3

I'm currently redesigning a reservation system, and it doesn't do this, because you don't actually need to know all the available times.

There are two ways users look for available time:

1. They want to view a calendar of all bookings to get a visual representation of all available times. You don't need to explicitly query available times for this.
2. They want to look for availability of a specific type of "item" at a specific time period. Often the query to check availability is quite simple (simple overlap checks of the time period with the bookings), and an availability table doesn't help that much.

As other people point out, if you keep both bookings and available slots, the synchronization between the two is a weak point. To me this is a case of premature optimization. Build it without an availability table first, but leave the option open to add it later on if necessary. Most likely, YAGNI.