We are implementing a system where a user can purchase 'credits' to be spent on two items. I am hoping for suggestions on how to implement such a system. We have two ways of spending credits: ItemA and ItemB.
When purchasing credits the user can purchase credits in bulk which reduces the cost of a credit, for example (this is not true cost but just an example):
Cost | Cost ItemA (each) | Cost ItemB (each) ----------------------------------------------- $400 | 16c | 10c $800 | 12c | 8c $1200 | 8c | 4c
An example scenario:
A user purcahsed $400 of credits and then next week buy $1200 of credits. This means they may have $400 of credits at 16c (ItemA) and 10c (ItemB) each and also $1200 of credits at 8c (ItemA) and 4c (ItemB) in the system.
All the user will see in the system is that they have a total of $1600 of credits (1200+400) available, when they spend the credit it will reduce the total by what they spend eg:
- Spend 2000 of ItemA Reduce by $320 (16c each), $80 remaining of first credit buy $1280 overall remaining - Spend 2000 of ItemB Reduce by $80 (10c each) 800 credits Reduce by $48 (4c each) 1200 credits
We have a database to store the credits, which will be distributed in the form of a key which contains the amount of credits it contains as well as validation checks. The items the spend eg ItemA or ItemB will also be stored in the database.
How would you implement the above in regards to database table structure and logic to calculate the credits remaining and spent?