Skip site navigation (1) Skip section navigation (2)

An Invoicing db design, how would you do it

From: Medi Montaseri <medi(at)cybershell(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: An Invoicing db design, how would you do it
Date: 2002-03-28 07:21:01
Message-ID: 3CA2C45D.9E90AA91@cybershell.com (view raw or flat)
Thread:
Lists: pgsql-general
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
-------------------------------------------------------------------------




Responses

pgsql-general by date

Next:From: Frank JoerdensDate: 2002-03-28 11:33:21
Subject: Bytea vs. BLOB (what's the motivation behind the former?)
Previous:From: Tom LaneDate: 2002-03-28 07:05:05
Subject: Re: Why won't the query planner use my index?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group