2

I am creating a database for a Basketball Season. At this point, I am keeping it simple, and storing tables:

-League id[PK], name->(NBA, NCAAM, etc)

-Year id[PK], league_id[FK], year->(2012-2013, 2011-2012, etc)

-Team id[PK], league_id[FK], name->(Bulls, Lakers, Etc)

-Game id[PK], home_team[FK], away_team[FK]

-Score id[PK], game_id[FK], home_score, away_score

My question is about table structure. I am somewhat new to sql, but I want to be able to make calcuations based on database info. For example:

Team A on the road, playing team B, where team A's road score avg is x and team B's home score average is y... and so on

Anything you see that screams DONT DO IT THAT WAY? Just wanted you guys to take a quick look.

Eventually would like to develop a cross sport DB similar to this:

http://www.bc.edu/content/dam/files/schools/cas_sites/cs/local/bach/2008/08LawrenceChang.pdf

But want to start here to get a base scraping algorithm, and some basic stats to start out. Let me know whatcha think. Thanks a bunch!

Eddie
  • 179
  • 2
  • 14

2 Answers2

2

At first glance I would put the scores in the game table and add a game date field. I would rename the year table as season, and include start and stop dates. Also consider adding a season id to the game table.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Ya you're totally right. Game can just have home_team, home_score, etc. Also will put 2 date fields in the Season table, for beginning and end of season, will certainly help distinguish the end of a season in 2013 and the beginning of the next in 2013! Saweeeet! – Eddie Feb 27 '13 at 03:26
0

The Year and Score tables both seem unnecessary to me. They can easily be added onto the Game table (including both a date and a season field for the Year).

Also I don't see why you need to generate the auto-increment primary key on the League table. The League name by itself would work perfectly fine, or better yet get rid of the League table altogether and just use ENUM on the league field in the Team table.

Matt
  • 313
  • 1
  • 6