MySQL stored procedure’s come in really handy at times. I like to let the database do the work for me sometimes.

For example, in an e-commerce system you may need to calculate a tax. For our example today though, we will use the tables we made in my previous article on the XREF table. We are going to focus this tutorial on getting the appropriate tables in place to preform our history logging.

To start, we are going to create a module’s table so that our history table can be one table instead of many and we are going to create a users table. We are going to keep record of parent and child relationships of the deleted record. So we will add two more tables: history_children and history_parents along w/ our history table.

Let’s create our modules table:

CREATE TABLE `test_db`.`modules` ( `id` INT NOT NULL AUTO_INCREMENT , `code` VARCHAR(45) NOT NULL , `name` VARCHAR(45) NOT NULL , `description` VARCHAR(128) NULL , `history_data_description` VARCHAR(45) NOT NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) , UNIQUE INDEX `code_UNIQUE` (`code` ASC) , UNIQUE INDEX `name_UNIQUE` (`name` ASC) ) ENGINE = InnoDB;

Notice the data_description field. We will use this for our history table, to know what data we are keeping in our history table.

Let’s create the users tables:

CREATE TABLE `test_db`.`users` ( `id` INT NOT NULL AUTO_INCREMENT , `login` VARCHAR(24) NOT NULL , `first_name` VARCHAR(24) NOT NULL , `last_name` VARCHAR(24) NOT NULL , `password` VARCHAR(255) NOT NULL , PRIMARY KEY (`id`) , UNIQUE INDEX `id_UNIQUE` (`id` ASC) , UNIQUE INDEX `login_UNIQUE` (`login` ASC) ) ENGINE = InnoDB;

Now let’s create the rest of the tables:

CREATE TABLE `test_db`.`history` ( `id` INT NOT NULL AUTO_INCREMENT , `module_id` INT NOT NULL , `old_id` INT NOT NULL , `data`...