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.