Running into a couple of problems, if any one could give some ideas on how to do it

Coffee Lounge
1

I currently am designing a booking system.
I have two tables:

  1. Dates (dateID, date, day, closed) - Already populated with dates
  2. Times (autoID, dateID, time, is_booked) - Already populated with dateID and times

What I need to do is for example:
If someone books 1-4 people then I need to find a half hour slot that is available.
However if someone books 5-8 people then I need to find an hour slot that is available.
In the ‘Times’ table I already have slots fill with times and dateID for the next 20 years.

I am getting lost in how to search for the hour slot (which would be two records one after another, as long as they are not booked)

If anyone can suggest the best way to do this it would be appreciated.

2

For efficiency and performance, you should be looking at utilising SQL window functions - they’re available in most SQL databases.

https://mode.com/sql-tutorial/sql-window-functions/

2 Likes
3

Wow.
Mode is a lot of fun!
Valuable resource!
Thanks for pointing this out!
So this is only for SQL strict queries, correct?
Is there a mysql or postgresql online equivalent?

1 Like
4

All leading SQL DBs have windowing functions. Once you get to grips with them, they’re extremely powerful. We’ve built a highly performant diary search and filter function that has saved our users hours weekly finding and scheduling patients.

5

Did you utilize the Mode platform for this backend?

6

No. We’re postgres and redis and SQL (including window functions for our diary). I just liked that article as a soft intro for @gunnery

1 Like