Database Design Advice

[William Blunn]

On 07/11/2013 13:03, Smylers wrote:
> That is, if there is a record for document_id = 3846, version = 6
>    then there should also be records for version 1 to 5 of that document
>    ID. Is there a way of enforcing that at the DB level? We're using
>    Postgres, if that makes a difference.

Instead of storing a version ID (e.g. 1, 2, 3, 4, 5, 6), you could store 
a SERIAL.

So for one document ID, you might store versions 10, 11, 12, 50, 75, 87.

If you then later want to know the version number as a consecutive 
counting number (e.g. 1, 2, 3, 4, 5, 6), then you could compute it using 
a PostgreSQL window function.

This should guarantee that you always get consecutive counting numbers.

Regards,

Bill