Database Design Advice

[Kieren Diment]

On 08/11/2013, at 19:38, Mark Stringer <mark at repixl.com> wrote:

> On 11/08/2013 08:17 AM, James Laver wrote:
>> 
>> 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 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.
>> 
>> Performance (and complexity) would be much better than triggers
> From the OP: "But version should start at 1 for each document and be consecutive."
> 
> I'd guess that the date_created is being stored in the table, so could be used to order the records without the need for a serial, if ordering is the only concern.

I've tried to get away with that in the past. I was told that I'm not the messiah, I'm a very naughty boy. It's fine in one respect, but a total pain to fix if the business logic shifts on you at a later stage.