Re: Hierarchy Dilemma

Hi.
I had the similar problem. IMHO, this can be splitted into 2 tasks. 1) Storing nodes hierarchy
2) Storing data for nodes

First task have 2 solutions. First - was described by Lennart Jonsson (idea with supertype).
Another is to creare a static table with the following structure

id - PK

foreign keys:
firm_id
division_id,
office_id,
account_id,

very usefull will be field
node_type - it will keep information about for what owner this node is (for firm, division, office or account)

As you can see, this idea will demand to alter table if you will need to add a new node in your hierarchy.
Suppose, Account can contains several SubAccounts.

If you will use the SuperType solution, you won't have to do alter.

You can choose any of those ideas, because of the small size of your data (2000 rows - its a really small size and there is no need to think about perfomance problems)

Second task, I suppose, require from you not only store data for each node in hierarchy, but support "overriding".
What I mean under this. If on the Firm level defined value for some attribute, for example equal to 10, and on the level of Office this attribute has overriden value of 20, when selecting this attribute value for all Accounts in this Office, you should get value 20, not 10.

To support this you can use flag "isleaf". And always select records where isleaf = 1. When overriding some attribute value, you should update field "isleaf", set it to 0 for values been overriden and set it to 1 to the newly adding values.

That's all. I hope this would help you.
Any other suggestions would be greatly appreciated.

P.S. Sorry for my English :-) That's not my native language. I hope have understood what I have wrote here.

"hs" <harry827_at_yahoo.com> wrote in message news:12c8e030.0212110920.2e25cac4_at_posting.google.com...
> Here is a synopsis on the problem that I am struggling with:
>
> 1. 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 Thu Dec 12 2002 - 13:47:51 CET