title: Star schema modeling - many-to-many
body: <p>I'm building a data warehouse based on NFL statistics for educational purposes as I learn this paradigm - I have the following modelling problem</p>
  
  <p>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)</p>
  
  <p>What is the best way to model the assignment between players, coaches and teams for different years?</p>
  
  <p>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</p>
  
  <p>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</p>
  
  <p>What're some of the other possible solutions and pros / cons / correctness of each approach? </p>