Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Question Sports statistics database design question No.2!

    My first question was too long and too complicated so here comes a short and simple one for those who know!

    How do I track player performance in regards to each club that a player has played in?

    Pitcher A:
    plays in St. Louis from 1999-2003, Pittsburgh 2004-2006 and Cinncinati from 2006-

    If you have 4 tables: Players, Clubs, EventsPlayed and PlayerEventStats

    Players table has:
    PlayerId
    CurrentClubId

    EventsPlayed has:
    EventId as PK
    HomeTeamId
    AwayTeamId

    PlayerEventStats table has:
    PlayerId
    EventId
    SomeStats....

    Now the problem is if a player changes a club he plays for, and you query the PlayerEventStats table it turns out that all the stats of player would look as if they were, in this example, achieved while playing for Cinncinati!

    So how do you model that correctly, so that change in club leaves true records in PlayerEventStats?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    have a read of Rudy's normalisation page

    ..get to grips with what normalisation means....


    in my view if you need to track which player played in which team for your stats to have any value you would need to know when a player played for a specific team. you would then need to records whatever statistics against a player, not a team. you would probably need to know when a game was played, as that would enable you to identify who was a member of the team during that game, you may also want to qualify whether a player actually played that day, or if they played the full game.

    btw what do you actually require for this assignment?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would model it by having a table where the player's membership in clubs is defined with from/to date ranges

    you wouldn't need CurrentClubID because that's simply the latest entry in that table, although i suppose you could carry it redundantly just to save some cycles

    EventsPlayed would also have a date, and be joined on date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arg, sniped by a single second

    thanks for the mention, healdem, but it isn't really "my" article, it's just hosted on my site (i have permission)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    108
    Thanks for the replies!

    Of course, Events would always carry a date - I just didn't want my post to be too long!

    r937 the solution with from/to range came to my mind seconds after posting!

    You could have something like Employment or Engagement table where you have:

    EngagmentID
    PlayerID
    TeamID
    StartDate
    EndDate

    ...representing player's time spent in a club!

    And Stats table where you have:
    EngagementID as FK
    EventID as FK
    SomeStats...

    ...representing what a player did in a particular event while playing for a certain contracted period at a given club!

  6. #6
    Join Date
    Apr 2007
    Posts
    108
    btw what do you actually require for this assignment?
    I was reading some other posts and realized people post various kind of homeworks!

    So if above comment is intended in that way I want to clarify my position: I don't work in IT industry, I'm in medical field and I have no homework assignments!

    I want to design such sports database as a hobby and I take interest in various computer-related topics as a hobby! :-)

  7. #7
    Join Date
    Apr 2007
    Posts
    108
    r937 here's the similar question with slight difference!

    You have teams table having:

    TeamID
    TeamName
    TeamHomeTown
    TeamHomeFacility
    TeamOwner
    TeamLogoGIF

    Now if you look at it TeamID is in fact the only static data in such table, everything else can change, team can change only it's name (example Croatia Zagreb to Dinamo Zagreb in 2000.), TeamHomeFacility can be a solitary change (Arsenal London goes from Highbury to Emirates Stadium) and the club can change it's home town, home facility, name and logo at the same time and in the middle of an ongoing competition (like Vancouver Grizzlies move to Memphis).

    Would you to accomodate such possibilites have two tables TeamID and TeamDescription with from/to ranges where you would change from one column to all for any possible change or would you have more tables to make possibility for each change to happen without interfering with other?

    Thanks!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in that scenario i would put a date range into that table to track changes to any column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    its up to you as to how you want store information
    if you need to identify a venue where a game was played then you need to to separately identify the stadium
    if you need to display the statistics for a team and display the appropriate name of logo or owner or whatever then you need to store a history.

    its up to you and your application


    however if you are going to store such information then you probably need to store each element in its own table with a datefrom or dateto or daterange. you could roll that up into a child table to the team with say an elementtype

    elementype
    elementtypeID autonumber 'identifies what type of data element we are storing
    elementTypeDesc string 'describes the type

    eg
    1 = logo
    2 = name
    3 = stadium
    4 = ?????
    n = name of the kitman

    TeamData 'stores details of data elements defined by team
    TeamDataID 'autogenerated
    TeamID 'fk to team
    ElementTypeID 'FK to elementTypeDesc
    element 'string contains the value
    datefrom 'identifies the date this eleemnt is valid from /to
    dateto

    eg
    1 | 1 | 1 | //myserver/my/path/to/logo/MCFCLogo.PNG | 01/01/2006 | 01/01/2007
    9 | 1 | 2 | Manchester City FC | 00/00/0000 | 99/99/9999
    2 | 1 | 3 | Maine Road | 01/01/1923 | 11/05/2003
    3 | 1 | 3 | City of Manchester Stadium | 12/05/2003
    4 | 2 | 2 | Newton Heath LYR FC | 00/00/0000 | 01/01/1890
    7 | 2 | 2 | Newton Heath FC | 01/01/1890 | 01/01/1902
    5 | 2 | 2 | Trafford Town AKA Man Ure AKA Manchester United FC Aka those cheating barstewards | 01/01/1902 | 99/99/9999
    ...etc

    its not very pretty and it may not be applicable in your case. it may be better to model it as separate discrete elements eg have a child table each for logos, managers, owners, stadia etc.....

    I apologise for the comment re assignment, but in my defence yerhooner your proposed db is very similar to a standard assignment question that crops up frequently. it had the sniff of homework about it.........
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2007
    Posts
    108
    Thanks healdem and r937 that was helpful!

    I've done and excelled in most of my main-field of activitiy (medicine) homeworks, unfortunately it hasn't done me much good. That's why i have these self-imposed "homeworks", to turn me from to

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    to make matters a bit more complex, this spring Cleveland has played some of it's home games in Minneapolis. (snow)

    For a year after Katrina, the New Orleans Saints played their home football games in San Antonio, Texas, I believe.

    You'll need a venue table to store data re where the events occur, as well as a key field referencing this table in your events table. (probably, in the team table as well, if for nothing except the default home location)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Apr 2007
    Posts
    108
    That's right loquin, I'm aware of that fact!

    Ideally you would store sports facility in the Events table where the game was played regardless of the fact wheter it's a home facility for any particular team! No respectable game is played on a loan!

    Just because someone is declared home team doesn't mean it happens in their home town. World Cup finals and CL finals also have home teams although it's played on neutral ground!

    And each facility must be in some city and that's static data for now, so that's easy to model!

Posting Permissions

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