Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Bill Of Material Database Design

    I will start to design a database for storing the Bill Of Material. The first idea now I had is to create two tables as below.
    --------------- / -----------------
    | |-----------------| |
    | Item | \ | Item |
    | master | | details |
    | |-----------------| |
    --------------- -----------------
    Each Item Master has many Item details and each Item Details may be the final component or another Item Master. This way, the full list of Bill Of Material can be listed out by looping these two tables.
    With this design, is there any disadvantages? Is this the popular design? Or is there any other better alternatives? Please advise, thanks.

    SK

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Bill Of Material Database Design

    Originally posted by siukaileung
    I will start to design a database for storing the Bill Of Material. The first idea now I had is to create two tables as below.
    --------------- / -----------------
    | |-----------------| |
    | Item | \ | Item |
    | master | | details |
    | |-----------------| |
    --------------- -----------------
    Each Item Master has many Item details and each Item Details may be the final component or another Item Master. This way, the full list of Bill Of Material can be listed out by looping these two tables.
    With this design, is there any disadvantages? Is this the popular design? Or is there any other better alternatives? Please advise, thanks.

    SK
    That looks fairly standard. All the information about a component would be in Item Master. Item Detail would just link a component to its subcomponent(s), it would not contain information about the subcomponent, e.g.:

    Item_Master( ID, Name, Size, Color, Weight, ... );
    Item_Detail( Master_ID, Detail_ID );

    If you want each subcomponent to be part of only 1 master, then Detail_ID would be unique.

    This model allows more flexibility than the other common alternative of a recursive or "pig's ear" relationship:

    Item( ID, Master_ID, Name, Size, Color, Weight, ... );

    where Master_ID would be NULL for a top-level component.

    The first method is more flexible because:
    (a) It allows for a many-to-many relationship if required
    (b) It allows multiple hierarchies if required (by adding a hierarchy_type column to Item_Detail)

Posting Permissions

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