Creating players table
This table contains all of the biographic and demographic detail of all players. A couple notes here.
- ‘team_id’ and ‘league_id’ refer to the team and league of a given player at the time of the last data dump. We will be ignoring these columns from this table in all of our career and prior-year stats. They will be useful when we’re looking for a player’s team mid-season.
- At some point, I posted a request for a new feature. The request was that although we can view a coach’s former player profile, we can’t see a player’s coach profile if that player had retired and became a coach. That feature can be implemented by adding a column ‘coach_id’ to this table.
CREATE TABLE `players` (
`player_id` int(11) NOT NULL,
`team_id` int(11) DEFAULT NULL,
`league_id` int(11) DEFAULT NULL,
`first_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`nick_name` varchar(50) DEFAULT NULL,
`age` smallint(6) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
`city_of_birth_id` int(11) DEFAULT NULL,
`nation_id` int(11) DEFAULT NULL,
`second_nation_id` int(11) DEFAULT NULL,
`weight` smallint(6) DEFAULT NULL,
`height` smallint(6) DEFAULT NULL,
`retired` tinyint(4) DEFAULT NULL,
`free_agent` tinyint(4) DEFAULT NULL,
`last_league_id` int(11) DEFAULT NULL,
`last_team_id` int(11) DEFAULT NULL,
`organization_id` int(11) DEFAULT NULL,
`last_organization_id` int(11) DEFAULT NULL,
`language_ids0` int(11) DEFAULT NULL,
`language_ids1` int(11) DEFAULT NULL,
`uniform_number` smallint(6) DEFAULT NULL,
`experience` smallint(6) DEFAULT NULL,
`person_type` smallint(6) DEFAULT NULL,
`bats` smallint(6) DEFAULT NULL,
`throws` smallint(6) DEFAULT NULL,
`personality_greed` smallint(6) DEFAULT NULL,
`personality_loyalty` smallint(6) DEFAULT NULL,
`personality_play_for_winner` smallint(6) DEFAULT NULL,
`personality_work_ethic` smallint(6) DEFAULT NULL,
`personality_intelligence` smallint(6) DEFAULT NULL,
`personality_leader` smallint(6) DEFAULT NULL,
`historical_id` varchar(50) DEFAULT NULL,
`historical_team_id` varchar(50) DEFAULT NULL,
`best_contract_offer_id` int(11) DEFAULT NULL,
`injury_is_injured` tinyint(4) DEFAULT NULL,
`injury_dtd_injury` tinyint(4) DEFAULT NULL,
`injury_career_ending` tinyint(4) DEFAULT NULL,
`injury_dl_left` smallint(6) DEFAULT NULL,
`injury_dl_playoff_round` smallint(6) DEFAULT NULL,
`injury_left` smallint(6) DEFAULT NULL,
`dtd_injury_effect` smallint(6) DEFAULT NULL,
`injury_id` int(11) DEFAULT NULL,
`prone_overall` smallint(6) DEFAULT NULL,
`prone_leg` smallint(6) DEFAULT NULL,
`prone_back` smallint(6) DEFAULT NULL,
`prone_arm` smallint(6) DEFAULT NULL,
`fatigue_pitches0` smallint(6) DEFAULT NULL,
`fatigue_pitches1` smallint(6) DEFAULT NULL,
`fatigue_pitches2` smallint(6) DEFAULT NULL,
`fatigue_pitches3` smallint(6) DEFAULT NULL,
`fatigue_pitches4` smallint(6) DEFAULT NULL,
`fatigue_pitches5` smallint(6) DEFAULT NULL,
`fatigue_points` smallint(6) DEFAULT NULL,
`fatigue_played_today` tinyint(4) DEFAULT NULL,
`running_ratings_speed` smallint(6) DEFAULT NULL,
`running_ratings_stealing` smallint(6) DEFAULT NULL,
`running_ratings_baserunning` smallint(6) DEFAULT NULL,
`position` smallint(6) DEFAULT NULL,
`role` smallint(6) DEFAULT NULL,
`college` tinyint(4) DEFAULT NULL,
`draft_year` smallint(6) DEFAULT NULL,
`draft_round` smallint(6) DEFAULT NULL,
`draft_supplemental` tinyint(4) DEFAULT NULL,
`draft_pick` smallint(6) DEFAULT NULL,
`draft_overall_pick` smallint(6) DEFAULT NULL,
`draft_eligible` tinyint(4) DEFAULT NULL,
`hidden` tinyint(4) DEFAULT NULL,
`draft_league_id` int(11) DEFAULT NULL,
`draft_team_id` int(11) DEFAULT NULL,
`turned_coach` tinyint(4) DEFAULT NULL,
`hall_of_fame` tinyint(4) DEFAULT NULL,
`rust` smallint(6) DEFAULT NULL,
`inducted` smallint(6) DEFAULT NULL,
`strategy_override_team` tinyint(4) DEFAULT NULL,
`strategy_stealing` int(11) DEFAULT NULL,
`strategy_running` int(11) DEFAULT NULL,
`strategy_bunt_for_hit` int(11) DEFAULT NULL,
`strategy_sac_bunt` int(11) DEFAULT NULL,
`strategy_hit_run` int(11) DEFAULT NULL,
`strategy_hook_start` int(11) DEFAULT NULL,
`strategy_hook_relief` int(11) DEFAULT NULL,
`strategy_pitch_count` int(11) DEFAULT NULL,
`strategy_only_allow_single_inning_saves` tinyint(4) DEFAULT NULL,
`strategy_pitch_around` int(11) DEFAULT NULL,
`strategy_sticky_lineup` tinyint(4) DEFAULT NULL,
`strategy_sticky_position` tinyint(4) DEFAULT NULL,
`strategy_no_pinch_if_rested` tinyint(4) DEFAULT NULL,
`strategy_never_pinch_hit` tinyint(4) DEFAULT NULL,
`strategy_defensive_sub` tinyint(4) DEFAULT NULL,
`strategy_never_defensive_sub_for` tinyint(4) DEFAULT NULL,
`local_pop` smallint(6) DEFAULT NULL,
`national_pop` smallint(6) DEFAULT NULL,
`draft_protected` tinyint(4) DEFAULT NULL,
`morale` smallint(6) DEFAULT NULL,
`morale_player_performance` smallint(6) DEFAULT NULL,
`morale_team_performance` smallint(6) DEFAULT NULL,
`morale_team_transactions` smallint(6) DEFAULT NULL,
`expectation` smallint(6) DEFAULT NULL,
`morale_player_role` smallint(6) DEFAULT NULL,
PRIMARY KEY (`player_id`),
INDEX `players_ix1` (`team_id`),
INDEX `players_ix2` (`league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;