Re: Hard Design/Performance Issue (long) - Help!

We are having a similar problem in our organization.

my suggestion would be to denormalize the table in the following way

( client_key, parent_org_member_key, org_member_key )

this table could be filled every morning and then updated as changes are made to the organizational structure.

in the case of the magazine campaign you would have the entries

( 1, 5, 5 )
( 1, 5, 6 )
( 1, 5, 7 )
( 1, 5, 8 )

and select org_member_key from xxx where client_key = 1 and parent_org_member_key = 5 should give you the correct result.

naturally this table will be very large, especially if the trees are deep but you will be using the primary key to look up data.

hope this helps

In article <8v3udl$v0h$1_at_nnrp1.deja.com>,   haniff_at_cyberdude.com wrote:
> We have a database that stores organization structure trees for many
> clients. For each client, the stored tree has a maximum depth, and
 each
> "level" of the tree has it's own name. Each client's tree contains a
> number of "nodes" that are the members of the organization (it's
> confusing to us, also!). We modeled this as a recursive structure.
>
> Tables look like:
>
> Client(Client_Key, Client_Name)
>
> Organization_Level(Organization_Level_Key, Client_FK, Org_Level_Name,
> Org_Level_Depth)
>
> Organization_Member(Org_Member_Key, Organization_Level_Key,
> Org_Member_Name, Parent_Org_Member_Key)
>
> An example may clarify:
>
> Say Well-Mart has two national regions: East and West. The East region
> has two territories: North and South. The South territory has three
> stores: Parkway Well-Mart, Midtown Well-Mart, and Suburban Well-Mart.
> Therefore we have:
>
> 1 Client record
> (1, "Well-Mart")
>
> 4 Organization_Level records
> (1, 1, "MASTER", 0) <-- every client has this record
> (2, 1, "region", 1)
> (3, 1, "territory", 2)
> (4, 1, "store", 3)
>
> 8 Organization_Member records
> (1, 1, NULL, NULL)
>
> (2, 2, "East", 1)
> (3, 2, "West", 1)
>
> (4, 3, "North", 2)
> (5, 3, "South", 2)
>
> (6, 4, "Parkway Well-Mart", 5)
> (7, 4, "Midtown Well-Mart", 5)
> (8, 4, "Suburban Well-Mart", 5)
>
> Whew. Congrats if you're still with me. What we want to be able to do
> is specify parameters for a member (e.g. a Magazine campaign for the
> East region) and have those parameters applied to all descendents of
> that member (all three stores). Parameters lower in the tree "hide"
> those higher in the tree.
>
> This means we could assign a Television campaign to Well-Mart's MASTER
> member , and a Magazine campaign to the South region.
>
> Finally...the point. We are having a TERRIBLE time getting reasonable
> select performance from this structure (there are about 400,000 nodes
> total in all client trees).
>
> 1)Are we using the right structure?
>
> 2) What could we do to improve select performance? (typical query:
> "what campaign should Well-Mart Parkway use?")
>
> Any thoughts, critiques, or discussion would be helpful.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 22 2000 - 04:03:12 CST