[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

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!
0
lthames
Asked:
lthames
2 Solutions
 
UnifiedISCommented:
A reservation needs to have site identifier, guest identifier and date(s). For your usage, I'm leaning towards a secondary reservation table where each record consists of a reservation id, site id and and 1 date. The primary reservation table will hold the guest id. This will allow a guest to reserve multiple sites under a single reservation.  Availability can be easily checked for a site/day combination because there is a single record within the secondary reservation table.
0
 
Dale FyeCommented:
I don't think you would need the secondary table.  With siteID, GuestID, StartDate, you would have a unique recordset, no matter how many sites the guest books for a particular date.

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].
0
 
JimFiveCommented:
I'm with UnifiedIS on having a row for each date reserved.  This will speed up the calendar calculation and make overlap very easy to find.  It may be reasonable to denormalize a bit and have both a Reservation Table(ReservationID, GuestID, SiteID, StartDate, Duration) AND a Reserved Table (ReservationID, DateReserved)  That depends on how the cost for maintaining that table compares to the speed improvement for calendar creation.

I want to say, though, that 300 sites times 365 potential reserved nights is only 110,000 reservation days a year, that is not a huge amount.  Assuming reasonable design and indexing, your web code is likely to be more of a bottleneck than the database.
0
 
lthamesAuthor Commented:
Thanks for the input!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now