Re: How to normalize these 3 tables?

Susana,
I would have done it this way.

table PROJECT:
project_id integer primary key
project_name varchar2(20)

--as you would need more information about a drog table DRUG:
drug_id integer primary key
drug_name varchar2(20)

--

table DRUG_PROJECT:
drug_id integer
project_id integer foreign key project(project_id)

(drug_id, project_id) Primary key



If you needto have a single column as primary key for joint between drug and
project, then there is no way you could have a normalised database.
but if this is what you want then remove the column drug_project_id  from
Drug table
and
create a unique index on drug_project (project_id, drug_id )
This index will make sure that no duplicated records are entered.

Tom

<susana73_at_hotmail.com> wrote in message news:8a6p8t$dne$1_at_nnrp1.deja.com...


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