6 Replies Latest reply on Sep 22, 2006 8:13 AM by paross1

    Best Method To Do This?

    Frank_D'Elia
      Hello,
      I was asked to add a pretty complex (in my opinion) feature to an app I am developing.

      What I have is a database with the following tables:
      - distributors
      - customers

      Each customer has a distributor assigned to them. Each distributor gets paid compensation based on the customers they have. I have to figure out in real time, what the distributor has earned so far (20% of customers sales). That is easy, what I need help with, is the fact that distributors can sign up other distributors under them (level II distributors) and make compensation (6%) on their customers as well. A distributor can sign up as many distributors as they can.

      I have very complex cfloops and querys, etc. to loop through all distributors under the distributor I am looking up but it is getting unmanagable. Does anyone have any ideas on getting this information as easy as possible.

      The way I am tracking what distributors are under who I am looking up is by having the distributor ID in the ParentID field in the distributor database.

      I know this may be confusing, but will answer and questions you need answered. I will even go as far as paying for a solution I am getting overwhelmed with other work and this "feature" would finish this project.

      --
      Looking forward to a response,
      Thanks You,
      Frank
        • 1. Re: Best Method To Do This?
          Dan Bracuk Level 5
          I would do this at the database design level. Specifically, I would use a 3 column many to many relationship table with

          distributor_id
          customer_id
          rate_id (you really want to do it this way in case commissions change over time)
          • 2. Re: Best Method To Do This?
            TSB
            I think this may work

            SELECT tableName.dist, tableName_1.dist AS distLevel2 , tableName.dId, tableName.pId, tableName_1.pId AS pIdLevel2
            FROM tableName FULL OUTER JOIN
            tableName tableName_1 ON tableName.dId = tableName_1.pId
            WHERE (tableName.pId = yourTopLevelDistID)

            This should select all 1st and 2nd level dist. Of course you will have multiple records for the 1st level because you have 1 for ever 2nd level dist they have.

            Trevor
            www.burnette.us
            • 3. Re: Best Method To Do This?
              paross1 Level 2
              I would go with Dan's suggestion, since I am assuming that customers could possibly have more than one distributor, and distributors can have more than one customer. This being the case, you really can't have a single distributor ID value within the customer table, nor can you have a single customer ID within the distributor table. In data model terms, you need an associatative entity (link table) to resolve the many-to-many relationship between customer and distributor. Once you do that, then you can link as many distributors to as many customers as you wish, plus you can add attributes to this link, such as status flags and effective dates, etc.

              My 2 cents.

              Phil
              • 4. Re: Best Method To Do This?
                tclaremont Level 2
                Phil's response hits the nail on the head. Even if you don't need the flexibility that it affords right now, ADDING that flexibility later can prove to be a nightmare.
                • 5. Re: Best Method To Do This?
                  Frank_D'Elia Level 1
                  Guys,
                  Thank you so much for the responses, I have been out of the office due to a friends death. The thing is that a customer would only have 1 distributor, but a distributor could have as many customers as they sign up. The thing i am having a problem with is the fact that I need to input one distributor say with the ID of "1000".

                  Now I need to search what customers are under him and display the output like:

                  Self Sales
                  First Name Last Name Price Per Month Commision
                  data data data data
                  data data data data
                  data data data data
                  data data data data

                  Then figure out what distributors are under him (If there are distributors signed up under the one I am looking up, they have a parentID of "1000". What I want to do is then group the output like so):

                  Level One Sales

                  Distributor Name Here
                  First Name Last Name Price Per Month Commision
                  data data data data
                  data data data data
                  data data data data
                  data data data data

                  Distributor Name Here
                  First Name Last Name Price Per Month Commision
                  data data data data
                  data data data data
                  data data data data
                  data data data data

                  And so on for 3 levels.

                  Sorry if it is confusing, but any help would be appreciated.

                  --
                  Thanks, Frank
                  • 6. Best Method To Do This?
                    paross1 Level 2
                    Since distributors can be assigned to another distributor just like a customer, you may want to consider combining your customer and distributor tables into a single entity (table) with an attribute that identifies what class or category of user they are, such as level 1 distributor, level 2 distributor, and customer, etc. Then you could create a "link" table that allows you to associate "customers" to level 1 distributors, and also associate level2 distributors to level 1 distributors. Then your query would be a snap, as you would select by distributor (user) id on your "link" table, which would give you a list of all customers and level 2 distributors associated with that level 1 distributor, using the user attribute to identify which "type" they were.

                    It is similar in some respects to having an employee table, where some are subordinates, and some are supervisors. They are all employees, but you need to have a means of associating your subordinates with their supervisors, and the supervisors to their supervisors, etc.

                    Phil