• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

Schedule and Booking db design help

Hey Guys,
I was just assigned a task to complete and I need serious help in the db structure/design.
Our company employees techs to perform tasks at client's locations. all of these tasks are flat rate based and they are allocated time blocks in 30 minutes interval, hence the confusion in db design.
I have created these tables with this structure.

EmployeeTable
EmpID -->(int PK)
EmpScheduleID (int FK to scheduletable)
EmpFname
EmpLname and so on

Scheduletable
ScheduleID (int PK)
SundayStart (smalldatetime)
SundayEnd (smalldatetime)
--rest of  week days --

SkuTable
SkuID (int PK)
SDescription (varchar(50))
Taxable (bit)
Durration (int) -->should represent the 30 minutes blocks as in 1=30 minutes, 2=60 minutes

CallsTable
CallID (int PK)
ClientID (FK to Clientstable)
SkuID (FK to Skutable)
EmployeeID (FK to employeetable)
CallDate (smalldatetime) something like 12/04/2005
StartTime (smalldatetime) something like 1:30 PM

I can not figure out how to implement a time slicing table and have it linked to the schedule table in order to get a calendar like representation of the schedule.
I have seen this post which includes a very similar situation but due to my lack of DB design I can not understand building the structure of the tables based on the last posting http://www.webservertalk.com/archive289-2005-9-1206387.html

Any ideas would be greatly appreciated even if they including changing the database structure completely.

Thanks in advance

0
Sammy
Asked:
Sammy
  • 12
  • 10
1 Solution
 
nmcdermaidCommented:
There is a lot of overlap in your tables.

I suggest this:


EmployeeTable
EmpID -->(int PK)
EmpFname
EmpLname and so on


CallsTable
CallID (int PK)
ClientID (FK to Clientstable)
SkuID (FK to Skutable)
EmployeeID (FK to employeetable)
CallDate (smalldatetime)
StartTime (smalldatetime)
Duration (number of 30 minute blocks)


SkuTable
SkuID (int PK)
SDescription (varchar(50))
Taxable (bit)


Scheduletable
This could just be a view from 'Calls'. I can explain this a bit more when you are happy with the table design above.


Basically the only table that actually holds any 'activity' is CallsTable

It describes when a call was made, who did the work for the call, how long they took, when they did it.

The other tables are just lookup tables.


This is OK as long as only one person is ever dispatched for one call.
0
 
SammyAuthor Commented:
you are correct the system should not allow more than one employee to be dispacthed to a single call.
I am interested in any design that allows me to perform the task, I have no problem in creating the database completely and starting all over as long as I can achieve the following
still have the calls performed based on a 30 minutes block
Be able to see who is available on a given date and time to be scheduled when a client calls to book an appointment.

The way I see the asp.net application going is like this
Client calls to book an appointment for 12/05/05 at 5:30 PM, the dispach picks the data using a calendar control and picks type in the time the client requested and click on search to see the first available employee.
What I forgot to mention is I want to allow the employees to book themselves off to perform admin tasks and take part in training, when they are booked off they shouldnt be available for calls.

Does that make sense?
0
 
nmcdermaidCommented:
>> This is OK as long as only one person is ever dispatched for one call.

Actually this is OK as long as only one person is ever dispatched for one call and only provides one SkuID (service?)


So I need to know more about your businss process.

-A call is 'opened'
-Can more than one person be dispatched to this call?
-Can this person provide more than one SKuID (Service?)

If so we need to possibly split CallsTable out.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
SammyAuthor Commented:
client calls and a call is opened
only 1 person can be dispached to the call
yes 1 call can have more than one sku
after the employee provides the service or services the call is closed

0
 
nmcdermaidCommented:
In the interests of making your ASP.Net app a little less coding intensive, we can put some 'helper' calculated fields in the table.


CallsTable
CallID (int PK)
ClientID (FK to Clientstable)
SkuID (FK to Skutable)
EmployeeID (FK to employeetable)
CallDate (smalldatetime)
StartDateTime (smalldatetime)
StartDate - a calculated field from StartDateTime which just truncates the time off the end
StartTime (smallint) - a calculated field from StartDateTime which turns the time into an 24hour time that we can use
EndTime (smallint) - a calculated field from StartDateTime and duration which turns the end time into an 24hour time that we can use
Duration (number of 30 minute blocks)



StartTime and EndTime will be a number field between 0 and 2359 which just contains a 24 hour time that can be searched on.


When a new call occurs you insert the a record with these fields populated

CallID (int PK) - identity - automatically populated
ClientID (FK to Clientstable) - entered by dispatch
SkuID (FK to Skutable) - entered by dispatch
CallDate (smalldatetime) - entered by dispatch
StartDateTime (smalldatetime) - entered by dispatch
Duration (number of 30 minute blocks) - entered by dispatch
StartDate - automatically populated
StartTime - automatically populated
EndTime - automatically populated




Now we can write a stored procedure or view which takes the callID and works out who is not occupied during that time.

The way I would do this is get a list of employees who ~cant~ service the time and list anyone who is not in that list!

This is the list of employees who can't service our call as they are busy:


SELECT EmployeeID
FROM
CallTable As CurrentCall
INNER JOIN
CallTable As BusyEmployee
ON CurrentCall.StartDate = BusyEmployee.StartDate
AND (
  BusyEmployee.StartTime >= CurrentCall.StartTime AND BusyEmployee.StartTime <= CurrentCall.EndTime
  OR
  BusyEmployee.EndTime >= CurrentCall.StartTime AND BusyEmployee.EndTime <= CurrentCall.EndTime
  )
WHERE CurrentCall.CallID = <yourcallid>







Therefore this is the list of employees who can service the call:







This is the list of employees who can't service our call as they are busy:


SELECT * FROM Employee WHERE EmployeeID NOT IN (

SELECT EmployeeID
FROM
CallTable As CurrentCall
INNER JOIN
CallTable As BusyEmployee
ON CurrentCall.StartDate = BusyEmployee.StartDate
AND (
  BusyEmployee.StartTime >= CurrentCall.StartTime AND BusyEmployee.StartTime <= CurrentCall.EndTime
  OR
  BusyEmployee.EndTime >= CurrentCall.StartTime AND BusyEmployee.EndTime <= CurrentCall.EndTime
  )
WHERE CurrentCall.CallID = <yourcallid>

)


Sorry I have to rush off now but chew that over!
0
 
SammyAuthor Commented:
Thanks nmcdermaid,
I will take a deeper look at the design in the AM "its 1:03 AM now" and I will let you know

sammy
0
 
nmcdermaidCommented:
In light of possibly > 1 sku per call, we need two tables:



CallsTable
CallID (int PK)
ClientID (FK to Clientstable)
EmployeeID (FK to employeetable)
CallDate (smalldatetime)
StartTime (smalldatetime)
Duration (number of 30 minute blocks)
~ and calculated fields above


ServiceTable
CallID (FK to CallsTable)
SkuID
Duration



In this case
-the Duration field in CallsTable indicates the expected duration of the work for booking purposes.
-the Duration field in ServiceTable indicates the actual duration of the work for charging purposes.


So now we clearly have one table for scheduling and one table for actual work done.


0
 
SammyAuthor Commented:
one question though,
when a client calls to book the appointment, the app should list only the employees available to do the work when they are on their shift. if an employee1 has sunday off  how would they be eliminated? isnt there supposed to be a an employeeSchedule with the days, start time and end time to avoid booking an employee when they are already off the clock?
0
 
SammyAuthor Commented:

I am posting the the new DDL in case I have misunderstood any of the table layouts
Calls Table
USE [IBOOKING]
GO
/****** Object:  Table [dbo].[CallsTable]    Script Date: 05/05/2006 13:02:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CallsTable](
      [CallID] [int] IDENTITY(1,1) NOT NULL,
      [ClientID] [int] NOT NULL,
      [EmployeeID] [int] NOT NULL,
      [CallDate] [smalldatetime] NOT NULL,
      [Durration] [int] NOT NULL,
      [StartTime] [smalldatetime] NOT NULL,
      [StartDate] [int] NOT NULL,
      [EndTime] [int] NOT NULL,
 CONSTRAINT [PK_CallsTable] PRIMARY KEY CLUSTERED
(
      [CallID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [IBOOKING]
GO
ALTER TABLE [dbo].[CallsTable]  WITH CHECK ADD  CONSTRAINT [FK_CallsTable_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([ClientID])
GO
ALTER TABLE [dbo].[CallsTable]  WITH CHECK ADD  CONSTRAINT [FK_CallsTable_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmpID])
*****************************************************************
Clients Table
USE [IBOOKING]
GO
/****** Object:  Table [dbo].[Clients]    Script Date: 05/05/2006 13:03:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Clients](
      [ClientID] [int] NOT NULL,
      [CfName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [ClName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Cphone] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [CaltPhone] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Caddress] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Ccity] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
      [ClientID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
*******************************************************************
Employees Table
USE [IBOOKING]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 05/05/2006 13:04:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employees](
      [EmpID] [int] IDENTITY(1,1) NOT NULL,
      [fName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [lName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [HomePhome] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [CellPhone] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Address] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [PostalCode] [nchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [ScheduleID] [int] NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
      [EmpID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [IBOOKING]
GO
ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([EmpID])
REFERENCES [dbo].[Schedule] ([SID])
************************************************************************
Service Table
USE [IBOOKING]
GO
/****** Object:  Table [dbo].[ServiceTable]    Script Date: 05/05/2006 13:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ServiceTable](
      [SCallID] [int] NOT NULL,
      [SkuID] [int] NOT NULL,
      [Durration] [int] NOT NULL
) ON [PRIMARY]

GO
USE [IBOOKING]
GO
ALTER TABLE [dbo].[ServiceTable]  WITH CHECK ADD  CONSTRAINT [FK_ServiceTable_CallsTable] FOREIGN KEY([SCallID])
REFERENCES [dbo].[CallsTable] ([CallID])
*************************************************************************
SKUS table
USE [IBOOKING]
GO
/****** Object:  Table [dbo].[Skus]    Script Date: 05/05/2006 13:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Skus](
      [SkuID] [int] NOT NULL,
      [SDescription] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Taxable] [bit] NOT NULL,
 CONSTRAINT [PK_Skus] PRIMARY KEY CLUSTERED
(
      [SkuID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
*******************************************************************
As you can see I still need an employee schedule table to make sure no employee will be booked when they are off
0
 
SammyAuthor Commented:
Decided to double points after knowing how much work this question may involve
0
 
nmcdermaidCommented:
>> if an employee1 has sunday off  how would they be eliminated

You can create a SkuID which stands for training or public holidays or Sundays or whatever, then put an entry into CallsTable so the entire schedule of an employee can be found in CallsTable, whether they are at a clients, on holiday, training whatever. If they have a record in CallsTable then they are 'busy'

If you are uncomfortable creating a SKU for this purpose then we can create an extra field in CallsTable which points at a table containing 'non billable' items.

We may want to rename 'CallsTable' to call it 'schedule' or something.



>> I still need an employee schedule table to make sure no employee will be booked when they are off

I just want to clarify the purpose of this 'calendar -like' table. The CallsTable currently unambiguously defines whether someone is busy or not, and the format of CallsTable as it is is much better for an application to look up availability. A calender-like table (with a column for each day) is actually much more difficult for an application to check availability.

If you specifically have an ASP control or something that requires a calendar-like table then I will work on that. Otherwise I will first work on getting the intial table structures right, then at the end I can show you a view (basically a saved select statement) that will display a schedule with a column for each day.

To actually work out which employees cant service a booking, you can use this SQL I supplied above. You could for example use this SQL to populate a drop down box of possible employees to assign to the service call..... it will only list employees that can service a given service call.



Have you worked out how you will be entering dates and time from your ASP app yet? If you have any particular controls in mind. That will effect exactly how we store the start date and start time, whether we use a datetime data type or split it into varchar or int data types.



0
 
SammyAuthor Commented:
I have no problem adding additional skus as long as it will eliminate the employee from being booked on days off
I have already built the GUI for the app, the date will be picked using the calendar control by using statement like this
Dim dtCallDate as date = calBooking.selecteddate.toshortdatestring
dtCallDate= 5/17/2006

so far I have the the GUI allows the desired time to be entered by Concatenating 2 strings from 2 dropdownlists, One for the hours and will be for AM or PM
0
 
SammyAuthor Commented:
nmcdermaid, Can you please take a look at the DDL and tell me if you think these are the proper fields?
Thanks
0
 
nmcdermaidCommented:
Yep, everything looks good. The only thing tho think about is how dates and time will be entered as they can be a bit painful.

As your calldate will be entered as a date variable, this can remain a datetime. I justy want to double check, do you want distinct fields for the date the call was taken, and the actual date of the schedule?

At this stage I suggest that StartTime and EndTime be entered as a time-only component of a smalldatetime. You'll need to allow for this in your data entry.



CREATE TABLE [dbo].[CallsTable](
     [CallID] [int] IDENTITY(1,1) NOT NULL,
     [ClientID] [int] NOT NULL,
     [EmployeeID] [int] NOT NULL,
     [CallDate] [smalldatetime] NOT NULL,  -- captures the time the operator entered took the call and entered the schedule - is this necessary?
     [StartDate] [smalldatetime] NOT NULL,  -- the day the work will be performed, without a time component, i.e. '2007-05-20 00:00:00'
     [Duration] [tinyint] NOT NULL,               -- how long the work will take (never larger than 48) in 30 minute increments
     [StartTime] [smalldatetime] NOT NULL,                -- smalldatetime indicating start time ie '1900-01-01 08:30:00' for 8:30 am
     [EndTime] [smalldatetime] NOT NULL,                  -- calculated field = DATEADD(mi,duration*30,starttime) - put this in the 'formula' field in Enterprise Manager



From my understanding of your requirements I think that all looks right.

Specifically:

1. The date that the call was taken is captured
2. All 'busy' times (including non chargeable) are recorded in CallsTable. This table may be used to derive when a particular employee is busy, and which client it is for.
3. One employee may perform more than one task against a scheduled time block. This will allow the employee to assign more time to the task than was originally scheduled.



Withe current DDL, this query will list all employees who may be assigned to a given call, because they are not currently assigned in CallsTable:


SELECT * FROM Employees WHERE EmployeeID NOT IN (

SELECT EmployeeID
FROM
CallTable As CurrentCall
INNER JOIN
CallTable As BusyEmployee
ON CurrentCall.StartDate = BusyEmployee.StartDate
AND (
  BusyEmployee.StartTime >= CurrentCall.StartTime AND BusyEmployee.StartTime <= CurrentCall.EndTime
  OR
  BusyEmployee.EndTime >= CurrentCall.StartTime AND BusyEmployee.EndTime <= CurrentCall.EndTime
  )
WHERE CurrentCall.CallID = <***PUT THE CALL ID IN HERE***>
)




Note that this is effectively a function - you plug in the call ID and you get a list of available employees.

This may be created as a view, or more likely a stored procedure.
0
 
nmcdermaidCommented:
Did you still want the calendar representation or does that view in the prior post do what you need?
0
 
SammyAuthor Commented:
> do you want distinct fields for the date the call was taken, and the actual date of the schedule?
Yes I would like that for reporting purposes in the future

>1. The date that the call was taken is captured "Answer is Yes"
>2. All 'busy' times (including non chargeable) are recorded in CallsTable. This table may be used to derive when a particular employee is busy, and which client it is for. "You are correct here as well"
>3. One employee may perform more than one task against a scheduled time block. This will allow the employee to assign more time to the task than was originally scheduled. "And correct here as wel"

does the calendar representation have any advantage over what you suggested? my ownly reason for the calendar representation is the visual element thats all, I wanted the employees to see all their weekly calls when they login to check their schedule.

Thanks
0
 
nmcdermaidCommented:
Its never a good idea to implement visual functionality in a database. Its always a much better idea to do it in the GUI, in this case the ASP.Net App.

Have a think and see if you can work out how you would render that calls table in a large table in ASP.

If you are having difficulty working it out I can write a view which will bring up a calendar.... right now I'm a little busy but I can do it in a couple of days of thats OK.
0
 
SammyAuthor Commented:
That would be perfect thanks
I am still working on the rest of the website's Gui
0
 
nmcdermaidCommented:
OK, Here is a ~start~ on that calendar table view anyway:

-It only shows a week at a time - you need to substitute in the start and end dates
-It requires another table called tblDaySlices to 'fill in' unallocated time.


Here is the actual select statement:


SELECT
PeriodStart,
MAX(Sunday) As Sunday,
MAX(Monday) As Monday,
MAX(Tuesday) As Tuesday,
MAX(Wednesday) As Wednesday,
MAX(Thursday) As Thursday,
MAX(Friday) As Friday,
MAX(Saturday) As Saturday
FROM
(
SELECT
PeriodStart,
CASE WHEN DATEPART(dw,StartDate) = 1 THEN Activity ELSE 0 END As Sunday,
CASE WHEN DATEPART(dw,StartDate) = 2 THEN Activity ELSE 0 END As Monday,
CASE WHEN DATEPART(dw,StartDate) = 3 THEN Activity ELSE 0 END As Tuesday,
CASE WHEN DATEPART(dw,StartDate) = 4 THEN Activity ELSE 0 END As Wednesday,
CASE WHEN DATEPART(dw,StartDate) = 5 THEN Activity ELSE 0 END As Thursday,
CASE WHEN DATEPART(dw,StartDate) = 6 THEN Activity ELSE 0 END As Friday,
CASE WHEN DATEPART(dw,StartDate) = 7 THEN Activity ELSE 0 END As Saturday

FROM

(
SELECT
C.StartDate,
H.PeriodStart,
MAX(CASE WHEN H.Period_DT BETWEEN StartTime AND DATEADD(mi,-1,EndTime) THEN ClientID ELSE 0 END) AS Activity
FROM
CallsTable C
CROSS JOIN
tblDaySlices H
WHERE C.StartDate BETWEEN <week start date> AND <week end date>
GROUP BY C.StartDate,H.PeriodStart
) A
) B
GROUP BY PeriodStart




Here is the DDL for tblDaySlices:



CREATE TABLE tblDaySlices (
      Period tinyint NOT NULL ,
      PeriodStart varchar (5) NOT NULL ,
      Period_DT smalldatetime NOT NULL ,
      CONSTRAINT PK_tblDaySlices PRIMARY KEY  CLUSTERED
      (
            Period
      )  ON PRIMARY
) ON PRIMARY
GO


Here is some sample data for the first four rows:

Period    PeriodStart    Period_DT
1            08:30            1900-01-01 08:30:00
2            09:00            1900-01-01 09:00:00
3            09:30            1900-01-01 09:30:00
4            10:00            1900-01-01 10:00:00

This needs to extend all the way up to:
....
..
.
18      17:00      1900-01-01 17:00:00






If you then go and fill in Calls table and run the above select statement it will show you a period from 8:30 to 17:00 for each day of the week, with a client id number in each half hour slot.



As you can see, visual effects (particularly crosstabs) are best not done in the database.



Just one other option I can think of... you could actually have a calendar table and then populate it automatically from a trigger on CallsTable. But I don't think that the 'calendar' representation is what you are really after... you should be able to find a control that can convert CallsTable into a calendar type representation in your GUI.
0
 
SammyAuthor Commented:
Thanks nmcdermaid,
I will work on this this thursday and friday and let you know :-)

0
 
SammyAuthor Commented:
nmcdermaid,
My apologies man, I couldnt do any work on the database since our sql machine crashed and had to restore it.
I will post back with the outcome when I am done
0
 
nmcdermaidCommented:
No worries.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now