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.
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
- int_eventID (INT) - int_serviceID (INT) - date_eventDueDate (DATETIME) - date_eventCreationDate (DATETIME) - int_userID (INT) - int_customerID (INT) - int_eventOnlineBooked (INT)
- 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)
int_serviceID (INT) str_serviceName (VARCHAR) str_serviceDescription (VARCHAR) int_servicePrice (INT) int_serviceTimescale (TIME)
int_userID (INT) str_userFirstName (VARCHAR) str_userLastName (VARCHAR) str_userEmail (VARCHAR) str_userPassword (VARCHAR) str_userCellPhone (VARCHAR)