{"id":73,"date":"2017-09-21T18:00:19","date_gmt":"2017-09-21T18:00:19","guid":{"rendered":"http:\/\/www.ootp.cavebutter.net\/blog\/?p=73"},"modified":"2017-09-21T18:00:19","modified_gmt":"2017-09-21T18:00:19","slug":"tables-9-individual-batting-stats-states-and-team-affiliations","status":"publish","type":"post","link":"http:\/\/www.ootp.cavebutter.net\/blog\/archives\/73","title":{"rendered":"Tables 9: Individual Batting Stats, States, and Team Affiliations<\/font>"},"content":{"rendered":"

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. \u00a0However, I do see some use for them down the road.<\/p>\n

Creating players_individual_batting_stats table<\/strong><\/p>\n

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. \u00a0Significantly, this is not Plate Appearances and, therefore, does not include Walks, Sacrifice Hits, or Sacrifice Flies.<\/p>\n

CREATE TABLE `players_individual_batting_stats` (\r\n  `player_id` int(11) NOT NULL, #Batter\r\n  `opponent_id` int(11) NOT NULL, #Pitcher\r\n  `ab` smallint(6) DEFAULT NULL,\r\n  `h` smallint(6) DEFAULT NULL,\r\n  `hr` smallint(6) DEFAULT NULL,\r\n  PRIMARY KEY (`player_id`,`opponent_id`),\r\n  INDEX `pibs_ix1` (`opponent_id`)    \r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;<\/code><\/pre>\n
 <\/p>\n
Creating states table<\/strong><\/p>\n
Logically, this table belongs further up with nations and cities. \u00a0It simply defines states, which reside in nations. \u00a0Cities defines cities, which reside in states, which reside in nations. \u00a0Nations reside in continents, but I have not imported that table and don’t see the need to.<\/p>\n
CREATE TABLE `states` (\r\n  `state_id` int(11) NOT NULL,\r\n  `nation_id` int(11) NOT NULL,\r\n  `name` varchar(50) DEFAULT NULL,\r\n  `abbreviation` varchar(50) DEFAULT NULL,\r\n  `population` int(11) DEFAULT NULL,\r\n  `main_language_id` int(11) DEFAULT NULL,\r\n  PRIMARY KEY (`state_id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;<\/code><\/pre>\n
 <\/p>\n
Creating team_affiliations table<\/strong><\/p>\n
This is essentially a join table that establishes a relationship with an affiliate team. \u00a0The parent team (i.e. the Major League team) is the ‘affiliated_team_id’ and the minor league team is the ‘team_id’ team.<\/p>\n
Actually, it could be reversed – due to an oversight I didn’t include this data in the dump – just set up the table. \u00a0It won’t be used until all the stats are done anyway – and it will be used to help identify useful players in trade negotiations. \u00a0For 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. \u00a0I’d use this table to find players on teams affiliated with Seattle. \u00a0Will have to come back to this one later, obviously.<\/p>\n
CREATE TABLE `team_affiliations` (\r\n  `team_id` int(11) NOT NULL,\r\n  `affiliated_team_id` int(11) NOT NULL,\r\n  PRIMARY KEY (`team_id`,`affiliated_team_id`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
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. \u00a0However, 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… Continue reading Tables 9: Individual Batting Stats, States, and Team Affiliations<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[10],"tags":[14,7,4,22,20],"class_list":["post-73","post","type-post","status-publish","format-standard","hentry","category-tables","tag-batting","tag-mysql","tag-ootp","tag-players","tag-teams"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9cxb5-1b","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/73","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/comments?post=73"}],"version-history":[{"count":2,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/73\/revisions"}],"predecessor-version":[{"id":75,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/posts\/73\/revisions\/75"}],"wp:attachment":[{"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/media?parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/categories?post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ootp.cavebutter.net\/blog\/wp-json\/wp\/v2\/tags?post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}