Star schema modeling - many-to-many
I'm building a data warehouse based on NFL statistics for educational purposes as I learn this paradigm - I have the following modelling problem
Players can play for different teams different years and similarly coaches can coach different teams different years of their career开发者_高级运维; players could also potentially play different positions in different years too (rare but possible)
What is the best way to model the assignment between players, coaches and teams for different years?
Would I store the different years' roster information in a dimension? eg DimTeamRoster which would have a TimeKey, TeamKey and CoachKey (since a team can only have one Head Coach) with a FactTeamRoster with a TeamRosterKey, PlayerKey, Positionkey
Or would I have a FactTeamRoster which would have a TimeKey, TeamKey, PlayerKey, PositionKey? But then would this approach make sense because this fact-table wouldn't really be storing any measures, it would be simply storing the assignment for that year
What're some of the other possible solutions and pros / cons / correctness of each approach?
For starters, this would do -- I think.
dimRole
may look something like
RoleKey RoleName RoleGroup
---------------------------------------
1 quarterback player
2 tackle player
3 head coach coach
4 assistant coach coach
精彩评论