FK -> non PK - bad design?
Date: 7 Apr 2003 21:55:19 -0700
Message-ID: <5606b639.0304072055.29001bfe_at_posting.google.com>
Hello,
I created 2 tables and was told that having a FK in one point to a non-PK colum in the other one is a bad relational model design. I was wondering if anyone could teach me why that is so. The tables in questions are simple and look like this:
CREATE TABLE user_info
(
id SERIAL CONSTRAINT pk_user_info_id PRIMARY KEY , account_id INTEGER CONSTRAINT fk_user_info_account_id REFERENCES account(id) , plan_type INTEGER CONSTRAINT fk_user_info_service_plan_typeREFERENCES service_plan(type)
...
);
CREATE TABLE service_plan
(
id SERIAL
CONSTRAINT pk_service_plan_id PRIMARY KEY ,
type INTEGER NOT NULL UNIQUE
...
);
I was told that this design, where 'plan_type' FK in user_info table
points to a column in service_plan that is _not_ a PK, is bad.
Is it really?
Could somebody please explain why this is bad?
When I designed these two tables I was dwelling on whether I should
point plan_type FK in user_info to service_plan's PK (the id column)
or the non-PK type column. I decided that pointing to 'type' instead
would be better, because 'type' will always contain a known value
specified when the row is INSERTed.
Therefore, I reasoned, if I DELETE a row from service_plan, and INSERT
a new row with the same value for 'type' back into service_plan, I
will not break all rows in user_info that pointed to the deleted row
in service_plan.
I realize that I should not be able to DELETE a row from service_plan
while rows in user_info point to it.....but still, I found this design
more intuitive at first....now I am no longer sure.
Thank you! Received on Tue Apr 08 2003 - 06:55:19 CEST