Re: Invoice Table Design

----- Original Message -----
> From: "rob" <rob(at)216software(dot)com>
> To: pgsql-general(at)postgresql(dot)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 uniformly incremented 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 reports be identified by uniformly incremented integers on a per employee and per year basis, but the pattern applies to many application domains ... like invoice numbers.

Implementation involves a control value to record the most-recently used key value and a before-insert trigger to increment the 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 about it 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 to assure that no checks go missing or otherwise fail to be accounted for. A similar serialized accountability could apply in 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 consider it the proper term for this design pattern.

-- B