[thelist] methods for creating a history table
rudy
r937 at interlog.com
Fri Feb 7 11:24:01 CST 2003
> 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
More information about the thelist
mailing list