• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Best Method To Do This?

New Here ,
Sep 14, 2006 Sep 14, 2006

Copy link to clipboard

Copied

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
TOPICS
Advanced techniques

Views

287

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Sep 14, 2006 Sep 14, 2006

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Sep 14, 2006 Sep 14, 2006

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Sep 14, 2006 Sep 14, 2006

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Sep 14, 2006 Sep 14, 2006

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Sep 22, 2006 Sep 22, 2006

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Mentor ,
Sep 22, 2006 Sep 22, 2006

Copy link to clipboard

Copied

LATEST
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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation