Re: primary and foregin keys

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 24 Dec 2004 13:25:30 -0800
Message-ID: <41cc8842$1_3@127.0.0.1>

rallykarro_at_hotmail.com wrote:

> Tony Andrews wrote:
> 

>>rallykarro_at_hotmail.com wrote:
>>
>>>As far as I understand the primary key shold be set on the columns
>>>which make the rows in the table unique?!
>>>Since I want history tracking of my products the valid post is
>>
>>actually
>>
>>>the one with the latest date. This is why the product_number is not
>>>unique itself in my "product" table.
>>
>>True. But if you want history tracking per product then you need 2
>>tables:
>>
>>create table product (prod_number integer constraint pk_prod primary
>>key, ...);
>>
>>create table product_history (prod_number references product ,
>>prod_date date,
>>..., constraint pk_prod_hist primary key (prod_number, prod_date));
>>Now you storage table can have a foreign key to the product table.

> 
> 
> 
> Okey, I get your point. This solution will make me do a transfer of
> data from the "product" table into the "product_history" table each
> time the "product" table is updataded with an already existing
> "product_number". Let's imagine that I go with my solution. How would
> you solve the problem if you only got the tables "products" and
> "storage". Would you use a foregin key pointing out the
> "product_number" in "products" from "storage" even if the
> "product_number" itself not is the primary key in "products" or is this
> totaly against the constraint concept?

This discussion has all the hallmarks of a bad design and, having read most of the thread, I am left wondering how many of the contributors have actually taken any time to study data normalization?

One and only one table is required.
Add a status column consisting of a single byte flag. Move no data.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Dec 24 2004 - 15:25:30 CST