locked
Table design for Product hierarchy - OLTP RRS feed

  • Question

  • Table design for Optional hierarchy - OLTP

    Hi,

    I am designing a data model for a transaction system and looking for some assistance in find the correct table structure for handling Product hierarchy.

    Our product hierarchy consists of 3 levels.

    • Product Category
    • Product Sub Category (optional for some products)
    • Product

    One product can be part of more than one product category or sub category. And sub category is optional i.e. a product can be linked to product category directly.

    Table Design

    Product Category

    Prod Category ID (pk)

    Product sub category

    Prod sub Category ID (pk)

    Prod Category ID (fk)

    Product ID (fk)

    Product

    Product ID (pk)

    Prod name

    Product Bridge

    Prod Bridge ID (pk)

    Prod Category ID (fk)

    Prod sub Category ID (fk)  (possible null values if sub category is not available)

    Product ID (fk)

     Is there better design to implement this relationship?

    Monday, April 16, 2012 5:31 PM

Answers

  • So my assumption is that you have a fixed hierarchy and the goal is to keep usage simple...

    The downside is that you have duplicated data in the Category and SubCategory in the Bridge table (very Kimball name that :), but as long as you protect against that, and your hierarchy is fixed, then this should work. 

    I might first a sub category to not be null, and just say that if there is "no" subCategory, then there is one name "default" perhaps.  That could make processing a lot easier since you wouldn't get into the mess of having to case when this is filled in then do this, else....


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Monday, April 16, 2012 10:26 PM

All replies

  • E.g. diagram
    Monday, April 16, 2012 5:32 PM
  • So my assumption is that you have a fixed hierarchy and the goal is to keep usage simple...

    The downside is that you have duplicated data in the Category and SubCategory in the Bridge table (very Kimball name that :), but as long as you protect against that, and your hierarchy is fixed, then this should work. 

    I might first a sub category to not be null, and just say that if there is "no" subCategory, then there is one name "default" perhaps.  That could make processing a lot easier since you wouldn't get into the mess of having to case when this is filled in then do this, else....


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Monday, April 16, 2012 10:26 PM
  • Totally Agree with Louis,

    More you will face problems in searching and summarizing the cost and think about reporting format as well.

    Not sure if it suits your requirment , instead of keeping subcategory Null Put the subcategory same as category on second level and then product.

    Hope it will help you.


    Ali Maqbool | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.

    Thursday, April 19, 2012 12:26 PM