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: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

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).
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
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
As invoices are generated, move records from TempTransactions to
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
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

Perhaps there are better ways of doing this, hence this post.


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


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-2017 The PostgreSQL Global Development Group