Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    newbie best design q many lookup vs one

    For an inventory system on Oracle…looking for best design… pros and cons

    Our original design had lookup tables for each code in the database. Eg. Condition code table was a parent table to Asset table with a condition code column. This meant quite a few lookup tables, but the design was relatively easy to implement and understand for db staff and developers.

    A new design has been suggested where all codes for all tables are kept in one Code table. (All indicators in one indicator table, all dates in one date table, all remarks in one remark table, and all locations in one location table.)

    The code type table would have columns:
    Code type - eg condition code
    Code type nomen - eg used to describe the condition of the asset.

    The code type table would be a parent table to the code identifier table with columns:
    Code type eg condition code
    Code identifier eg A
    Code identifier nomen eg ready to ship
    Active ind eg Y

    A junction table between each major table and the code identifier table eg (asset code identifier asset) would have columns:

    Asset code id pk eg 1111
    Asset code id eg A
    Asset code type eg condition code
    Asset pk eg 777777

    The second design is confusing to developers and db staff. It also requires the developers to use the application to enforce codes instead of the database. A retreival that used query one table will now have to query many (> 10?) tables.

    I would like to know the pros and cons of using each design.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: newbie best design q many lookup vs one

    Originally posted by Jxr103
    I would like to know the pros and cons of using each design.
    You pretty well answered your own question... which is a good thing, often if you state a question clearly it answers itself.

Posting Permissions

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