title: Order/Invoice/Payment database modeling
body: <p>I am designing an e-commerce website that has the following scenario:</p>
  
  <ol>
  <li>A customer can purchase items and create an order.</li>
  <li>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.</li>
  <li>(Optional) The customer can submit a single payment for multiple
  orders.</li>
  </ol>
  
  <p>Currently, I have an <code>Order</code> table and each order may consist of multiple <code>OrderLineItem</code>s (simplified schema):</p>
  
  <pre><code>Order
  =====
  customer
  line_items
  total
  status
  
  OrderLineItem
  =============
  price
  quantity
  order
  product
  </code></pre>
  
  <p>A payment is associated with an order (simplified schema):</p>
  
  <pre><code>Payment
  =======
  order
  payment_account
  total
  result
  </code></pre>
  
  <p>It seems to be very hard to support the multiple payments for a single order scenario in the current implementation. I reckon that I have to introduce immutable invoices in the system, and the payment should be associated with an invoice instead of an order. However, I would need some help with the order/invoice/payment modeling for the above scenario. Some specific questions I have:</p>
  
  <ol>
  <li>An order and an invoice look very similar to me (e.g. both have
  items and totals). What is the major difference in typical
  e-commerce systems?</li>
  <li>How should I model invoices for my scenario? Should I have
  <code>OrderLineItem</code>s for an <code>Order</code> AND <code>InvoiceLineItem</code>s for an <code>Invoice</code>?</li>
  <li>Some preliminary thoughts: I will have multiple invoices associated
  with a certain order. Whenever the order changes the total, I have
  to somehow calculate the difference and send a new/immutable invoice
  to the customer. Then, the customer can pay and the payment will be
  associated with the invoice.</li>
  </ol>
  
  <p>Would love to hear some advice. Much appreciated. Thanks!</p>