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

database-designreferential-integrity

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?

Best Answer

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

Related Question