Database Design Advice
bill+london.pm at blunn.org
Thu Nov 7 14:43:40 GMT 2013
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
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.
More information about the london.pm