www.webdeveloper.com
Results 1 to 6 of 6

Thread: Is this correct database schema?

Hybrid View

  1. #1
    Join Date
    Jun 2013
    Posts
    33

    Is this correct database schema?

    I am using phpmyadmin.
    I want to create a website (like fantasy football) in which users will create account and select the players from the pool to create their team of 10 players.
    Based on performance each player will earn points. and then user will also earn points from 10 players(his team).

    so i searched on internet and came out with db schema.

    1)User (u_id, details)

    2)Players(P_id,details,points)

    3)user_team(t_id, u_id(FK), p_id(FK), team_name, points)

    so now problem is, there must be only 10 rows in the user_team table for a particular user, correct?

    I am confused about it? Is this the correct schema? or am i missing something..something more accurate?
    Pls help.
    Thanks.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,387
    I'd probably enforce the team size restriction in the application code, for two reasons: (1) you can then easily change that restriction by changing a single configuration parameter in your code, and (2) I can't think of any way I'd want to do it via the DB.

    PS: I have some vague thoughts about the overall schema, but no time to explore it at the moment. Maybe I'll get back to you later today or this weekend?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jun 2013
    Posts
    33
    thanks for reply.
    Yeah, i heard about restriction. But i dont know what to say now. i am still confused. Hope you will suggest me something. thanks again.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,387
    Okay, so this would be my starting point (subject to change without notice if I think more about it), generated with MySQL Workbench:

    league_erd.png

    Code:
    -- -----------------------------------------------------
    -- Table `mydb`.`user`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`user` (
      `user_id` INT NOT NULL ,
      PRIMARY KEY (`user_id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`team`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`team` (
      `team_id` INT NOT NULL ,
      `user_id` INT NULL ,
      PRIMARY KEY (`team_id`) ,
      INDEX `user_id_idx` (`user_id` ASC) ,
      CONSTRAINT `user_id`
        FOREIGN KEY (`user_id` )
        REFERENCES `mydb`.`user` (`user_id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`player`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`player` (
      `player_id` INT NOT NULL ,
      PRIMARY KEY (`player_id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`player_to_team`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`player_to_team` (
      `player_id` INT NOT NULL ,
      `team_id` INT NOT NULL ,
      INDEX `player_fk_idx` (`player_id` ASC) ,
      INDEX `team_fk_idx` (`team_id` ASC) ,
      PRIMARY KEY (`player_id`) ,
      UNIQUE INDEX `player_team` (`player_id` ASC, `team_id` ASC) ,
      CONSTRAINT `player_fk`
        FOREIGN KEY (`player_id` )
        REFERENCES `mydb`.`player` (`player_id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `team_fk`
        FOREIGN KEY (`team_id` )
        REFERENCES `mydb`.`team` (`team_id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,387
    As far as enforcing the 10 player per team limit, the two options I'd look at are to either simply enforce it in the application code (select count of players by team ID before allowing an insert to be done), or you could create a stored procedure for the insert that would return false if the team already had 10 players, else go ahead and do the insert. The latter would be more efficient and there fore faster, but the former may well be plenty fast enough if you index everything correctly and aren't getting Facebook type traffic to your site.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #6
    Join Date
    Jun 2013
    Posts
    33
    Thanks, NogDog.
    I think schema you provided is a simpler version.
    In my earlier schema, user_team table might have more repeated attribute values for like rank, team_points and etc.
    In this schema, 'team' table is very simple to manage (Every user_id will have only one team_id).

    But in the player_to_team table, team_id still have to be repeated 10 time to include player.
    But i dont think there's other option,is there?

    You also mentioned indexes. To be honest, i dont really know anything about indexes. But i will find a source to learn what it is
    and how it will help me here. Thanks again.
    and let me know if you find more efficient solution.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles