teams | MySQL MyStruggle

Tables 10: Team History Financials and Team Relations

September 21, 2017 cavebutterLeave a comment

Creating team_history_financials table

This table tracks a whole bunch of team financial data per year. Useful in doing team by team financial comparisons as well as deriving fun statistics such as $/win or looking at correlation between fan interest/loyalty and wins. Of note, the inclusion of league, sub_league, and division columns is a little confusing and seems to be asking for trouble. Those relationships are established on the ‘teams’ table and seem superfluous here.

CREATE TABLE `team_history_financials` (
  `team_id` int(11) NOT NULL,
  `year` smallint(6) NOT NULL,
  `league_id` int(11) DEFAULT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `division_id` int(11) DEFAULT NULL,
  `gate_revenue` int(11) DEFAULT NULL,
  `season_ticket_revenue` int(11) DEFAULT NULL,
  `media_revenue` int(11) DEFAULT NULL,
  `merchandising_revenue` int(11) DEFAULT NULL,
  `other_revenue` int(11) DEFAULT NULL,
  `revenue_sharing` int(11) DEFAULT NULL,
  `playoff_revenue` int(11) DEFAULT NULL,
  `cash` int(11) DEFAULT NULL,
  `cash_owner` int(11) DEFAULT NULL,
  `cash_trades` int(11) DEFAULT NULL,
  `previous_balance` int(11) DEFAULT NULL,
  `player_expenses` int(11) DEFAULT NULL,
  `staff_expenses` int(11) DEFAULT NULL,
  `stadium_expenses` int(11) DEFAULT NULL,
  `season_tickets` int(11) DEFAULT NULL,
  `attendance` int(11) DEFAULT NULL,
  `fan_interest` smallint(6) DEFAULT NULL,
  `fan_loyalty` smallint(6) DEFAULT NULL,
  `fan_modifier` smallint(6) DEFAULT NULL,
  `ticket_price` double DEFAULT NULL,
  `local_media_contract` int(11) DEFAULT NULL,
  `local_media_contract_expires` int(11) DEFAULT NULL,
  `national_media_contract` int(11) DEFAULT NULL,
  `national_media_contract_expires` int(11) DEFAULT NULL,
  `development_budget` int(11) DEFAULT NULL,
  `draft_budget` int(11) DEFAULT NULL,
  `draft_expenses` int(11) DEFAULT NULL,
  `budget` int(11) DEFAULT NULL,
  `market` smallint(6) DEFAULT NULL,
  `owner_expectation` smallint(6) DEFAULT NULL,
  `scouting_budget` int(11) DEFAULT NULL,
  `scouting_amateur` int(11) DEFAULT NULL,
  `scouting_major` int(11) DEFAULT NULL,
  `scouting_minor` int(11) DEFAULT NULL,
  `scouting_international` int(11) DEFAULT NULL,
  PRIMARY KEY (`team_id`,`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Creating team_relations table

I think I included this table in the dump because I thought it did what team_affiliations does. It does not. I think this table is only useful for showing current standings without having to do complicated joins on the teams, leagues, sub_leagues, and divisions tables. Well, not complicated exactly, but cumbersome.

CREATE TABLE `team_relations` (
  `league_id` int(11) NOT NULL,
  `sub_league_id` int(11) NOT NULL,
  `division_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  PRIMARY KEY (`league_id`,`sub_league_id`,`division_id`,`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

September 21, 2017 cavebutterLeave a comment

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;

Tables 4: Parks and Teams

September 21, 2017September 21, 2017 cavebutterLeave a comment

Creating parks table

Parks is another one of those tables that has a ton of columns of which we need only a couple. It’s a standalone table that doesn’t have any columns that could be construed as a foreign key (if we were using them). We’re really only interested in ‘park_id’ and the park factor columns about halfway down:

‘avg’
‘avg_l’
‘avg_r’
‘d’
‘t’
‘hr’
‘hr_l’
‘hr_r’

And of those, at least for the time being, we’re only interested in ‘avg’. This is the factor we’ll be using to adjust some of the advanced statistics by to account for the specific conditions of the park in which each player played his home games.

CREATE TABLE `parks` (
  `park_id` int(11) NOT NULL,
  `dimensions_x` smallint(6) DEFAULT NULL,
  `dimensions_y` smallint(6) DEFAULT NULL,
  `batter_left_x` smallint(6) DEFAULT NULL,
  `batter_left_y` smallint(6) DEFAULT NULL,
  `batter_right_x` smallint(6) DEFAULT NULL,
  `batter_right_y` smallint(6) DEFAULT NULL,
  `bases_x0` smallint(6) DEFAULT NULL,
  `bases_x1` smallint(6) DEFAULT NULL,
  `bases_x2` smallint(6) DEFAULT NULL,
  `bases_y0` smallint(6) DEFAULT NULL,
  `bases_y1` smallint(6) DEFAULT NULL,
  `bases_y2` smallint(6) DEFAULT NULL,
  `positions_x0` smallint(6) DEFAULT NULL,
  `positions_x1` smallint(6) DEFAULT NULL,
  `positions_x2` smallint(6) DEFAULT NULL,
  `positions_x3` smallint(6) DEFAULT NULL,
  `positions_x4` smallint(6) DEFAULT NULL,
  `positions_x5` smallint(6) DEFAULT NULL,
  `positions_x6` smallint(6) DEFAULT NULL,
  `positions_x7` smallint(6) DEFAULT NULL,
  `positions_x8` smallint(6) DEFAULT NULL,
  `positions_x9` smallint(6) DEFAULT NULL,
  `positions_y0` smallint(6) DEFAULT NULL,
  `positions_y1` smallint(6) DEFAULT NULL,
  `positions_y2` smallint(6) DEFAULT NULL,
  `positions_y3` smallint(6) DEFAULT NULL,
  `positions_y4` smallint(6) DEFAULT NULL,
  `positions_y5` smallint(6) DEFAULT NULL,
  `positions_y6` smallint(6) DEFAULT NULL,
  `positions_y7` smallint(6) DEFAULT NULL,
  `positions_y8` smallint(6) DEFAULT NULL,
  `positions_y9` smallint(6) DEFAULT NULL,
  `avg` double DEFAULT NULL,
  `avg_l` double DEFAULT NULL,
  `avg_r` double DEFAULT NULL,
  `d` double DEFAULT NULL,
  `t` double DEFAULT NULL,
  `hr` double DEFAULT NULL,
  `hr_r` double DEFAULT NULL,
  `hr_l` double DEFAULT NULL,
  `temperature0` smallint(6) DEFAULT NULL,
  `temperature1` smallint(6) DEFAULT NULL,
  `temperature2` smallint(6) DEFAULT NULL,
  `temperature3` smallint(6) DEFAULT NULL,
  `temperature4` smallint(6) DEFAULT NULL,
  `temperature5` smallint(6) DEFAULT NULL,
  `temperature6` smallint(6) DEFAULT NULL,
  `temperature7` smallint(6) DEFAULT NULL,
  `temperature8` smallint(6) DEFAULT NULL,
  `temperature9` smallint(6) DEFAULT NULL,
  `temperature10` smallint(6) DEFAULT NULL,
  `temperature11` smallint(6) DEFAULT NULL,
  `rain0` smallint(6) DEFAULT NULL,
  `rain1` smallint(6) DEFAULT NULL,
  `rain2` smallint(6) DEFAULT NULL,
  `rain3` smallint(6) DEFAULT NULL,
  `rain4` smallint(6) DEFAULT NULL,
  `rain5` smallint(6) DEFAULT NULL,
  `rain6` smallint(6) DEFAULT NULL,
  `rain7` smallint(6) DEFAULT NULL,
  `rain8` smallint(6) DEFAULT NULL,
  `rain9` smallint(6) DEFAULT NULL,
  `rain10` smallint(6) DEFAULT NULL,
  `rain11` smallint(6) DEFAULT NULL,
  `wind` smallint(6) DEFAULT NULL,
  `wind_direction` smallint(6) DEFAULT NULL,
  `distances0` smallint(6) DEFAULT NULL,
  `distances1` smallint(6) DEFAULT NULL,
  `distances2` smallint(6) DEFAULT NULL,
  `distances3` smallint(6) DEFAULT NULL,
  `distances4` smallint(6) DEFAULT NULL,
  `distances5` smallint(6) DEFAULT NULL,
  `distances6` smallint(6) DEFAULT NULL,
  `wall_heights0` smallint(6) DEFAULT NULL,
  `wall_heights1` smallint(6) DEFAULT NULL,
  `wall_heights2` smallint(6) DEFAULT NULL,
  `wall_heights3` smallint(6) DEFAULT NULL,
  `wall_heights4` smallint(6) DEFAULT NULL,
  `wall_heights5` smallint(6) DEFAULT NULL,
  `wall_heights6` smallint(6) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `picture` varchar(200) DEFAULT NULL,
  `picture_night` varchar(200) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `capacity` int(11) DEFAULT NULL,
  `type` smallint(6) DEFAULT NULL,
  `foul_ground` smallint(6) DEFAULT NULL,
  `turf` tinyint(4) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `relative_path_3d_model` varchar(200) DEFAULT NULL,
  `file_name_3d_model` varchar(200) DEFAULT NULL,
  `home_team_dugout_is_at_first_base` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`park_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Creating teams table

This table contains, obviously, information about all of the teams in our OOTP universe. Again, there are a bunch of columns that are only useful in-game. We’re interested in the teams’ names, nicknames, league, sub_league, and park_id.

CREATE TABLE `teams` (
  `team_id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `abbr` varchar(50) DEFAULT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `logo_file_name` varchar(200) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `park_id` int(11) DEFAULT NULL,
  `league_id` int(11) DEFAULT NULL,
  `sub_league_id` int(11) DEFAULT NULL,
  `division_id` int(11) DEFAULT NULL,
  `nation_id` int(11) DEFAULT NULL,
  `parent_team_id` int(11) DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  `prevent_any_moves` tinyint(4) DEFAULT NULL,
  `human_team` tinyint(4) DEFAULT NULL,
  `human_id` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `background_color_id` varchar(8) DEFAULT NULL,
  `text_color_id` varchar(8) DEFAULT NULL,
  `ballcaps_main_color_id` varchar(8) DEFAULT NULL,
  `ballcaps_visor_color_id` varchar(8) DEFAULT NULL,
  `jersey_main_color_id` varchar(8) DEFAULT NULL,
  `jersey_away_color_id` varchar(8) DEFAULT NULL,
  `jersey_secondary_color_id` varchar(8) DEFAULT NULL,
  `jersey_pin_stripes_color_id` varchar(8) DEFAULT NULL,
  `allstar_team` tinyint(4) DEFAULT NULL,
  `historical_id` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`team_id`),
  INDEX `teams_ix1` (`park_id`),
  INDEX `teams_ix2` (`league_id`),
  INDEX `teams_ix3` (`sub_league_id`),
  INDEX `teams_ix4` (`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;