Invoice table design

Question

I am creating an invoice table for the customers. Sometime they will have to send me the commission fees or sometime I send them fees (account balance). How to design a invoice table in this situation?

I have came up with this solution, im not sure if this is correct or what is the alternative way?

tbl_invoice
- invoice_id (PK)
- order_id (FK)
- invoice_date
- amount (copy the price from tbl_order.total table)
- status (Invoice Sent, Cancelled, Amount Received, Amount Sent)


tbl_Payments
 - invoice_id (FK)
 - amount_received (recieved commission fees from customer)
 - amount_sent (sent fees to customer)
 - date_received
 - date_sent

if the tbl_invoice.amount is -30.00 that mean customer will send me the fees.

if the tbl_invoice.amount is 30.00 then I will send the customer the fees.

Do I need tbl_invoice.amount field?

If you could redesign my tables how it should be that be great.

Answer 1

You should be tracking:

• amount_due
• amount_sent
• amount_received

All three are important. And in the payments, also track the fee_paid.

Lastly, it's usually better to use T-ledger accounting (i.e. debit/credit) for this kind of stuff. It makes things much cleaner if you ever need to worry about discounts, coupons, refunds, chargebacks, etc.

Answer 2

A few things:

1. Normalize invoice status to be its own lookup table, then put a Status ID in the invoice table rather than 'Sent', 'Cancelled', etc.

2. Definitely keep invoice amount. This might have to be different from the price value in tbl_order.total if you ever need to take into account discounts. In any case, numerical data is cheap to store and will be faster to query if you dont have to do any joins.

3. Give the Payments table its own ID column and make it the PK.

4. The rest looks ok. There is a case for having two tables, one for payments outgoing, and another for payments incoming. If you really only need to keep the amount and date information, then I dont think you need to make it any more complicated.

Thanks, Chris.