Help with good RDBMS transactional schema design that mimicks a sports league

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:

-
Well normalized

-
Transactional

-
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 a

I imagine that most sports leagues don't change their hierarchy of conference, division, etc... very often so it's probably safe to have each level in its own table.

If your goal is a system that can handle different leagues that have different hierarchy depths, then the recursive table approach is probably better.

I don't know how easy it would be to do this with an ORM but I have a general mistrust of ORMs when it comes to anything other than simple CRUD operations... To prevent a team from existing at multiple tiers of the hierarchy, I'm not sure why your proposed Team.LeagueGroupId wouldn't work. You know what level the LeagueGroup referred to by LeagueGroupID is so you know that the team exists at the level of that LeagueGroup.

StackExchange Database Administrators Q#20858, answer score: 3