#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    Database design help!!


    Hey I am currently trying to create a web application to keep track of table tennis games for myself and my workmates. Im trying to design the DB. Im 100% sure its not right. Im just not sure where. What my application does is a user logs in and registers for a match. Im thinking a different table to store this so when there is a pair then move them into the hasmatch? Also the user will be able to submit the results of the match but im not sure how to do this. I need to keep track of all the results so a seperate league table with games played lost won etc? Any help is appreciated. here is the script generated from forward engineering my design.
    [MYSQL]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,ALLOW_INVALID_DATES'; DROP SCHEMA IF EXISTS `mydb` ; CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `mydb` ; -- ----------------------------------------------------- -- Table `mydb`.`tb_users` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_users` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_users` ( `UID` INT NOT NULL AUTO_INCREMENT , `UNAME` VARCHAR(64) NOT NULL , PRIMARY KEY (`UID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`tb_cup` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_cup` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_cup` ( `CUPID` INT NOT NULL AUTO_INCREMENT , `CNAME` VARCHAR(45) NOT NULL , PRIMARY KEY (`CUPID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`tb_league` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_league` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_league` ( `LEAGUEID` INT NOT NULL AUTO_INCREMENT , `LNAME` VARCHAR(45) NOT NULL , PRIMARY KEY (`LEAGUEID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`tb_results` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_results` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_results` ( `RESULTSID` INT NOT NULL AUTO_INCREMENT , `RESULTTYPE` VARCHAR(1) NOT NULL , PRIMARY KEY (`RESULTSID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`tb_matches` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_matches` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_matches` ( `MID` INT NOT NULL AUTO_INCREMENT , `DATE` DATETIME NULL , `tb_cup_CUPID` INT NOT NULL , `tb_league_LEAGUEID` INT NOT NULL , `tb_results_RESULTSID` INT NOT NULL , PRIMARY KEY (`MID`) , INDEX `fk_tb_matches_tb_cup1_idx` (`tb_cup_CUPID` ASC) , INDEX `fk_tb_matches_tb_league1_idx` (`tb_league_LEAGUEID` ASC) , INDEX `fk_tb_matches_tb_results1_idx` (`tb_results_RESULTSID` ASC) , CONSTRAINT `fk_tb_matches_tb_cup1` FOREIGN KEY (`tb_cup_CUPID` ) REFERENCES `mydb`.`tb_cup` (`CUPID` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_tb_matches_tb_league1` FOREIGN KEY (`tb_league_LEAGUEID` ) REFERENCES `mydb`.`tb_league` (`LEAGUEID` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_tb_matches_tb_results1` FOREIGN KEY (`tb_results_RESULTSID` ) REFERENCES `mydb`.`tb_results` (`RESULTSID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`tb_groups` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_groups` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_groups` ( `GID` INT NOT NULL AUTO_INCREMENT , `GNAME` VARCHAR(45) NOT NULL , PRIMARY KEY (`GID`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`tb_users_has_tb_groups` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_users_has_tb_groups` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_users_has_tb_groups` ( `tb_users_UID` INT NOT NULL , `tb_groups_GID` INT NOT NULL , PRIMARY KEY (`tb_users_UID`, `tb_groups_GID`) , INDEX `fk_tb_users_has_tb_groups_tb_groups1_idx` (`tb_groups_GID` ASC) , INDEX `fk_tb_users_has_tb_groups_tb_users1_idx` (`tb_users_UID` ASC) , CONSTRAINT `fk_tb_users_has_tb_groups_tb_users1` FOREIGN KEY (`tb_users_UID` ) REFERENCES `mydb`.`tb_users` (`UID` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_tb_users_has_tb_groups_tb_groups1` FOREIGN KEY (`tb_groups_GID` ) REFERENCES `mydb`.`tb_groups` (`GID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `mydb`.`tb_users_has_tb_matches` -- ----------------------------------------------------- DROP TABLE IF EXISTS `mydb`.`tb_users_has_tb_matches` ; CREATE TABLE IF NOT EXISTS `mydb`.`tb_users_has_tb_matches` ( `tb_users_UID` INT NOT NULL , `tb_matches_MID` INT NOT NULL , PRIMARY KEY (`tb_users_UID`, `tb_matches_MID`) , INDEX `fk_tb_users_has_tb_matches_tb_matches1_idx` (`tb_matches_MID` ASC) , INDEX `fk_tb_users_has_tb_matches_tb_users1_idx` (`tb_users_UID` ASC) , CONSTRAINT `fk_tb_users_has_tb_matches_tb_users1` FOREIGN KEY (`tb_users_UID` ) REFERENCES `mydb`.`tb_users` (`UID` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_tb_users_has_tb_matches_tb_matches1` FOREIGN KEY (`tb_matches_MID` ) REFERENCES `mydb`.`tb_matches` (`MID` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;[/MYSQL]
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    Oh crap..Apologies. I didnt think it would look this crazy!!
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by tidyjohn
    Oh crap..Apologies. I didnt think it would look this crazy!!
    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
      , ALLOW_INVALID_DATES';
      
    DROP SCHEMA IF EXISTS mydb ;
    CREATE SCHEMA IF NOT EXISTS mydb 
      DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
    USE mydb ;
    
    DROP TABLE IF EXISTS mydb.tb_users ;
    CREATE TABLE IF NOT EXISTS mydb.tb_users 
    ( UID INT NOT NULL AUTO_INCREMENT 
    , UNAME VARCHAR(64) NOT NULL 
    , PRIMARY KEY (UID) 
    ) ENGINE = InnoDB;
    
    DROP TABLE IF EXISTS mydb.tb_cup ;
    CREATE TABLE IF NOT EXISTS mydb.tb_cup
    ( CUPID INT NOT NULL AUTO_INCREMENT 
    , CNAME VARCHAR(45) NOT NULL 
    , PRIMARY KEY (CUPID) 
    ) ENGINE = InnoDB;
    
    DROP TABLE IF EXISTS mydb.tb_league ;
    CREATE TABLE IF NOT EXISTS mydb.tb_league
    ( LEAGUEID INT NOT NULL AUTO_INCREMENT 
    , LNAME VARCHAR(45) NOT NULL 
    , PRIMARY KEY (LEAGUEID) 
    ) ENGINE = InnoDB;
    
    DROP TABLE IF EXISTS mydb.tb_results ;
    CREATE TABLE IF NOT EXISTS mydb.tb_results
    ( RESULTSID INT NOT NULL AUTO_INCREMENT 
    , RESULTTYPE VARCHAR(1) NOT NULL 
    , PRIMARY KEY (RESULTSID) 
    ) ENGINE = InnoDB;
    
    DROP TABLE IF EXISTS mydb.tb_matches ;
    CREATE TABLE IF NOT EXISTS mydb.tb_matches
    ( MID INT NOT NULL AUTO_INCREMENT 
    , DATE DATETIME NULL 
    , tb_cup_CUPID INT NOT NULL 
    , tb_league_LEAGUEID INT NOT NULL 
    , tb_results_RESULTSID INT NOT NULL 
    , PRIMARY KEY (MID) 
    , INDEX fk_tb_matches_tb_cup1_idx (tb_cup_CUPID ASC) 
    , INDEX fk_tb_matches_tb_league1_idx (tb_league_LEAGUEID ASC) 
    , INDEX fk_tb_matches_tb_results1_idx (tb_results_RESULTSID ASC) 
    , CONSTRAINT fk_tb_matches_tb_cup1 
      FOREIGN KEY (tb_cup_CUPID) REFERENCES mydb.tb_cup (CUPID) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
    , CONSTRAINT fk_tb_matches_tb_league1 
      FOREIGN KEY (tb_league_LEAGUEID) REFERENCES mydb.tb_league (LEAGUEID) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
    , CONSTRAINT fk_tb_matches_tb_results1 
      FOREIGN KEY (tb_results_RESULTSID) REFERENCES mydb.tb_results (RESULTSID) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE = InnoDB;
    
    DROP TABLE IF EXISTS mydb.tb_groups ;
    CREATE TABLE IF NOT EXISTS mydb.tb_groups
    ( GID INT NOT NULL AUTO_INCREMENT 
    , GNAME VARCHAR(45) NOT NULL 
    , PRIMARY KEY (GID) 
    ) ENGINE = InnoDB;
    
    DROP TABLE IF EXISTS mydb.tb_users_has_tb_groups ;
    CREATE TABLE IF NOT EXISTS mydb.tb_users_has_tb_groups
    ( tb_users_UID INT NOT NULL 
    , tb_groups_GID INT NOT NULL 
    , PRIMARY KEY (tb_users_UID, tb_groups_GID) 
    , INDEX fk_tb_users_has_tb_groups_tb_groups1_idx (tb_groups_GID ASC) 
    , INDEX fk_tb_users_has_tb_groups_tb_users1_idx (tb_users_UID ASC) 
    , CONSTRAINT fk_tb_users_has_tb_groups_tb_users1 
      FOREIGN KEY (tb_users_UID) REFERENCES mydb.tb_users (UID) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
    , CONSTRAINT fk_tb_users_has_tb_groups_tb_groups1 
      FOREIGN KEY (tb_groups_GID) REFERENCES mydb.tb_groups (GID) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE = InnoDB;
    
    DROP TABLE IF EXISTS mydb.tb_users_has_tb_matches ;
    CREATE TABLE IF NOT EXISTS mydb.tb_users_has_tb_matches
    ( tb_users_UID INT NOT NULL 
    , tb_matches_MID INT NOT NULL 
    , PRIMARY KEY (tb_users_UID, tb_matches_MID) 
    , INDEX fk_tb_users_has_tb_matches_tb_matches1_idx (tb_matches_MID ASC) 
    , INDEX fk_tb_users_has_tb_matches_tb_users1_idx (tb_users_UID ASC) 
    , CONSTRAINT fk_tb_users_has_tb_matches_tb_users1 
      FOREIGN KEY (tb_users_UID) REFERENCES mydb.tb_users (UID) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
    , CONSTRAINT fk_tb_users_has_tb_matches_tb_matches1 
      FOREIGN KEY (tb_matches_MID) REFERENCES mydb.tb_matches (MID) 
      ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE = InnoDB;
    
    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
    FTFY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    Cheers r937. Looks tidy. Any suggestions on the design?
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by tidyjohn
    Any suggestions on the design?
    nah, sorry, man... TL;DR

    suggestion: populate your tables with multiple rows of representative data, and if you have any problems doing it, this will indicate the design flaws (if any)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo