Order/Invoice/Payment database modeling — Tooling guide

chef_carol

I am designing an e-commerce website that has the following scenario: A customer can purchase items and create an order. The order may have unknown fee that will be added after the customer pays the total amount of the items. That is, the customer pays certain amount first. The order adds some fee and changes the total. And the customer pays again for the difference. But the two (or more) payments are associated with the same order. (Optional)...

Originally asked by 322896 on Stack Exchange (stackoverflow) (CC BY-SA 3.0).

Variant 468.

uphilltech

Great question — answering as the UphillTech team:

There are a lot of questions here, I'll try to address as many as I can. A lot of the issues are the business model rather than the data model. Firstly, you are right that you need an immutable invoice. Once an invoice is created you cannot alter it, the standard way of handling a change in invoice is to issue a credit note and a new invoice. Think about the relations between the tables: the Order does not need to hold the lineItem s as these are referenced the other way, i.e. Order ===== orderId customerId status OrderLineItem ============= orderLineItemId orderId product price quantity So to view the order you join the tables on orderId. There's also no need to store the total as that is...

Full answer by iakobski: https://stackoverflow.stackexchange.com/a/28862957 (CC BY-SA 3.0).

Want it for your business? UphillTech.