I'm working on an intranet with a database. It's php and mysql. I'm using Git for version control.
My problem is that very often I need to change the database structure. At the moment, we have a file where we write what we have done to the database, and whenever we do a pull we're supposed to apply the most recent commands in that file. It looks like this:
#2017-08-14 TAN: Remove swedish char from DB column name.
ALTER TABLE artiklar CHANGE COLUMN `alarmvalue` min_stored_amount int(11) DEFAULT '0';
#2017-08-29 SAG: Added changelog in database
CREATE TABLE `changelog` (
`id` int(6) unsigned NOT NULL AUTO_INCREMENT,
`reg_date` timestamp NOT NULL,
`comment` varchar(10000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 2017-08-25 TAN: Add new role "monitorStock" to person.
ALTER TABLE personal ADD COLUMN monitorStock int(1) DEFAULT '0' after qc;
This both seems unnecessary messy and also error prone. It is easy to forget but in that case we notice it quickly, but it can cause worse problems. As long as I only add tables or columns to existing tables, everything is fine, but if I rename or remove anything there's no way to reverse it. It must be a better way to solve this.
In short, I want to version control the database structure, but not its content.
