3

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)

bumperbox
  • 10,166
  • 6
  • 43
  • 66

2 Answers2

11

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

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • this one is of interest, i have emailed barry to see if the more detailed version of the design is available. thanks http://www.databaseanswers.org/data_models/invoices_and_payments/index.htm – bumperbox Aug 02 '11 at 09:49
  • 2
    @bumperbox: This site is excellent to start you off. You may need to donate :-) I have no link to the site but the guy does all this for the love of it... – gbn Aug 02 '11 at 09:51
  • I only have one problem. Seeing the design from the comment, there are no table that defines the amount. Where can I put the amount for each transaction? – Mark Oct 23 '13 at 02:08
  • @Mark, the model is only showing key fields. Add whatever other fields you deem necessary, such as TransAmount in Financial_Transactions. – June7 Nov 01 '21 at 18:20
2

Have a look at this similar question Database schema design for a double entry accounting system? . I came across it googling for 'bookkeeping database design' as I reckon you'll easily find free or relatively low-priced databases already exist - as you say - simplicity is the goal.

Community
  • 1
  • 1
Barry Kaye
  • 7,682
  • 6
  • 42
  • 64