Re: Primary Keys and Foreign Keys

From: Jan Hidders <hidders_at_REMOVE.THIS.win.tue.nl>
Date: 29 Nov 2000 17:07:26 GMT
Message-ID: <903d4e$5db$1_at_news.tue.nl>


Chris Collins wrote:
> This is releated to the thread titled 'primary key in a hierarchy'.
>
> Take for instance an ORDER has ORDER_ITEMS this can be represented in two
> tables very nicely.
>
> ORDER
> ORDER_ID
> ORDER_DATA
> ...
>
> ORDER_ITEMS
> ORDER_ID
> ITEM_ID
> OTHER ITEM DATA
> ...
>
> add a third table ITMES that has all possible items
> ITEMS
> ITEM_ID
> ITEM_DATA
> ...
>
> In my opinion the keys should be as follows:
> alter table ORDER add constraint primary key (ORDER_ID);
> alter table ORDER_ITMES add constraint primary key (ORDER_ID, ITEM_ID);
> alter table ITEMS add constraint primary key (ITEM_ID);
>
> alter table ORDER_ITEMS add constraint FOREIGN key "fk1" (ORDER_ID)
> references ORDER on (ORDER_ID);
> alter table ORDER_ITEMS add constraint FOREIGN key "fk2" (ITEM_ID)
> references ITEMS on (ITEM_ID);
>
> Note: ORDER_ID and ITEM_ID are external data not some internal counter.
> this is data that the user will see and use.
>
> Please offer fead back on problems with this or other methods of doing this
> and why they are better.

I see nothing wrong with your design; it is the standard way to represent a many-to-many relationship. I would consider optimization by introducing articial primary keys only if the applications hide the table design from the end-users. But even then you are probably either making things more complicated or are ignoring some database constraints.

> I am currently evaluating many ERD Datamodel designing software and
> none have allowed me to have on column participate in both a primary
> key and a foreign key.

Can you tell us which ones you are evaluating?

-- 
  Jan Hidders
Received on Wed Nov 29 2000 - 18:07:26 CET

Original text of this message