How does one move the balance of payment to next month for an Affiliate system?

Hello all,

I am updating our Affiliate system to be in Php & MySQL.
I have run into a problem that I am having hard time to solve and since this is a common aspect of any Affiliate system I thought best to ask you all to see if you based on your experience in same could suggest what is the best way to get this task done.

To be exact if the payment for an Affiliate is less than a certain threshold amount then the payment for that month
is to be moved into the next month. How does one design this in MySQL & PhP?
I cannot visualize it :frowning:

Hi…

I have done affiliate tracking, and yes it is full of rather arbitrary rules. You are kind of both right.

Anthony is definitely right, you must start with the history. All other tables are caching for performance.

The history never changes, but your calculations might. This means you will want to be able to replay your past logic. This means your history should include all the constants used in the calculation. As constants are never constants, you’ll want the date range too…


create table calculation_values (
    name varchar(255) not null,
    value numeric(10, 2) not null,
    calculation varchar(255) not null references (calculations.name));

create table calculations (
    name varchar(255) not null primary key,
    created datetime not null,
    expired datetime not null);

(The uniquness constraints on calculation_values is beyond the capabilities of MySQL, but trivial otherwise)

Assuming the code for the calculation doesn’t change (the calculation name is probably a class name or stored proc) once live data is created, you should always be able to rebuild any monthly summaries.

Once you get the history layer right (layered architecture, yay!), then you are free to rebuild your calculation caches at will. As the domain is inevitably complex, you will be doing this a lot. For each cache/summary table, have a single class or function to automatically rebuild it. Handy for testing, migrations, building sample data, etc.

This freedom to play about without wrecking the core data will take a lot of stress out of the whole process.

yours, Marcus

Hi,

I am afraid it is much more complicated than a simple:
“Store the credits/debits as transactions, or rather entries.”

Have you ever done such an Affiliate system?
If so, you would know that the challenge is:
How to move the commission earned from month 1 to month 4, when the commission is less than threshold which results in payment, assuming they generated no sales in month 2 & 3

Actually the above description does not do justice to how really complex an issue this is. OTN, we had 2 different groups who designed this Affiliate system before and each design failed after a few months of usage, with real world data coming at it.

Anthony is right - there is nothing to move around.
Store all transactions in one table, store the sum of what they have in an extra column in the users table and you’re done.
Each time you want to do payouts you just check this column to see if someone is eligible.
After the payout you add a transaction stating the deduction, deduct the amount from the column - perfect, simple, clean.

A bank account, as Anthony mentioned, is an absolutely perfect matching example.
There is no “money this month” - either there is money or there is not, independent of depositing it in January, July or December. [Period].

You’re thinking about it wrong, does your bank statement tell you how much money you have this month? Or, does it tell you what you have right now? :wink:

Store the credits/debits as transactions, or rather entries. Then when the time for payment comes, perform the math to see if the total value outstanding is suitable for payment.