What is the most efficient mySQL table/data struture for a reservation system?
Hi!
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the input!
I would prefer to store the Departure_Date, rather then [Duration], but that is just a preference as you can easily compute the Departure Date from [Start Date] and [Duration].