Database Design Advice

[Adam Witney]

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.

Adam