Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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