Structuring a relational database to run an automatic process

Question

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.

Requirements:
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

2. Entities:
• 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
• Payments
• 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:

Option 1:
• 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?

Option 2:
• 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.

Questions:
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

Answer 1

Which option is best suited? [table per commission status/type or one tbale with status column]

I would consider the commission records to all be of the same entity type, just with a status property, so all else being equal I'd go for the single table option.

But always consider how the data is going to be accessed and used, and to an extent how requirements might evolve, at least as much as you consider how it is going to be input and stored:

• Having them all in the same table makes reporting on all commissions more efficient and allows new statuses to be added (“cancelled” perhaps?) without structural change. Having a table per status means that to list all you need to union then together, you can do this with views so it is a one-off cost not something you have to do in every bit of code that reads the information but depending on what else is joined into queries on this view (and how they are joined) the unioning could make things noticeably more complex for the query planner and result in significant inefficiency. Changing the status is simpler too, just updating the column's value instead of deleting one row and inserting another, potentially reducing the potential for bugs. Also referencing commissions in other tables by foreign key is much easier (with multiple tables you'll need something like a simple table inherence pattern to achieve that).
• Conversely: if you usually want to read the data for one type only, especially if it could be problematical to accidentally include rows of a status you don't want (stepping away from your example: a common case for this is soft-delete flags which, if forgotten when they shouldn't be, result in deleted data being presented to the user as current) then the separate tables option may become a bit more attractive despite the advantages of the single table approach noted above.

_{As a side note: when using the single-table-with-status-column method, consider using constraints to enforce the expected values to avoid bugs later: if using strings a CHECK (your_column IN('value1', 'value2', 'value3'))^† constraint, if using an enum use a constraint to make sure the values are not larger than the list length, or keep the statuses in a list table and reference by foreign key.}

alternative (e.g. using triggers or procedures)?

A trigger would not be relevant here: they fire upon data changes not time based events which is what you need. Your solution might be implemented with procedures, wrapping the changes needed to note commissions paid (including transaction management and error handling) in a single call, but they can't do it for you alone as you still need to call them at appropriate times.

2. How should the status field automatically change to ‘qualified’ after 3 months? Via a daily cron job?

Daily cron or equivalent would be my go-to for this sort of change. Be sure to monitor to make sure it is firing and working correctly though (perhaps if your app has an admin dashboard present a warning if there are rows due to change that have not, etc.).

Many DBs including postgres support computed columns, instantiated views, and other tricks, that could be used to make this even more automatic, but these have platform specific gotchas around syntax and efficiency, and they don't feel appropriate for this use in any case IMO.

---

_{[†] this is MS SQL Server syntax, you may need to alter it for pg}