An Invoicing db design, how would you do it

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.

Thanks

--
-------------------------------------------------------------------------
Medi Montaseri medi(at)CyberShell(dot)com
Unix Distributed Systems Engineer HTTP://www.CyberShell.com
CyberShell Engineering
-------------------------------------------------------------------------