Q: Historical primary key

From: Roger Wernersson <roger.wernersson_at_adra.se>
Date: 1997/01/15
Message-ID: <32DCF592.20AF@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         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)
);

I am not comfortable with this solution. 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?

-- 
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