Setting parameters at Hierarchy within a hierarchy

From: hs <harry827_at_yahoo.com>
Date: 2 Mar 2003 10:46:51 -0800
Message-ID: <12c8e030.0303021046.35027328_at_posting.google.com>

All,
I have a situation where I have two hierarchies : the Oragnization hierarchy which has 6 levels, for example Firm
  Entity

      Business_Unit
          Division
               Region
                   Office

and the second hierarchy is
Business_Group

              Product_Group
                           Product
                                  Sec_Type
                                          Sec_Sub_type

I have resolved the above two hierarchies by creating 1 table at each level and setting up a 1:M relationship.

From a processing perspective, the business rules requirements are to be able to set parameters at
Firm

    Business_Group

         Product_Group
               Product
                   Sec_Type
                      Sec_Sub_type

Entity

    Business_Group

         Product_Group
               Product
                   Sec_Type
                      Sec_Sub_type

and so on ..... all the way down to the account level. Parameters set the lowest level will override the values at a higher level. No matter where the parameters are set, the requirements is to be able to see the values at the ACCOUNT - SEC_SUB_TYPE level.

My first thought was to create one table for every level meaning Firm - Business_Group, Firm-Product_Group etc. etc. ..... Then I have thought of completely de-normalizing the hierarchy and carry all combinations in 1 big table. Both seem a little extreme, although I must confess I am leaning towards the latter. I realize that the maintenance would be higher because of repeating elements but the parameters will be changes no more than once every couple of months. Also the number of records on both sides are fairly small.

This matrix that I am thinking about will be at most a cartesian product between office and Sec_Sub_type. There are about 30 parameters that need to be set in this manner.

Any thoughts on this ? One one side I want to normalize this, because that what makes sense to me, but then on the other side, I am thinking why do it and create 30 some tables in the process ? Since everything has to be resolved down to the lowest level, why not just create a matrix.

This is an important design consideration for me since there will be other parameters that may follow a similar pattern in the future and performance is very important.

What do you all think ?

Regards
Harry Received on Sun Mar 02 2003 - 19:46:51 CET