Re: An Invoicing db design, how would you do it

On Thu, 28 Mar 2002, Paul M Foster wrote:

> Accounting point: once an invoice has been generated, you _don't_ go
> back and edit it. You can void it, issue another invoice or credit, but
> never edit an existing invoice.

I second that point. Never change, always create a second -- correcting --
entry.

> As to design, I'd do it this way: the transaction table wouldn't really
> need an invoice_id. You would add in an "invoice detail" table which
> would scoop up all the uninvoiced and non-voided records for that
> customer from the transaction table. Then you'd have an "invoice header"
> file which would contain invoice_id, cust_id, etc. Invoices would print
> using the header and detail tables. When you do the "scooping", you'd
> mark the records in the transaction table with some status code showing
> they had been invoiced.

This can work, but it's awkward and prone to fail in the future. Take a
look at any accounts receivable module in accounting software (for service
businesses, at least) and you'll find tables for time entry and expense
entry. Keep these separate with dates and project number on each item.

When you want to create an invoice run a query that groups by client ID
and covers the date range you want. Pull data from the time and expense
tables to create your invoices. No need to mark which ones are processed as
you have the invoice itself that covers a date range and was created from
all time and expense records for that range.

If, for some reason, you don't want to bill for a particular amount of
time or some expenses, modify your invoice before sending it out.

This design works.

Rich

Dr. Richard B. Shepard, President

Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard(at)appl-ecosys(dot)com
http://www.appl-ecosys.com