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?

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 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

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)

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!

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! :-)

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!

in that scenario i would put a date range into that table to track changes to any column

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.........

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

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)

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!