I am writing a web based reservation system for a campground/rv park and I'm just not sure of the best way to save the data for the dates/sites that are reserved so that queries that return date/site availability are not slow.
We will have a table for guest information (like a customer table), a table of information for the sites that can be reserved, and a table for the reservations.
Should I store a site number, beginning date, and number of nights in the reservation table? Or store a site number, date, and reservation number for each DATE reserved in a separate table?
I am going to want to be able to pull availability in 3 ways -
1 - Enter a date plus a number of nights and see all available sites (let's say 300 sites maximum)
2 - Enter a site number and see all available dates.
3 - View a master calendar (table format) for one month with days across the top and all sites down the rows, with the availability shown in Green (available) or Red (already reserved)
Thanks for any suggestions!