Correctly Modeling Sports Statistics in a Relational Database

Question

I'm trying to model football statistics in PostGreSQL and I have something that works but it doesn't quite feel right. Here's the problem:

I have teams, games and stats. For every game there are two teams. Each team has a single offensive statistic line that might include rushing, passing, etc. However, that offensive stat is implicitly the other team's defensive stat, e.g. if the the offensive team has 100 yards rushing in a game against the the defensive team, the defensive team gave up 100 yards rushing. It's important to track both offensive and defensive stats.

My current model has FootballTeam, FootballGame, FootballStat and FootballTeamStat as a linking table between FootballTeam and FootballStat. FootballStat has a foreign key to FootballGame. FootballTeamStat has two foreign keys, one to FootballTeam and one to FootballStat. It also has a StatTypeId which is a flag for either offensive or defensive. By doing this, I can avoid a lot of redundancy in FootballStat where I would otherwise have to have two rows for the same stat but one with an offensive flag and one with a defensive flag.

This works pretty well in my current application but it has never felt completely right. Is there a better way to do this? My database modeling experience pretty much is limited to projects I've done myself after reading Database Design for Mere Mortals and while this seems to follow most of my experience there, I'm not sure it's the best way.

EDIT: Updated to make it generic instead of league specific.

Answer 1

You're on the right track, I'd just recommend naming the tables a little better. FootballStat and FootballTeamStat read as containing statistics that existed with and without a Team context. Here's what I spec'd based on your post:

TEAM-STATISTICS-CATEGORY-CODE

• CODE
• DESCRIPTION

IE: DEF: Defensive, OFF: Offensive

---

TEAM-STATISTICS-TYPE-CODE

• TEAM-STATISTICS-TYPE-CODE-ID
• CODE (FK: TEAM-STATISTICS-CATEGORY-CODE)
• DESCRIPTION

IE: 1, OFF, Rushing

---

TEAM

• TEAM-ID (pk)

---

TEAM-STATISTICS

• TEAM-STATISTICS-ID
• TEAM-ID
• GAME-ID
• TEAM-STATISTICS-TYPE-CODE-ID (FK: TEAM-STATISTICS-TYPE-CODE)
• VALUE

IE: 1, 1, 1, 100

I realize that offensive and defensive stats are important, but if a stat will always be associated as offensive and defensive - you could get away with logging only the offensive stats & using SQL to calculate the defensive ones. IE: If Offensive Rushing of 100 yards means a defensive loss of 100 yards, do you benefit from storing the defensive info or should you create the mapping in the db so you can get SQL to reverse the values via query or view?

---

GAME

• GAME-ID
• HOME-TEAM-ID
• AWAY-TEAM-ID
• HOME-SCORE
• AWAY-SCORE

Answer 2

Well, another way to do this is to get rid of FootballTeamStat.

Instead, in FootballStat add two columns:

• OffensiveTeam
• DefensiveTeam

You'll also need a constraint to guarantee that OffensiveTeam!=DefensiveTeam and you'd have to scan through the table twice to calculate the stats for a team. But you do save on a couple of joins.

Answer 3

You can avoid storing the stats twice. You should, in fact avoid this as this can lead to data inconsistencies via an update operation even if you do all the right validations before an Insert operation. You could have multiple relationships between teamstats and team

TeamStats(stat, OffensiveTeamID, DefensiveTeamID)

example row

Insert into TeamStats('100 yards rushing', 'Team A', 'Team B')

In this case, Team A had an offensive of 100 yards rushing and team B had a defensive of 100 yards defending. You need to make OffensiveTeamID and DefensiveTeamID NOT NULL columns.

Answer 4

As long as every game is played by two teams in the database, that will work. But will you ever have a game in which just one team is in the database?