Re: Invoice Table Design

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(dot)216(dot)238(dot)112(dot)88(dot)1102080278(dot)squirrel%40216(dot)238(dot)112(dot)88#60243(dot)216(dot)238(dot)112(dot)88(dot)1102080278(dot)squirrel(at)216(dot)238(dot)112(dot)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