is Payment table needed when you have an invoice table like this?

Question

this is my invoice table:

Invoice Table: invoice_id creation_date due_date payment_date status enum('not paid','paid','expired') user_id total_price

I wonder if it's Useful to have a payment table in order to record user payments for invoices.

payment table can be like this: payment_id payment_date invoice_id price_paid status enum('successful', 'not successful')

Answer 1

If you want to allow more than one payment for an invoices, then yes a payment table would be useful.

It's also a good idea to keep your database normalized as much as possible.

Answer 2

Yes. Sometimes they might not pay in full or overpay, or pay in multiple transactions. Having the separate table allows you to keep track of this.

With just one table, all you can see is the original amount and the amount remaining to be paid, but not the history of payments.

Answer 3

Could a payment span multiple invoices?

Could an invoice be payed via multiple payments?

My guess is you will need a payment table and probably an account table and some sort of transaction table as well.

Answer 4

Many accounting applications have invoice_header, invoice_line, account_ledger, ledger_application and account tables.

The account_ledger table typically contains the items on the customer/vendor accounts. These items could be invoices, payments, or whatever.

The ledger_application table holds the information on the many-to-many relationship between payments and invoices.

The invoice_header table contains all information associated with an invoice which does not belong in the account_ledger table.

The invoice_line table has the data pertaining to the individual items/quantities/amounts on the invoice.

The account table stores data about the customer/vendor such as address, contact info, etc.