db design efficient

SQL Apprentice Question
I have this db with theses tables. I need to make sure no employee is booked
when they are Off. just so you can understand why the design is the way it
is now. This is what we are trying to achieve.
Client calls to book an appointment, dispatch input the desired date and
time and search for the first available employee to book. once at client's
location they employee can sell additional skus and all Skus are based on 30
minutes time block.
I need an expert's advice on the design if its efficient and the best way to
create an employee schedule with all working days including starting time
and ending time.
************DDL*********
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
****End DDL*******

Celko Answers
Let's begin by throwing out your schema. IDENTITY is never a key, we
don't put "-table" on table names (ISO-11179) and we do not store
redundant computed columns. And "duration" was also spelled wrong
as well as redundant and in the wrong place. .

The original table design allows for double booking, since you had to
store a completed job and have no keys. The right way to do this is to
leave the ending time as a NULL that you can COALESCE() to the current
timestamp. You MUST enforce your natural key, which is long in this
case.


There was clearly no reserchor thoguht put into the data elements. You
really have people with first names that long? The USPS says a street
name is 35 characters, but you don't. You have no legally required
employee id, so you just number them randomly? The columns
"schedule_id" is never explained. You tell us that each SKU has a
duration, but you do not put it in the inventory (much better name than
"SKUs"; also, does another table model "Personnel" or
"Employees"? Think sets and not sequences; use collective names.)
Do not say things like "skuid" -- a SKU is a well-known, industry
standard identifier -- woudl sayd ISBNid in the bok trade, VINid for a
n automobile?


You do not understand the temproal model in T-SQL; We havew DATETIME,
not a separate DATE and TIME data types. What kind of calls are you
modeling? Surely not things related to furniture as implied by your
original name "Calltable" -- think about how silly that it (Department
of Redundacy Department)..


CREATE TABLE ServiceCalls
(client_id INTEGER NOT NULL REFERENCES Clients,
emp_id INTEGER NOT NULL REFERENCES Personnel,
start_time DATETIME NOT NULL,
PRIMARY KEY (client_id, emp_id, start_time),
end_time DATETIME, null is an open job
CHECK (end_time > start_time));


CREATE TABLE Clients
(client_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(20) NOT NULL,
phone_nbr CHAR(15) NOT NULL,
phone_nbr_2 CHAR(15),
client_street VARCHAR(35) NOT NULL,
client_city VARCHAR(20) NOT NULL)


CREATE TABLE Personnel
(emp_id CHAR(9) NOT NULL PRIMARY KEY,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(20) NOT NULL,
home_phome_nbr CHAR(15) NOT NULL,
cell_phone_nbr CHAR(15) NOT NULL,
street_addr VARCHAR(35) NOT NULL,
city VARCHAR(20) NOT NULL,
postal_code CHAR(7) NOT NULL,
schedule_id INTEGER NOT NULL);


CREATE TABLE ServiceTrips
(client_id INTEGER NOT NULL REFERENCES Clients,
emp_id INTEGER NOT NULL REFERENCES Personnel,
start_time DATETIME NOT NULL,
FOREIGN KEY (client_id, emp_id, start_time)
REFERENCES (client_id, emp_id, start_time),
sku INTEGER NOT NULL
REFERENCES Inventory(sku),
PRIMARY KEY (client_id, emp_id, start_time, sku)
);


CREATE TABLE Inventory
(sku INTEGER NOT NULL PRIMARY KEY,
stock_descr VARCHAR(50) NOT NULL,
tax_rate DECIMAL(5,3) NOT NULL,
duration INTEGER NOT NULL);


Now add a table for the scheudles of your employees.


CREATE TABLE PersonnelSchedules
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id),
start_avail_date DATETIME NOT NULL,
end_avail_date DATETIME NOT NULL
CHECK (start_avail_date < end_avail_date)
PRIMARY KEY (emp_id, start_avail_date) );


When the job's start date falls between start_avail_date and
end_avail_date AND he is not on another job, then you can book him.
That is a bit tricky, so put it into a VIEW.


You might also conisder a concatenated job ticket code derived from
(client_id, emp_id, start_time) for display purposes and the paper
work.