Solved

What is the most efficient mySQL table/data struture for a reservation system?

Posted on 2014-01-28
4
679 Views
Last Modified: 2014-01-31
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
Comment
Question by:lthames
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 250 total points
ID: 39815516
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39815534
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
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 250 total points
ID: 39824244
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
 

Author Closing Comment

by:lthames
ID: 39824491
Thanks for the input!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question