寻找自然的关键 [英] Finding the natural key
问题描述
每当我想要查询帮助时，我都会被告知我的设计是错误的，所以这个
时间我在设计阶段发布了一条消息：我怎么去
完美设计以下内容？
我们希望能够为多个模块跟踪用户的时间，现在是
一个Schedule模块和一个Punchlist模块。这些模块已经存在，并且还有许多其他模块我们将添加到
列表中，一次两个或三个 - 所以它应该可能是相当容易地将
添加到相关模块列表中。
在我看来，自然键是UserOrContactId（
$ b的id） $ b特定的人），时间的日期和ReferenceId到它所在的
模块，PunchlistItemId或ScheduleTaskId。我还没有用b / b
来完成外键，一旦桌面设计结束，我就会这么做。 MinutesSpent将是这个人花费的时间来执行特定的计划任务或打卡列表项目，这将是从小时和分钟到数据库的几分钟转换的
a限制每天的总长度不超过24小时。
创建和更新用户/时间是我们所有的标准物品
表用于审计目的。 ForDate将是小时
发生的日期，所以如果完成正确的自然键将会照顾
检查两个条目是否都是相同的一天，即如果条目存在，则应该更新总额
horus。
问题在于我不能将自然键作为主键密钥是因为
你不能在主键中有空值，即它将是一个/或
PunchlistItem或一个Schedule Task，但不是两者都是特定记录。
过去我做过类似RefType =''Schedule''，RefId =
ScheduleTaskId，但是外键不能应用。
那么什么是完美的解决方案，这样当我在几个月内询问有关它的查询问题时，我就不会受到设计的影响
？ Celko，把它全部搞定你的系统中的
- 我不懂数据库设计，等等等等等等等等等等等等等等，但是给我看看完美的解决方案。 />
CREATE TABLE [dbo]。[TimeSpent]（
[UserOrContactId] [int] NOT NULL，
[ForDate] [smalldatetime ] NOT NULL，
[PunchlistItemId] [int] NULL，
[ScheduleTaskId] [int] NULL，
[MinutesSpent] [int] NOT NULL，
[描述] [varchar]（100）COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL，
[注释] [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]不是NULL或[ScheduleTaskId]不是
NULL））
GO
>>每当我想要查询帮助时，我都会被告知我的设计是错误的，所以这个时间我在设计阶段发布了一条消息：我是怎么做的要完美地设计以下内容吗？
首先在
公共新闻组上询问有关特定数据库设计的问题本质上是一个坏主意。
商业/概念模型之后的逻辑数据库设计，因此这个新闻组中没有人熟悉
与您的商业模式不同。您的问题可能与设计的其余部分有关，可能会影响其他相关子系统/模块/
基础设施等。您在此处收到的建议可能基于
其他人认为问题可能不是实际设计
问题，更不用说误解的可能性和
误解了非常高。
我们希望能够为多个模块的用户跟踪时间，现在是一个Schedule模块和一个Punchlist模块。这些模块已经存在，并且还有许多其他模块我们将添加到列表中，一次两个或三个 - 所以应该可以添加到
相关模块相当容易。
根据这个叙述，人们可以得出结论，模块是一个定义明确的
实体，并且可以提供如下表格：
CREATE TABLE模块（
Module_id INT NOT NOT PRIMARY KEY，
Module_name VARCHAR（15）NOT NULL）;
INSERT模块SELECT 1，''Schedule'';
INSERT Modules SELECT 1，''Punchlist'';
....
在我看来，自然键是UserOrContactId（特定人员的id），时间日期和ReferenceId到它所在的模块，
PunchlistItemId或ScheduleTaskId 。我还没有完成外键
并且一旦桌面设计结算就会这样做。 MinutesSpent
将是用户执行特定时间表任务或打卡列表项目所需的时间，该项目将从小时和分钟转换为数据库的数分钟，但约束为每天总长度不超过24小时。
除非你能准确地解释每个方面，否则这并不是很有意义。新闻组帖子限制内系统的
。
根据叙述，如果一个人在给定时间在一个模块中工作，你可以/>
可以表示以下表格中的事实：
CREATE TABLE人员（
Person_id INT NOT NOT PRIMARY KEY，
名称VARCHAR（40）NOT NULL，
...）;
INSERT Persons SELECT 1，''Joe'';
INSERT Persons SELECT 2，''Kim'';
....
CREATE TABLE活动（
Person_id NOT NULL
参考人员（Person_id）
ON UPDATE CASCADE
ON DELETE CASCADE，
Module_id NOT NULL
REFERENCES模块（Module_id）
ON UPDATE CASCADE
ON DELETE CASCADE，
Begin_date INT NOT NOT DEFAULT CURRENT TIMESTAMP，
End_date INT NOT NULL DEFAULT（''9999-12-31''），
...
PRIMARY KEY（Person_id，Module_id））;
INSERT活动SELECT 1,1，''2006-01-01''，''2006-01-03''，...;
INSERT活动SELECT 1,2， ''2006-02-01''，''2006-02-15''，...;
INSERT活动SELECT 2,1，''2006-01-02''，' '2006-01-14''，...;
INSERT活动SELECT 2,2，''2006-01-28''，''2006-02-20''，.. 。;
每个模块都有不同的时间属性，可以推断出一个人在每个模块上花费的时间。如果多个人可以在
相同模块上工作，请考虑将Begin_Date列添加到密钥。在这种情况下，
如果这个表被许多其他表引用，考虑代理可能是一个很好的想法。
就像我说的那样，除非对商业模式进行彻底的分析，否则大部分工作将浪费在荒谬的争论之上而不是愚蠢的问题
喜欢什么是自然的什么不是。如果你觉得这个时间很关键，那么任务是压倒性的，或许考虑一个专业的雇佣可能会给你带来帮助。
-
Anith
Anith说的话。有一个模块表。从长远来看更加幸福。
Aniht在插入声明中有一个拼写错误 - 他们不能同时拥有
相同的ID。我确定这是一个错字。
另外，为什么描述和注释？是选择列表中的描述？如果
所以，请有一个描述表。
Anohter模块可能是假期，很快就会有一个标准
时间跟踪系统。
用户打算在某一天做一些工作，然后他们就是
去记录他们花了30分钟，2小时或
的事实，无论是*特定的时间表任务*并且想要报告它后来的b
。你是说我应该将30分钟变成一个日期范围？
用户不会指定这些信息 - 他们只知道
周二他们花了一个小时工作在星期一的任务1和两个星期二的工作日期任务2的两个小时。
我喜欢模块表的想法，我要去玩桌子
设计一下，看看它是如何工作的 - 谢谢。但我需要使用模块和模块中的行项目的一些
组合作为
的一部分，这是唯一性的关键。
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
>> Whenever I want help on a query, I get told my design is wrong, So thistime 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
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.
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.
这篇关于寻找自然的关键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
