Re: Capturing an employee in a team at a point in time

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Feb 13, 4:17 pm, Rod Plastow <RodPlas...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
At the risk of upsetting Allen may I add my twopennyworth? Allen's design is
faultless and his post is, as ever, lucid. Follow his advice and you won't go
wrong; you will have a robust design that will not need altering.

I suppose we must take your comments at face value and assume
sycophantism rather than sarcasm :)

I cannot agree your comments and, due to the nature of newsgroup posts
(a.k.a. design-by-email), I'm suspect Allen would be wary of agreeing
with you also.

The most obvious flaw if that the tables do not have keys e.g. try
this

INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (1, 'Team1', #2006-01-01 00:00:00#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (2, 'Team1', #2006-01-01 00:00:00#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (3, 'Team1', #2005-06-01 23:59:59#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (4, 'Team1', #2007-01-01 23:59:59#, #2003-01-01 00:00:00#)
;

Opps! We have duplicates, overlapping dates for 'Team 1', end dates
before start dates, etc. You could argue that TeamID is business key
which Allen left intentionally undefined but I'd counter that
disparate teams with the same name existing at the same time is a
little unrealistic. I would guess it's a so-called 'surrogate' lacking
a natural key.

Also consider this scenario:

INSERT INTO Employee (EmployeeID) VALUES (44)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (55, 'Team 2', #2005-12-01#, NULL)
;
INSERT INTO Team (TeamID, EmployeeID, JoinStart, DepartDate)
VALUES (55, 44, #2001-12-01#, NULL)
;

The employee has joined the team (2001-12-01) before it came into
existence (2005-12-01). Again, effective keys and constraints are
required to prevent duplicates and ensure data integrity e.g. may
require a sequenced primary key an employee can only be affiliated
with one team.

Rather than a 'faultless and robust design', Allen has provided a mere
outline sketch that is completely lacking in keys, constraints, data
typing, referential integrity, etc and with no explanation. That's not
meant as a criticism (although admittedly the 'primary key'
designations are a bit misleading i.e. is he proposing constraints or
not?): posting a complete design is a newsgroup thread is hard and
time consuming. Here

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

it took me a few hours, a thousand or so words plus a couple of
hundred lines of code to describe a single history table of the kind
being discussed in this thread, and that was using SQL DDL, being
perhaps the least verbose way of describing a table design

Jamie.

--


.