Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
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? –  maple_shaft Jul 14 '12 at 13:10
    
OK, one by one .. 1) Yes, PartOf points to parent PositionID 2) To allow for check constraint LevelID = 4 –  Damir Sudarevic Jul 14 '12 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! –  maple_shaft Jul 14 '12 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.

share|improve this answer
    
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. ;-) –  maple_shaft Jul 13 '12 at 19:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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