Re: Deriving unique rows from historical data

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.

.

Relevant Pages

  • Re: SSCE_M_CYCLEDETECTED
    ... We have MDB file with schema, this schema is extracted with MSAccess VSA into SQL statements which are then executed ... fkeyCrdDataset int DEFAULT 0 NOT NULL REFERENCES tblDatasetsON ... Removing "ON UPDATE CASCADE" from this line eliminates the ...
    (microsoft.public.sqlserver.ce)
  • Re: object model to table design mapping problem
    ... the INCITS H2 Database Standards Committee(nee ANSI X3H2 ... NOT NULL PRIMARY KEY, ... REFERENCES Courses, ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)
  • Re: String Search
    ... Sample data is also a good idea, ... (proj_nbr INTEGER NOT NULL PRIMARY KEY, ... ON UPDATE CASCADE ... REFERENCES Projects ...
    (microsoft.public.sqlserver.programming)
  • Re: Redundant Indexes?
    ... ALTER TABLE.WITH NOCHECK ADD CONSTRAINT PRIMARY KEY CLUSTERED ... CONSTRAINT FOREIGN KEY ... REFERENCES., ...
    (microsoft.public.sqlserver.server)
  • import to clone schema revisited
    ... Task is to clone a schema. ... tablespace references to the TS of the original exported objects. ... Various solutions found on the web involved extracting the DDL from the ... select 'spool off' from dual; ...
    (comp.databases.oracle.server)
Loading