Posted by --CELKO-- on 10/06/58 11:30

Your DDL is wrong in almost every way possible. IDENTITY is not a key,
barcodes are fixed length and none of them are CHAR(50) -- you never
did even the minimal research!! You use the magical, "I have no
brains!!" VARCHAR(50) all over the place, TIMESTAMP is a reserved word
in SQL, etc.

Where did you get the stupid idea that you need to put "fld-" and
"tbl-" prefixes on names? In violation of both common sense and
ISO-11179? One of the major principles of RDBMS is to avoid redundance;
Do you put "noun-" in your English?

When you design a history table, you need to learn that time comes in
durations; you need a (stsrt, end). You need to think of the schema as
a whole and not a bunch disjoint files. you need to avoid havign more
NULLs than the entie payroll of Genral Motors. More like this: .

CREATE TABLE EmpLocationHistory
(emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
location_code INTEGER NOT NULL
REFERENCES Locations(location_code)
ON UPDATE CASCADE,
start_time DATETIME NOT NULL,
end_time DATETIME, -- null means current
CHECK (start_time < end_time),
PRIMARY KEY (emp_id, location_code,start_time),
etc. );

Google how to code for this schema.

Among the errors in this posting, you do not know that SQL uses
ISO-8601 format for temporal data. You might want to look at the
research on camelCase and program readability; it sucks because the eye
jumps to the uppercase letter then flicks back to the start of the
word.

I was not kidding when I said that your code is wrong in almost every
way possible.

[Back to original message]