Simulation of an order / invoice / settlement database

Question

I am creating an e-commerce site that has the following scenario:

• The customer can purchase goods and create an order.
• An order may contain an unknown fee, which will be added after the customer pays for the total number of items. That is, the client pays a certain amount first. The order adds some fee and changes the total. And the customer pays for the difference. But two (or more) payments are associated with the same order.
• (optional) The customer can submit a single payment for several orders.

I currently have an Order table, and each order can consist of several OrderLineItem (simplified diagram):

 Order ===== customer line_items total status OrderLineItem ============= price quantity order product 

Payment is associated with the order (simplified scheme):

 Payment ======= order payment_account total result 

It seems very difficult to support multiple payments for a single order scenario in the current implementation. I believe that I need to enter invariable invoices into the system, and the payment should be related to the invoice, and not to the order. However, I will need help with the order / invoice / payment model for the scenario described above. Some specific questions I have are:

• The order and invoice are very similar to me (for example, both have items and totals). What is the main difference between typical e-commerce systems?
• How do I simulate invoices for my scenario? Should I OrderLineItem for Order AND InvoiceLineItem for Invoice ?
• Some preliminary thoughts: I will have several related facts with a certain order. Whenever an order changes the total quantity, I have to somehow calculate the difference and send a new / unchanged invoice to the customer. Then the customer can pay, and the payment will be related to the invoice.

I would like to hear some tips. Very grateful. Thanks!

Answer 1

There are many questions here, I will try to solve as many as I can. Many problems are a business model, not a data model.

Firstly, you are correct that you need an immutable invoice. After you create an invoice, you cannot change it; the standard way to handle invoice changes is to issue a credit note and a new invoice.

Think about the relationships between tables: Order does not need to hold lineItem , because they are referenced in a different way, i.e.

 Order ===== orderId customerId status OrderLineItem ============= orderLineItemId orderId product price quantity 

So, to view the order you attach to the tables on orderId. There is also no need to store total as calculated from the connection.

Try not to duplicate your data: your account will refer to orderLineItem s, but not necessarily the same in the order. For example, a customer orders A and B, but B is absent. You send A and create an invoice that refers to orderLineItemId for A. Thus, your invoice tables may look like this:

 invoice ======= invoiceId status invoiceLineItem =============== invoiceId orderLineItemId quantity 

In other words, there is no need to have item details. You might be wondering why you are not just adding invoiceId to the orderLineItem table - the reason is that the customer can order 10 items A, but you only send 8 of them, and the other two will go to the next account.

Payments are not made against orders, they are against billing. Therefore, your payment table should reference invoiceId.

Then you touch reconciliation. If everything is perfect, the client will make a payment against a certain account, even if a partial payment. In fact, this will be your biggest headache. Suppose a customer has several outstanding invoices for amounts x, y, and z. If they make payment p, to which do you distribute it? Perhaps in order of dates, so if p> x the remainder will be allocated against y. What if p = z? Perhaps the client intends to pay z now, but disputes y and lost x? How you deal with such things is up to you, but I can say that most billing systems do this very poorly.