[Dbix-class] Strict sequence primary key

[Rob Kinyon]

THIS IS A RELATIONAL THEORY ANSWER, NOT A DBIx::Class SPECIFIC ANSWER.
YOU HAVE BEEN WARNED.

On Wed, Jan 20, 2010 at 17:36, Wes Cravens <wcravens at cortex-it.com> wrote:
>> Why do require strictly sequential primary keys?
>
> It's not always necessary but we do have architecture in an accounting
> transaction system where items must be strictly sequential and can also
> never be deleted.  The auditing side of the application require this.

Relational databases, particularly ones that may need to scale into
clusters, are very poor candidates for providing strict sequentiality.
Instead, sequentiality should be ensured through grouping and columns
recording high-resolution time-of-mutation (mutation == insertion /
modification / deletion). Time of mutation can be synchronized across
servers using NTP or similar protocols. Or, database servers do
provide timing mechanisms. Given that this is an accounting
application, you are likely using Oracle or Sybase. Both provide
high-resolution (1000th of a second) timestamps that are mediated by
the server.

Grouping can be done by either identifying a parent row(s) and/or
providing a group number that's assigned when the group is formed.

>From this, sequentiality can be reconstructed as needed, plus you are
capturing more information.

Rob