Ragged Hierarchy

  • Hi,

    I have a Dimension table for products and it has hierarchy which is unbalanced.

    for one product hierarchy level may be

    root-->brand-->size-->color-->category-->finished product

    next product hierarchy level may be

    root-->category-->brand-->size-->field4-->finished product

    like this many products got many hierarchies

    how can i store the hierarchie levels into the tables and how can i keep track of it using procedures / for reports?

    Please someone help with the table structure.

    Thanks

    Best Regards,

    Viji

  • I'd suggest a single table of parent-child relationships, and a second table, if necessary, translating relationship levels (0,1,...n) into appropriate names for specific product lines. In other words, treat it as a Bill of Material.

    (And no, I've never done that for other than BOM's, and never for BOM's in a relational database. In other words, it's just a hunch!)

  • Thanks Mr. Jim,

    right now i've planned a product table with fields like

    hierarchyid(FK), level1,level2,level3.....

    then a hierarchy mapping table where it stores the meaning like --

    hierarchyid(PK), level1-brand,level2-size, level3-category etc.

    next record could be level1-category, level2-subctegory, level3-size, level4-color......

    like this how many products group exists in the database that many records will be created and in product table the hierarchy id will be assigned.

    hope the above will support Unbalanced hierarchy.

  • Not sure that would constitute normal form, but whatever works for you.

    My suggestion would have been a two column table with parent and child foreign keys. That way, you would not have to worry up front about the depth of your trees and how to handle them getting deeper.

    In your design, the significance of the level 2 column depends on the contents of the level 1 column, etc., which might cause problems down the road.

    But good luck!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply