Database Structure with Recurring Payment

database-design

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) :

enter image description here

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.

I think, I have a beginning of a solution.

Customer cannot buy 2 subscriptions products in one sale.

Here are my use cases :

In one Sale, 1 product is normal (70$), 1 is a subscription for 12 months (29$/Month). Customer ask a refund on december, so subscription stop at this moment.

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /   
998 /   112 /   99$ /   89$ /   2-20170927-SZZ-345  /   2   /   2017-09-17

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1021 / 998 / 89 / 70$ / 2017-09-17 /    2099-12-12
1022 / 998 / 29 / 29$ / 2017-09-17  /   2017-12-17

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
129 / 998 / P / 99$ / 2017-09-17 / 0
345 / 998 / P / 29$ / 2017-10-17 / 0
523 / 998 / P / 29$ / 2017-11-17 / 0
657 / 998 / R / 29$ / 2017-12-17 / 29$

Table TransactionEvent
TransactionID / Event   / Created   /   Amount  /   Reason
657 /   R   /   2017-12-23  /   29$ /

One sale with 1 normal product (19$)

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /   
997 /   189 /   19$ /   16$ /   2-20171027-XXS-990  /   2   /   2017-09-17

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1009 / 997 / 78 / 19$ / 2017-09-17 /    2099-12-12

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
119 / 997 / P / 19$ / 2017-09-17 / 0

One sale with 1 subscription product for 29$/month on 12 months

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /   
1002    /   234 /   29$ /   25$ /   2-20171029-AAS-120  /   2   /   2017-10-29

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1044 / 1002 / 99 / 29$ / 2017-10-29 /   2018-01-29

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
789 / 1002 / P / 19$ / 2017-10-29 / 0
844 / 1002 / P / 19$ / 2017-11-29 / 0
901 / 1002 / P / 19$ / 2017-12-29 / 0

Best Answer

Systems I have worked with in the past stored for each customer a row for each recurring product they have. Each row has a product id, start date and anticipated end date, and a "billed to date". Each month when you bill them you increment the date.

From that you generate an invoice (storing the values to make up the invoice), and they customer makes payments against that. Their balance is calculated from opening balance + invoices +/- payments.

It gets complicated when product prices change over time, or discounts only apply for a specific period. You need to store enough detail that you can recreate the invoice correctly at that point in time. If you give a refund that you don't want to refund too much, and if required can you pro-rate adjustments?

A good approach is to ask yourself what level of detail do you need to see on an invoice. If I had to recreate the invoice, adjust it or provide a refund, where would I get the data from. You need to either store the detail or be able to reproduce it reliably. Can you?

The next level to consider is, what accounting information to I need to report? How much did we sell last month? what discounts applied? how many customers have paid? who owes us money and how much?(debt mngt) How much tax have i charged? (is anything exempt? different rates?)

Look and the end results you need and work back from there.

Related Question