Database Design Advice

[Mark Stringer]

On 11/07/2013 01:28 PM, Abigail wrote:
>
> I'm not familiar with Postgres, but I've used triggers to enforce
> business rules like this in the past (using Sybase). In Sybase,
> triggers are run before a transaction is committed, and you can
> rollback a transaction frow within a trigger.
>
> On an insert, you'd check whether the number of documents with the
> same document_id, and version less than the to be inserted version is
> 1 less than the version. You'd disallow updates of the version number,
> and on deletions, you check if no higher version exists with the same
> document_id.
>
Another RDBMS, so may be different again, but when I've used triggers in 
the past (MS SQL), I've used BEFORE INSERT triggers to select the max 
existing version ID, then increment that by one for the new version of 
the doc - preventing the need to know the current max version and supply 
it manually for the insert. It also prevents any possibility of 
application layer issues causing version number woes.