One interesting feature in each of these tables is the ‘gender’ column. Leaving aside the question of why leagues, subleagues, or divisions would need to be gendered (at least in English), it does beg the question of whether, perhaps OOTP will ever consider mixed or even female-only leagues.
Creating the leagues table
The leagues table has a LOT of columns. Part of me thinks that I should figure out a way to truncate the data before loading it into the database because there’s just sooo many! And I really only need the first several. On the other hand, I would have to this every time I generated a data dump. There will never be so many leagues that this table will become too large, so I will leave it alone for now. Note that we are only interested in the first 4 columns:
CREATE TABLE `leagues` (
`league_id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`abbr` varchar(50) DEFAULT NULL,
`nation_id` int(11) DEFAULT NULL,
`language_id` int(11) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`historical_league` tinyint(4) DEFAULT NULL,
`logo_file_name` varchar(200) DEFAULT NULL,
`players_path` varchar(200) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`preferred_start_date` date DEFAULT NULL,
`pitcher_award_name` varchar(50) DEFAULT NULL,
`mvp_award_name` varchar(50) DEFAULT NULL,
`rookie_award_name` varchar(50) DEFAULT NULL,
`defense_award_name` varchar(50) DEFAULT NULL,
`fictional_players` tinyint(4) DEFAULT NULL,
`start_fantasy_draft` tinyint(4) DEFAULT NULL,
`trading_deadline` tinyint(4) DEFAULT NULL,
`winter_meetings` tinyint(4) DEFAULT NULL,
`arbitration_offering` tinyint(4) DEFAULT NULL,
`show_draft_pool` tinyint(4) DEFAULT NULL,
`rosters_expanded` tinyint(4) DEFAULT NULL,
`draft_date` date DEFAULT NULL,
`rule_5_draft_date` date DEFAULT NULL,
`roster_expand_date` date DEFAULT NULL,
`trade_deadline_date` date DEFAULT NULL,
`allstar_date` date DEFAULT NULL,
`days_until_deadline` int(11) DEFAULT NULL,
`next_draft_type` int(11) DEFAULT NULL,
`parent_league_id` int(11) DEFAULT NULL,
`league_state` smallint(6) DEFAULT NULL,
`season_year` int(11) DEFAULT NULL,
`historical_year` smallint(6) DEFAULT NULL,
`league_level` smallint(6) DEFAULT NULL,
`stats_detail` int(11) DEFAULT NULL,
`historical_import_path` varchar(200) DEFAULT NULL,
`foreigner_percentage` smallint(6) DEFAULT NULL,
`was_ootp6` tinyint(4) DEFAULT NULL,
`was_65` tinyint(4) DEFAULT NULL,
`allstar_game` tinyint(4) DEFAULT NULL,
`auto_schedule_allstar` tinyint(4) DEFAULT NULL,
`allstar_team_id0` int(11) DEFAULT NULL,
`allstar_team_id1` int(11) DEFAULT NULL,
`schedule_file_1` varchar(200) DEFAULT NULL,
`schedule_file_2` varchar(200) DEFAULT NULL,
`rules_rule_5` tinyint(4) DEFAULT NULL,
`rules_minor_league_options` tinyint(4) DEFAULT NULL,
`rules_trading` tinyint(4) DEFAULT NULL,
`rules_draft_pick_trading` tinyint(4) DEFAULT NULL,
`rules_financials` tinyint(4) DEFAULT NULL,
`rules_amateur_draft` tinyint(4) DEFAULT NULL,
`rules_fa_compensation` tinyint(4) DEFAULT NULL,
`rules_schedule_balanced` tinyint(4) DEFAULT NULL,
`rules_schedule_inter_league` tinyint(4) DEFAULT NULL,
`rules_schedule_force_start_day` tinyint(4) DEFAULT NULL,
`rules_trades_other_leagues` tinyint(4) DEFAULT NULL,
`rules_free_agents_from_other_leagues` tinyint(4) DEFAULT NULL,
`rules_free_agents_leave_other_leagues` tinyint(4) DEFAULT NULL,
`rules_allstar_game` tinyint(4) DEFAULT NULL,
`rules_spring_training` tinyint(4) DEFAULT NULL,
`rules_active_roster_limit` smallint(6) DEFAULT NULL,
`rules_secondary_roster_limit` smallint(6) DEFAULT NULL,
`rules_expanded_roster_limit` smallint(6) DEFAULT NULL,
`rules_min_service_days` smallint(6) DEFAULT NULL,
`rules_waiver_period_length` smallint(6) DEFAULT NULL,
`rules_dfa_period_length` smallint(6) DEFAULT NULL,
`rules_fa_minimum_years` smallint(6) DEFAULT NULL,
`rules_salary_arbitration_minimum_years` smallint(6) DEFAULT NULL,
`rules_minor_league_fa_minimum_years` smallint(6) DEFAULT NULL,
`rules_foreigner_limit` smallint(6) DEFAULT NULL,
`rules_foreigner_pitcher_limit` smallint(6) DEFAULT NULL,
`rules_foreigner_hitter_limit` smallint(6) DEFAULT NULL,
`rules_schedule_games_per_team` smallint(6) DEFAULT NULL,
`rules_schedule_typical_series` smallint(6) DEFAULT NULL,
`rules_schedule_preferred_start_day` smallint(6) DEFAULT NULL,
`rules_amateur_draft_rounds` smallint(6) DEFAULT NULL,
`rules_minimum_salary` int(11) DEFAULT NULL,
`rules_salary_cap` int(11) DEFAULT NULL,
`rules_player_salary0` int(11) DEFAULT NULL,
`rules_player_salary1` int(11) DEFAULT NULL,
`rules_player_salary2` int(11) DEFAULT NULL,
`rules_player_salary3` int(11) DEFAULT NULL,
`rules_player_salary4` int(11) DEFAULT NULL,
`rules_player_salary5` int(11) DEFAULT NULL,
`rules_player_salary6` int(11) DEFAULT NULL,
`rules_player_salary7` int(11) DEFAULT NULL,
`rules_average_coach_salary` int(11) DEFAULT NULL,
`rules_average_attendance` int(11) DEFAULT NULL,
`rules_average_national_media_contract` int(11) DEFAULT NULL,
`rules_cash_maximum` int(11) DEFAULT NULL,
`rules_average_ticket_price` double DEFAULT NULL,
`rules_revenue_sharing` tinyint(4) DEFAULT NULL,
`rules_national_media_contract_fixed` tinyint(4) DEFAULT NULL,
`rules_owner_decides_budget` tinyint(4) DEFAULT NULL,
`rules_schedule_auto_adjust_dates` tinyint(4) DEFAULT NULL,
`rules_historical_import_rookies` tinyint(4) DEFAULT NULL,
`avg_rating_contact` int(11) DEFAULT NULL,
`avg_rating_gap` int(11) DEFAULT NULL,
`avg_rating_power` int(11) DEFAULT NULL,
`avg_rating_eye` int(11) DEFAULT NULL,
`avg_rating_strikeouts` int(11) DEFAULT NULL,
`avg_rating_stuff` int(11) DEFAULT NULL,
`avg_rating_movement` int(11) DEFAULT NULL,
`avg_rating_control` int(11) DEFAULT NULL,
`avg_rating_fielding0` int(11) DEFAULT NULL,
`avg_rating_fielding1` int(11) DEFAULT NULL,
`avg_rating_fielding2` int(11) DEFAULT NULL,
`avg_rating_fielding3` int(11) DEFAULT NULL,
`avg_rating_fielding4` int(11) DEFAULT NULL,
`avg_rating_fielding5` int(11) DEFAULT NULL,
`avg_rating_fielding6` int(11) DEFAULT NULL,
`avg_rating_fielding7` int(11) DEFAULT NULL,
`avg_rating_fielding8` int(11) DEFAULT NULL,
`avg_rating_fielding9` int(11) DEFAULT NULL,
`avg_rating_overall` int(11) DEFAULT NULL,
`avg_rating_age` double DEFAULT NULL,
`league_totals_ab` int(11) DEFAULT NULL,
`league_totals_h` int(11) DEFAULT NULL,
`league_totals_d` int(11) DEFAULT NULL,
`league_totals_t` int(11) DEFAULT NULL,
`league_totals_hr` int(11) DEFAULT NULL,
`league_totals_bb` int(11) DEFAULT NULL,
`league_totals_hp` int(11) DEFAULT NULL,
`league_totals_k` int(11) DEFAULT NULL,
`league_totals_pa` int(11) DEFAULT NULL,
`league_totals_babip` double DEFAULT NULL,
`league_totals_mod_h` double DEFAULT NULL,
`league_totals_mod_d` double DEFAULT NULL,
`league_totals_mod_t` double DEFAULT NULL,
`league_totals_mod_hr` double DEFAULT NULL,
`league_totals_mod_bb` double DEFAULT NULL,
`league_totals_mod_hp` double DEFAULT NULL,
`league_totals_mod_k` double DEFAULT NULL,
`league_totals_mod_babip` double DEFAULT NULL,
`ml_equivalencies_avg` double DEFAULT NULL,
`ml_equivalencies_hr` double DEFAULT NULL,
`ml_equivalencies_eb` double DEFAULT NULL,
`ml_equivalencies_bb` double DEFAULT NULL,
`ml_equivalencies_k` double DEFAULT NULL,
`ml_equivalencies_hp` double DEFAULT NULL,
`player_creation_modifier_contact` double DEFAULT NULL,
`player_creation_modifier_gap` double DEFAULT NULL,
`player_creation_modifier_power` double DEFAULT NULL,
`player_creation_modifier_eye` double DEFAULT NULL,
`player_creation_modifier_strikeouts` double DEFAULT NULL,
`player_creation_modifier_stuff` double DEFAULT NULL,
`player_creation_modifier_movement` double DEFAULT NULL,
`player_creation_modifier_control` double DEFAULT NULL,
`player_creation_modifier_speed` double DEFAULT NULL,
`player_creation_modifier_fielding` double DEFAULT NULL,
`financial_coefficient` double DEFAULT NULL,
`world_start_year` int(11) DEFAULT NULL,
`current_date` date DEFAULT NULL,
`background_color_id` varchar(8) DEFAULT NULL,
`text_color_id` varchar(8) DEFAULT NULL,
`scouting_coach_id` int(11) DEFAULT NULL,
PRIMARY KEY (`league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Creating the sub_leagues table
The sub_leagues table is much more manageable. It serves to name each league’s sub_leagues and attach them to a parent league. Note here that I made a composite PK rather than adding an AUTO_INCREMENT:
CREATE TABLE `sub_leagues` (
`league_id` int(11) NOT NULL,
`sub_league_id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`abbr` varchar(50) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
`designated_hitter` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`league_id`,`sub_league_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Creating the divisions table
This one, too, is very straightforward. Again, a composite primary key rather than an AUTO_INCREMENT:
CREATE TABLE `divisions` (
`league_id` int(11) NOT NULL,
`sub_league_id` int(11) NOT NULL,
`division_id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
PRIMARY KEY (`league_id`,`sub_league_id`,`division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;