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_at_127.0.0.1>
>>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.
Date: Fri, 24 Dec 2004 13:25:30 -0800
Message-ID: <41cc8842$1_3_at_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 - 22:25:30 CET