Solved

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

Posted on 2014-01-28
4
652 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
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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