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` VARCHAR(512) NOT NULL ,
`user_id` INT NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
INDEX `history_module` (`module_id` ASC) ,
INDEX `history_user` (`user_id` ASC) ,
CONSTRAINT `history_module`
FOREIGN KEY (`module_id` )
REFERENCES `test_db`.`modules` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `history_user`
FOREIGN KEY (`user_id` )
REFERENCES `test_db`.`users` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;We do no action on the deletes here because we want that constraint error if someone tries to delete a module or user. Make them think about what they are really doing, and if it is the best decision. Let’s make the last two tables:
CREATE TABLE `test_db`.`history_parents` (
`history_id` INT NOT NULL ,
`parent_id` INT NOT NULL ,
`parent_module_id` INT NOT NULL ,
PRIMARY KEY (`history_id`, `parent_id`, `parent_module_id`) ,
INDEX `history_parents_history_id` (`history_id` ASC) ,
CONSTRAINT `history_parents_history_id`
FOREIGN KEY (`history_id` )
REFERENCES `test_db`.`history` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB; CREATE TABLE `test_db`.`history_children` (
`history_id` INT NOT NULL ,
`child_id` INT NOT NULL ,
`child_module_id` INT NOT NULL ,
PRIMARY KEY (`history_id`, `child_id`, `child_module_id`) ,
INDEX `history_children_history_id` (`history_id` ASC) ,
CONSTRAINT `history_children_history_id`
FOREIGN KEY (`history_id` )
REFERENCES `test_db`.`history` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Notice a combination of the foreign-key history_id and the other two columns are what make up the Primary-key. Also, the parent_id and child_id in these tables are going to be the actual id from the original record. If the parent_id or child_id has been deleted, then it will be found in the history table in the old_id column. You will have to search the old_id and module_id on the history table to find out information about these parent and children records. If they have not been deleted then you will have to find them in the production tables for said module.
Let’s insert some data so that this all makes some sense. The module’s table has a code column in it. We will use this code to get our modules id when saving to the history table.
INSERT INTO `test_db`.`modules` (`code`, `name`, `description`, `history_data_description`) VALUES ('accounts', 'Accounts', 'Account Information', 'name, phone');
INSERT INTO `test_db`.`modules` (`code`, `name`, `description`, `history_data_description`) VALUES ('account_notes', 'Account Notes', 'Notes for Accounts', 'subject, body');So for the history_data_description we put the columns from that modules table that will be saved. Let’s insert a user.
INSERT INTO `test_db`.`users` (`login`, `first_name`, `last_name`, `password`) VALUES ('kacie', 'Kacie', 'Houser',SHA1('happyhappyjoyjoy'));Note the use of the SHA1 function for password’s. Please never store plain text passwords.
Now we have our structure for our history table. In part two we will be creating the necessary stored procedure’s and triggers required to delete records from our CRM system. Have a look at the dirgram below to make sure all your tables are correct.
