Ragged Hierarchy

Question

Ragged Hierarchy

Anamika

SSCertifiable

Points: 6202
More actions
September 28, 2008 at 9:05 pm

#193752

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

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

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

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 1

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!)

Anamika SSCertifiable Points: 6202 More actions · Answer 2

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.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 3

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!