Re: How to efficiently make an "history" ?

>> Here is the target: we want to be able to have a list of fields
(Name, escription, age, city, ...) describing an entity. The number of fields is not fixed and can be quite large. <<

First of all, please post DDL and not personal narratives or pseudo-code. Secondly, SQL has no "fields" -- tables are made of rows, rows are made of columns and columns hold values. This is important!!

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

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

All your queries will run like glue if you actually do this. Why do you need it? for how long do you need a history? How many thousands do you get like your exmapels per day? Received on Thu Aug 30 2001 - 06:23:28 CEST