Tables 9: Individual Batting Stats, States, and Team Affiliations

Three small tables (at least as far as column count goes) that don’t have impact on my first goal of producing a lot of statistics.  However, I do see some use for them down the road.

Creating players_individual_batting_stats table

This one pairs batters and pitchers (‘player_id’ and ‘opponent_id’, respectively) and sums the results of each AB in terms of Hits and HR.  Significantly, this is not Plate Appearances and, therefore, does not include Walks, Sacrifice Hits, or Sacrifice Flies.

CREATE TABLE `players_individual_batting_stats` (
  `player_id` int(11) NOT NULL, #Batter
  `opponent_id` int(11) NOT NULL, #Pitcher
  `ab` smallint(6) DEFAULT NULL,
  `h` smallint(6) DEFAULT NULL,
  `hr` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`player_id`,`opponent_id`),
  INDEX `pibs_ix1` (`opponent_id`)    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating states table

Logically, this table belongs further up with nations and cities.  It simply defines states, which reside in nations.  Cities defines cities, which reside in states, which reside in nations.  Nations reside in continents, but I have not imported that table and don’t see the need to.

CREATE TABLE `states` (
  `state_id` int(11) NOT NULL,
  `nation_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbreviation` varchar(50) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `main_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`state_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Creating team_affiliations table

This is essentially a join table that establishes a relationship with an affiliate team.  The parent team (i.e. the Major League team) is the ‘affiliated_team_id’ and the minor league team is the ‘team_id’ team.

Actually, it could be reversed – due to an oversight I didn’t include this data in the dump – just set up the table.  It won’t be used until all the stats are done anyway – and it will be used to help identify useful players in trade negotiations.  For example, let’s say I am close to getting a deal done with Seattle and want to find one more pitching prospect from their farm system.  I’d use this table to find players on teams affiliated with Seattle.  Will have to come back to this one later, obviously.

CREATE TABLE `team_affiliations` (
  `team_id` int(11) NOT NULL,
  `affiliated_team_id` int(11) NOT NULL,
  PRIMARY KEY (`team_id`,`affiliated_team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;