Database Design Advice
awitney at sgul.ac.uk
Fri Nov 8 12:03:22 GMT 2013
On 8. 11. 2013 10:06, Smylers wrote:
> 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.
I would probably do this with a view, rule and a pl/pgsql function to
encode the logic, if that helps.
More information about the london.pm