Re: How to efficiently make an "history" ?

Hello Mati,

> > >> Furthermore, when a user, from the front-end, updates a particular
> > column [sic: field], we want to have a revision commentmand to keep
> > all old data i.e. change 'name' from 'james' to 'bob' with revision
> > comment 'he prefers shorter names...'. <<
> >
> > Don't do this -- use a log file if you need to follow the history and
> > keep a separate text file with this useless commentary.
>
> I think we can't judge which data is useless in Kesako's business. As
> Kesako is planning to register the commentary, he/she has already probably
> considered the information as worth saving.

Yes. It is a requirement to be able, for a particular attribute*, to navigate through all its previous values, and see why it has changed. No strong performance is required when querying this "descriptive" information, but we need to be able to build reports on the evolution of particular attributes*.

*) Terminology: these are not SQL columns, rows, or anything else. It is in the business sense: the attributes that define a particular entity. We then map these business attributes to SQL concepts. Walk on eggs...

> This looks like a revision history issue. Have you been looking the
> existing version control software products (eg Merant PVCS, Microsoft
> VSS)?
I will take a look at this, right. Great idea. Thank you.

> They may give you some ideas. If the columns in tables are fixed,
> you can store new revisions as new rows, identify them with InsertionDate,
> and have a column for the commentary.

No, columns are not fixed. Furthermore, in this scheme, it means that we create some "sets" of columns and we need to update a complete row at the same time. This is not the wanted behaviour. We want to be able to modify the information, attribute by attribute, independantly, with a revision comment for each modification of each attribute.

Thank you for your help Mati. Cheers,

                                Sacha