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

In our last gripping episode 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.
>

Have you thought of organizing this as a data warehouse? It would appear that a star or snowflake [partly or fully normalized star] schema (fact tables, dimension tables) would suit this data quite well.

Just my "two cents".

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.