How to efficiently make an "history" ?

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)
• FieldName : String (name of the field i.e. 'name', 'city', ...)
• Value : String (value of the field i.e. 'bob')
• 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?

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