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_General_CP1_CI_AS NOT
NULL,
[Notes] [text] COLLATE SQL_Latin1_General_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

[Anith Sen]

>> 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?
First of all asking questions regarding a specific database design on a
public newsgroup is inherently a bad idea. Logical database design following
business/conceptual model and as such nobody in this newsgroup is familiar
with your business model other than you. Your problem may well be related to
the rest of the design and may affect other relevant subsystems/ modules/
infrastructure etc. And the advice which you receive here may be based on
what others perceive as the problem and may not be the actual design
problem, not to mention the chances of misunderstanding and
misinterpretation are very high.
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.
Based on this narrative, one could conclude that a module is a well defined
entity and could come up with a table like:

CREATE TABLE Modules (
Module_id INT NOT NULL PRIMARY KEY,
Module_name VARCHAR(15) NOT NULL ) ;

INSERT Modules SELECT 1, 'Schedule' ;
INSERT Modules SELECT 1, 'Punchlist' ;
....
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.

This does not make full sense, unless you can explain precisely each aspect
of the system within the limits of a newsgroup post.

Based on the narrative, if a person works in a module at a given time, you
can represent those facts in tables like:

CREATE TABLE Persons (
Person_id INT NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL,
... );

INSERT Persons SELECT 1, 'Joe' ;
INSERT Persons SELECT 2, 'Kim' ;
....

CREATE TABLE Activities (
Person_id NOT NULL
REFERENCES Persons ( Person_id )
ON UPDATE CASCADE
ON DELETE CASCADE,
Module_id NOT NULL
REFERENCES Modules ( Module_id )
ON UPDATE CASCADE
ON DELETE CASCADE,
Begin_date INT NOT NULL DEFAULT CURRENT TIMESTAMP,
End_date INT NOT NULL DEFAULT ('9999-12-31'),
...
PRIMARY KEY (Person_id, Module_id ) );

INSERT Activities SELECT 1, 1, '2006-01-01', '2006-01-03', ... ;
INSERT Activities SELECT 1, 2, '2006-02-01', '2006-02-15', ... ;
INSERT Activities SELECT 2, 1, '2006-01-02', '2006-01-14', ... ;
INSERT Activities SELECT 2, 2, '2006-01-28', '2006-02-20', ... ;

Having distinct temporal attributes for each module, allows you to infer the
time spent on each module by a person. If multiple people can work on the
same module, consider adding the Begin_Date column to the key. In that case,
if this table is being referenced by many other tables, it might be a good
idea to consider a surrogate.

Like I said, unless a thorough analysis of the business model is done, most
of the effort will be wasted over nonsensical arguments over silly issues
like what is "natural" and what is not. If you find this time critical and
the task is overwhelming, perhaps considering a professional hire might
help.

--
Anith

[Doug]

What Anith said. Have a modules table. Much happier in the long run.
Aniht has a typo in the insert statement - they can't both have the
same id. I'm sure it is a typo.

Also,why description AND notes? Is description from a pick list? if
so, have a descriptions table.

Anohter module could be "Vacation", and pretty soon you have a standard
time tracking system.

[pb648174]

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
later. Are you saying I should turn the 30 minutes into a date range?
The user won't specify that information - they will just know that on
Tuesday they spent an hour working on schedule task 1 on Monday and two
hours working on schedule task 2 on Tuesday.

I do like the Modules table idea, I am going to play with the table
design a bit and see how that works - Thanks. But I need to use some
combination of the Module and the line item in the module as part of
the key to uniqueness.

[pb648174]

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

I'll probably take out the description column, but sometimes people
want a short description of something but they also want to type a
novel to describe it.. Having one short and one long text fields lets
them do that.

[pb648174]

I played with adding a Module table but then I lose ref integrity when
the module line items are deleted. So I would need to add a
ModuleLineItem table as well (??) and then I have ref integrity
problems with the other tables linking to it...

P.S. Identity columns are awesome.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

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
later. Are you saying I should turn the 30 minutes into a date range?
The user won't specify that information - they will just know that on
Tuesday they spent an hour working on schedule task 1 on Monday and two
hours working on schedule task 2 on Tuesday.
Given that information, it seems reasonable to just have a counter,
suggestively in minutes. (Disclaimer: I did not read your original post
very closely.)

....however, it reminds me of our old time-reporting system we had my
office for several years, a hack developed by a colleague over a
Christmas holiday. Here you could enter start and stop for activities.
You didn't have to, and the only thing that mattered was was the hours
worked. But it was very user-friendly, since I didn't have to count
hours in my head, I could just write down how my day had been, easily
rearranging some hours if I wanted to save some typing.
Oh yeah.. And why are your dates created as integers?

I suspect that was a type of Anith. Maybe he had hours or minutes
originally and then changed his mind.

In any case, there is a flaw with his date formats, remove the hyphens:
99991231 etc, to make sure that the dates are always interpreted the
same.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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]

That is a good idea, I think I might include that feature, but also
allow minutes/hours to be entered. In that case, should I have just a
start and end date and just make one up if they enter minutes, or just
have the minutes as null if they start/stop and vice versa? I think I
lean towards the second option so later I'll be able to tell if they
did start/stop or manually entered their time.

[pb648174]

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))
GO

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,
[CreateUser] [int] NOT NULL,
[CreateTime] [smalldatetime] NOT NULL,
[UpdateUser] [int] NOT NULL,
[UpdateTime] [smalldatetime] NOT NULL,
CONSTRAINT [PK_TimeSpentActual_1] PRIMARY KEY CLUSTERED
(
[UserOrContactId] ASC,
[ForDate] ASC,
[TimeSpentItemId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[TimeSpentActual] WITH CHECK ADD CONSTRAINT
[FK_TimeSpentActual_TimeSpentItemId] FOREIGN KEY([TimeSpentItemId])
REFERENCES [dbo].[TimeSpentItem] ([Id])
GO
ALTER TABLE [dbo].[TimeSpentActual] WITH CHECK ADD CONSTRAINT
[CK_TimeSpentActual_ValidForDate] CHECK ((datepart(hour,[ForDate])=(0)
AND datepart(minute,[ForDate])=(0) AND datepart(second,[ForDate])=(0)))
GO
ALTER TABLE [dbo].[TimeSpentActual] WITH CHECK ADD CONSTRAINT
[CK_TimeSpentActual_ValidTime] CHECK ((isnull([Minutes],(0))<(1439)))

[Doug]

>played with adding a Module table but then I lose ref integrity when
the module line items are deleted. So I would need to add a

so, don't let them be deleted. Trust me on this one, you REALLY want to
have a modules table.

[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_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, es****@sommarskog.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.