Re: Primary Keys and Foreign Keys

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.

One problem with this design is that you can't have an item twice in the same order. If this is in accordance to the application, it shouldn't be a big deal, unless the application has to change this requirement at some time. I'd prefer a primary key consisting of ORDER_ID and ORDER_ITEM_NR. Otherwise it looks just like the standard way to model such a relation.

Regards,
Heinz Received on Thu Nov 30 2000 - 07:39:46 CET