Re: Primary key on one or two fields

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 6 Aug 2003 15:50:11 -0700
Message-ID: <a264e7ea.0308061450.4f9e1a9b_at_posting.google.com>

>> I currently have the following information: <<

Want to share it with us? Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

From the narrative, is sounds like you meant to have a table for Rates and their history, but someone split out the attributes of these rates into too many talbes, thus destroying the data model for them. Try this:

CREATE TABLE RateHistory

(rate_id INTEGER NOT NULL, -- standard industry code?
 rate_name CHAR(15) NOT NULL,
 rate_type CHAR(1) DEFAULT '?' NOT NULL
           CHECK(rate_type IN (...)),

 rate FLOAT NOT NULL,
 effective_date DATE NOT NULL,
 termination_date DATE, -- null means current rate  CHECK (effective_date <= termination_date),  ...,
 PRIMARY KEY (rate_id, effective_date));

if there is an industry standard code for the revisions, then use it in this history table. This VIEW might be handy:

CREATE VIEW CurrentRates (..., termination_date) AS SELECT ..., CURRENT_TIMESTAMP
     FROM Rates
    WHERE termination_date IS NULL;

>> First: Each rate has a unique RevisionId (I can get the database
to generate this using autonumber/identity). <<

No, IDENTITY, GUID, ROWID and other proprietary, non-relational crap are **by definition** not keys. At best, they are redundant in a good data model and since there is no way to verify them against the reality of the data model, you can kiss data integrity goodbye.

>> ..minimum number of bits of information to define the record [sic]
(the RevisionIds will be smaller). <<

You're still thinking in terms of physical file structures and not logical models. You even use the words "record" and "field", like this was a file system and want a sequential record number in it.

Who cares about the size yet? Get the model right and then worry about the PHYSICAL storage and speed. Hey, if the data does not have to be right, I can make it really fast :)

>> It would also allow me to join the RateRevisionEmployees table
directly to the Rates table. <<

What is the meaning of this table in your data model? I did not understand that at all. Received on Thu Aug 07 2003 - 00:50:11 CEST