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 are your thoughts on this approach, and do you see any viable alternatives to this?

2 Answers 2


The basic idea is to separate hierarchy of an organizational chart from actuals teams.

The Level table looks like

LevelID | LevelType
  1      League 
  2      Division
  3      Conference
  4      Team_Slot
  • Team_Slot is a "pigeon-hole" for an actual team to fill-in over time.

enter image description here

Few more notes:

  • Alternate Key (PositionID, LevelID) is propagated into TeamPosition in order to allow a check constraint LevelID = 4 -- actual teams can fill-in only Team_Slots.

  • Team can not change Team_Slot within one season.

  • For simplicity, the hierarchy is here modelled with the levelled-adjacency-list; not most efficient for SQL hierarchies, but good enough for an explanation.

  • For more efficient hierarchy models, see Celko's book or just google 'SQL hierarchies'.

  • Wow, this is a little complex so I am trying to understand it. IsPartOf is self referential to a parent OrgChart. TeamPosition maps a team to a season so Teams have the flexibility of changing org structure between seasons. What I don't understand is why TeamPosition has a foreign key to Level? Jul 14, 2012 at 13:10
  • OK, one by one .. 1) Yes, PartOf points to parent PositionID 2) To allow for check constraint LevelID = 4 Jul 14, 2012 at 14:42
  • Wonderful answer! I especially like how you address that players and teams will change positions over time, which I honestly neglected to consider. For my purposes however I think that a team construct will be temporary with the season because this isn't a real sports league, just a mock league for a game. Still requirements may change where a team may carry over into other seasons so I think I will end up doing it this way just to be safe. Thank you! Jul 14, 2012 at 15:19

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.

  • I have a general mistrust of ORMs when it comes to anything other than simple CRUD operations I used to think this way until I took a bad developer to the knee. Bad developers need tools that limit their creativity, so that when they run into situations like this they are forced to stop what they are doing and ask somebody with more experience for help. ;-) Jul 13, 2012 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.