Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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

618 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