I have this database and that i need normalisation. I am a newbie and need some assistance with normalisation and also how do i delete vehicle content. i want to also delete the image, item and associated image for that content when i hit delete. any help would be greatly appreciated.
below is the query for creating the tables
/* Source Server : LOCAL MYSQL Source Server Type : MySQL Source Server Version : 50542 Source Host : localhost Source Database : vehicle_showcase Target Server Type : MySQL Target Server Version : 50542 File Encoding : utf-8 Date: 04/18/2015 22:02:00 PM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `administrative_users` -- ---------------------------- DROP TABLE IF EXISTS `administrative_users`; CREATE TABLE `administrative_users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL DEFAULT '1', `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for `images` -- ---------------------------- DROP TABLE IF EXISTS `images`; CREATE TABLE `images` ( `img_id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL DEFAULT '1', `file_name` varchar(255) NOT NULL, `file_path` varchar(255) NOT NULL, `vehicle_image` tinyint(1) DEFAULT '0', `is_media` tinyint(1) DEFAULT '0', `media_type` varchar(4) DEFAULT NULL, PRIMARY KEY (`img_id`) ) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for `item_assoc_images` -- ---------------------------- DROP TABLE IF EXISTS `item_assoc_images`; CREATE TABLE `item_assoc_images` ( `item_assoc_images_id` int(11) NOT NULL AUTO_INCREMENT, `img_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `item_assoc_image_name` varchar(255) NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '1', PRIMARY KEY (`item_assoc_images_id`), KEY `item_id` (`item_id`), KEY `img_id` (`img_id`), CONSTRAINT `fk_image_image_id` FOREIGN KEY (`img_id`) REFERENCES `images` (`img_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_item_assoc_item_id` FOREIGN KEY (`item_id`) REFERENCES `vehicle_items` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for `vehicle_content` -- ---------------------------- DROP TABLE IF EXISTS `vehicle_content`; CREATE TABLE `vehicle_content` ( `content_id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL DEFAULT '1', `vehicle_id` int(11) NOT NULL, `sort_id` int(11) NOT NULL, `item_id` int(11) NOT NULL, `main_content_id` int(11) NOT NULL, PRIMARY KEY (`content_id`), KEY `item_id` (`item_id`), KEY `vehicle_id` (`vehicle_id`), KEY `main_content_id` (`main_content_id`), CONSTRAINT `fk_item_id` FOREIGN KEY (`item_id`) REFERENCES `vehicle_items` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_vehicle_content_vehicle_main_content_1` FOREIGN KEY (`main_content_id`) REFERENCES `vehicle_main_content` (`main_content_id`), CONSTRAINT `fk_vehicle_id` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`vehicle_id`) ) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for `vehicle_items` -- ---------------------------- DROP TABLE IF EXISTS `vehicle_items`; CREATE TABLE `vehicle_items` ( `item_id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL DEFAULT '1', `name` varchar(255) NOT NULL, `sort_id` int(11) NOT NULL, `img_id` int(11) NOT NULL, PRIMARY KEY (`item_id`), KEY `img_id` (`img_id`), CONSTRAINT `fk_img_id` FOREIGN KEY (`img_id`) REFERENCES `images` (`img_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for `vehicle_main_content` -- ---------------------------- DROP TABLE IF EXISTS `vehicle_main_content`; CREATE TABLE `vehicle_main_content` ( `main_content_id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(4) NOT NULL DEFAULT '1', `main_content_name` varchar(50) NOT NULL, PRIMARY KEY (`main_content_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for `vehicles` -- ---------------------------- DROP TABLE IF EXISTS `vehicles`; CREATE TABLE `vehicles` ( `vehicle_id` int(11) NOT NULL AUTO_INCREMENT, `active` tinyint(1) NOT NULL DEFAULT '1', `vehicle_name` varchar(100) NOT NULL, `img_id` int(11) NOT NULL, `vehicle_manufacturer` varchar(255) NOT NULL, `year_manufactured` int(4) NOT NULL, PRIMARY KEY (`vehicle_id`), KEY `img_id` (`img_id`), CONSTRAINT `fk_img_vehicle_id` FOREIGN KEY (`img_id`) REFERENCES `images` (`img_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1; SET FOREIGN_KEY_CHECKS = 1;