1

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
  • It's not safe to recur monthly on any day, if they sign up for a month with 31 how will you handle it? You either prorate for the first month, and recur on the 1st. or you recur every 30 days (bankers month) – Evan Carroll Nov 18 '17 at 23:48
  • This can get complicated very quickly. Sales vs invoice vs accounts vs accounting. My reccomendation would be to capture details of a purchase/invoice and the the payments refunds seperately. If you keep all transactions you be able to calculate thier balance. – Sir Swears-a-lot Nov 19 '17 at 8:13
  • @peter So, one order will correspond to one transaction only? – Portekoi Nov 19 '17 at 8:35
  • 1
    There is no single correct answer for this. It will depend on your requirements. If the business requires you to be able to receive multiple payments on one sale, refunds, potentially adjustments or discounts then you need to cater for that. So what is your "business requirement?" – Sir Swears-a-lot Nov 19 '17 at 9:01
  • 1
    @DiegoVieira I've used app.sqldbm.com – Portekoi Feb 26 at 8:46
1

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.

  • You right. I don't need to go so far in details. So, in a sale, I will authorize 'normals products' or 'only one subscription'. – Portekoi Nov 20 '17 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.