I need a schema discussion...for an MLM commissions table

I need a schema discussion...for an MLM commissions table

I am writing a "poor man's Multi Level Marketing" solution in WordPress and using MySql. But, anyone with Sql knowledge can contribute.

I use a recursive User Table to define my "network". It contains two user columns. The first is the owner's UserId. The other is the owner's upline UserId. Six levels deep requires six queries, where the final query finds aUser with an upline UserId of NULL.

Okay so far?

So, for computing of monthly commissions earned by all users, I need a running total, a table, which maintains the total commission earned for the month. Then, for each sale, I need to calculate commission three levels deep. That is not a vary difficult task.

So, I need to increment the commissions for three users, by a calculated amount.

Then, after midnight, at the end of the final day on the month, that table contains the final commissions to be paid.

Am I missing anything?

Is there a better way to do this?

I like knowing the total commissions so I can alway predict my total commission liability is.

What kind of schema design do you suggest?

Also, I have never needed to design a table that accepts currency, in this case, dollars and cents. Any advice of field types?

Thanks