InstaGov/schema/edit log

About

• Purpose: Maintains an edit history of changes to everything except ratings, i.e. things which shouldn't be edited very much to begin with. The size of this table should be kept to a minimum by recording only changes to existing records, not their initial creation.
• Fields:
  • Table: name of the table whose modification is being recorded. If table names are ever changed, data in this field should be updated too (if schema remains compatible).
  • Field: name of field whose modification is being recorded; same note applies as for Table
  • ID_Rec: value of primary key (typically "ID") for record being modified
  • ValOld: the value before the edit
  • ValNew: the value after the edit

History

• 2013-01-03 added WhenStarted, WhenFinished, ID_User

SQL

<mysql>CREATE TABLE `edit_log` (

  `ID`      INT               NOT NULL AUTO_INCREMENT,
  `Table`   VARCHAR(15)       NOT NULL COMMENT "name of table being modified",
  `Field`   VARCHAR(15)       NOT NULL COMMENT "name of field being modified",
  `ID_Rec`  INT               NOT NULL COMMENT "primary key value of record being modified",
  `ValOld`  VARCHAR(255)      NOT NULL COMMENT "value before edit",
  `ValNew`  VARCHAR(255)      NOT NULL COMMENT "value after edit",
  `WhenStarted`  DATETIME     NOT NULL COMMENT "when the save was attempted",
  `WhenFinished` DATETIME DEFAULT NULL COMMENT "when the save was successfully completed",
  `ID_User`      INT          NOT NULL COMMENT "numerical ID of user who made the edit",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;