Mysql – Table structure recommendation

database-designMySQLtable

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:

  1. every credit has an accumulation date
  2. every credit can be used to purchase products (unless expired)
  3. purchased credit will not expire
  4. 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.

Goals:

  1. A table structure that can hold both type of credits(purchased as
    well as promotional). I thought of this – see image.
    enter image description here
  2. 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).

  1. 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 UNION).

I'll give more information, if required.

Best Answer

Your structure looks fine, you would want to query in ascending order by expiry date and value. So if the customer wants to cash in 60 credits and the nearest date has values 10, 40 and 75, you want to "consume" the 10 and 40 and take the remaining from the 75. This makes it easier to use a brute force algorithm to use the soonest and smallest credits first.

This calculation would likely be better placed in the app rather than using SQL. The code would be fed a sorted list of records and the code would issue a series of 0 or more deletes followed by possibly one update.

Related Question