Iterating schemas over time is not an uncommon thing. Often requirements emerge only after you have data, and then directed action is possible. Consequently, working on existing data, and structuring and cleaning it up is a common task.
In todays example we work with a log table that logged state
transitions of things in freeform
After some time the log table grew quite sizeable, and the log
strings are repeated rather often, contributing to the overall
size of the table considerably.
We are starting with this table:
CREATE TABLE `log` ( `id` int NOT NULL AUTO_INCREMENT, `device_id` int NOT NULL, `change_time` datetime NOT NULL, `old_state` varchar(64) NOT NULL, `new_state` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB
That is, our log table has an
id field to allow
individual row addressing, and then logs the state change of a …