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

An Invoicing db design, how would you do it

From
Medi Montaseri
Date:
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
-------------------------------------------------------------------------




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

From
Paul M Foster
Date:
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

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

From
Rich Shepard
Date:
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


Postgres and Foxbase

From
"David Siebert"
Date:
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)


Re: Postgres and Foxbase

From
Paul M Foster
Date:
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

Re: Postgres and Foxbase

From
Jean-Luc Lachance
Date:
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

Re: Postgres and Foxbase

From
Paul M Foster
Date:
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