Looking for a advice on table(s) structure for MySQL schema.
Background: I have several tables that can hold information about my product, customers and their purchases using CC/DC/direct banking etc. It's all working fine so far. Now, I'm planning to offer my customers an option to purchase store credit(using existing payment methods) and accumulate promotional credit by referring friends or using coupons while purchasing credit. Characteristics of credit include:
- every credit has an accumulation date
- every credit can be used to purchase products (unless expired)
- purchased credit will not expire
- promotional credit will have an expiry date
I already have a table for offers/coupons, their validity, benefits etc. to be able to offer any promotional credit.
- A table structure that can hold both type of credits(purchased as well as promotional). I thought of this - see image.
- A way to automatically deduct credits with closest expiry date first and then deduct from next accumulation if short of credits from one accumulation and so on...
Example: Customer has 50 purchased credits(no expiry) and has 15 (promo) credits expiring on 25th Sept. and another 20 (promo) credits expiring on 29th Sept. Now, let's say customer wants to buy a product worth 20 credits from the store. So, instead of deducting all 20 from his second promo accumulation, I'd want to deduct 15 credits that expire earliest from today and then remaining 5 from the ones expiring later (and of course not touching the purchased 50 credit yet).
- Another table to hold all transactions from credits (kind of a ledger)
Any ideas on this plan? I'd also appreciate any complex SQL statements involved for insert/fetch/update operations on the advised plan. I can myself take care of basic to moderate SQL statements (though I'm not very good at
GROUP BY and
I'll give more information, if required.