Finding the natural key

[pb648174]

Whenever I want help on a query, I get told my design is wrong, So this
time I'm posting a message during the design phase: How am I going to
perfectly design the following?

We want to be able to track time for users for multiple modules, for
now a Schedule module and a Punchlist module. These modules already
exist and there are dozens of other modules which we will add to the
list as well, two or three at a time - so it should be possibly to add
to the list of related modules fairly easily.

In my mind the natural key is the UserOrContactId (an id for a
particular person), the date the time is for and ReferenceId to the
module it is in, either PunchlistItemId or ScheduleTaskId. I haven't
done the foreign keys yet and will do that once the table design is
settled. The MinutesSpent is going to be the minutes the person spent
doing a particular schedule task or punchlist item, which will be
converted from hours and minutes to just minutes for the database with
a constraint for total miuntes per day not being more than 24 hours.
Create and Update User/Time is a standard thing we put on all our
tables for auditing purposes. The ForDate will be the date the hours
happen so if the natural key is done right that will take care of
checking that two entries aren't made on the same day, i.e. the total
horus should just be updated if an entry exists.

The problem is that I can't make the natural key a primary key because
you can't have nulls in the primary key, i.e. it will be either a
PunchlistItem OR a Schedule Task, but not both for a particular record.
In the past I have done something like RefType = 'Schedule', RefId =
ScheduleTaskId, but then foreign keys can't be applied.

So what's the perfect solution so that I won't get heckled for design
when I ask a query question about it in a few months? Celko, get it all
out of your system - I don't understand database design, blah, blah,
blah, but show me the perfect solution.

CREATE TABLE [dbo].[TimeSpent](
[UserOrContactId] [int] NOT NULL,
[ForDate] [smalldatetime] NOT NULL,
[PunchlistItemId] [int] NULL,
[ScheduleTaskId] [int] NULL,
[MinutesSpent] [int] NOT NULL,
[Description] [varchar](100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL,
[Notes] [text] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL,
[CreateUser] [int] NOT NULL,
[CreateTime] [smalldatetime] NOT NULL,
[UpdateUser] [int] NOT NULL,
[UpdateTime] [smalldatetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[TimeSpent] WITH CHECK ADD CONSTRAINT [CK_RefIds]
CHECK (([PunchlistItemId] IS NOT NULL OR [ScheduleTaskId] IS NOT
NULL))
GO

[Doug]

>A user is going to do some work on a particular day and then they are
going to record the fact that they spent 30 minutes, 2 hours or
whatever on a *particular schedule task* and want to report on it

so, back to the subject line. The "natural key" is description, notes,
date, and module.

or, the identity key and display the rest of the junk.

BTDT.

[Anith Sen]

>> Oh yeah.. And why are your dates created as integers?

It is supposed to be a temporal datatype like DATETIME or SMALLDATETIME
depending on your needs.

--
Anith

[Erland Sommarskog]

pb648174 (go****@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_TimeSpentIte m] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TimeSpentItem] WITH CHECK ADD CONSTRAINT
[CK_TimeSpentIte m_ValidReferenc e] 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_Gene ral_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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

You mean something different than TimeSpentItem or is TimeSpentItem
sufficient? I usually use varchar, but this time I'm experimenting with
not just to see how much of a hassle it is. No matter what we make the
text limit, someone, somewhere ends up complaining about it.