mibeach7
Actually, although the SQL statement looks ugly, you have provided some beautiful information -- you provided us with context and your objective. This type of information is much more helpful -- it helps us avoid dead-end alleys and brick walls.
My first comment is that you are using a separate scheduling table for each stylist -- much, much easier to use only one stylist. Why? Well now you can query one table to find your info. For example - with three tables, finding out who is not working at 6:15 PM, you have to query each table. For example - your business takes off, and you hire two more stylists - do you add two more tables, edit all relevent queries, tables and forms?
A more typical approach would be to add the Stylist ID number or CODE to the schedule table.
...Now for a much tougher issue, which is probably how you got where you are. How to create the schedule(s).
This tough because there are several ways how to do this, and each one is complicated.
- You can use exceptions. Only track booked time slots.
- Have a schedule for each stylist and flag them as available, booked or not working
- Have a schedule for each "seat" in the salon, and assign the stylist to the "seat" for each working time slot. And assign a customer to the "seat".
The last idea seems to be the best. However, I know that stylists often only work at one "seat", and other stylist do not share. In a sense this seems almost like assigning booking to a stylist. But you can assign a free time slot to a seat without worrying about the stylist.
There are variations of this, and to be honest, at this time I do not know which is best. For example, indicate a sytlist is avaiable on a certain date from start time to end time.
Regardless, I would also move the "booking" times to a table -- this would allow you to "edit" time slices, and simplify your coding.
You can then create a template for each day of the week, and use the template to create schedules for each date. For example, use the TUE template to create a schedule of available bookings for July 13, 2004.
...Moving on
I setup a test database. The design was as follows...
StylistTbl
StylistID - primary key
StylistLN - last name
StylistFN - first name
+ info on the stylist - phone number, etc.
DayOfWeekTbl
DayOfWeek - primary key, text, 3 letters - SUN, MON, TUE...
DOWNumber - numeric 1, 2 ... for SUN to SAT, specific for coding
TimeSlotTbl
SlotID - primary key
StartTime - date (time, medium format)
EndTime - date (time, medium format)
TaskTbl
TaskCode - primary key, text, CUT_YTH, CUT_ADL, PERM_1
TaskCost
ScheduleTbl
SchedID - primary key
StylistID - foreign key to Stylist table
SchedDate - date (medium format)
DayOfWeek - foreign key to DayOfWeek, not sure if this is required
SlotID - foreign key to Time Slot table
Booked - text, name of customer
BookedPh - phone number or other info on customer
TaskCode - foreign key to Task table
...Now this is just at the top of my head, but I think it works.
I created a sample SQL statement using Query builder - looks like the following...
Code:
SELECT S.StylistLN, D.SchedDate, D.DayOfWeek, T.SlotStart
FROM DayofWeekTbl as DW
INNER JOIN (TimeSlotTbl as T INNER JOIN (ScheduleTbl as D
INNER JOIN StylistTbl ON D.StylistID = S.StylistID)
ON T.SlotID = D.SlotID) ON DW.DayOfWeek = D.DayOfWeek;
With sample data (I used 30 min slots, not 15 min), it gave me...
[tt]
StylistLN SchedDate DayOfWeek SlotStart
Hope 07-Jun-04 MON 9:00 AM
Saunders 07-Jun-04 MON 9:00 AM
Morgan 07-Jun-04 MON 9:00 AM
Hope 07-Jun-04 MON 9:30 AM
Saunders 07-Jun-04 MON 9:30 AM
Morgan 07-Jun-04 MON 9:30 AM
Hope 07-Jun-04 MON 10:00 AM
Saunders 07-Jun-04 MON 10:00 AM
Morgan 07-Jun-04 MON 10:00 AM
Saunders 07-Jun-04 MON 10:30 AM
Morgan 07-Jun-04 MON 10:30 AM
Saunders 07-Jun-04 MON 11:00 AM
Morgan 07-Jun-04 MON 11:00 AM
Saunders 07-Jun-04 MON 11:30 AM
Morgan 07-Jun-04 MON 11:30 AM
Hope 07-Jun-04 MON 12:00 AM
Hope 07-Jun-04 MON 12:30 AM
[/tt]
Pretty rough, but I think this may be a better direction than the one you are heading.
Comments?
Richard
