database/table design and storing hierarchy information
Posted on 2012-04-12
I'm about to design a table (hierarchy | tree) since will be dealing with products where each product line has different levels and format. Also, I must add that going to use hierarchyid as data type.
Just as an example:
product line # 1 can break into:
1)division e.g. division A
1-n) rest of the lower levles are attributes
another division can break into:
1)division e.g. division B
1-n) again, rest of the lower levles are attribute
what worries me that this will led us to a huge size of data/table sepecailly because each product has its own attributes which are broken into few groups e.g. evnironmental, mechanical and etc (listed as 1-n in above). Most of the division have a general set of attributs that can be access via a lookup table. So I'm thinking of storing these attributes into seprate (normal tables or non-hierarchical) this way tree level can decrease.
I will appreciate it if experts could advise me on this topics.