# System design for credit calculation usage

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?

• Not offering a solution here, but are you really sure you want to have such a complex system and hide its internals from the user? If you want to offer discounts, offer them on credit price when the user buys them. After that, the discount should not matter anymore when user spends credits. If you want to give different per-item discounts, create different credit types. Make it transparent to the user to avoid dissatisfaction. Commented Jul 17, 2018 at 4:47

What you need is a table with the items and their base price, and then couple that with a table of the quantities and discounts at the given quantity.

The formula becomes: Adjusted Price = Base Price - Discount at a given quantity

A sample "Items" table:

``````+------------------------+
| Items                  |
+----+------+------------+
| Id | Name | Base Price |
| 1  | A    | 16         |
| 2  | B    | 10         |
+----+------+------------+
``````

And a table used to calculate the discounts:

``````+-------------------------------+
| Item Discounts                |
+---------+----------+----------+
| Item ID | Quantity | Discount |
| 1       | 25       | 0        |
| 1       | 67       | 4        |
| 1       | 150      | 8        |
| 2       | 40       | 0        |
| 2       | 100      | 2        |
| 2       | 300      | 6        |
+---------+----------+----------+
``````

This gives you a lot of flexibility in defining discounts — and redefining discounts — without redesigning the application. The discounts become data-driven.

Create a database view to flatten these two tables out, and calculate the discounted price to make your job easier at the application level:

``````+-------------------------------------------------------------+
| Item Discounts View                                         |
+---------+----------+----------+------------+----------------+
| Item ID | Quantity | Discount | Base Price | Discount Price |
| 1       | 25       | 0        | 16         | 16             |
| 1       | 67       | 4        | 16         | 12             |
| 1       | 150      | 8        | 16         | 8              |
| 2       | 40       | 0        | 10         | 10             |
| 2       | 100      | 2        | 10         | 8              |
| 2       | 300      | 6        | 10         | 4              |
+---------+----------+----------+------------+----------------+
``````

The "Discount Price" being the difference between the item's base price and the current discount in each row. This could be auto calculated right in the database view, and querying for the price can be as simple as:

``````SELECT "Discount Price"
FROM "Item Discounts View"
WHERE "Item ID" = 1
AND "Quantity" <= <QuantityRequestedByShopper>
ORDER BY "Quantity" DESC
LIMIT 1;
``````

Since this is properly normalized, adjusting the base price of an item will cascade to the view, and all prices (including discounts) are instantly adjusted. It also gives you a data set to reference when splitting the transaction out (the first 25 are 16c, the next 67 are 12c, etc.).

This sort of database view has the added benefit of being the data source to display to shoppers informing them of the discounts they can get before making a transaction. The marketing department would probably like that.

• Thanks for this comment it has given me some good ideas. Commented Jul 17, 2018 at 23:05