[thelist] methods for creating a history table

[rudy]

> What I'm thinking right now is that I might need to match the old data
> with the new data and create an array with the names of the columns
> that don't match. But I don't really want to have to do that.

yeah, that'd be awful

bite the bullet, create one history table per table that it's, um,
historying

do not use a separate autonumber as the primary key of this history table,
rather, use the same primary key values as the original table, plus a
datetime

thus, if your original table is

   create table foo
    ( id integer  auto_increment
    , bar1  ...
    , bar2  ...
    , primary key (id)
    )

then your history table would be

   create table foohist
    ( id integer
    , changeType char(1)
    , changeDate datetime
    , changedBy varchar(30)
    , bar1  ...
    , bar2  ...
    , primary key (id, changeDate )
    )


by the way, a comment about the mysql INTEGER(M) datatype

an integer is an integer and will always store in 4 bytes no matter how many
digits it has

the "display length" parameter M seems to have no effect

for portability, i recommend INTEGER without this non-standard attribute


rudy