Hierarchy Dilemma
Date: 11 Dec 2002 09:20:05 -0800
Message-ID: <12c8e030.0212110920.2e25cac4_at_posting.google.com>
Here is a synopsis on the problem that I am struggling with:
- I have two hierarchies: Account, which has the firm, linked to the division which in turn links to Office and fineally Account. Graphically,
Firm
Division
Office
Account
These are all separate tables and are a 1:M relationship.
2. Second hierarchy is the Security hierarchy Product_Group
Product
Security_Type
Security
Again they are separate tables with a 1:M relationship
Now I have to design a table that will hold attributes at different levels acroos the two hierarchies. For example, certain attributes may be set at Firm, Product_Group level, while certain parameters may be set at Office, Security_type level. The lower level parameters - in this case Office, Security_type will take precedence over Firm, Product_group level parameters. Utimately, for processing purposes, everything has to be resolved to the lowest grain so in this case Account, Security level.
Not sure how to approach this since the combinations in worst case scenario are a cartesian product between the two hierarchies, but in reality that would never happen. Tables are very small no more than 2000 recs per table.
My first thought is to create a matrix and resolve everything to the lowest grain, but the maintenance will become a nightmare. If I keep it at a high level then I will end up with quite a few tables such as
Firm - Product_group
Firm - Product
Firm_Security_type
and so on and so on ......
This does not look right to me either, and on top of that processing logic will have a hard time resolving this.
I would give up some maintenance for better performance.
Any ideas ?
Thanks
Harry
Received on Wed Dec 11 2002 - 18:20:05 CET