Q: Historical primary key

From: Roger Wernersson <roger.wernersson_at_adra.se>
Date: 1997/01/15
Message-ID: <32DCF414.3E59@adra.se>#1/1

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?

-- 
Sport radio: people listening to people watching people having fun
Mailto:roger.wernersson_at_adra.se
BTW: All opinions are mine, all mine, and nobody's but mine.

Received on Wed Jan 15 1997 - 00:00:00 CST