William Blunn writes: > 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),
Yes, it's a requirement to display the document's version number. > then you could compute it using a PostgreSQL window function. > > This should guarantee that you always get consecutive counting > numbers. Thank you for the suggestion; I hadn't used window functions before, so have just tried this out. A window function only operates over the rows that are being selected in the query, not all matching rows in the table. Which means that when selecting a single record, the Rank() window function always returns 1. So, using your numbers above, selecting the version with primary key 75 and calculating its version number as being 5, seems to involve something along the lines of: SELECT * FROM ( SELECT *, Rank() OVER (PARTITION BY document_id ORDER BY id) FROM document_version WHERE document_id = ( SELECT id FROM document_version WHERE id = 75 ) ) AS _ WHERE id = 75; The middle SELECT finds the records for all versions of the document, and calculates a rank for each one. The outer SELECT then discards all the rows except for the one we're interested in. The inner SELECT is to find the document ID for the rows which will be selected by the middle query and discarded by the outer one — which therefore involves passing the primary key 75 to the query twice. So it's certainly possible. But I'm thinking the above complexity is worse than the original issue I was trying avoid with it. However I feel richer for having learnt about window functions, which I'm pleased to now have in my toolbox and expect will come in handy sooner or later. Thanks. Smylers -- The UK gov's proposed gagging law will suppress protest and campaigning by charities and political groups. Read more: http://civilsocietycommission.info/ Please sign this petition: http://38d.gs/1cJ9ViA Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND