Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Database Normalisation with delete command

mysql php database query

This topic has been archived. This means that you cannot reply to this topic.
No replies to this topic

#1 seristee

seristee

    CC Regular

  • Member
  • PipPipPip
  • 30 posts

Posted 19 April 2015 - 04:10 PM

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;