I’m working on a new database structure for a new website.
I need to accept 2 types of payment : OneShot and Recurring (monthly)
In a single order (basket), I can put a product “OneShot” and “Recurring” and paid both.
Here is my actual structure (I do not provide all columns) :
Transaction
table can have multiples lines. If I make an order with OneShot Product ID:89 - 70$
and Recurring Product ID:129 - 29$ /Month
Table Order OrderId / OrderNumber / CustomerID / TotalAmount / CompanyId 998 / AA-22-ER / 11 / 99$ / 2 Table OrderItem OrderId / ProductId / UnitPrice / Quantity / Created 998 / 89 / 70$ / 1 / 2017-09-17 998 / 29 / 29$ / 1 / 2017-09-17 Table Transaction TransactionID / OrderID / Status / Amount / Created 129 / 998 / P / 99$ / 2017-09-17 ==> OneShot + First Recurring Payment 345 / 998 / P / 29$ / 2017-10-17 ==> recurring payment 523 / 998 / P / 29$ / 2017-11-17 ==> recurring payment
How can I deal with rebill and/or refund?
For example, I want to refund the OneShot Product ID:89
, do I need to stock this information in Transaction
Table with Status
colum? But how can I know quickly which OrderItem
is still paid?
In another way, how can I deal with recurring payment
? Hope I’m clear in my question.