Re: How to normalize these 3 tables?

Susana,

Why do you need the 3th table DRUG_PROJECT ? Just specify a FK within PROJECT referencing DRUG and drop the FK constraint (and column drug_project_id) in DRUG. Then you have implemented the fact that the same drug can in many projects and you have your own unique id specifing this relationship, namely PROJECT_ID.

If you allow that a certain project can be associated with many drugs, than you need to create an intermediate table with a primary key comprised of 2 columns, defined as 2 FK's referencing PROJECT and DRUG. If you would like to add an unique identifier specifying this relationship you can add a non-key column and specify an unique constraint on this new column (probably filled up by a sequence ?) or you could define a new column and make this your primary key and specify a unique conmstraint on both FK's.

Regards,

Patrick Dewael

susana73_at_hotmail.com wrote:

> Hi, I was given 3 tables and am wondering how to normalize or
> restructure them:
>
> table PROJECT:
> project_id integer primary key
> project_name varchar2(20)
>
> table DRUG:
> drug_id integer primary key
> drug_project_id integer foreign key drug_project(drug_project_id)
>
> table DRUG_PROJECT:
> drug_project_id integer primary key
> project_id integer foreign key project(project_id)
> drug_id integer foreign key drug(drug_id)
>
> The reason I have drug_project is because the same drug can associate
> with different projects and I would like to have an unique id for each.
> But it looks like it won't work since I have to foreign keys pointing
> each other on 2nd and 3rd tables.
>
> I appreciate your help. Thanks!
>
> Susana
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Mar 09 2000 - 11:28:09 CET