Tables 10: Team History Financials and Team Relations

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;