MySQL Forums
Forum List  »  MySQL Workbench

Re: Database Modeling
Posted by: Al Lombardo
Date: June 23, 2009 02:14PM

reverse engineer this, which is a rudimentary model of what you asked for.

CREATE SCHEMA IF NOT EXISTS `yourDBNameHere` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `yourDBNameHere`;

CREATE TABLE IF NOT EXISTS `yourDBNameHere`.`manufacturer` (
`manufacturer_id` INT NOT NULL AUTO_INCREMENT ,
`manufacturer_name` VARCHAR(45) NULL ,
PRIMARY KEY (`manufacturer_id`) )
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `yourDBNameHere`.`product` (
`product_id` INT NOT NULL AUTO_INCREMENT ,
`manufacturer_id` INT NOT NULL ,
`product_name` VARCHAR(45) NULL ,
PRIMARY KEY (`product_id`, `manufacturer_id`) ,
INDEX fk_product_manufacturer (`manufacturer_id` ASC) ,
CONSTRAINT `fk_product_manufacturer`
FOREIGN KEY (`manufacturer_id` )
REFERENCES `mydb`.`manufacturer` (`manufacturer_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `yourDBNameHere`.`version` (
`version_id` INT NOT NULL AUTO_INCREMENT ,
`product_id` INT NOT NULL ,
`product_manufacturer_id` INT NOT NULL ,
`version` VARCHAR(45) NULL ,
PRIMARY KEY (`version_id`, `product_id`, `product_manufacturer_id`) ,
INDEX fk_version_product (`product_id` ASC, `product_manufacturer_id` ASC) ,
CONSTRAINT `fk_version_product`
FOREIGN KEY (`product_id` , `product_manufacturer_id` )
REFERENCES `mydb`.`product` (`product_id` , `manufacturer_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `yourDBNameHere`.`employee` (
`employee_id` INT NOT NULL AUTO_INCREMENT ,
`employee_name` VARCHAR(45) NULL ,
PRIMARY KEY (`employee_id`) )
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `yourDBNameHere`.`experience` (
`employee_id` INT NOT NULL ,
`version_id` INT NOT NULL ,
`product_id` INT NOT NULL ,
`product_manufacturer_id` INT NOT NULL ,
`years_experience` INT NULL ,
PRIMARY KEY (`employee_id`, `version_id`, `product_id`, `product_manufacturer_id`) ,
INDEX fk_experience_employee (`employee_id` ASC) ,
INDEX fk_experience_version (`version_id` ASC, `product_id` ASC, `product_manufacturer_id` ASC) ,
CONSTRAINT `fk_experience_employee`
FOREIGN KEY (`employee_id` )
REFERENCES `mydb`.`employee` (`employee_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_experience_version`
FOREIGN KEY (`version_id` , `product_id` , `product_manufacturer_id` )
REFERENCES `mydb`.`version` (`version_id` , `product_id` , `product_manufacturer_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

Options: ReplyQuote


Subject
Views
Written By
Posted
2882
May 22, 2009 10:35AM
Re: Database Modeling
1682
June 23, 2009 02:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.