Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Apr 2008
    Posts
    17

    Exclamation Help with producing a league database

    Hi I'm currently trying to produce the database schema for an Inline hockey league. but am having serious trouble understanding how to link them together, which should be primary and foreign keys and making sure it is in third normal form?

    I have read many other threads about league databases, but they're not really helping with what i need.

    I plan to eventually design this using MySQL.


    the tables I think I need are:

    Players
    Player Id Primary Key
    Firstname
    Surname
    Address1
    Address2
    Address3
    Postcode
    Telephone number
    Shirt number
    Photo
    Team Id.

    Team
    Team Id Primary Key
    Team name
    League Id
    Age Group Id
    Area of team
    Web Address
    Date Formed
    Manager FName
    Manager SName

    League stats
    League Id Primary Key
    Team Id
    Game Id
    Season
    Games Played
    Games Won
    Games Lost
    Games Drawn
    Goals For
    Goals Against
    Goal Diff
    Points

    Fixtures
    Game Id Primary Key
    Team Id
    League Id
    Date
    Age Group Id
    Time
    Home Team
    Away Team
    HomeT Result
    AwayT Result
    Location of game

    League
    League Id Primary Key
    League Name

    AgeGroup
    Age Group Id Primary Key
    Age Group Title

    If anyone can help would really appreciate it, or if you could point me to somewhere or the web where i can find an example of a similar database example.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That looks pretty good to me. Age Group ID in fixtures? You sure?

    The only other thing is, depending on the functionality you want, is some of those attribute might be temporal e.g. player - team relationship, player - shirt number relationship, team - manager relationship.

    Also - is this homework or real world? In one of these senarios I would not use league stats.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2003
    Location
    Toronto, Ontario, Canada
    Posts
    203
    If a Player changes Teams, will you be storing the original team they belonged to. What about a player's individual stats (as opposed to team stats)?
    When it rains, it pours.

  4. #4
    Join Date
    Apr 2008
    Posts
    17
    This is a uni project but i plan to set it up on the web for my hockey league as well.

    Setting it up for player stats would be good, but was trying to keep it simple until i understand it a bit better.

    Would player stats just be a separate table with a primary key linked into Players table?

    Not so bothered about if a player changes teams, will just delete one record and start a fesh one. Unless you think it will be easy to add in and worth while?

    Also confused about whether I need Team Id in fixtures, as home and away team will have to be entered some how, but how can I relate this to the 'team' table?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Beg your pardon - you are quite right. Dump TeamID from fixtures. Why do you think there will be any issue relating to the team table?

    The questions I asked depend on the use you will make of the database. Think about the sort of questions you will ask of it. If you will not ask "Who was the previous manager of team ABC?", "How many goals did player Z score in season 123?", "Who has played for the most teams in the last 10 years?" then you don't need to consider the issues rockingred and I raised.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2008
    Posts
    17
    I see what you're saying, I'm not too bothered about recording who previous managers were and for which team, however player stats would be good, so have revised the tables to incorporate this.
    I have also added what I think to be the primary and foreign keys which need to be identfied for the relationships.
    It would be helpful if you (or anyone else) could give me some confirmation on whether these are correct as it will cause more problems for me later if I program this in MySQL to find out my schema is all wrong.
    Have been searching on net for other examples of league databases so I could get some idea if mine is correct, but had no luck.
    All your help is appreciated. thanks.


    Players
    Player Id Primary Key
    Firstname
    Surname
    Address1
    Address2
    Address3
    Postcode
    Telephone number
    Shirt number
    Photo
    Team Id. FOREIGN KEY

    Team
    Team Id Primary Key
    Team name
    League Id FOREIGN KEY
    Age Group Id
    Area of team
    Web Address
    Date Formed
    Manager FName
    Manager SName

    League stats
    League Id Primary Key
    Team Id FOREIGN KEY
    Game Id ALSO POSSIBLY FOREIGN KEY?
    Season
    Games Played
    Games Won
    Games Lost
    Games Drawn
    Goals For
    Goals Against
    Goal Diff
    Points

    Fixtures
    Game Id Primary Key
    League Id FOREIGN KEY
    Date
    Age Group Id
    Time
    Home Team
    Away Team
    HomeT Result
    AwayT Result
    Location of game

    League
    League Id Primary Key
    League Name

    AgeGroup
    Age Group Id Primary Key
    Age Group Title

    Player Stats
    Player stats Id Primary Key
    Player Id FOREIGN KEY
    Season
    Played
    Goals
    Assists
    Points
    Penalties(min)

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would remove league stats. Work these out on the fly. However, this is personal preference.

    I would relate player stats to fixtures. It is no extra work but then you get extra info about a player since you know who he achieved his stats with\ against. As such, your player stats would be per game and you would generate the aggregations on the fly too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2008
    Posts
    5

    Question Guys thanks

    Quote Originally Posted by SVEN1
    I see what you're saying, I'm not too bothered about recording who previous managers were and for which team, however player stats would be good, so have revised the tables to incorporate this.
    I have also added what I think to be the primary and foreign keys which need to be identfied for the relationships.
    It would be helpful if you (or anyone else) could give me some confirmation on whether these are correct as it will cause more problems for me later if I program this in MySQL to find out my schema is all wrong.
    Have been searching on net for other examples of league databases so I could get some idea if mine is correct, but had no luck.
    All your help is appreciated. thanks.


    Players
    Player Id Primary Key
    Firstname
    Surname
    Address1
    Address2
    Address3
    Postcode
    Telephone number
    Shirt number
    Photo
    Team Id. FOREIGN KEY

    Team
    Team Id Primary Key
    Team name
    League Id FOREIGN KEY
    Age Group Id
    Area of team
    Web Address
    Date Formed
    Manager FName
    Manager SName

    League stats
    League Id Primary Key
    Team Id FOREIGN KEY
    Game Id ALSO POSSIBLY FOREIGN KEY?
    Season
    Games Played
    Games Won
    Games Lost
    Games Drawn
    Goals For
    Goals Against
    Goal Diff
    Points

    Fixtures
    Game Id Primary Key
    League Id FOREIGN KEY
    Date
    Age Group Id
    Time
    Home Team
    Away Team
    HomeT Result
    AwayT Result
    Location of game

    League
    League Id Primary Key
    League Name

    AgeGroup
    Age Group Id Primary Key
    Age Group Title

    Player Stats
    Player stats Id Primary Key
    Player Id FOREIGN KEY
    Season
    Played
    Goals
    Assists
    Points
    Penalties(min)
    thanks SVEN1
    what is mean by Season??

  9. #9
    Join Date
    Apr 2008
    Posts
    17
    Season is classed by year. eg 2008 season. 2009 seaon etc.

  10. #10
    Join Date
    Apr 2008
    Posts
    5
    Quote Originally Posted by SVEN1
    Season is classed by year. eg 2008 season. 2009 seaon etc.
    thanks really cool design

  11. #11
    Join Date
    Apr 2008
    Posts
    17
    Does anyone know of any books which relate to creating a league database? (preferably not baseball) ISBN, or just name? thanks.

  12. #12
    Join Date
    Apr 2008
    Posts
    17
    Anyone?.......

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No. I doubt there will be any at all. There is not a method of normalising a league database, and one for a forum and another for a questionnaire. Database principles are broadly implementation-agnostic - it doesn't really matter what you are modelling. There are some minor exceptions but nothing like what you mention.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Apr 2008
    Posts
    17
    Really?.... It seems like this forum is just for people who already know a lot about database design, in which case why do you need a forum to ask questions if you already know it all.

    for any users , who like me are new to databases and want to LEARN, here is an excellent book for creating a league database, and will help you understand how to design it. It is called systems building with oracle. ISBN 1-4039-0169-4. this book has helped me a great deal, with everything i needed to know, unlike this forum.

    If you people who are in 'THE KNOW' don't actually know, why bother wasting your time posting on these sites.

    For everyone else, I hope this book helps.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SVEN1
    If you people who are in 'THE KNOW' don't actually know, why bother wasting your time posting on these sites.
    actually, we do actually know

    we post simply because we want to bring more people such as yourself into "the know"

    and anyhow, why would i be interested in an oracle book

    yeah, it might cover fixture design, but look at all the other crap it covers which i can't use


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

Posting Permissions

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