Thread: 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@CyberShell.com Unix Distributed Systems Engineer HTTP://www.CyberShell.com CyberShell Engineering -------------------------------------------------------------------------
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
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@appl-ecosys.com
http://www.appl-ecosys.com
There is a guy at my office trying to use Postgres with ODBC and Foxbase. He
is having a terrible time with it. He can not eseem to get it to see the
data and or views he has created. The ODBC driver seems to work fine with
access and excel for him but not Foxbase.
I can not help him because I have no need or want to learn "Foxbase" I will
stick with Java thank you.
Any suggestions?
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Paul M Foster
Sent: Thursday, March 28, 2002 6:35 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Tue, Apr 02, 2002 at 09:53:28AM -0500, David Siebert wrote: > There is a guy at my office trying to use Postgres with ODBC and Foxbase. He > is having a terrible time with it. He can not eseem to get it to see the > data and or views he has created. The ODBC driver seems to work fine with > access and excel for him but not Foxbase. > I can not help him because I have no need or want to learn "Foxbase" I will > stick with Java thank you. > Any suggestions? Foxbase was the predecessor to FoxPro. I doubt anything knows how to talk to Foxbase, it's so old. Now, if you really meant to say FoxPro, there should be ODBC drivers around for FoxPro. At about version 3 or so of FoxPro, Microsoft mangled the file format, so you'll have to know whether it's prior to or later than version 3. If FoxPro ODBC drivers won't work, try dBase III. The file format is almost identical. However, the index and memo files are different. HTH, Paul
If I recall correctly, Foxbase uses the same file format as DBase. You can use dbf2pg to dump the Foxbase database and reload it into PosgreSQL. Paul M Foster wrote: > > On Tue, Apr 02, 2002 at 09:53:28AM -0500, David Siebert wrote: > > > There is a guy at my office trying to use Postgres with ODBC and Foxbase. He > > is having a terrible time with it. He can not eseem to get it to see the > > data and or views he has created. The ODBC driver seems to work fine with > > access and excel for him but not Foxbase. > > I can not help him because I have no need or want to learn "Foxbase" I will > > stick with Java thank you. > > Any suggestions? > > Foxbase was the predecessor to FoxPro. I doubt anything knows how to > talk to Foxbase, it's so old. Now, if you really meant to say FoxPro, > there should be ODBC drivers around for FoxPro. At about version 3 or so > of FoxPro, Microsoft mangled the file format, so you'll have to know > whether it's prior to or later than version 3. If FoxPro ODBC drivers > won't work, try dBase III. The file format is almost identical. However, > the index and memo files are different. > > HTH, > > Paul > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Tue, Apr 02, 2002 at 06:37:15PM -0500, Paul M Foster wrote: > On Tue, Apr 02, 2002 at 09:53:28AM -0500, David Siebert wrote: > > > There is a guy at my office trying to use Postgres with ODBC and > Foxbase. He > > is having a terrible time with it. He can not eseem to get it to see the > > data and or views he has created. The ODBC driver seems to work fine with > > access and excel for him but not Foxbase. > > I can not help him because I have no need or want to learn "Foxbase" I will > > stick with Java thank you. > > Any suggestions? > > Foxbase was the predecessor to FoxPro. I doubt anything knows how to > talk to Foxbase, it's so old. Now, if you really meant to say FoxPro, > there should be ODBC drivers around for FoxPro. At about version 3 or so > of FoxPro, Microsoft mangled the file format, so you'll have to know > whether it's prior to or later than version 3. If FoxPro ODBC drivers > won't work, try dBase III. The file format is almost identical. However, > the index and memo files are different. > Ohmygosh, I completely forgot about this. I wrote a program called dbfsak (originally called dbfdump) on SourceForge. It was originally designed to dump the contents of dBase/FoxPro/Clipper files in human-readable content. But later, I added the capability to dump a dBase/FoxPro/Clipper file in PostgreSQL SQL format. Naturally, source is available. I can't believe I forgot that I wrote that. Maybe that would help. Paul