Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Question need opinion in my resume db design

    Hi all this is going to be my first db, its a simple teachers' resume db and I hope to do it right, I just need to know if I am going through the right path here designing this db and what would you suggest to improve the design and what mistakes I may have done?

    Click image for larger version. 

Name:	resumedb_dbEERdiagram.png 
Views:	356 
Size:	56.5 KB 
ID:	10003

    Code:
    -- -----------------------------------------------------
    -- Table `mydb`.`subject`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`subject` (
      `idsubject` INT NOT NULL ,
      `subject` VARCHAR(45) NULL ,
      PRIMARY KEY (`idsubject`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`grade`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`grade` (
      `idgrade` INT NOT NULL ,
      `grade` INT NOT NULL ,
      PRIMARY KEY (`idgrade`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`subject_grade`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`subject_grade` (
      `idsubject` INT NOT NULL ,
      `idgrade` INT NOT NULL ,
      `idteacher` INT NULL ,
      PRIMARY KEY (`idsubject`, `idgrade`) ,
      INDEX `fk_subject_grade_subject1` (`idsubject` ASC) ,
      INDEX `fk_subject_grade_grade1` (`idgrade` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`teacher`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`teacher` (
      `idteacher` INT NOT NULL ,
      `name` VARCHAR(45) NULL ,
      `gender` VARCHAR(45) NULL ,
      `birthdate` DATE NULL ,
      `placeofbirth` VARCHAR(45) NULL ,
      `marital_status` VARCHAR(45) NULL ,
      `military_status` VARCHAR(45) NULL ,
      PRIMARY KEY (`idteacher`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`email`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`email` (
      `idteacher` INT NOT NULL ,
      `email` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`email`) ,
      INDEX `fk_email_teacher1` (`idteacher` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`phone`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`phone` (
      `idphone` INT NOT NULL ,
      `idteacher` INT NULL ,
      `phone` VARCHAR(45) NULL ,
      `cellphone` VARCHAR(45) NULL ,
      PRIMARY KEY (`idphone`) ,
      INDEX `fk_phone_teacher1` (`idteacher` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`district`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`district` (
      `iddistrict` INT NOT NULL ,
      `district` VARCHAR(45) NULL ,
      PRIMARY KEY (`iddistrict`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`city`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`city` (
      `idcity` INT NOT NULL ,
      `city` VARCHAR(45) NULL ,
      `iddistrict` INT NULL ,
      PRIMARY KEY (`idcity`) ,
      INDEX `fk_city_district1` (`iddistrict` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`country`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`country` (
      `idcountry` INT NOT NULL ,
      `country` VARCHAR(45) NULL ,
      `idcity` INT NULL ,
      PRIMARY KEY (`idcountry`) ,
      INDEX `fk_country_city1` (`idcity` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`address`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`address` (
      `idaddress` INT NOT NULL ,
      `idteacher` INT NULL ,
      `address` VARCHAR(45) NULL ,
      `idcountry` INT NULL ,
      PRIMARY KEY (`idaddress`) ,
      INDEX `fk_address_teacher1` (`idteacher` ASC) ,
      INDEX `fk_address_country1` (`idcountry` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`education`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`education` (
      `ideducation` INT NOT NULL ,
      `idteacher` INT NULL ,
      `iduniversity` INT NULL ,
      `idinstitute` INT NULL ,
      `graduationyear` YEAR NULL ,
      `highereducationgrade` VARCHAR(45) NULL ,
      `highschoolname` VARCHAR(45) NULL ,
      `highschooldegree` VARCHAR(45) NULL ,
      `highschoolgrade` INT NULL ,
      PRIMARY KEY (`ideducation`) ,
      INDEX `fk_education_teacher1` (`idteacher` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`university`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`university` (
      `iduniversity` INT NOT NULL ,
      `universityname` VARCHAR(45) NULL ,
      `idfaculty` INT NULL ,
      PRIMARY KEY (`iduniversity`) ,
      INDEX `fk_university_education1` (`iduniversity` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`faculty`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`faculty` (
      `idfaculty` INT NOT NULL ,
      `facultyname` VARCHAR(45) NULL ,
      `faculty_major` VARCHAR(45) NULL ,
      PRIMARY KEY (`idfaculty`) ,
      INDEX `fk_faculty_university1` (`idfaculty` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`institute`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`institute` (
      `idinstitute` INT NOT NULL ,
      `institutename` VARCHAR(45) NULL ,
      `institute_major` VARCHAR(45) NULL ,
      PRIMARY KEY (`idinstitute`) ,
      INDEX `fk_institute_education1` (`idinstitute` ASC) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`job`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`job` (
      `idjob` INT NOT NULL ,
      `job_title` VARCHAR(45) NULL ,
      `employer` VARCHAR(45) NULL ,
      `employer_idcountry` INT NULL ,
      `job_from_date` DATE NULL ,
      `job_to_date` DATE NULL ,
      `job_description` VARCHAR(45) NULL ,
      `current_job` TINYINT(1) NULL ,
      `idteacher` INT NULL ,
      PRIMARY KEY (`idjob`) ,
      INDEX `fk_job_teacher1` (`idteacher` ASC) ,
      INDEX `fk_job_country1` (`employer_idcountry` ASC) ,
      INDEX `fk_job_subject_grade1` (`idteacher` ASC) )
    ENGINE = InnoDB;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here are a couple of starting points

    you do ~not~ need "idgrade" to be the PK for the grades table -- use the grade as the PK here, and as the FK in the subject_grade table

    having phone and cellphone side by side in the phones table is wrong

    using an id for the country table is a poor choice for PK when there are several international standards for country codes

    also, it appears that each country can have only one city

    the address table needs work, as it doesn't give a city

    the education and jobs tables need a lot of work


    tip: if you go so far as to create actual tables from your design, it is only a small additional step to populate these tables with a few sample rows of meaningful data (not garbage) -- this will often bring problems with PKs and FKs to light
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •