Posted by teddysnips on 12/22/05 17:25

Table DDL below:

The tables I have contain Timesheet information. Each row in the
tblTSCollected table contains an entry for an employee into the
timesheet system, specifically by scanning the barcode on their badge.

A whole bunch of business logic periodically attempts to "pair" these
into logically matched scans. For example, some employees will scan in
and out of a single place of work. For these there will be a row
written to the tblTSRuleApplied table which contains, inter alia and
some redundant data, the fldCollectedID for the two rows. The earlier
will be put into the fldStartTimeCollectedID, and the later into the
fldEndTimeCollectedID. Some employees will clock on at their base,
then perform sub-duties at different locations during the day, and
clock off at their home base at the end of their shift. For these, the
system would identify the outer records as a matching pair, and then
pair up inner records by location.

However, if the employee fails to enter a valid "clocking in and out"
pair (for example, if they clock in at the wrong location) the system
needs to generate a "dummy" "clocking in and out" record for the
payroll department. Ideally, this would have NULL values in the
fldStartTimeCollectedID and fldEndTimeCollectedID columns. This would
alert a user in a different part of the system, where missing
timesheets were being arbitrated, that an employee appeared to have
failed to clock in for that day. Of course, the user could see
on-screen that they had clocked in, but at an incorrect location.

Unfortunately, the database designer is not here for the moment (he was
knocked off his bicycle recently), but he put a unique index on the
tblTSRuleApplied table that prevents the same value being entered into
the fldStartTimeCollectedID and fldEndTimeCollectedID columns. This is
generally A Good Thing, since we don't want the same timesheet scan to
form both a "clocking on" event and a "clocking off" event.

So, is there any way of retaining the requirement that the
fldStartTimeCollectedID and the fldEndTimeCollectedID columns may not
contain the same value in a single row, UNLESS that value is NULL in
which case all is hunky dory. I should add that the clients don't much
care for Triggers (and neither do I for that matter).

Many thanks if you are able to help.

Edward


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
FK_tblTSRuleApplied_tblTSCollected
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApplied_tblTSCollected1]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSRuleApplied] DROP CONSTRAINT
FK_tblTSRuleApplied_tblTSCollected1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSArbAccept_tblTSRuleApplied]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
FK_tblTSArbAccept_tblTSRuleApplied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSCollected_tblTSRuleApplied]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
FK_tblTSCollected_tblTSRuleApplied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSCollected]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSCollected]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRuleApplied]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSRuleApplied]
GO

CREATE TABLE [dbo].[tblTSCollected] (
[fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldEmployeeID] [int] NULL ,
[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[fldTimeStamp] [datetime] NULL ,
[fldRuleAppliedID] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[fldProcessed] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRuleApplied] (
[fldEmpRuleID] [int] NOT NULL ,
[fldRuleAppliedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldStartTime] [datetime] NULL ,
[fldEndTime] [datetime] NULL ,
[fldStartTimeCollectedID] [int] NULL ,
[fldEndTimeCollectedID] [int] NULL ,
[fldStartArbStatus] [smallint] NULL ,
[fldEndArbStatus] [smallint] NULL ,
[fldDurationArbStatus] [smallint] NULL ,
[fldPrimary] [smallint] NOT NULL ,
[fldDateEntered] [datetime] NULL ,
[fldEnteredBy] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSCollected_fldProcessed] DEFAULT (0) FOR
[fldProcessed],
CONSTRAINT [PK_tblTimesheetCollected] PRIMARY KEY CLUSTERED
(
[fldCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRuleApplied] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRuleApplied_fldPrimary] DEFAULT (1) FOR
[fldPrimary],
CONSTRAINT [PK_tblTSRuleApplied] PRIMARY KEY CLUSTERED
(
[fldRuleAppliedID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_tblTSRuleApplied_1] UNIQUE NONCLUSTERED
(
[fldStartTimeCollectedID],
[fldEndTimeCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] ADD
CONSTRAINT [FK_tblTSCollected_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSCollected_tblLocation] FOREIGN KEY
(
[fldLocationCode]
) REFERENCES [dbo].[tblLocation] (
[fldLocationCode]
),
CONSTRAINT [FK_tblTSCollected_tblTSRuleApplied] FOREIGN KEY
(
[fldRuleAppliedID]
) REFERENCES [dbo].[tblTSRuleApplied] (
[fldRuleAppliedID]
)
GO

ALTER TABLE [dbo].[tblTSRuleApplied] ADD
CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected] FOREIGN KEY
(
[fldStartTimeCollectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSCollected1] FOREIGN KEY
(
[fldEndTimeCollectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSDurationStatus] FOREIGN KEY
(
[fldDurationArbStatus]
) REFERENCES [dbo].[tblTSDurationStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSEmpRules] FOREIGN KEY
(
[fldEmpRuleID]
) REFERENCES [dbo].[tblTSEmpRules] (
[fldEmpRuleID]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus] FOREIGN KEY
(
[fldStartArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApplied_tblTSTimeStatus1] FOREIGN KEY
(
[fldEndArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
)
GO

[Back to original message]