MySQL Forums
Forum List  »  Triggers

Restaurant sql reserervations with base schedule availability and exceptions
Posted by: Gregory Premdani
Date: December 02, 2013 08:02AM

Im working on a project where users can place reservations with restaurants, but only on the dates that the restaurant allows. The restaurant can also set how many seats are available at a certain time.
So a restaurant can supply a schedule which is formatted weekly but also can supply exceptions (like holidays).
What I need is a complete representation of the available ranges and seats for a given time frame.
So for example a restaurant has a week schedule that says that every weekday they have 10 seats available, but also has a couple reservations I need the output like this so I can generate a calendar where the user can see the availability of the restaurant.


+---------------------+---------------------+-----------+
| start | end | available |
+---------------------+---------------------+-----------+
| 2013-02-14 08:00:00 | 2013-02-14 17:00:00 | 10 |
| 2013-02-15 08:00:00 | 2013-02-15 12:00:00 | 8 | <= 2 reservations
| 2013-02-15 12:00:00 | 2013-02-16 15:00:00 | 4 | <= 6 reservations
| 2013-02-15 15:00:00 | 2013-02-16 15:00:00 | 7 | etc...
| 2013-02-16 15:00:00 | 2013-02-16 17:00:00 | 4 |
| 2013-02-17 08:00:00 | 2013-02-17 17:00:00 | 10 |
| 2013-02-18 12:00:00 | 2013-02-18 18:00:00 | 10 |
+---------------------+---------------------+-----------+

reservation lasts only an hour but i need a complete representaion of the available ranges and how many seats there are a available. so lets say that a restaurant has a maximum of 10 seats available on weekdays between 08:00 and 17:00, if there is a reservation on 12:00 till 13:00 for 2 people, the output has to be 08:00 - 12:00 with 10 available seats, 12:00 - 13:00 with 8 available seats, 13:00 - 17:00 with 10 available seats,

Options: ReplyQuote


Subject
Views
Written By
Posted
Restaurant sql reserervations with base schedule availability and exceptions
2422
December 02, 2013 08:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.