开发者

Star schema modeling - many-to-many

开发者 https://www.devze.com 2023-04-09 17:12 出处:网络
I\'m building a data warehouse based on NFL statistics for educational purposes as I learn this paradigm - I have the following modelling problem

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.

Star schema modeling - many-to-many

dimRole may look something like

RoleKey     RoleName        RoleGroup
---------------------------------------
  1         quarterback      player
  2         tackle           player
  3         head coach       coach
  4         assistant coach  coach
0

精彩评论

暂无评论...
验证码 换一张
取 消