[The DB Project] Done!

Wait for it … wait for it! I’ve got some big news I’d like to share with you:

As of 4pm yesterday, I am done with my DB Project.

I first mentioned my crazy idea of amassing a professional basketball database from scratch back in May of 2010. A few days later, I published the initial details about this massive undertaking. How naive I was back then! Well, although it’s been a tedius two-and-a-half years since then, I have finally come out the other end with a gigantic server full of stats! Here are the list of seasons my database currently covers:

• 1926-1931, 1934-1953 American Basketball League (ABL) teams and players
• 1938-1949 National Basketball League (NBL) teams and players
• 1947-1949 Basketball Association of America (BAA) teams and players
• 1968-1976 American Basketball Association (ABA) teams and players
• 1950-2012 National Basketball Association (NBA) teams and players

For each of these seasons, I have three team tables:

Team Miscellaneous Table – for all of the non-basic stats related information about the team

• Year
• League
• unique Team Abbreviation (for foreign key purposes)
• full Team Name (city + team name)
• Franchise ID (to keep track of team relocations and name changes)
• Conference and Division
• Partial Season status (if a team didn’t make it to the end of the year)
• Games Played, win-loss record and forfeited wins/losses
• Pythagorean wins-losses, Simple Rating System and Point Differential
• Team Offensive Rating, Defensive Rating, and Pace
• Offensive and Defensive Four Factors
• Arena and Attendance
• Logo image
• Team Colors
• Notes (when team disbanded, relocated, etc.)

Team Offense Table – regarding all of the stats accumulated by the roster of the team.

• Year
• League
• unique Team Abbreviation (for foreign key purposes)
• Games Played
• Team Minutes Played
• Team Field Goals, Field Goal Attempts, Three Pointers, Three Point Attempts, Free Throws, Free Throw Attempts and Percentages
• Team Offensive, Defensive and Total Rebounds
• Team Assists, Steals, Blocks, Turnovers, Personal Fouls, and Points
• Notes (how stats are determined, etc.)

Team Defense Table – regarding all of the stats accumulated by the roster of the team’s opponents.

• Year
• League
• unique Team Abbreviation (for foreign key purposes)
• Games Played
• Opponent Minutes Played
• Opponent Field Goals, Field Goal Attempts, Three Pointers, Three Point Attempts, Free Throws, Free Throw Attempts and Percentages
• Opponent Offensive, Defensive and Total Rebounds
• Opponent Assists, Steals, Blocks, Turnovers, Personal Fouls, and Points
• Notes (how stats are determined, etc.)

Each one of those tables has 1,699 separate team-season instances.

Next, I have one table that has information for every individual player-team-season instance, the Player Regular Season Table. This table has 28,655 separate player-team-season instances. Yeah, it was a ton of work.

• unique Player Identification (for foreign key purposes)
• Year
• League
• Team Abbreviation (TOT for year totals to handle players who played for multiple teams)
• First and Last Name
• Position(s) played
• Height (inches) and Weight (pounds)
• Age (as of January 1st of the year)
• Games and Minutes Played
• Field Goal, Field Goal Attempts, Three Pointers, Three Point Attempts, Free Throws and Free Throw Attempts
• Offensive, Defensive and Total Rebounds
• Assists, Steals, Blocks, Turnovers, Personal Fouls and Points
• Adjusted Pace Performance
• Notes (any miscellaneous information I want to keep track of for player)
• Edit Notes (used to keep track of changes to player data, where I acquired data, etc.)
• Partial season status
• Partial Order (to keep track of which team player was on in what order)
• Team Pace
• Similar ID (used for cloning purposes later on)

Now, the vast majority of my work was just getting in this data into the database and cleaning it. I had to make sure the players’ names were consistent, that stats for two players with the same names were separated correctly, and that players had correct height, weights and positions. There were many tasks to execute behind the scenes that wouldn’t make for a great blog – and I’m not really satisfied with what’s here now – but I just wanted to update everyone that the goal I set out to accomplish has, indeed, been accomplished!

Oh … but by no means am I really done. Not at all! I’ve still got to figure out how I wish to present this data and what I’m going to do with it. Oh, and of course I’ll be adding more and more to it – I wouldn’t be a good database builder if I didn’t succumb to scope creep!

For tonight, however, I’ll bask in this small triumph!