--CELKO-- wrote:
I designed a databse for a company that makes time keeping equipment
(http://www.exaktime.com/). They sell mostly to the construction
trades because their hardware is mil spec quality and can be used by
illiterate workers if they are not color blind.
Based on actually doing such a data base, I think your design is
fundamentally flawed and you need to start over. You have no real
keys, the tables are not normalized, there are too many NULL-able
columns, repeated groups to destroy even the hope of 1NF, BIT flags,
absurd data element names like "<something>TypeID" and columns that are
far too large (ever see a CHAR(50) password?, CHAR(100) email
address?). Your temporal model is wrong (look up Rick Snodgrass and
temporal SQL for an entire book on why).
I'm relieved to be able to say that I didn't design the database! The
DDL that I posted upthread was simplified to illustrate the problem.
If you have time, I should be extremely interested in your comments on
the REAL data design, which I post below.
In any case, many thanks for time taken.
Edward
CREATE TABLE [dbo].[TimesheetItem] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[Start] [smalldatetime] NOT NULL ,
[DurationMins] [int] NOT NULL ,
[WorkID] [int] NULL ,
[WorkComponentID] [int] NULL ,
[WorkItemID] [int] NULL ,
[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OffSite] [tinyint] NULL ,
[TravelTo] [smalldatetime] NULL ,
[TravelToMins] [int] NULL ,
[TravelFrom] [smalldatetime] NULL ,
[TravelFromMins] [int] NULL ,
[TravelMileage] [int] NULL ,
[NonChargeableMins] [int] NULL ,
[OTAuthorisedID] [int] NULL ,
[OTAuthorisedDate] [smalldatetime] NULL ,
[Abroad] [bit] NULL ,
[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ApprovalID] [int] NULL ,
[AprovalDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[BankHolidays] (
[BankHoliday] [smalldatetime] NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Department] (
[DepartmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Employee] (
[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DepartmentID] [int] NOT NULL ,
[JobDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[StartDate] [smalldatetime] NOT NULL ,
[EndDate] [smalldatetime] NULL ,
[DefaultRatePerHour] [smallmoney] NULL ,
[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[UserGroupID] [int] NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastLogon] [datetime] NULL ,
[PasswordChange] [smalldatetime] NULL ,
[PreviousPassword1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword3] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword4] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousPassword5] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[HolidayAllocation] (
[EmployeeID] [int] NOT NULL ,
[HolidayYear] [int] NOT NULL ,
[DaysAllocated] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Invoice] (
[InvoiceID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkOrderID] [int] NULL ,
[InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InvoiceValue] [money] NULL ,
[InvoiceDate] [datetime] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RecurringWorkItem] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkComponentID] [int] NULL ,
[RefID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TypeID] [int] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssignedEmployeeID] [int] NULL ,
[RecurStart] [smalldatetime] NULL ,
[RecurEnd] [smalldatetime] NULL ,
[RecurWeekly] [bit] NULL ,
[RecurMonthly] [bit] NULL ,
[RecurMonthlyDayNo] [int] NULL ,
[RecurMonthlyWeekDayOrder] [int] NULL ,
[RecurMonday] [bit] NULL ,
[RecurTuesday] [bit] NULL ,
[RecurWednesday] [bit] NULL ,
[RecurThursday] [bit] NULL ,
[RecurFriday] [bit] NULL ,
[RecurSaturday] [bit] NULL ,
[RecurSunday] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserGroup] (
[UserGroupID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccessTimesheetEntry] [int] NULL ,
[AccessTimesheetAdmin] [int] NULL ,
[AccessTimesheetReview] [int] NULL ,
[AccessWorkAdmin] [int] NULL ,
[AccessEmployeeAdmin] [int] NULL ,
[AccessContacts] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WeekProfile] (
[WeekProfileID] [int] IDENTITY (1, 1) NOT NULL ,
[EmployeeID] [int] NOT NULL ,
[PeriodStart] [smalldatetime] NOT NULL ,
[PeriodEnd] [smalldatetime] NULL ,
[Profile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DailyHours] [decimal](18, 2) NOT NULL ,
[DailyValidation] [bit] NOT NULL ,
[StartTime] [datetime] NULL ,
[LunchStartTime] [datetime] NULL ,
[LunchDurationMins] [int] NULL ,
[WeeklyHours] [decimal](18, 2) NOT NULL ,
[WeeklyValidation] [bit] NOT NULL ,
[MondayHours] [decimal](18, 2) NOT NULL ,
[TuesdayHours] [decimal](10, 2) NOT NULL ,
[WednesdayHours] [decimal](10, 2) NOT NULL ,
[ThursdayHours] [decimal](10, 2) NOT NULL ,
[FridayHours] [decimal](10, 2) NOT NULL ,
[SaturdayHours] [decimal](10, 2) NOT NULL ,
[SundayHours] [decimal](18, 2) NOT NULL ,
[WeekDayValidation] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WeekProfileTemplate] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[Profile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DailyHours] [decimal](10, 2) NOT NULL ,
[DailyValidation] [bit] NOT NULL ,
[StartTime] [datetime] NULL ,
[LunchStartTime] [datetime] NULL ,
[LunchDurationMins] [int] NULL ,
[WeeklyHours] [decimal](10, 2) NOT NULL ,
[WeeklyValidation] [bit] NOT NULL ,
[MondayHours] [decimal](18, 2) NOT NULL ,
[TuesdayHours] [decimal](18, 2) NOT NULL ,
[WednesdayHours] [decimal](18, 2) NOT NULL ,
[ThursdayHours] [decimal](18, 2) NOT NULL ,
[FridayHours] [decimal](18, 2) NOT NULL ,
[SaturdayHours] [decimal](18, 2) NOT NULL ,
[SundayHours] [decimal](18, 2) NOT NULL ,
[WeekDayValidation] [bit] NOT NULL ,
[Notes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Work] (
[WorkID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Chargeable] [bit] NOT NULL ,
[ClientID] [int] NULL ,
[ClientContactID] [int] NULL ,
[Entered] [smalldatetime] NULL ,
[ApprovalRequired] [tinyint] NULL ,
[SQ_BlockID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkComponent] (
[WorkComponentID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[Component] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkComponentDefaults] (
[WorkComponentDefaultID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NOT NULL ,
[Component] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkEmployeeRate] (
[WorkEmployeeRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NULL ,
[EmployeeID] [int] NULL ,
[RateMultipiler] [float] NULL ,
[RatePerHour] [smallmoney] NULL ,
[OffSiteOnly] [tinyint] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItem] (
[WorkItemID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkComponentID] [int] NULL ,
[RefID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TypeID] [int] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Scheduled] [smalldatetime] NULL ,
[PriorityID] [int] NULL ,
[StatusID] [int] NULL ,
[ResolutionID] [int] NULL ,
[PhaseID] [int] NULL ,
[AreaID] [int] NULL ,
[VersionID] [int] NULL ,
[VersionReleasedID] [int] NULL ,
[WorkOrderID] [int] NULL ,
[AllocatedHours] [real] NULL ,
[PercentComplete] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemAreaLUT] (
[WorkItemAreaID] [int] IDENTITY (1, 1) NOT NULL ,
[Area] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemAssignment] (
[WorkItemID] [int] NOT NULL ,
[EmployeeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemPhaseLUT] (
[WorkItemPhaseID] [int] IDENTITY (1, 1) NOT NULL ,
[Phase] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemPriorityLUT] (
[PriorityID] [int] IDENTITY (1, 1) NOT NULL ,
[Priority] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemResolutionLUT] (
[WorkItemResolutionID] [int] IDENTITY (1, 1) NOT NULL ,
[Resolution] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[IsFinal] [bit] NOT NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemStatusLUT] (
[WorkItemStatusID] [int] IDENTITY (1, 1) NOT NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[IsFinal] [bit] NULL ,
[Sequence] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkItemTypeLUT] (
[WorkItemTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkTypeID] [int] NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sequence] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRate] (
[WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [smalldatetime] NOT NULL ,
[TimeTo] [smalldatetime] NOT NULL ,
[RateMultipiler] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOTRateDefaults] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkDay] [int] NOT NULL ,
[TimeFrom] [datetime] NULL ,
[TimeTo] [datetime] NULL ,
[RateMultipiler] [float] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkOrder] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NOT NULL ,
[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[OrderDate] [datetime] NOT NULL ,
[OrderValue] [money] NOT NULL ,
[FixedPrice] [bit] NOT NULL ,
[Prepaid] [bit] NOT NULL ,
[AllocatedHours] [int] NULL ,
[RatePerHour] [money] NULL ,
[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SQ_BlockID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkType] (
[WorkTypeID] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DepartmentID] [int] NULL ,
[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkItemPriority] [bit] NOT NULL ,
[WorkItemStatus] [bit] NOT NULL ,
[WorkItemResolution] [bit] NOT NULL ,
[WorkItemPhase] [bit] NOT NULL ,
[WorkItemArea] [bit] NOT NULL ,
[WorkItemVersion] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WorkVersion] (
[WorkVersionID] [int] IDENTITY (1, 1) NOT NULL ,
[WorkID] [int] NULL ,
[Version] [float] NULL ,
[ReleaseDate] [smalldatetime] NULL ,
[Notes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [PK_TimesheetItem] PRIMARY KEY CLUSTERED
(
[ItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[BankHolidays] WITH NOCHECK ADD
CONSTRAINT [PK_BankHolidays] PRIMARY KEY CLUSTERED
(
[BankHoliday]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] WITH NOCHECK ADD
CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[HolidayAllocation] WITH NOCHECK ADD
CONSTRAINT [PK_HolidayAllocation] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[HolidayYear]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Invoice] WITH NOCHECK ADD
CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED
(
[InvoiceID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecurringWorkItem] WITH NOCHECK ADD
CONSTRAINT [PK_RecurringWorkItem] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserGroup] WITH NOCHECK ADD
CONSTRAINT [PK_UserGroup] PRIMARY KEY CLUSTERED
(
[UserGroupID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WeekProfile] WITH NOCHECK ADD
CONSTRAINT [PK_HoursProfile] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[PeriodStart]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WeekProfileTemplate] WITH NOCHECK ADD
CONSTRAINT [PK_HoursProfileTemplate] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [PK_Work] PRIMARY KEY CLUSTERED
(
[WorkID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkComponent] WITH NOCHECK ADD
CONSTRAINT [PK_WorkComponent] PRIMARY KEY CLUSTERED
(
[WorkComponentID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkComponentDefaults] WITH NOCHECK ADD
CONSTRAINT [PK_WorkTypeComponentDefaults] PRIMARY KEY CLUSTERED
(
[WorkComponentDefaultID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkEmployeeRate] WITH NOCHECK ADD
CONSTRAINT [PK_WorkEmployeeRate] PRIMARY KEY CLUSTERED
(
[WorkEmployeeRateID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItem] WITH NOCHECK ADD
CONSTRAINT [PK_WorkActivity] PRIMARY KEY CLUSTERED
(
[WorkItemID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemAreaLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemAreaLUT] PRIMARY KEY CLUSTERED
(
[WorkItemAreaID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemAssignment] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemAssignment] PRIMARY KEY CLUSTERED
(
[WorkItemID],
[EmployeeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPhaseLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemPhaseLUT] PRIMARY KEY CLUSTERED
(
[WorkItemPhaseID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPriorityLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemPriorityLUT] PRIMARY KEY CLUSTERED
(
[PriorityID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemResolutionLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemResolutionLUT] PRIMARY KEY CLUSTERED
(
[WorkItemResolutionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemStatusLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemStatusLUT] PRIMARY KEY CLUSTERED
(
[WorkItemStatusID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemTypeLUT] WITH NOCHECK ADD
CONSTRAINT [PK_WorkActivityType] PRIMARY KEY CLUSTERED
(
[WorkItemTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOTRate] WITH NOCHECK ADD
CONSTRAINT [PK_WorkOTRate] PRIMARY KEY CLUSTERED
(
[WorkOTRateID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOTRateDefaults] WITH NOCHECK ADD
CONSTRAINT [PK_WorkOTRateDefaults] PRIMARY KEY CLUSTERED
(
[PKID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [PK_WorkItemOrder] PRIMARY KEY CLUSTERED
(
[WorkOrderID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkType] WITH NOCHECK ADD
CONSTRAINT [PK_WorkType] PRIMARY KEY CLUSTERED
(
[WorkTypeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkVersion] WITH NOCHECK ADD
CONSTRAINT [PK_WorkVersion] PRIMARY KEY CLUSTERED
(
[WorkVersionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] WITH NOCHECK ADD
CONSTRAINT [DF_TimesheetItem_TypeID] DEFAULT (0) FOR [TypeID],
CONSTRAINT [DF_TimesheetItem_DurationMins] DEFAULT (0) FOR
[DurationMins],
CONSTRAINT [DF_TimesheetItem_OffSite] DEFAULT (0) FOR [OffSite],
CONSTRAINT [DF_TimesheetItem_TravelToMins] DEFAULT (0) FOR
[TravelToMins],
CONSTRAINT [DF_TimesheetItem_TravelFromMins] DEFAULT (0) FOR
[TravelFromMins],
CONSTRAINT [DF_TimesheetItem_NonChargeableMins] DEFAULT (0) FOR
[NonChargeableMins],
CONSTRAINT [DF_TimesheetItem_Abroad] DEFAULT (0) FOR [Abroad],
CONSTRAINT [DF_TimesheetItem_Reviewed] DEFAULT (0) FOR [ApprovalID],
CONSTRAINT [CK_TimesheetItem] CHECK ([TypeID] like '[0-3]' and
[OffSite] like '[0-1]')
GO
ALTER TABLE [dbo].[Department] WITH NOCHECK ADD
CONSTRAINT [IX_DepartmentName] UNIQUE NONCLUSTERED
(
[Name]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[RecurringWorkItem] WITH NOCHECK ADD
CONSTRAINT [DF_RecurringWorkItem_RecurWeekly] DEFAULT (0) FOR
[RecurWeekly],
CONSTRAINT [DF_RecurringWorkItem_RecurMonthly] DEFAULT (0) FOR
[RecurMonthly],
CONSTRAINT [DF_RecurringWorkItem_RecurMonday] DEFAULT (0) FOR
[RecurMonday],
CONSTRAINT [DF_RecurringWorkItem_RecurTuesday] DEFAULT (0) FOR
[RecurTuesday],
CONSTRAINT [DF_RecurringWorkItem_RecurWednesday] DEFAULT (0) FOR
[RecurWednesday],
CONSTRAINT [DF_RecurringWorkItem_RecurThursday] DEFAULT (0) FOR
[RecurThursday],
CONSTRAINT [DF_RecurringWorkItem_RecurFriday] DEFAULT (0) FOR
[RecurFriday],
CONSTRAINT [DF_RecurringWorkItem_RecurFriday1] DEFAULT (0) FOR
[RecurSaturday],
CONSTRAINT [DF_RecurringWorkItem_RecurSaturday1] DEFAULT (0) FOR
[RecurSunday]
GO
ALTER TABLE [dbo].[UserGroup] WITH NOCHECK ADD
CONSTRAINT [DF_UserGroup_AccessTimesheetEntry] DEFAULT (0) FOR
[AccessTimesheetEntry],
CONSTRAINT [DF_UserGroup_AccessTimesheetAdmin] DEFAULT (0) FOR
[AccessTimesheetAdmin],
CONSTRAINT [DF_UserGroup_AccessTimesheetReview] DEFAULT (0) FOR
[AccessTimesheetReview],
CONSTRAINT [DF_UserGroup_AccessWorkCodeAdmin] DEFAULT (0) FOR
[AccessWorkAdmin],
CONSTRAINT [DF_UserGroup_AccessEmployeeAdmin] DEFAULT (0) FOR
[AccessEmployeeAdmin],
CONSTRAINT [DF_UserGroup_AccessContacts] DEFAULT (0) FOR
[AccessContacts],
CONSTRAINT [IX_UserGroupName] UNIQUE NONCLUSTERED
(
[Name]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_UserGroup] CHECK ([AccessTimesheetEntry] like '[0-3]'
and [AccessTimesheetAdmin] like '[0-3]' and [AccessWorkAdmin] like
'[0-3]' and [AccessEmployeeAdmin] like '[0-3]' and
[AccessTimesheetReview] like '[0-3]' and [AccessContacts] like '[0-3]')
GO
ALTER TABLE [dbo].[WeekProfile] WITH NOCHECK ADD
CONSTRAINT [DF_WeekProfile_DailyHours] DEFAULT (0) FOR [DailyHours],
CONSTRAINT [DF_WeekProfile_DailyHoursValidation] DEFAULT (0) FOR
[DailyValidation],
CONSTRAINT [DF_WeekProfile_WeekHours] DEFAULT (0) FOR [WeeklyHours],
CONSTRAINT [DF_WeekProfile_WeeklyHoursValidation] DEFAULT (0) FOR
[WeeklyValidation],
CONSTRAINT [DF_EmploymentProfile_MondayHours] DEFAULT (0) FOR
[MondayHours],
CONSTRAINT [DF_EmploymentProfile_TuesdayHours] DEFAULT (0) FOR
[TuesdayHours],
CONSTRAINT [DF_EmploymentProfile_WednesdayHours] DEFAULT (0) FOR
[WednesdayHours],
CONSTRAINT [DF_EmploymentProfile_ThursdayHours] DEFAULT (0) FOR
[ThursdayHours],
CONSTRAINT [DF_EmploymentProfile_FridayHours] DEFAULT (0) FOR
[FridayHours],
CONSTRAINT [DF_EmploymentProfile_SaturdayHours] DEFAULT (0) FOR
[SaturdayHours],
CONSTRAINT [DF_WeekProfile_SundayHours] DEFAULT (0) FOR [SundayHours],
CONSTRAINT [DF_WeekProfile_WeekDayValidation] DEFAULT (0) FOR
[WeekDayValidation]
GO
ALTER TABLE [dbo].[WeekProfileTemplate] WITH NOCHECK ADD
CONSTRAINT [DF_WeekProfileTemplate_DailyHours] DEFAULT (0) FOR
[DailyHours],
CONSTRAINT [DF_WeekProfileTemplate_DailyValidation] DEFAULT (0) FOR
[DailyValidation],
CONSTRAINT [DF_WeekProfileTemplate_WeekHours] DEFAULT (0) FOR
[WeeklyHours],
CONSTRAINT [DF_WeekProfileTemplate_WeeklyValidation] DEFAULT (0) FOR
[WeeklyValidation],
CONSTRAINT [DF_HoursProfileTemplates_MondayHours] DEFAULT (0) FOR
[MondayHours],
CONSTRAINT [DF_HoursProfileTemplates_TuesdayHours] DEFAULT (0) FOR
[TuesdayHours],
CONSTRAINT [DF_HoursProfileTemplates_WednesdayHours] DEFAULT (0) FOR
[WednesdayHours],
CONSTRAINT [DF_HoursProfileTemplates_ThursdayHours] DEFAULT (0) FOR
[ThursdayHours],
CONSTRAINT [DF_HoursProfileTemplates_FridayHours] DEFAULT (0) FOR
[FridayHours],
CONSTRAINT [DF_HoursProfileTemplates_SaturdayHours] DEFAULT (0) FOR
[SaturdayHours],
CONSTRAINT [DF_WeekProfileTemplate_SundayHours] DEFAULT (0) FOR
[SundayHours],
CONSTRAINT [DF_WeekProfileTemplate_StrictValidation] DEFAULT (0) FOR
[WeekDayValidation],
CONSTRAINT [IX_HoursProfileTemplate] UNIQUE NONCLUSTERED
(
[Profile]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Work] WITH NOCHECK ADD
CONSTRAINT [DF_Work_Chargeable] DEFAULT (0) FOR [Chargeable],
CONSTRAINT [DF_WorkItem_ReviewRequired] DEFAULT (0) FOR
[ApprovalRequired],
CONSTRAINT [IX_WorkCode] UNIQUE NONCLUSTERED
(
[WorkCode]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_Work] CHECK ([ApprovalRequired] like '[0-1]')
GO
ALTER TABLE [dbo].[WorkComponent] WITH NOCHECK ADD
CONSTRAINT [IX_WorkComponent] UNIQUE NONCLUSTERED
(
[WorkID],
[Component]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkComponentDefaults] WITH NOCHECK ADD
CONSTRAINT [IX_WorkTypeComponentDefaults] UNIQUE NONCLUSTERED
(
[WorkTypeID],
[Component]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkEmployeeRate] WITH NOCHECK ADD
CONSTRAINT [DF_WorkEmployeeRate_OffSiteOnly] DEFAULT (0) FOR
[OffSiteOnly],
CONSTRAINT [IX_WorkEmployeeRate] UNIQUE NONCLUSTERED
(
[WorkID],
[EmployeeID],
[OffSiteOnly]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_WorkEmployeeRate] CHECK ([OffSiteOnly] like '[0-1]')
GO
ALTER TABLE [dbo].[WorkItem] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemCode] UNIQUE NONCLUSTERED
(
[WorkItemID],
[RefID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [CK_WorkItem] CHECK ([PercentComplete] like '[0-100]')
GO
ALTER TABLE [dbo].[WorkItemAreaLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemAreaLUT] UNIQUE NONCLUSTERED
(
[Area]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPhaseLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemPhaseLUT] UNIQUE NONCLUSTERED
(
[Phase]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemPriorityLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkItemPriorityLUT] UNIQUE NONCLUSTERED
(
[Priority]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemResolutionLUT] WITH NOCHECK ADD
CONSTRAINT [DF_WorkItemResolutionLUT_IsFinal] DEFAULT (0) FOR
[IsFinal]
GO
ALTER TABLE [dbo].[WorkItemStatusLUT] WITH NOCHECK ADD
CONSTRAINT [DF_WorkItemStatusLUT_IsFinal] DEFAULT (0) FOR [IsFinal],
CONSTRAINT [IX_WorkItemStatusLUT] UNIQUE NONCLUSTERED
(
[Status]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkItemTypeLUT] WITH NOCHECK ADD
CONSTRAINT [IX_WorkActivityType] UNIQUE NONCLUSTERED
(
[WorkTypeID],
[Type]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkOTRate] WITH NOCHECK ADD
CONSTRAINT [DF_WorkDayRate_WorkDay] DEFAULT (0) FOR [WorkDay],
CONSTRAINT [CK_WorkDayRate] CHECK ([WorkDay] like '[0-3]')
GO
ALTER TABLE [dbo].[WorkOTRateDefaults] WITH NOCHECK ADD
CONSTRAINT [DF_WorkDayRateDefaults_WorkDay] DEFAULT (0) FOR [WorkDay],
CONSTRAINT [CK_WorkDayRateDefaults] CHECK ([WorkDay] like '[0-3]')
GO
ALTER TABLE [dbo].[WorkOrder] WITH NOCHECK ADD
CONSTRAINT [DF_WorkOrder_FixedPrice] DEFAULT (0) FOR [FixedPrice],
CONSTRAINT [DF_WorkOrder_Prepaid] DEFAULT (0) FOR [Prepaid],
CONSTRAINT [IX_WorkItemOrder] UNIQUE NONCLUSTERED
(
[WorkID],
[OrderNo]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkType] WITH NOCHECK ADD
CONSTRAINT [DF_WorkType_WorkItemPriority] DEFAULT (0) FOR
[WorkItemPriority],
CONSTRAINT [DF_WorkType_WorkItemStatus] DEFAULT (0) FOR
[WorkItemStatus],
CONSTRAINT [DF_WorkType_WorkItemResolution] DEFAULT (0) FOR
[WorkItemResolution],
CONSTRAINT [DF_WorkType_WorkItemPhase] DEFAULT (0) FOR
[WorkItemPhase],
CONSTRAINT [DF_WorkType_WorkItemArea] DEFAULT (0) FOR [WorkItemArea],
CONSTRAINT [DF_WorkType_WorkItemVersion] DEFAULT (0) FOR
[WorkItemVersion],
CONSTRAINT [IX_WorkType] UNIQUE NONCLUSTERED
(
[Type]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[WorkVersion] WITH NOCHECK ADD
CONSTRAINT [IX_WorkVersion] UNIQUE NONCLUSTERED
(
[Version]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TimesheetItem] ADD
CONSTRAINT [FK_TimesheetItem_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_Employee1] FOREIGN KEY
(
[OTAuthorisedID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_TimesheetItem_WorkCode] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
)
GO
ALTER TABLE [dbo].[Employee] ADD
CONSTRAINT [FK_Employee_Department] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Department] (
[DepartmentID]
),
CONSTRAINT [FK_Employee_UserGroup] FOREIGN KEY
(
[UserGroupID]
) REFERENCES [dbo].[UserGroup] (
[UserGroupID]
)
GO
ALTER TABLE [dbo].[HolidayAllocation] ADD
CONSTRAINT [FK_HolidayAllocation_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Invoice] ADD
CONSTRAINT [FK_Invoice_WorkOrder] FOREIGN KEY
(
[WorkOrderID]
) REFERENCES [dbo].[WorkOrder] (
[WorkOrderID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RecurringWorkItem] ADD
CONSTRAINT [FK_RecurringWorkItem_Employee] FOREIGN KEY
(
[AssignedEmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
)
GO
ALTER TABLE [dbo].[WeekProfile] ADD
CONSTRAINT [FK_HoursProfile_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Work] ADD
CONSTRAINT [FK_Work_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
)
GO
ALTER TABLE [dbo].[WorkComponent] ADD
CONSTRAINT [FK_WorkComponent_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkComponentDefaults] ADD
CONSTRAINT [FK_WorkTypeComponentDefaults_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkEmployeeRate] ADD
CONSTRAINT [FK_WorkEmployeeRate_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_WorkEmployeeRate_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkItem] ADD
CONSTRAINT [FK_WorkItem_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE ,
CONSTRAINT [FK_WorkItem_WorkComponent] FOREIGN KEY
(
[WorkComponentID]
) REFERENCES [dbo].[WorkComponent] (
[WorkComponentID]
),
CONSTRAINT [FK_WorkItem_WorkItemAreaLUT] FOREIGN KEY
(
[AreaID]
) REFERENCES [dbo].[WorkItemAreaLUT] (
[WorkItemAreaID]
),
CONSTRAINT [FK_WorkItem_WorkItemPhaseLUT] FOREIGN KEY
(
[PhaseID]
) REFERENCES [dbo].[WorkItemPhaseLUT] (
[WorkItemPhaseID]
),
CONSTRAINT [FK_WorkItem_WorkItemPriorityLUT] FOREIGN KEY
(
[PriorityID]
) REFERENCES [dbo].[WorkItemPriorityLUT] (
[PriorityID]
),
CONSTRAINT [FK_WorkItem_WorkItemResolutionLUT] FOREIGN KEY
(
[ResolutionID]
) REFERENCES [dbo].[WorkItemResolutionLUT] (
[WorkItemResolutionID]
),
CONSTRAINT [FK_WorkItem_WorkItemStatusLUT] FOREIGN KEY
(
[StatusID]
) REFERENCES [dbo].[WorkItemStatusLUT] (
[WorkItemStatusID]
),
CONSTRAINT [FK_WorkItem_WorkItemType] FOREIGN KEY
(
[TypeID]
) REFERENCES [dbo].[WorkItemTypeLUT] (
[WorkItemTypeID]
),
CONSTRAINT [FK_WorkItem_WorkOrder] FOREIGN KEY
(
[WorkOrderID]
) REFERENCES [dbo].[WorkOrder] (
[WorkOrderID]
),
CONSTRAINT [FK_WorkItem_WorkVersion] FOREIGN KEY
(
[VersionID]
) REFERENCES [dbo].[WorkVersion] (
[WorkVersionID]
),
CONSTRAINT [FK_WorkItem_WorkVersion1] FOREIGN KEY
(
[VersionReleasedID]
) REFERENCES [dbo].[WorkVersion] (
[WorkVersionID]
)
GO
ALTER TABLE [dbo].[WorkItemAssignment] ADD
CONSTRAINT [FK_WorkItemAssignment_Employee] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employee] (
[EmployeeID]
),
CONSTRAINT [FK_WorkItemAssignment_WorkItem] FOREIGN KEY
(
[WorkItemID]
) REFERENCES [dbo].[WorkItem] (
[WorkItemID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkItemTypeLUT] ADD
CONSTRAINT [FK_WorkItemTypeLUT_WorkType] FOREIGN KEY
(
[WorkTypeID]
) REFERENCES [dbo].[WorkType] (
[WorkTypeID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkOTRate] ADD
CONSTRAINT [FK_WorkOTRate_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkOrder] ADD
CONSTRAINT [FK_Work_WorkOrder] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[WorkType] ADD
CONSTRAINT [FK_WorkType_Department] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [dbo].[Department] (
[DepartmentID]
)
GO
ALTER TABLE [dbo].[WorkVersion] ADD
CONSTRAINT [FK_WorkVersion_Work] FOREIGN KEY
(
[WorkID]
) REFERENCES [dbo].[Work] (
[WorkID]
) ON DELETE CASCADE
GO
