Re: Q: Historical primary key

Roger Wernersson (roger.wernersson_at_adra.se) wrote:
: Hello all!
 

: Let's say I have a customer table and I want to be able to schedule
: address changes then I might create a table that looks like this:
 

: CREATE TABLE customer (
: name VARCAHR2 (20) NOT NULL,
: address VARCHAR2 (20) NOT NULL,
: valid_from DATE NOT NULL,
: invalid_from DATE NOT NULL,
: PRIMARY KEY (name, valid_from)
: );
 

: Now, this part was easy. Now say I want a scheduleable phone number
: table. It might look like this:
 

: CREATE TABLE phone (
: customer VARCHAR2 (20) NOT NULL,
: customer_valid_from DATE NOT NULL,
: phone_number VARCHAR2 (20) NOT NULL,
: valid_from DATE NOT NULL,
: invalid_from DATE NOT NULL,
: PRMIARY KEY (customer, customer_valid_from, phone_number, valid_from)
: );
 

: I am not comfortable with this solution. How would you do it?

Hi Roger -

I'm not Mr. Normalization, but don't the above tables violate normalization rules ? The fields 'valid_from' and 'invalid_from' appear to only be dependent on 'phone_number'. Maybe you could break them out into another table ?

Just my two cents...

Brett Cunningham
zgeist_at_ee.net Received on Fri Jan 17 1997 - 00:00:00 CST