Database Design Advice

[James Laver]

On Fri, Nov 8, 2013 at 10:06 AM, Smylers <Smylers at stripey.com> wrote:
> James Laver writes:
>
>> 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?

Yes. Phone keyboard, sorry.

>> 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.

In this case, a view seems like the best solution. But if you're going
to materialise it for extra performance, then you're back in the world
of triggers (assuming eager materialisation), and back comes the
complexity :(

James