pk vs index in hierarchal database structure? [Archive]

gilaniKashif

07-08-02, 14:52

Hi,
I am working on a database design. The problem is that the structure of the information is hierarchal, which means the primary keys from the upper level is always a part of the primary key of below levels making it incremental composite primary key.
Eg.
Table A
---------
VARCHAR(5) SHOP_ID (PK)
VARCHAR(50) NAME

PK: SHOP_ID

Table B
---------
VARCHAR(5) SHOP_ID (FK)
VARCHAR(5) MENU_ID (PK)
VARCHAR(50) TITLE

PK: SHOP_ID & MENU_ID

TABLE C
----------
VARCHAR(5) SHOP_ID (FK)
VARCHAR(5) MENU_ID (FK)
VARCHAR(5) ITEM_ID (PK)

PK: SHOP_ID & MENU_ID & ITEM_ID

and so on....

I end up having 7 fields of compostite primary key in the lower most table in the hierarchy.

What do I do?
1) Show I use all the fields as a composite primary key?
2) Make Auto Increment as a primary key and all other foreign keys as indexes?
3) Any other solution?

Apel

07-11-02, 11:09

Usually you should not include all the pks of the master tables in your detail tables. It is sufficient to only store the pk of the direct master table to maintain data integrity. A 7 fields composite key is likely to be slow, especially as you already have quite wide primary keys.
Hower if you often need to reference the top master table from the lower detail tables you might want to insert those as foreign keys, but no need for a composite key. Another advantage of having multiple indexes on the single fields (rather than one composite on the pk) is it can efficiently be used to access any of the fields not only the first or the complete key. Otherwise the structure would look like:

Table A
---------
VARCHAR(5) SHOP_ID (PK)
VARCHAR(50) NAME

Table B
---------
VARCHAR(5) SHOP_ID (FK)
VARCHAR(5) MENU_ID (PK)
VARCHAR(50) TITLE

TABLE C
----------
VARCHAR(5) MENU_ID (FK)
VARCHAR(5) ITEM_ID (PK)

TABLE D
----------
VARCHAR(5) ITEM_ID (FK)
VARCHAR(5) PROPERTY_ID (PK)

....