Results 1 to 5 of 5

Thread: design advice

  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Talking design advice

    Anyone have a db schema to model a multilevel marketing (pyramid)?

    we crrently use an adjacency model and it's a DOG!

    was looking at celko's nested sets...not sure if it's appropriate or not.

    the recursion on my adjacensy model is killing my performance to identify downlines(branches/leafs) for only 2000 branches and 40000 leafs.

    help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, there's your problem, you're trying to do downlines recursively

    to traverse a subtree for just one level down, using the adjacency model, starting at a particular node, means that you have to find all the nodes that point to that node

    that's a self-join

    then you have to find all the nodes that point to those nodes

    another self-join

    if you had a multiple self-join with as many versions of the table as levels down that you wanted to go, you could get all the nodes in the subtree with one call to the database, right?

    so one of the options you have is to re-program your traversal so that you retrieve "several levels down" all in one query, rather than calling the database once per node, which, if i'm not mistaken, is what happens in a recursive scheme

    just an idea, i'm basically thinking out loud

    in a marketing pyramid you have to deal with N levels, and i've never done that, i've only worked on hierarchies that were some maximum number of levels deep, and i just wrote one query for the entire tree (the most i ever did was eight levels -- it was a rather large self-join, i.e. the sql statement was big, but it ran really fast)

    if you wrote a multiple self-join that went down M levels, where M is less than N, the maximum depth, then this might improve your performance

    you wouldn't have to retrieve all the data in the tree-traversal query either, you could just retrieve the keys, their parents, and generate a "level number" that would aid in nesting (i.e. sorting) the results

    then after you've called your M-level query a few times and assembled the (sub)tree of keys in an array, you could go back and fetch all the data columns in one single query, not a self-join, using a big IN list

    again, just thinking out loud

    the thing that's always bugged me about the celko method is that i could never figure out how to do simple things like move a node -- and its subtree! -- to another parent


    rudy

  3. #3
    Join Date
    Feb 2003
    Posts
    3

    Talking thank you

    you've given me a few interesting ideas.

    the multilevel marketing scheme is mostly based upon 8 levels from parent to 8-th level child. but the whole tree is a few hundred levels deep overall.

    one of the things we do is calculate commissions per rep up to 8 levels deep. so i think your approach might help us a bit.

    at the end of each month, we run a full commission for the full tree and it takes 8 hours. The first rep's run of 8 levels takes 11 mins to calculate...then we move on to all of his 1st levels, run the same 8 level query to calc comms, then go to his level 2, run all calcs on level 2s, then 3s, etc.

    So to pull an 8 level report given a rep, i think you're advice will help. but man, when we have to calc the whole tree, we're at 8 hours and climbing!

    right now we have 2000 reps, and 40k+ customers. we anticipate 3000 reps, 120k+ customers this year. Our experience is that the commission report calculation time increases exponentially...and our fear is that when we do hit 3000 reps that it will take 16-20 hours or more just to calculate month end commissions.

    SO there's got to be a better way. We've recently put 4 pentium iv xeon processors, 4gb ram, ultra360 mirrored drives into the picture and the calc times are the same (roughly speaking). SO it's obvious to me that it is our 1) architecture, and 2) our recursion that is killing performance.

    I've never worked for Amway, or Mary Kay, or any other MLM group, but somebody else has to have figured this db architecture problem out.

    just learning about these new models myself. frankly, my background uses eitther 1) hard coding number of levels into the db architecture, or 2) using adjacency model.

    I've only just begun to toy with celko's nested sets. not convinced that its right for this siutation...though there definitely seems to be some advantages to it (easily get all children of a parent by selecting records where left values > parents_left, and right values are <parents_right. bamb...bring back all children lickety split! but how to get just eight levels of children?

    i was talking with a co-developer today. others must've solved this problem...it's just a matter of finding their solution from a db schema/architetcture problem.

    Also, I'm learning that SQL 2000 from Microsoft leaves a lot to be desired. other db platforms have hierarchy functions built in.

    ANwyas...great thoughts you had there, gonna explore some of your ideas!

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    I never had tree big like this.... and I don't need to tell you that you have to try more ways....

    all of us probably agree that the the best way is to have

    ItemID
    ParentItemID
    Description
    ....

    this system allows you to move easily sub-tree somewhere else. If you will keep all the moves and deletes on SQL server (using stored procedures and triggers), then you will enjoy smooth (maybe not fastest) and client independent solution....


    check this http://msdn.microsoft.com/library/de...UsingT-SQL.asp


    let us know.... this is really interesting problem.

    jiri
    Last edited by playernovis; 02-05-03 at 01:34.

  5. #5
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    I have been looking at these sorts of problems too, in mapping a complex medical curriculum. I have found the following article very useful and have started to implement something along the line shown.

    http://www.sqlteam.com/item.asp?ItemID=8866

    Hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •