Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Join Date
    Oct 2010
    Posts
    37

    normalising ER diagram

    Can someone help me normalise my ER diagram please. Also it would be great if people can suggest improvements I can make to the current design.

    Thanks in advance
    Last edited by FullAdder; 11-24-10 at 15:40.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    player table and goal table can't both have an FK to each other

    team table and group table can't both have an FK to each other

    player table is missing an FK to team (can a player ever change teams?)

    match table has two FKs to team but team doesn't have a matching column

    match table has a goal_id? why?

    match table has a group_id? why?

    booking table has no PK

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

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    As this is not specific to MySQL, its more to do with the overall db design this thread has been moved to the concepts

    An earlier related thread may be found at:
    http://www.dbforums.com/mysql/166149...ll-league.html
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2010
    Posts
    37
    player table is missing an FK to team (can a player ever change teams?)

    all players are signed to one team and they can't change over

    match table has two FKs to team but team doesn't have a matching column

    the two FK indicate that two teams are facing each other in a match

    match table has a goal_id? why?
    soon as a player scores a goal, a goal_id would be generated in the match table. but come to think of it now I don't need a GOAL_ID in the match table. the ID would be generated in the goal table soon as the player scores


    I hope this answered your question, but let me known if you have an idea which is better than mine
    Last edited by FullAdder; 11-24-10 at 15:00.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by FullAdder View Post
    I hope this answered your question...
    actually, i had several

    and they were theoretical questions (pun intended)

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

  6. #6
    Join Date
    Oct 2010
    Posts
    37

    glicent said

    Can you indicate which table require normalisation?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by FullAdder View Post
    Can you indicate which table require normalisation?
    only after you fix all the PK/FK issues

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

  8. #8
    Join Date
    Oct 2010
    Posts
    37
    match table has two FKs to team but team doesn't have a matching column

    I don't understand the above question, can you give me more details?
    Last edited by FullAdder; 11-24-10 at 17:36.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by FullAdder View Post
    match table has two FKs to team but team doesn't have a matching column

    I don't understand the above question, can you give me more details?
    let me ask you... which column in the teams table do the fteam_id and steam_id columns reference?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2010
    Posts
    37
    I was think about have the fteam_id & steam_id as attribute. I hope that makes sense

  11. #11
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by FullAdder View Post
    I was think about have the fteam_id & steam_id as attribute. I hope that makes sense
    What column in the team table properly completes this:

    Code:
    create table match (
        ...
        fteam_id int references team (???),
        steam_id int references team (???),
        ...
    )
    Also, you resolved your team-group FK opposite of what it should be.

  12. #12
    Join Date
    Oct 2010
    Posts
    37
    I am totally confused

    I was thinking about manually adding the team in the match table.

    I understand that FTEAM_ID & STEAM_ID has no reference in the team table
    Last edited by FullAdder; 11-24-10 at 16:44.

  13. #13
    Join Date
    Oct 2010
    Posts
    37
    I think what you are trying to say is to resolve the issue I should label the FTEAM_ID & STEAM_ID as a FK

    am I correct?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by FullAdder View Post
    I think what you are trying to say is ...
    no, if i was trying to say something, i would just say it

    i was actually hoping you might try to answer the question i gave you --

    which column in the teams table do the fteam_id and steam_id columns reference?

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

  15. #15
    Join Date
    Oct 2010
    Posts
    37
    they refer to the TEAM_NAME

Posting Permissions

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