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:

• Seller ID 3 would earn 30% of his sale, or $2.70
• Seller ID 2 would earn 40% of his/her sales (.4 * $5) + (.4 * $10) = $6 PLUS override on his/her child. In this case, he/she earns the difference in commissions plus the amount of the sale (.4 - .3) * $9.00 = $0.90 so Seller ID 2 would earn $6.90 total.
• Seller ID 1 had no individual sales but earns override on his/her child and all subsequents: (.5-.4)(10.00) + (开发者_如何学JAVA.5-.4)(5.00) + (.5-.4)*(9.00) = $2.40

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?

---

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.