Database Design Advice

[Smylers]

James Laver writes:

> Smylers <Smylers at stripey.com> wrote:
> 
> > William Blunn writes:
> >
> > > 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.
> 
> This was my immediate thought as well, but I'd probably cheat and
> declarr the document version numbers to be oureky decorative
                                             ^^^^^^
“purely”, I presume?

> and thus the realm of userspace to turn them into 1,2,3,4,5 etc. --
> one can, after all fix this with a single line of code.

True. I'm all for cheating.

But reporting will be done by Crystal Reports connecting directly to the
database (and there's a distinct possibility that there will end up
being more than one code base (in different programming languages) using
the database too), so I'd rather any serial-number-to-per-document-
version-number conversion was handled in the database, so it's in just
one place.

That conversion could be abstracted by a view, so the complexity is
hidden for somebody just doing a SELECT — but my attempts with the
Rank() window function seem like too complex complexity to be worth it.

> Performance (and complexity) would be much better than triggers

Worth bearing in mind. 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