----- Original Message -----
> From: "rob" <rob@216software.com>
> To: pgsql-general@postgresql.org
> Sent: Tuesday, November 29, 2016 3:45:21 AM
> Subject: Re: [GENERAL] Invoice Table Design
>
> 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.
The other bit of experience I'll share is the suggestion that invoicing is a situation that lends itself to the
uniformlyincremented sequence pattern. Accountants and comptrollers love this.
I detailed the pattern for a specific example of expense reports for which the business requirement was that expense
reportsbe identified by uniformly incremented integers on a per employee and per year basis, but the pattern applies to
manyapplication domains ... like invoice numbers.
Implementation involves a control value to record the most-recently used key value and a before-insert trigger to
incrementthe value and effectively serialize inserts, allowing rollback that does not cause non-uniformities in the
sequence.
Some novice data base designers just like the idea of having no missing numbers in the sequence, and when they ask
aboutit on this forum, they usually get some well-deserved flak, but there is a very practical aspect from an auditing
perspective.Think of the situation with a traditional hard-copy check book. You count on the numbers being in sequence
toassure that no checks go missing or otherwise fail to be accounted for. A similar serialized accountability could
applyin many other circumstances.
The whole message thread is here
https://www.postgresql.org/message-id/flat/758d5e7f0608171414l548db1e9x43f2372c560c3c2%40mail.gmail.com
Specifically my detailed explanation within that thread is here:
https://www.postgresql.org/message-id/44E376F6.7010802@seaworthysys.com
Some refer to this as the "gapless" sequence. Personally I dislike that term. Recently, I observed someone on this
forum(... sorry, I can't find a link to credit it ...) used the term "keyed sequence", which I find appealing enough to
considerit the proper term for this design pattern.
-- B