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