Database Design Advice

[Smylers]

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