Hard Design/Performance Issue (long) - Help!

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. Received on Fri Nov 17 2000 - 12:46:15 CST