I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are:
Application developers can model it reasonably well with popular ORM tools
Attributes of the desired schema:
There can be many Leagues.
There may exist 1 or more Divisions in a single League.
There may exist 1 or more Conferences in a single Division.
There may exist 1 or more Teams in a single Conference.
There may exist 1 or more Teams in a Division if and only if the Division has not been partitioned into Conferences (In other words, a Division does not need to be split into Conferences and instead a Team may have a reference directly to a single Division instead of a Conference)
There may exist 1 or more Players in a single Team.
Players exist in 1 and only 1 Team.
This would be simple enough to do and maintain referential integrity if the League structure was to be rigid. Here is what I was thinking, but I wondered if this would be a good design or if there is a better approach for this situation.
League Table: ...
LeagueGroup Table: FK - LeagueId NOT NULL, FK - LeagueGroupParentId NULLABLE, LeagueGroupType
Team: FK - LeagueGroupId NOT NULL
Player: FK - TeamId NOT NULL
So basically I was thinking that Divisions and Conferences would be the same table with a self-referential foreign key looking upwards for its grouping parent. The advantage of this is that league grouping can actually go N deep.
Disadvantages are that this might not be very easy or useful to map in an ORM framework. Further the application developers might need to write a bit of logic to build this into tree like object structure that is useful for their purposes. Further still, without a trigger of some kind, I wouldn't know how to enforce that teams can't exist at both the Division and the Conference level at the same time from the Database level.
What are your thoughts on this approach, and do you see any viable alternatives to this?