looking for a simple database design for accounts receivable

Question

I am in the process of adding accounts receivable to one of my webapps. Essentially, I want to be able to create sales invoices and record payments received.

The reports, I generate are

• statement with balance outstanding
• invoice
• receipt

To create a statement, I was thinking of doing a union of receipts and invoices ordered by date.

I also need to cater for refunds/credits, which i am doing by refund = receipts with a negative amount, and credit = invoice with a negative amount.

All the invoices/receipts are exported to a full accounting package (so don't require double entry system at this end)

What i have come up with is

INVOICES
id
customer_id
total
tax_amount
reference
user_id
created

INVOICE_LINES
id
invoice_id
description
qty
unit_price
total
tax_amount

RECEIPTS
id
customer_id
reference
internal_notes
amount
user_id
created

Is there anything that i am missing?

Would a single transactions table be simpler instead of having separate invoice/receipt tables?

Another thought, is it normal to link a receipt to an invoice? what if a receipt was for multiple invoices.

Any advice appreciated (simplicity is the goal)

Answer 1

Look at the "Library of Free Data Models" from DatabaseAnswers.org

They have many basic designs that should inspire you. For example "Accounting Systems"