Results 1 to 15 of 15

Thread: Football league

  1. #1
    Join Date
    Oct 2010
    Posts
    37

    Unanswered: Football league

    Hi

    I am designing a mysql database.

    I am having trouble with the relationships and the best way to implement the database.

    Also I want the database to show the player with the highest goals.

    Any help would be nice
    Last edited by FullAdder; 10-25-10 at 13:01.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you got so far
    what entities do you perceive
    what relationships do you see
    what other outcomes do you need other than
    Also I want the database to show the player with the highest goals.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2010
    Posts
    37
    Am I correct with the relationships?


    Also I want the database to show the player with the highest goals.
    above it's the only outcome I need.
    Last edited by FullAdder; 10-25-10 at 12:45.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that will probably work for the group stage, but not for later on in a tournament such as the World Cup.
    don't see how or where you intend storing the results
    don't see how or where you intend storing who was in the team, bearing in minf that each team can take many players, not all of whom play.
    don't see who is playing in a specific game
    don't see how you identify who scored, and therefore no way to identify the one clear requirement
    depending on your assignment there may well be more than one stadium used in any one tournament, although I'm not clear as to why you need to associate stadiums at he tournament levelk
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2010
    Posts
    37
    This the ERD I made couple of weeks ago

    After all your question I have figured out I need to add more attributed.

    Which tables do I need more attribute
    Last edited by FullAdder; 10-25-10 at 15:54.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its your coursework
    after if you have figured out you need more attributes then presumably you have also figured out where those attributes should go
    think back to the requirment
    does your model support that requirement
    if you think it does then I'd suggest you create those entities put some test data in and see if the model adequately supports the requirement
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2010
    Posts
    37
    I don't known how to modify the table to achieve the following:

    storing players in team
    and Identifying who scored the goal

    I can't seen to get my head around it.


    Its not homework. but problems at the end of the chapter in Database second edition.

  8. #8
    Join Date
    Oct 2010
    Posts
    37
    In the competition table I will include the group stages , Round of 16, Quarter-finals, Semi-finals and Final.

    The group table will hold all the teams in each groups.


    I hope this helps

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    think about what you are asking
    storing players in a team...
    to me that would suggest 3 tables
    a table of players
    a table for teams
    a table associating players with a team, a so called intersection table

    as goals in themselves are a separate event / entity, they require a separate table
    so you could identify who scored the goal in that table, presumably you would also want other stuff such as the fixture ID, and possibly also the time the goal occurred
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2010
    Posts
    37
    Hi

    After keeping mind what you have told me I redesign the erd.

    Please check and let me known what you think
    Last edited by FullAdder; 10-27-10 at 09:08.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your team entity is odd, seems to have a lot of derived data (such as number of..., goal... & points... thats generally frowned on, but can be OK if there are performance reasons to store it that way

    examine your own ERD
    if you are modelling something like the world cup then the group isn't correct, as its my understanding that after the initial group matches (the mini league format) successful teams then proceed to a knockout format so group is no longer relevant or used.
    your goal entity records who scored in a competition, but it doenst' tell you in which match

    the entity for competition is either misnamed or incorrect
    the competition is, say the world cup, within the world cup competition you have many fixtures.

    one thing you can do is work through your entities, put in some tests data and see if it supports your requirements. you don't need to do this in a db, writing down the data with pen and paper is often good enough especially with so few tables


    dont' understand the itme 'result'
    you could express that as team A score & team b score

    most db's use a composite value of date and time... depends on the db that should be datatype date or datetime
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Oct 2010
    Posts
    37

    owspent metnls

    Quote Originally Posted by healdem View Post
    your team entity is odd, seems to have a lot of derived data (such as number of..., goal... & points... thats generally frowned on, but can be OK if there are performance reasons to store it that way

    examine your own ERD
    if you are modelling something like the world cup then the group isn't correct, as its my understanding that after the initial group matches (the mini league format) successful teams then proceed to a knockout format so group is no longer relevant or used.

    you saying I should add another table for the knockout round?

    your goal entity records who scored in a competition, but it doenst' tell you in which match
    The competitionID is what keeps track of the number of matches

    the entity for competition is either misnamed or incorrect

    I don't understand
    the competition is, say the world cup, within the world cup competition you have many fixtures.

    one thing you can do is work through your entities, put in some tests data and see if it supports your requirements. you don't need to do this in a db, writing down the data with pen and paper is often good enough especially with so few tables


    dont' understand the itme 'result'
    you could express that as team A score & team b score

    Thanks I was thinking about that

    most db's use a composite value of date and time... depends on the db that should be datatype date or datetime

    thanks for you help,

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I have my own ideas of how a football model could be developed, but thats not pertinent here. what is , is how you develop your model.

    I think you will learn more from applying some test data with your current design and understand if it will meet your design requirements, and if not where the problems are.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Oct 2010
    Posts
    37
    OK ok ok o k
    Last edited by FullAdder; 11-01-10 at 15:38.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no
    I think you will benefit more from the exercise if you work through the design you have

    add some test data (if you prefer on paper) and then see if your current design is able to support the specified outcomes.
    this process is about you developing your model. if you keep getting someone to validate your attempts the amount you will learn is reduced. even worse you will get their idea of what is required, which may well not be appropriate for your requiremements

    most people in the DB community can usually 'see' the tables without having to go through this process, unless its a large system often with many designers all needing to understand the overall model.
    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
  •