Schedule Database

[sammy]

Hey Guys,
I am trying to create a schedule database for employee that do their jobs
based on a 30 minutes blocks. example
employeeA starts shift at 9:00 AM and Ends at 5:30 PM this employee would
have 2 service calls to do at client's location service1 from 10:00 AM to
12:00 PM and service2 from 1:30 to 3:30 PM.
I have employee table with all employee info like ID, Name, address
ServiceCalls table with callID employeeID ClientID TaskID and tasksLength
ClientsTable with all client's data
EmployeeSchedule table with employeeID, ShiftStartTime, ShiftEndTime
DaysTable with all DayID, OnDutyOrOff,

What i am trying to do is build a query to pull up the first available
employee to perform a specific task. my question is does the database design
make any sense to anyone, if not whats the best way to to achieve this goal?

Thanks for any input
Sammy

 

[Allen Browne]

Sammy, there are 2 basic ways to build this kind of schedule database, and
the one you outline is one of those, so yes, your approach makes good sense.

If you are certain that jobs will always be booked in 30-minute blocks, the
alternative is to create a record for each 30-minute block of the
appointment. With this approach, your appointments table would have 4
entries for service1, i.e. 10:00, 10:30, 11:00, and 11:30. The advantages of
this approach are that it makes it incredibly simple and efficient to see
when someone is free, find clashing appointments, and print schecules. It is
usually the preferred design where possible, though it is impractical if you
need the flexibilty of appointments that do not work in specified blocks.

If you go with the block design, I suggest you store the time as an integer
representing the number of 30-minute blocks since midnight, e.g. 0 =
midnight, 1 = 12:30am, 2 = 1:00am. This avoids the problems inherent in
trying to match floating point values (which is how the date/time field is
stored.) You will also want a table that defines the time blocks that make
up a shift.

If you go with your more flexible design, you might be interested in this
article:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

 

[sammy]

Thanks for the reply Allen. This is what I have so far.
EmployeeTable
EmployeeID --->Primary Key
EmployeeFName
EmployeeLName
EmployeeLocation
EmployeePhone

Calls Table
CallID --> Primary Key
Sku ---> links to Sku table
Status
CallDate
Notes
AssignedTo --->link to employee table
CustomerID --->Link to customers table

CustomersTable
CusID --PrimaryKey
CustomerFName
CustomerLName
City
Phone

Schedule Table
SID -- > Primary Key
EmployeeID --> link to Employee
StartTime "DateTime field" 9:00 AM"
EndTime "DateTime field "5:30 PM"

Sku table
SkuID -->primary Key
Duration "number field" 2 = 1 hour, 3 = 1 hour and 30 minutes
Description
Price
taxable "yesNo filed"

after doing this I find myself drawing blank when it comes to the rest. I
know I need at least 2 more table for the schedule but I have no idea where
to start.
any help would be greatly appreciated

Sammy

 

[Allen Browne]

Sammy, are you storing appointments (assignment of service calls, what you
expect to happen), or work (what actually happened)?

I suspect you will find that what actually happens (and therefore is
billable) will not be the same as what is anticipated. Some calls you expect
to take 2 hours may take 4, and others may be done in 1.

The sku table suggests you are trying to generate invoices out of this. Or
perhaps you need tables for both appointments and for actual work, and for
the resultant invoices (with invoice detail)?

 

[sammy]

Allen,
The nature of this business is like this. A customer calls requesting
service, the operator types in what the customer's best date and time and do
a search to find out the nearest appointment with the first available tech
and dispach the agent with an email with the customer's info.
I dont have a problem splitting the sku table to allow another table to
carry the work and invoices . my struggle is with the schedule. these techs
are scheduled based on 2 shifts "9:00 AM to 5:30 PM" and "12:00 PM to 8:30
PM". Yes sometimes some of the calls would take more than the allocated time
or less time but I dont see anyway to accommodate this in the database.
I hope this makes things clear and Thank you for your patience and desire to
help

 

[Allen Browne]

So you have 2 stages in order to establish who is available to meet the
customer's needs:
a) Who is scheduled on at this time.
b) Who amongst those scheduled on at the time has no booking for any part of
that period.

a)
The person is scheduled on at the time if the worker's shift starts before
the appointment time starts, and ends after the appointment time ends.

b)
The person is not available if both:
- they have another appointment that starts before this one ends, and
- this one starts before the other appointment ends.

As explained previously, you can simplify (b) by recording each block of
time as a record on its own, which then makes it dead simple to check for
each of the blocks you need for this client's appointment.

One interesting sideline is that this logic will never find anyone available
for the job if the client wants someone from 11:30am to 6:00pm.

 

[sammy]

Thanks again Allen,
I am afraid I am not following you when you say "As explained previously,
you can simplify (b) by recording each block of
time as a record on its own, which then makes it dead simple to check for
each of the blocks you need for this client's appointment."
Can you provide me with an example of what you mean please? like the
structure of a table and some sample data.

Thanks

 

[Allen Browne]

TimeSlot table has just one field:
TimeSlotID Number pk (primary key)

Each timeslot is half an hour, so enter values a record for each number
between 18 to 40. 18 half-hours after midnight is 9am--your first timeslot.
40 half-hours after midnight is 8pm--the start of your last timeslot in a
day. This defines the valid timeslots in your day.

ServiceCall table then has these fields:
CallID AutoNumber pk
EmployeeID Number fk to Employee.EmployeeID
ClientID Number fk to Client.ClientID
CallDate Date/Time just the date
TimeSlotID Number fk to TimeSlot.TimeSlotID

Now to book Employee 6 to Client 99 on 4/1/06 for 10am - noon, you enter
that info onto an unbound form and hit Go. The code adds these 4 records:
EmployeeID ClientID CallDate TimeSlotID
6 99 4/1/06 20
6 99 4/1/06 21
6 99 4/1/06 22
6 99 4/1/06 23

The advantage comes when you try to find out who is free for timeslots 22-25
in a day. You first create the query to ask who is scheduled on in that
period, and then add the subquery:
.... AND NOT EXISTS (SELECT CallID FROM ServiceCall
WHERE (ServiceCall.EmployeeID = EmployeeSchedule.EmployeeID)
AND (ServiceCall.CallDate = DateValue(EmployeeSchedule.ShiftStartDate))
AND (TimeSlotID Between 22 And 25))

 

[sammy]

Thanks Allen,
Now I understands what you meant when you tried to explain the better
approach that leaves me with one minor issue which is the sku length.
lets say I have a sku for Cleaning Vents cost is $100.00 durration 2 huours.
should that 2 hours be converted to 4 time slots?
thanks

 

[Allen Browne]

Probably, yes.

 

[sammy]

Thank you very much for your help Allen. after all of this and after getting
close to finalize the database, management decided they want to take this to
asp.net and Sql since they decided they want clients to do bookings
themseleves if they dont want to be hold waiting for a dispatch answer their
calls.
Now I am starting the whole thing in Sql 2000 and Asp.net
Thanks for your help and time.