Re: Q: Historical primary key

Roger Wernersson 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 VARCHAR2 (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),
> FOREIGN KEY (customer, customer_valid_from)
> REFERENCES customer (name, valid_from)
> );

Roger,

	I'm not sure how you are using valid_from and invalid_from,
	but I always use START_DATE and END_DATE, which may mean the
	same thing.  Then I would create a view for each table like
	this:

	Create table valid_customer as select * from customer where
	sysdate > start_date and sysdate < end_date;

	Do the same for phone.  Now you can use the valid_* tables
	if you only want valid customers and/or phones.  If you want
	to use other than current date just replace sysdate with your
	date.  You now have the option of joining using only valid
	customers and phones or use the base tables for other queries.

	It also makes a difference whether you want to portray this
	logical relationship:

	a customer has many addresses, and each address can have many
		phone numbers (example 1)

	OR:

	a customer has many addresses and also has many phone numbers
	(example 2)

	One suggestion would be to create a non-intelligent primary 
	key for customer_h and use application logic to insure that 
start_dates and end_dates for a particular customer name
	don't overlap.  Then you can use the non-intelligent key as 
	your foreign key instead of having to use the valid_from
	date as part of the key.  This might simplify things a little.
	
	On the other hand, it could also confuse things.  E-mail if
	you want to discuss it any further.  Be glad to help.

	

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.