|
Posted by pb648174 on 10/01/11 11:42
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
Navigation:
[Reply to this message]
|