Re: Q: Historical primary key

Roger Wernersson (roger.wernersson_at_adra.se) wrote:
> ...I want to be able to schedule
> address changes...
> ...I want a scheduleable phone number
> table...
> ...I am thinking about splitting
> the
> customer table into two, one containing static data and the other
> containing dynamic data but it seems like all data is dynamic so it
> would
> look like this:
>
> CREATE TABLE customer (
> name VARCAHR2 (20) NOT NULL,
> PRIMARY KEY (name)
> );
>
> CREATE TABLE customer_h (
> customer VARCHAR2 (20) NOT NULL,
> address VARCHAR2 (20) NOT NULL,
> valid_from DATE NOT NULL,
> invalid_from DATE NOT NULL,
> PRIMARY KEY (customer, valid_from),
> FOREIGN KEY (customer)
> REFERENCES customer (name)
> );
>
> CREATE TABLE phone_h (
> customer VARCHAR2 (20) NOT NULL,
> phone_number VARCHAR2 (20) NOT NULL,
> valid_from DATE NOT NULL,
> invalid_from DATE NOT NULL,
> PRMIARY KEY (customer, phone_number, valid_from),
> FOREIGN KEY (customer)
> REFERENCES customer (name)
> );
>
> How would you do it?

Based on requirements as stated, just as you suggest...except I might add the Customer_Valid_Date in your original draft to the Customer shown above if that is still required....and I might rename Customer_H and Phone_H to something like Customer_Address and Customer_Phone respectively. And maybe remove ph# from the primary key if I only wish to have one effective at a time? Or remove the first table and make Phone point to Address if you don't wish to know of someone without an address...etc., etc.

Note that Valid_From repeated in each of these is *not* a normalization violation because, despite having the same name, they are *not* the same thing:

    Address.Valid_From: date on which the associated *address*

        takes effect.
    Phone.Valid_From: date on which the associated *ph#* takes

        effect.

A question with which I have wrestled for years: whether to unambiguously name such fields (i.e., Address_Valid_From, Phone_Valid_From) or assume a column definition must always be contextual (i.e., Address.Valid_From, Phone.Valid_From).

By the way, what you are tackling here is 4th Normal Form: if 2 non-key attributes do *not* have the *same* relationship to the key, split them into separate tables. I find this is usually dictated as soon as one adds validity timeframe information in the way discussed here...or it makes it easy to add such later if one did the 4NF thing based on proper data definitions anyway :-)

You also offer my favourite example: typically address and ph# appear to have the same relationship 'coz someone moving will *usually* change both at the same time...except, of course (and as happened to a friend), they may move within an exchange area and keep the ph#...or, more often these days, they get phone-tically rezoned and change their ph# area code without a change of address...

HTH...BJ  

-- 
Barry Johnson  -  BJohnson_at_WorldBank.Org