Database Design Advice

[Kieren Diment]

I must say I quite like playing with triggers.  A well encapsulated trigger shouldn't take too much developer time, and so long as people read the schema definitions, and the trigger is correctly documented/linked to  in the right places (which may be tricky to anticipate in advance, where the right places are), then that's what I'd do.  Although I'd make a view instead if someone on the team felt strongly about it.

On 08/11/2013, at 10:41 PM, James Laver wrote:

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