Cross-reference table...dimension or fact?

Question

Beginner Dimensional-Modeling question:

How do you model relationships between dimensions outside of a formal 'business process'? For example, say you were modeling a Fantasy Baseball league. Some obvious dimensions would be Team and Player, and an example fact would be the result of a player coming to bat. What I'm confused about is how to simply keep track of which players are on which team.

In Third Normal Form I'd have a cross reference table with Team and Player FK's, and any additional fields which pertain specifically to the combination of the two (recruitment date, benched-player indicator, etc). Would this be any different with a star schema? If not, is this table considered a fact table then, with no numerical attributes?

The part that confuses me is that this cross-reference table is never much use on its own. It would only make sense when joined to other fact tables, to get the list of players on a team associated with another fact/process. This makes it feel more like a dimension than a fact.

Answer 1

In dimensional modeling you must chose which process you want model. If the Team Player relationship is secondary to your model, you could just ignore it and know that a player is belonging to a team when he goes to bat for the team.

Of course this leave out the players that never bat.

If you want to consider this relationship, being a many to many one, the obvious solution is another fact table. The fact table could even be factless (when you do not have additional information over it, but in this case the salary of the player would be an obvious and important fact).

Answer 2

Another option is to use a type 2 SCD for the player. This is a way of storing attribute changes against a player over time.

So for a single player you might have four dimension records, as that player moves accross four teams. The dimension records have start and end dates, so if the player moved teams in January and didn't start playing till February, that information is still stored.

You track any player attribute in this way, i.e. injuries etc.

This is a way of tracking 'slow' changes without needing a special fact.

If you do need some kind of historical status report you just join the player dimension to the date dimension.

Take a look at Type II in this article:

http://en.wikipedia.org/wiki/Slowly_changing_dimension