I’m learning SQL and relational database design and am trying to design an affiliate system and don’t know which option to pursue. To summarise, users should be able to refer other users, and if users pay for an enhanced account, the affiliates should get 30% commissions, but only for payments that aren’t refunded within 3 months.
I’ve done the recommended steps of listing the requirements and entities.
1. Users should be able to CRUD an account for free
2. Users should also be able to pay (monthly) for an enhanced account
3. Users should be able to refer other users (affiliates)
4. Referred users should show who referred them
5. 30% of all payments that referred users make should go to the referrer (after 3 months)
6. Referrers should only be paid the 30% of each payment after at least 3 months have passed from each payment date
7. Referrers should not be paid for any referred user payments that are refunded
8. Track a referrer’s unpaid balance, pending balance & qualified balance
Note: there could be a better way of doing this, but I thought to do it like this:
• pending balance: the total of all pending commissions (pending = hasn’t passed 3 months - the minimum qualification time)
• qualified balance: the total of all unpaid qualified commissions; the balance due to be paid
• unpaid (total) balance: the total of the pending and qualified balances
• payments to referrers made at the end of the month, hence why I thought a balance is needed
Note - 2 payment types:
• User payment: (monthly) payment from a user to pay for their enhanced account
• Payment to referrer: payment from the merchant/seller to the referrer
• Users (accounts)
• Affiliates - just make part of users (all users are automatically made affiliates anyway); no separate table
• Enhanced accounts - part of accounts, which is part of users (i.e. enhanced_user boolean field); no separate table
• Referrals - a table with id PK, referrer_id FK & referred_user_id FK? (Or no separate table & just a user referred_by FK field?)
• Commissions (id PK, referrer_id FK, paying_user_id FK & payment_id FK)
• Refunds - just make part of payments? (I.e. refunded field). Presumably better to have separate table (id PK, payment_id FK & amount field in case of partial refund)
• Balance - calculable via queries; no separate table
And two options I thought of:
• Create pending_commissions, qualified_commissions & paid_commissions tables
• New commissions go into pending
• When each commission is 3 months old, it’s moved into qualified - via a daily cron job?
• At the end of that month, all qualified commissions are added up, the merchant automatically pays the totals to the referrers, and all are moved from qualified table into paid table - via a monthly cron job?
• Single commissions table
• An extra ’status’ column (can either be ‘pending’, ‘qualified’ or ‘paid’) in the commissions table
• When each commission is 3 months old, it (somehow) has its status changed from ‘pending’ to ‘qualified’
• At the end of each month, each qualified commission is added up and automatically paid to referrers, and the statuses changed from ‘qualified’ to ‘paid’
I initially thought that a ‘balance’ field should be added to referrer users (i.e. added to the balance whenever a pending commission occurs, and payments to referrers get deducted from the balance - or something like this), but I assume this is not necessary.
I think both options are able to calculate the 3 balances, but I’m veering towards option 2.
1. Which option is best suited? Or is there a better alternative (e.g. using triggers or procedures)?
2. How should the status field automatically change to ‘qualified’ after 3 months? Via a daily cron job?
Thanks for any tips