Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    pk vs index in hierarchal database structure?

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    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)

    ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •