Posted by Erland Sommarskog on 10/01/12 11:42

pb648174 (google@webpaul.net) writes:
> Ok, so here is the final solution as I have it now, sans the start/stop
> stuff and references to other tables:
>
> CREATE TABLE [dbo].[TimeSpentItem](
> [Id] [int] IDENTITY(1,1) NOT NULL,
> [PunchlistItemId] [int] NULL,
> [ScheduleTaskId] [int] NULL,
> CONSTRAINT [PK_TimeSpentItem] PRIMARY KEY CLUSTERED
> (
> [Id] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> ALTER TABLE [dbo].[TimeSpentItem] WITH CHECK ADD CONSTRAINT
> [CK_TimeSpentItem_ValidReference] CHECK (([PunchlistItemId] IS NOT
> NULL OR [ScheduleTaskId] IS NOT NULL))

Can both be non-NULL? What does that mean? That you are working
on both in parallel?

Should there not be UNIQUE constraint on the two?

Having re-reread your original post a little better, I agree entirely
with anyone else tha you need a Modules table. You will have to change
that TimeSpentItem table for each new module that is added.

> CREATE TABLE [dbo].[TimeSpentActual](
> [UserOrContactId] [int] NOT NULL,
> [ForDate] [smalldatetime] NOT NULL,
> [TimeSpentItemId] [int] NOT NULL,
> [Minutes] [int] NOT NULL,
> [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

The users really need the ability to write novels? Would not a
varchar(8000) do? Text is fairly messy to deal with.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]