Re: How to efficiently make an "history" ?

Hello Joe,

> First of all, please post DDL and not personal narratives or
> pseudo-code.

This is because *it* aims to be narrative! I wanted to express the concept I wanted to reach. As you most probably know it, personal narrative is much less accurate but much more powerfull to express idea. Try to tell your girlfriend you love her in DDL ;)

> Secondly, SQL has no "fields" -- tables are made of
> rows, rows are made of columns and columns hold values. This is
> important!!

I didn't know rows were made of columns. I thought that tables were made of rows *and* columns.

Why are you seem allergic to the "field" word? We both seem to understand what it means and I don't see a possible confusion with something in SQL.

> >> 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 *need* to keep old values, all, and why they have been changed. This is a business requirement.

The exactly same case exists in the retail business: you want to keep the historic (maybe not the good term...again) of all prices a particular item had at a particular moment. You want to be able to query this, make calculation, ...

And what is the advantage of a text file over a SQL database in this case?

> >> The problem now is how to efficiently query this data. Typical
 ...
> '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?

in one year, we may imagine that we will have about 100'000 rows and, after a "data feeding period" (terminology...again), we will have relatively minimal data activity... a few hundreds new record each day is a maximum.

I have just artificially populated a database with 600'000 records of the previous pseudo-description and made some of the queries I've described above and it runs quite well (subjective judgement).

I continue my investigations.

Thank you for your help. Cheers,

                                    Sacha