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

On Wed, Mar 27, 2002 at 11:21:01PM -0800, Medi Montaseri wrote:

> Hi,
>
> I would like to seek your opinion on a simple invoicing database design
> consisting of Customers, Invoices and Transactions.
>
> Design A:
>
> Table Customers ( containing the usual customer information )
>
> Table Transactions containing
> - trans_id
> - cust_id
> - invoide_id (linking to Table Invoices, described later)
> - status_id (linking to Table Status containing "Void", etc)
> - UnitCost
> - Units
> - Summary
> - DetailDescription
> - TransactionDate
>
> Table Invoices containing
> - invoice_id
> - cust_id
> - Status (linked to Status table such as Void, Billed, Sent, Paid, etc)
> - Total Cost
>
> Transactions are for services provided (say a consulting service).
> Transactions
> are inserted over time and associated with a customer and associated
> with a magic
> invoice_id (say 1). At one point an Invioce is genereated for a customer
> over span
> of time. The newly generated invoice_id is then used to update all
> relevant
> transactions such that a transaction is now associated with a real
> invoice_id. The
> Total Cost is computed and written to Invoices.TotalCost.
>
> If a transaction is modified (due to dispute, or typos, or whatever),
> then a trigger
> would re-compute the new TotalCost.
>
> While this design is most flexiable, the problem is that the paper copy
> (print out)
> could get out of sync and no way of auditing previous changes. Hence
> Design B.
>
> Design B.
>
> Same setup, but have two Transactions; TempTransactions and
> PermTransactions.
> As invoices are generated, move records from TempTransactions to
> PermTransaction
> fully associated with a valid CustomerID and InvoiceID. If change is
> needed, status
> of that InvoiceID will be set to something (Void or something else) and
> a new
> InvoiceID is generated and all transactions are re-inserted to the
> PermTransactions
> again. While this design provides backward auditing, it does consume
> table space,
> but in practice it is hard to believe that an invoice could be disputed
> more than 10
> times.
>
> Perhaps there are better ways of doing this, hence this post.
>

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.

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.

You could modify transactions all you like, up to the point where they
were invoiced. At that point, they're set in stone, not to be further
modified. If the customer disputes something, then you issue a credit
for a specific line item on a separate credit memo or invoice.

Your "audit trail" isn't really needed for the transactions file. Once
they're echoed in the "detail" file, there's your audit trail.

Paul