Search Postgresql Archives

Re: Invoice Table Design

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



rob wrote:
Hi Rich,

thanks for the response -- going from Mongo to Postgres does require the
kind of approach you suggest.

I suppose my question was a little bit more along the lines if anyone has
experience with designing payment / invoicing systems and any caveats they
may have encountered along the way.

I have stumbled upon  table inheritance
<https://www.postgresql.org/docs/9.6/static/ddl-inherit.html>   available in
9.6, which I think might be helpful.


The closest thing I have done to what you are describing is to create invoices that had multiple component inputs, i.e., a section for time and labor, and a section for other direct expenses such as materials and travel. This system employed a model something like the three-table outline you suggested earlier where there is a top-level table that defines invoices and supplies a primary key, and then the different types of component elements like the labor, materials, and expense charges each appear in different tables specific to the nature of those charges with a foreign key reference to the invoice table. The invoice table primary key then provide a means to combine all the accumulated charges together for final invoice presentation. I did not use table inheritance.

I looked at table inheritance years ago and ended-up running into some show-stopping problem related to indexes. Review this thread for context:


https://www.postgresql.org/message-id/flat/60243.216.238.112.88.1102080278.squirrel%40216.238.112.88#60243.216.238.112.88.1102080278.squirrel@216.238.112.88


I have not followed whether table inheritance has changed, however, so there might be something new others can help with. Me personally, I stick with the earlier sound advice of Melvin Davidson and Rich Shepard and normalize.

Another caveat would relate to thinking about the response to invoices. Normally (or maybe naively), one would expect to issue an invoice and then receive a timely payment. In reality, one may or may not receive a timely payment. There might be end up being multiple invoices outstanding. You may have to re-issue an invoice. And if you get a payment, it might be a partial payment, or a payment that applies to more than one invoice. So you might want to think about a way to track and apply payments and partial payments to one or more invoices.

-- B







--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux