Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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)
share|improve this question

closed as not a real question by gnat, maple_shaft May 18 '12 at 11:16

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
So, You Need to Write a Program but Don't Know How to Start "One problem faced by all programmers, but possibly most difficult for some beginners, is getting started on a program...." –  gnat May 18 '12 at 6:46
    
If you get stuck, sometimes its worth having a look at existing solutions to similar problems; you might get some good ideas. There's some great links here for example: Reservation allocation algorithms –  dodgy_coder May 18 '12 at 7:25
    
This is entirely too broad to be answerable in the sites Q&A format. If you have a more specific or focused design question then please post back or you can earn a bit more reputation and ask for some guided help in our chat room. –  maple_shaft May 18 '12 at 11:18
add comment

2 Answers

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 ;)

share|improve this answer
add comment

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.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.