Invoice System Database Design

[shaunie]

Hi,

 

I am creating a system that invoices for events. An event will be of a type of service which will have a cost related to it. I had been planning to store all transactions (payments, invoices, refunds, charges etc) in a transactions table. However invoices are different to all other transactions as they will have invoice line items (made up of events). Should I create a separate invoices table or have an invoice id for each event in the event table?

 

I wanted to keep all transactions in one table as I felt this made sense and would be easier for calculating customer balances.

[Barand]

 Should I create a separate invoices table or have an invoice id for each event in the event table?

 

 

Both. You would at least need to store the invoice id and date (and maybe a reference to the customers order number) in an invoice table and then put the invoice id in the transactions covered by that invoice