Sql – Correctly Modeling Sports Statistics in a Relational Database

database-designsql

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.

Best Solution

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

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Sql – What are the options for storing hierarchical data in a relational database

My favorite answer is as what the first sentence in this thread suggested. Use an Adjacency List to maintain the hierarchy and use Nested Sets to query the hierarchy.

The problem up until now has been that the coversion method from an Adjacecy List to Nested Sets has been frightfully slow because most people use the extreme RBAR method known as a "Push Stack" to do the conversion and has been considered to be way to expensive to reach the Nirvana of the simplicity of maintenance by the Adjacency List and the awesome performance of Nested Sets. As a result, most people end up having to settle for one or the other especially if there are more than, say, a lousy 100,000 nodes or so. Using the push stack method can take a whole day to do the conversion on what MLM'ers would consider to be a small million node hierarchy.

I thought I'd give Celko a bit of competition by coming up with a method to convert an Adjacency List to Nested sets at speeds that just seem impossible. Here's the performance of the push stack method on my i5 laptop.

Duration for     1,000 Nodes = 00:00:00:870 
Duration for    10,000 Nodes = 00:01:01:783 (70 times slower instead of just 10)
Duration for   100,000 Nodes = 00:49:59:730 (3,446 times slower instead of just 100) 
Duration for 1,000,000 Nodes = 'Didn't even try this'

And here's the duration for the new method (with the push stack method in parenthesis).

Duration for     1,000 Nodes = 00:00:00:053 (compared to 00:00:00:870)
Duration for    10,000 Nodes = 00:00:00:323 (compared to 00:01:01:783)
Duration for   100,000 Nodes = 00:00:03:867 (compared to 00:49:59:730)
Duration for 1,000,000 Nodes = 00:00:54:283 (compared to something like 2 days!!!)

Yes, that's correct. 1 million nodes converted in less than a minute and 100,000 nodes in under 4 seconds.

You can read about the new method and get a copy of the code at the following URL. http://www.sqlservercentral.com/articles/Hierarchy/94040/

I also developed a "pre-aggregated" hierarchy using similar methods. MLM'ers and people making bills of materials will be particularly interested in this article. http://www.sqlservercentral.com/articles/T-SQL/94570/

If you do stop by to take a look at either article, jump into the "Join the discussion" link and let me know what you think.

Best Solution

Related Solutions

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Sql – What are the options for storing hierarchical data in a relational database

Related Question