I’m learning SQL and have a basic question. I have a transactions table which contains payments and commissions.
A payment:
| ID | Date | Amount | Type | Referrer ID | Balance |
|---|---|---|---|---|---|
| number | date | number | ‘commission’ or ‘payment’ | number | number |
A commission has the same fields as payments, plus two additional fields: qualified and customerPaymentId
| Qualified | Customer Payment ID |
|---|---|
| boolean (presumably 0 or 1) | number |
Whenever a payment is made, the balance decreases, and vice versa whenever a commission occurs.
I have a user table too, where a user can have payments but not commissions. Should I separate the transactions table to be 2 tables (commissions and payments), or keep a single transactions table where the user table has a relation with it (rather than a relation to a separate payments table)?
Any advice or pointers would be great too, thank you.
UPDATE:
Sorry, I don’t think I have been very clear at all here. This is the transactions table, that contains both payments and commissions:
| ID | Date | Amount | Type | Referrer ID | Balance | Qualified | Customer Payment Id |
|---|---|---|---|---|---|---|---|
| 1 | 20/1/22 | 5.00 | COMMISSION | 5454 | 5.00 | 0 | 0001 |
| 2 | 28/1/22 | 4.00 | COMMISSION | 5454 | 9.00 | 0 | 0002 |
| 1 | 20/1/22 | 5.00 | PAYMENT | 5454 | 4.00 | NULL | NULL |
| 1 | 20/1/22 | 4.00 | PAYMENT | 5454 | 0.00 | NULL | NULL |
The type of ‘commission’ or ‘payment’ is there to determine whether a record is a commission or payment. A commission would have data for the ‘qualified’ and ‘customer payment id’ columns (but payments wouldn’t).
The Customer Payment ID column actually represents a different type of payment - a payment that a user makes for a product. Conversely, the transaction payment is a different payment that pays towards an affiliate’s balance.
The commission.balance and payment.balance represent an overall balance - commissions add to a user’s total balance while payments deduct from it.
To give some better context, this is for an affiliate system - when a customer buys a product, a payment (different - NOT a transaction payment; it’s from another table) is made, with a Customer Payment ID. An affiliate might have earned a commission from that sale, hence a commission record is made (in the transactions table). The amount they are owed is added to the affiliate’s balance. After 3 months, the commission becomes qualified, and the affiliate is paid their commission at the end of that month, hence a payment is made (a payment from the business to the affiliate), which deducts from the affiliate’s balance. It’s not simple to explain, but that is the gist of it if that makes somewhat sense :) I may not have constructed this the best way.
CodePudding user response：
It's a little difficult to follow what's happening, but it seems you have a bunch of things which have some overlap in their fields but aren't actually the same. But sometimes you want to look at them all together.
I would suggest making separate tables for each kind of thing, and then a create a view to bring them all together.
For example...
create view transactions as
select created_on, amount, 'COMMISSION' as type, balance
from commissions
union all
select created_on, amount, 'PAYMENT' as type, balance
from payments
We use union instead of union all for performance. union removes duplicate rows which means the database has to do more work and operations are slower and more complicated. There are no duplicates in this instance.