?
Solved

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

Posted on 2014-01-28
4
Medium Priority
?
703 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 1000 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 48

Expert Comment

by:Dale Fye
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 1000 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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 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