|
|
Posted by Anith Sen on 10/01/81 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?
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
[Back to original message]
|