Booking system (Handle working hours, Get available time slots) [closed]

Question

I'm currently working on a time booking system for a friends hair salon. The plan is to try to keep it as simple as possible. The user view (where the owner see the booked appointments) is more or less complete so now it's time to start working on the view where the customer book their appointments.

The thing is, it feels a bit overwhelming and I really don't know where to start.

Problem 1 (work schedule):

The system is meant to be used by two persons and their working hours could differ a bit. What would be the best solution to store their work schedule? I did plan to place them in a own table but I couldn't come up with any good table layout.

Table example:

tbl_workschedule
    int_id              
    int_dayOfWeek       
    int_user_id     
    time_startTime  
    time_endTime

If I use this kind of layout I could end up with table content like the one below. The feeling I get is that it doesn't look like a good solution?

int_id  int_dayOf Week  int_userID  time_startTime      time_endTime
1       1               1           08:00               17:00
2       2               1           09:00               18:00
3       3               1           08:00               17:00
4       4               1           11:00               19:00
5       5               1           12:00               20:00
6       6               1           08:00               17:00
7       7               1           00:00               00:00
8       6               2           07:00               16:00
9       6               2           12:00               20:00
10      6               2           08:00               17:00
11      6               2           08:00               17:00
12      6               2           12:00               20:00
13      6               2           08:00               17:00
14      6               2           00:00               00:00

Problem 2 (time booking):

My basic idea is:

• The customer sign in and choose what kind of service he/she wants. I have a table which contains the different services and how long time each service takes (see db structure in the end of the post).

• The customer choose which hair dresser he/she want.

• The customer choose a date when he/she wants to book an appointment.

• I fetch the working hours for the choosen hair dresser.

• I fetch all booked appointments for the chosen hair dresser that specific date.

• In some way I have to collect all free slots and place them in a array.

• I loop through the free slots array and compare it against the time the specific appointment takes. If I find a free slot that matches I collect it in another array.

• I display the different time proposals to the customer.

First of all, is this a good way to handle it?

My basic idea (more detailed info):

• Fetch the working hours for the specific date and place them in two variables $dayStart, $dayEnd.
• Run a loop that checks the time difference between $dayStart and the start time of the first appointment of the day. If the slot time is more than, let's say the timescale of the service that takes the smallest amount of time I place it in the free slot array. For the next loop sequence I take the end time of the first appointment instead of the $dayStart time and do the same check as described above.
• When the whole day is done I have all free slots in a array like this: array(array(amount of free time, slot start time, slot end time)) E.g: array(array(04:00, 08:00, 12:00))
• Then I, as I wrote above, need to compare the free slot array against the timescale of specific appointment. Here I'm stuck.

---

Anyone that could guide me in the right direction?

Thank you in advance

Database

Tables:

tbl_events

- int_eventID (INT)
- int_serviceID (INT)
- date_eventDueDate (DATETIME)
- date_eventCreationDate (DATETIME)
- int_userID (INT)
- int_customerID (INT)
- int_eventOnlineBooked (INT)

tbl_customers

- int_customerID (INT)
- int_userID (INT) 
- str_customerFirstName (VARCHAR)
- str_customerLastName (VARCHAR)
- str_customerEmail (VARCHAR)
- str_customerPassword (VARCHAR)
- str_customerCellPhone (VARCHAR)
- str_customerHomePhone (VARCHAR)
- str_customerAddress (VARCHAR)

tbl_services

int_serviceID (INT)
str_serviceName (VARCHAR)
str_serviceDescription (VARCHAR)
int_servicePrice (INT)
int_serviceTimescale (TIME)

tbl_users

int_userID   (INT)
str_userFirstName (VARCHAR)
str_userLastName (VARCHAR)
str_userEmail (VARCHAR)
str_userPassword (VARCHAR)
str_userCellPhone (VARCHAR)

Answer 1

You're finding here that it's a tricky problem to solve well. That's because it is. I've built systems like this, and they're always fairly complex (that said, this is one of the simpler ones).

Presumably you know how long each appointment should take, so you'll just have to loop over the free slots array and remove any that the appointment will not fit inside (you may wish to apply a fudge/efficiency factor, to cover yourself in case an appointment overruns, but YMMV).

Then you can just present the remaining free slots to the user and let them choose.

Now, if you were trying to plan the most efficient day possible for your staff - that's when it gets complicated ;)

Answer 2

You are almost there.

Instead of having an array of "slots" and a "start time" "end time" for each headresser.

Have a table "slots" keyed by hairdresser_id and start-time, with a single "booked By" field.

When you create a hairdresser's schedule just insert all the slots where they are avaialble for the foreseeable future, with a "null" booked by column. When a customer books simply update to row to have the customer id in the BookedBy column.

You will need an Admin function to knock out slots for holidays,sickness doctors appointments etc.

The rest of the system should easily fall into place.