Re: How to efficiently make an "history" ?

Hello Theo,

> > - InsertionDate : Date (default=now)
> I would make this a Timestamp, type Date restricts updates to once per
> day.

This is what I meant (and used in my tests), sorry for the confusion by not using the appropriate data type.

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

Again... The actual columns names are in ... French. So ("Valeur") I translated them in English without first thinking if this was allowed.

Sorry for creating confusion.

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

We agree on this. But this is no more a problem: it was an error in my description.

> - updates of existing entities and insertion of new ones are
> undiscernable.

Not following here. I agree that we need to put business rules on how to use the schema correctly. Do we speak about the same thing?

> 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

Right. Again, this was to simplify the description of my problem, but you are totally right. With the current schema, I can only describe one entity.

Next time I will provide/use more accurate description/terminology/...

Thank you for your valuable help Theo. Cheers,

                        Sacha