Re: How to efficiently make an "history" ?

"kesako" <kesako_at_mail.com> wrote in message news:<9mja6i$qb8$1_at_news1.span.ch>...
> Hello,
>
> Here is the target: we want to be able to have a list of fields (Name,
> description, age, city, ...) describing an entity. The number of fields is
> not fixed and can be quite large. Furthermore, when a user, from the
> front-end, updates a particular field, we want to have a revision comment
> and to keep all old data i.e. change 'name' from 'james' to 'bob' with
> revision comment 'he prefers shorter names...'.
>
> To reach this goal, one possibility was to have a single table with these
> fields:
> - InsertionDate : Date (default=now)
I would make this a Timestamp, type Date restricts updates to once per day.

> - FieldName : String (name of the field i.e. 'name', 'city', ...)
> - Value : String (value of the field i.e. 'bob')
Value is a reserved word in SQL.

> - RevisionComment: String (i.e. 'he prefers shorter names...')
>
> with PK being composed of InsertionDate and FieldName. Until now, do you
> think this is ok or would you have a better idea/scheme?
Argh! Your basic idea is OK. However, with InsertionDate and Fieldname as PK,
- only one single name can be inserted/updated on a given day - updates of existing entities and insertion of new ones are undiscernable.

In order to decide which row belongs to which entity, you need to create another column identifiyn entities: (entityID, field_name, field_value, revision_comment, revision_time) PK is entityID, field_name, revision_time

>
> The problem now is how to efficiently query this data. Typical requests:
> - More recent values entered for fields named 'name', 'city' and 'country'
> - Last values entered on the 27th of August 2000 for fields named 'name',
> 'city' and 'country'
> - Historic of the Ten (or twenty, or...) last values entered on the 27th of
> August 2001 for the fields named 'name, 'city' and 'country'.
>
> and so on...
>
> How to efficiently write these queries, possibly by limiting the number of
> individual queries. As we are in a multitier system, each query is a network
> round-trip and I would prefer not use Stored procedure in a first approach.
>
> Any hint, advice, ... would be really helpful.
>
> Cheers,
>
>
>
> Sacha
Received on Thu Aug 30 2001 - 10:16:26 CEST