Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: Opinios on Database ER Schema

    Hello Guys,

    I'm new in database designing and I would like to ask your opinion about the database I've designed. Below, you can see the database schema and the MySQL code given by the MySQL Workbench.

    Do you think that the database can be more optimized?

    http://img842.imageshack.us/img842/7765/db1.png

    Code:
    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`.`users_details`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`users_details` (
      `userID` BIGINT NULL AUTO_INCREMENT ,
      `name` VARCHAR(50) NULL ,
      `last_name` VARCHAR(50) NULL ,
      `address` BIGINT NULL ,
      `country` VARCHAR(20) NULL ,
      `telephone` DECIMAL(10,0)  NULL ,
      `birthdate` DATE NULL ,
      `url` TEXT NULL ,
      `twitter` TEXT NULL ,
      `facebook` TEXT NULL ,
      `linkedin` TEXT NULL ,
      PRIMARY KEY (`userID`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`connection`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`connection` (
      `userID` BIGINT NULL AUTO_INCREMENT ,
      `contestID` BIGINT NULL ,
      PRIMARY KEY (`userID`, `contestID`) );
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`users`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`users` (
      `userID` BIGINT NULL AUTO_INCREMENT ,
      `username` VARCHAR(50) NOT NULL ,
      `password` VARCHAR(20) NOT NULL ,
      `email` VARCHAR(100) NOT NULL ,
      `registration_date` DATETIME NOT NULL ,
      `users_details_userID` BIGINT NOT NULL ,
      `connection_userID` BIGINT NOT NULL ,
      `connection_contestID` BIGINT NOT NULL ,
      PRIMARY KEY (`userID`, `connection_userID`, `connection_contestID`) ,
      CONSTRAINT `fk_users_users_details`
        FOREIGN KEY (`users_details_userID` )
        REFERENCES `mydb`.`users_details` (`userID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_users_connection1`
        FOREIGN KEY (`connection_userID` , `connection_contestID` )
        REFERENCES `mydb`.`connection` (`userID` , `contestID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_general_ci;
    
    CREATE INDEX `fk_users_users_details` ON `mydb`.`users` (`users_details_userID` ASC) ;
    
    CREATE INDEX `fk_users_connection1` ON `mydb`.`users` (`connection_userID` ASC, `connection_contestID` ASC) ;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`summary`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`summary` (
      `contestID` BIGINT NOT NULL AUTO_INCREMENT ,
      `organiser_name` VARCHAR(45) NOT NULL ,
      `prizeID` VARCHAR(45) NULL ,
      `timeline` DATETIME NOT NULL ,
      PRIMARY KEY (`contestID`, `organiser_name`, `prizeID`, `timeline`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`organiser`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`organiser` (
      `organiser_name` VARCHAR(45) NOT NULL ,
      `mobile` DECIMAL(10,0)  NULL ,
      `address` BIGINT NULL ,
      `url` TEXT NOT NULL ,
      `userID` BIGINT NOT NULL ,
      `users_userID` BIGINT NOT NULL ,
      `users_connection_userID` BIGINT NOT NULL ,
      `users_connection_contestID` BIGINT NOT NULL ,
      PRIMARY KEY (`organiser_name`, `userID`) ,
      CONSTRAINT `fk_organiser_users1`
        FOREIGN KEY (`users_userID` , `users_connection_userID` , `users_connection_contestID` )
        REFERENCES `mydb`.`users` (`userID` , `connection_userID` , `connection_contestID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);
    
    CREATE INDEX `fk_organiser_users1` ON `mydb`.`organiser` (`users_userID` ASC, `users_connection_userID` ASC, `users_connection_contestID` ASC) ;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`timeline`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`timeline` (
      `timelineID` BIGINT NULL AUTO_INCREMENT ,
      `timezone` TIME NULL ,
      `start` DATETIME NULL ,
      `end` DATETIME NULL ,
      `entry_type` TEXT NULL ,
      PRIMARY KEY (`timelineID`) );
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`contest`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`contest` (
      `contestID` BIGINT NULL AUTO_INCREMENT ,
      `title` TEXT NOT NULL ,
      `summary` MEDIUMTEXT NOT NULL ,
      `details` LONGTEXT NULL ,
      `terms` TEXT NOT NULL ,
      `countries` VARCHAR(45) NOT NULL ,
      `timeline_timelineID` BIGINT NOT NULL ,
      `connection_userID` BIGINT NOT NULL ,
      `connection_contestID` BIGINT NOT NULL ,
      `summary_contestID` BIGINT NOT NULL ,
      `summary_organiser_name` VARCHAR(45) NOT NULL ,
      `summary_prizeID` VARCHAR(45) NOT NULL ,
      `summary_timeline` DATETIME NOT NULL ,
      PRIMARY KEY (`contestID`, `timeline_timelineID`, `summary_contestID`, `summary_organiser_name`, `summary_prizeID`, `summary_timeline`) ,
      CONSTRAINT `fk_contest_timeline1`
        FOREIGN KEY (`timeline_timelineID` )
        REFERENCES `mydb`.`timeline` (`timelineID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_contest_connection1`
        FOREIGN KEY (`connection_userID` , `connection_contestID` )
        REFERENCES `mydb`.`connection` (`userID` , `contestID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_contest_summary1`
        FOREIGN KEY (`summary_contestID` , `summary_organiser_name` , `summary_prizeID` , `summary_timeline` )
        REFERENCES `mydb`.`summary` (`contestID` , `organiser_name` , `prizeID` , `timeline` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);
    
    CREATE INDEX `fk_contest_timeline1` ON `mydb`.`contest` (`timeline_timelineID` ASC) ;
    
    CREATE INDEX `fk_contest_connection1` ON `mydb`.`contest` (`connection_userID` ASC, `connection_contestID` ASC) ;
    
    CREATE INDEX `fk_contest_summary1` ON `mydb`.`contest` (`summary_contestID` ASC, `summary_organiser_name` ASC, `summary_prizeID` ASC, `summary_timeline` ASC) ;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`sponsor`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`sponsor` (
      `sponsor_name` VARCHAR(50) NOT NULL ,
      `moderation` BINARY NOT NULL ,
      `facebook` BINARY NOT NULL ,
      `prizeID` BIGINT NOT NULL ,
      PRIMARY KEY (`sponsor_name`, `prizeID`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`prize`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`prize` (
      `prizeID` BIGINT NULL AUTO_INCREMENT ,
      `prize_name` VARCHAR(50) NOT NULL ,
      `prize_value` DECIMAL(10,0)  NOT NULL ,
      `prize_details` LONGTEXT NULL ,
      `prize_image` TEXT NOT NULL ,
      `contest_contestID` BIGINT NOT NULL ,
      `contest_timeline_timelineID` BIGINT NOT NULL ,
      `sponsor_sponsor_name` VARCHAR(50) NOT NULL ,
      `sponsor_prizeID` BIGINT NOT NULL ,
      PRIMARY KEY (`prizeID`, `contest_contestID`, `contest_timeline_timelineID`) ,
      CONSTRAINT `fk_prize_contest1`
        FOREIGN KEY (`contest_contestID` , `contest_timeline_timelineID` )
        REFERENCES `mydb`.`contest` (`contestID` , `timeline_timelineID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_prize_sponsor1`
        FOREIGN KEY (`sponsor_sponsor_name` , `sponsor_prizeID` )
        REFERENCES `mydb`.`sponsor` (`sponsor_name` , `prizeID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);
    
    CREATE INDEX `fk_prize_contest1` ON `mydb`.`prize` (`contest_contestID` ASC, `contest_timeline_timelineID` ASC) ;
    
    CREATE INDEX `fk_prize_sponsor1` ON `mydb`.`prize` (`sponsor_sponsor_name` ASC, `sponsor_prizeID` ASC) ;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`contest_has_organiser`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`contest_has_organiser` (
      `contest_contestID` BIGINT NOT NULL ,
      `contest_timeline_timelineID` BIGINT NOT NULL ,
      `organiser_organiser_name` VARCHAR(45) NOT NULL ,
      PRIMARY KEY (`contest_contestID`, `contest_timeline_timelineID`, `organiser_organiser_name`) ,
      CONSTRAINT `fk_contest_has_organiser_contest1`
        FOREIGN KEY (`contest_contestID` , `contest_timeline_timelineID` )
        REFERENCES `mydb`.`contest` (`contestID` , `timeline_timelineID` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_contest_has_organiser_organiser1`
        FOREIGN KEY (`organiser_organiser_name` )
        REFERENCES `mydb`.`organiser` (`organiser_name` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION);
    
    CREATE INDEX `fk_contest_has_organiser_organiser1` ON `mydb`.`contest_has_organiser` (`organiser_organiser_name` ASC) ;
    
    
    
    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i will do just one table, users_details, the others you will have to do yourself based on these comments

    `userID` BIGINT NULL ,

    if you want PRIMARY KEY AUTO_INCREMENT, you have to say NOT NULL

    also, BIGINT is way too big, use INTEGER

    `address` BIGINT NULL ,

    BIGINT is wrong, use VARCHAR(255)

    `telephone` DECIMAL(10,0) NULL ,

    DECIMAL is wrong, use VARCHAR(20)

    `url` TEXT NULL ,
    `twitter` TEXT NULL ,
    `facebook` TEXT NULL ,
    `linkedin` TEXT NULL ,


    TEXT is wrong for all of these, use VARCHAR(255) instead

    finally, this should not be a separate table from the users table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2010
    Posts
    2
    r937 thank you for your reply and your help! I followed your instructions and here are the results:

    http://img828.imageshack.us/img828/4838/dbnew.png

    Better now?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you repeat the same information in the same table?

    I don't think you have grasped the concept of defining keys using workbench
    theres two main types
    identifying and non identifying
    identfying means the foreign key (the bit that refers to the other table) is also this tables primary key
    non-identfying means the foreign key (the bit that refers to the other table) does not comprise this tables primary key

    an identifying key will usually be found in an intersection table, a non identufying key will be more common and is used in foreign keys.

    Im alwasy suspicious of one to one relationships (not that they don't exist, but that in my experience they are relatively rare in the real world.

    I don't understand your concept of timeline, unless you say run the same contest multiple times, but in that case you woudl want t atable for contesttypes and then a table for specific contests (which would inlcude and date/time parameters as those are attributes of a specific contest.

    theres a heck oif a lot of elements comprising the primary key in contest, either your key definition is hooky, or you proabbly need to consider using an autogenerated ID for the contest table.

    generally speaking I don't think its a smarrt call to make a name a Primary key, unless you are confident that no one will ever change names.. its not good in perrformance terms to change the primary key especially if it has lots and lots of references elsewhere.

    the attribute countries in contest dosn't feel right, that sounds like a candidate for a countries table with an intersection table o associate sepcific countries with a specific contest
    presumably if that is your intention then you will need to store the user's country as well with a FK to countries

    what is the purpose of summary, is that not just pulling 4 columns from contest? if so then its an irrelevant table it can be drawn from the contest table whenever required.

    contesthasorganiser is an odd table
    there are 2 PK's on organiser and 3 in contest has organiser it looks to me as if you have miss defined columns comprising the key.

    in table organiser I doubt you need a composite pk
    either the organiser has a username /userid or deosn't personally I wouldn't use organisername as the PK, I'd use an organiserID
    by all means have a userid in that tabel but not part of the PK. presumably you are saying a contest MUST have an organiser but may or may not also be a user.

    I suspect you may benefit from boning up on the principles behind normalisation
    Fundamentals of Relational Database Design -- r937.com
    or
    The Relational Data Model, Normalisation and effective Database Design
    seem to be the most referred to on this site, but there are other texts out there
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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