design EER diagram using mysql Workbench

[mfaisalwarraich]

Hi everybody,

I am trying to make a EER diagram using mysql workbench to start with a database.

This is a vehicle parts database dat im trying to design. i have following five tables:

1. Make
2. Model
3. Year
4. Body
5. Trim

Now creating them with or without workbench is easy but im trying to make some business logic in it to avoid repetitions of data again and again by way of creating relationships and foreign keys.

Table Make have fields:
1. Id
2. name

Table Model have fields:
1. Id
2. name
3. make_id (foreign key)

Now from above i created a one to many relationship to between model and make table because a make have many models (e.g. honda may have civic and accord etc.) but a model will have only one make. That's y workbench passed the make id as foreign key into the table.

Table year have fields:
1. Id
2.year

Now dat each model may have many years and a year may have many models tells me dat i have to create a many to many relationship here. So i created many to many relationship between year table and model table. By doing that workbench created another table called year_has_model which has following fields:

1. model_id
2. model_make_id
3. year_id

Now i know that each model may have various years and a year may have various models and i can insert the values into it without any problem.

As i go further i have 2 tables left i.e. body and trim. Now a model in a year may have different bodies and a body in a year may have different trim levels.

This is really making me mad. im not getting how should i relate it to my tables. Wat is the best approach to solve this problem as speed is the most important thing and i dont wana repeat values.

If i create many to many relationship between body and year then body_has_year will not give me any info about the model and make and if i create many to many relationship between trim and body then wat about the years and how do i relate to it?

I am not able to solve this type of things. Please help me wat should i do?


Here is the generated sql code for quick reference please:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

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

-- -----------------------------------------------------
-- Table `mydb`.`make`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`make` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`make` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(75) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`model`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`model` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`model` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(75) NULL ,
  `make_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`, `make_id`) ,
  CONSTRAINT `fk_model_make`
    FOREIGN KEY (`make_id` )
    REFERENCES `mydb`.`make` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE INDEX `fk_model_make` ON `mydb`.`model` (`make_id` ASC) ;


-- -----------------------------------------------------
-- Table `mydb`.`year`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`year` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`year` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `year` VARCHAR(10) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`body`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`body` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`body` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(75) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`trim`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`trim` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`trim` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(75) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`model_has_year`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`model_has_year` ;

CREATE  TABLE IF NOT EXISTS `mydb`.`model_has_year` (
  `model_id` INT UNSIGNED NOT NULL ,
  `model_make_id` INT UNSIGNED NOT NULL ,
  `year_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`model_id`, `model_make_id`, `year_id`) ,
  CONSTRAINT `fk_model_has_year_model1`
    FOREIGN KEY (`model_id` , `model_make_id` )
    REFERENCES `mydb`.`model` (`id` , `make_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_model_has_year_year1`
    FOREIGN KEY (`year_id` )
    REFERENCES `mydb`.`year` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_model_has_year_model1` ON `mydb`.`model_has_year` (`model_id` ASC, `model_make_id` ASC) ;

CREATE INDEX `fk_model_has_year_year1` ON `mydb`.`model_has_year` (`year_id` ASC) ;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

thanking you.

regards

[TheServant]

I think a few of your tables are redundant, especially your year table. I cannot see how that is useful? Anyway, I think that if a model contains a unknown number of body/trim years, consider storing a string which can be passed as an array and unpacked later. Or even serialization of an array. It's a long post, and it's getting late, so don't fully understand what you want, but you might be able to adapt what I have said to help your problem.

[mfaisalwarraich]

wat i want is dat first i have to make a complete vehicle based on the selection. something like this:

1. Combobox for Makes: All makes will be populated in that combobox.
2. Combobox for Models: This will be populated on the basis of make selected, like if make is Honda then the combobox will have all models for honda. For example Accord, Civic, Civic Hybrid IMA etc.
3. Combobox for Years: Select a model will make this combobox filled with the possible year of production available against this model. Like Honda-Civic may have production year in 2009, 2010, 2007 .... infact each year a model have a production.
4. Combobox for body: Now selecting a year will make this combobox populated, like for Honda-Civic-2009 body style is Hatchback and for Honda-Civic-2000 body style is Coupe, Hatchback, Saloon, Estate (4 values in combobox).
5. Trim Level Combobox: Now on selecting a body type trim level will be populated, like for Honda-Civic-2009-Hatchback the trim level available are 1.8i VTEC, 1.8i, 1.8VTC, 1.8VTEC

So, i think now u should get it wat im asking for. I have tried to make an ERD for this approached but really not getting it done.

Year and Model table will have many to many relationship because a year have many models and a model have many years whereas make and model will have one to many relationship because a make have many models and a model have only one make.

But how do i relate other tables. or if someone has better solution without relationship please tell me. thank you.

[TheServant]

How about one table with: Makes, Models, Years, Body and Trim.
It will be big, and means that you will need to investigate the MySQL DISTINCT clause, but it will be better I think.

You can then use several where clauses like:

SELECT DISTINCT body FROM big_table WHERE make='Toyota' AND model='Corolla' AND year='2006'

And then:

SELECT DISTINCT trim FROM big_table WHERE make='Toyota' AND model='Corolla' AND year='2006' AND body='Hatchback'

[mfaisalwarraich]

big table will follow the database normalization? and it will have foreign key constraints and relationships?

like i have already explained make and model have one to many relationship. Model and year table has many to many relationship. Model_has_year table is a junction/joint table. a body can't exist without a model, a year and a make similarly a trim level can't exist without a body a year a model and a make.

so it has some business logic in it. but this is important dat a model have various years and against each year a model have varoius bodies and against each body a model has various trim levels. got it?
big table will messup all things. dats y i wana seperate everything by way of database normalization. which im trying to do.

please guide me thanx

[zorgi]

I like this problem :) So I'll have a go at it. First of all I noticed i noticed your year_has_model has one to many ids. You have this:

1. model_id
2. model_make_id
3. year_id

when you should have

1. model_id
2. year_id

make_id is already in your model table.

This probably happened because you created identifying 1:n relationship between make and model tables. You should use nonidentifying 1:n relationship here. Than workbench will not create make_id in your year_has_model table. This is why i like to break my n:m relationships myself and use workbanch mainly to visualise things.

Anyhow body problem.

You said: "If i create many to many relationship between body and year then body_has_year will not give me any info about the model"

So what will give you info about model, year and body? Did you try linking year_has_model and body n:n?

Hope this helps

[mfaisalwarraich]

ok i did it as u siad non identifying relationship between make and model so year_has_model now only have two ids.

now i created year_has_model and body n:m relationship this result in a new table year_has_model_ has_body. then i made n:m between year_has_model_ has_body and trim which result another table. but dont know if its correct or not. please tell me. cuz dat ids will be repeated again.

[zorgi]

Is this for school projec?
What do you mean by: "cuz dat ids will be repeated again."

[mfaisalwarraich]

its not a school project.

i mean if i create a n:m relationship between year_has_model and body it will create another table called year_has_model_ has_body including all ids from year_has_model and id from body table. then trim level left. and as i explained a body may have various trim levels too so i have to create another n:m relationship between year_has_model_ has_body and trim. by doing this another table will be created year_has_model_ has_body_has_tr im. so i think ids will be repeated in dat table again. dats wat im saying. i think now u get it.

[zorgi]

its not a school project.

i mean if i create a n:m relationship between year_has_model and body it will create another table called year_has_model_ has_body including all ids from year_has_model and id from body table. then trim level left. and as i explained a body may have various trim levels too so i have to create another n:m relationship between year_has_model_ has_body and trim. by doing this another table will be created year_has_model_ has_body_has_tr im. so i think ids will be repeated in dat table again. dats wat im saying. i think now u get it.

How you gonna reference records in the table if not by using id-s. You are fine as long as you do not have data repetition. Personally I would not just accept naming convention that workbench offers cos in case like this is not humanly readable. Workbench is great tool but letting it do everything for you is bit like letting dreamweaver write code for you.

[mfaisalwarraich]

u mean i should create foreign keys and relationships using phpmyadmin right? well i know this is easy too. but once if u develop a ERD or EER diagram then its easy to set it up. i just want to develop a business logic / EER diagram so dat i can start building a database with my own table names and field names. now i think i should pass id of trim into the body_has_year_h as model as foreign key and it will be working fine. am i right?

[zorgi]

body in a year may have different trim levels

If you do what you just said than for one (body_id, year_id) combination you have one trim_id and that is in disagreement with bolded statement above.

[mfaisalwarraich]

yeah

body in a year may have different trim levels against each model

so wat you suggest wat should i do then? wat will be the solution?