CODEWITHSUNDEEP
phpmysql
Blake M
Blake M

Reputation: 41

Can MySQL calculate totals from a multi-level parent child relationship?

For the sake of simplicity, let's assume the following tables exist:

Table 1 - List of Sellers

ID |  Parent_ID  |  Percentage
----------------------------
1  |    -        |     .5
2  |    1        |     .4
3  |    2        |     .3

This table shows 3 sellers. 1, the main parent, 2, an individual with parent 1 and 3 with parent 2 and super-parent 1. ID 1 gets a 50% commission on all individual sales PLUS the difference in commission for any subagents between their percentage levels.

For example:

The following table would represent a list of sales by Agent:

Table 2 - Sales by Agent

ID   |   Cost
-------------
2    |   10.00
2    |   5.00
3    |   9.00

In this scenario:

The super parent (ID 1 in this case) could have been the direct parent of ID 3 and earned (.5-.3). This is to say, the parent child relationship is not always linear and there is no set depth to where the hierarchy lay.

Ultimately, I am trying to develop a mysql or php (or combo) formula to determine the what each seller is due in the scenario above. Calculating the total of sales for each seller and the individual seller earnings based on those sales is easy. Figuring out how to apply earning to ID 1 and 2 based on 3's production is another story.

Any prior experience in this area?

Upvotes: 3

Views: 1425

Answers (1)

Alan Geleynse
Alan Geleynse

Reputation: 25139

Unfortunately, MySQL doesn't make trees easy.

MSSQL has something called common table expressions that make this easier.

With MySQL, the best solution is to use a recursive stored procedure. But those can be very difficult to get right.

Instead, I would suggest having another table that stores all the parents of a child and how many levels above they are. This complicates your insertion and deletion code since this table needs to be kept up to date (triggers can help), but it will make your calculations much easier.

Upvotes: 1

Related Questions