-- MySQL dump 9.10
--
-- Host: localhost    Database: BDB
-- ------------------------------------------------------
-- Server version	4.0.17-standard

--
-- Table structure for table `Master`
--

DROP TABLE IF EXISTS Master;
CREATE TABLE Master (
  idxLahman int(9) NOT NULL auto_increment,
  birthYear int(4) default NULL,
  birthMonth int(2) default NULL,
  birthDay int(2) default NULL,
  birthCountry varchar(50) default NULL,
  birthState char(2) default NULL,
  birthCity varchar(50) default NULL,
  deathYear int(4) default NULL,
  deathMonth int(2) default NULL,
  deathDay int(2) default NULL,
  deathCountry varchar(50) default NULL,
  deathState char(2) default NULL,
  deathCity varchar(50) default NULL,
  nameFirst varchar(50) default NULL,
  nameLast varchar(50) NOT NULL default '',
  nameNote varchar(255) default NULL,
  nameGiven varchar(255) default NULL,
  nameNick varchar(255) default NULL,
  weight int(3) default NULL,
  height double(4,1) default NULL,
  bats enum('L','R','B') default NULL,
  throws enum('L','R','B') default NULL,
  debut date default NULL,
  finalGame date default NULL,
  PRIMARY KEY  (idxLahman),
  KEY idxLahman (idxLahman,nameLast,nameFirst),
  KEY idxLahman_2 (idxLahman,debut),
  KEY idxLahman_3 (idxLahman,birthYear,birthMonth,birthDay)
) TYPE=MyISAM;

--
-- Table structure for table `Teams`
--

DROP TABLE IF EXISTS Teams;
CREATE TABLE Teams (
  idxTeams int(9) NOT NULL default '0',
  yearID smallint(4) unsigned NOT NULL default '0',
  lgID char(2) NOT NULL default '',
  idxTeamsFranchises int(9) default NULL,
  divID char(1) default NULL,
  Rank smallint(3) unsigned NOT NULL default '0',
  G smallint(3) unsigned default NULL,
  Ghome int(3) default NULL,
  W smallint(3) unsigned default NULL,
  L smallint(3) unsigned default NULL,
  DivWin enum('Y','N') default NULL,
  WCWin enum('Y','N') default NULL,
  LgWin enum('Y','N') default NULL,
  WSWin enum('Y','N') default NULL,
  R smallint(4) unsigned default NULL,
  AB smallint(4) unsigned default NULL,
  H smallint(4) unsigned default NULL,
  2B smallint(4) unsigned default NULL,
  3B smallint(3) unsigned default NULL,
  HR smallint(3) unsigned default NULL,
  BB smallint(4) unsigned default NULL,
  SO smallint(4) unsigned default NULL,
  SB smallint(3) unsigned default NULL,
  CS smallint(3) unsigned default NULL,
  HBP smallint(3) default NULL,
  SF smallint(3) default NULL,
  RA smallint(4) unsigned default NULL,
  ER smallint(4) default NULL,
  ERA decimal(4,2) default NULL,
  CG smallint(3) unsigned default NULL,
  SHO smallint(3) unsigned default NULL,
  SV smallint(3) unsigned default NULL,
  IPouts int(5) default NULL,
  HA smallint(4) unsigned default NULL,
  HRA smallint(4) unsigned default NULL,
  BBA smallint(4) unsigned default NULL,
  SOA smallint(4) unsigned default NULL,
  E int(5) default NULL,
  DP int(4) default NULL,
  FP decimal(5,3) default NULL,
  name varchar(50) NOT NULL default '',
  park varchar(255) default NULL,
  attendance int(7) default NULL,
  BPF int(3) default NULL,
  PPF int(3) default NULL
) TYPE=MyISAM;

--
-- Table structure for table `TeamsFranchises`
--

DROP TABLE IF EXISTS TeamsFranchises;
CREATE TABLE TeamsFranchises (
  idxTeamsFranchises int(9) NOT NULL auto_increment,
  franchID char(3) NOT NULL default '',
  franchName varchar(50) NOT NULL default '',
  active enum('Y','N','NA') NOT NULL default 'Y',
  NAassoc char(3) default NULL,
  PRIMARY KEY  (idxTeamsFranchises)
) TYPE=MyISAM;

--
-- Table structure for table `TeamsHalf`
--

DROP TABLE IF EXISTS TeamsHalf;
CREATE TABLE TeamsHalf (
  idxTeamsHalf int(9) NOT NULL auto_increment,
  idxTeams int(9) NOT NULL default '0',
  Half enum('1','2','') NOT NULL default '',
  DivWin enum('Y','N') NOT NULL default 'N',
  Rank smallint(3) unsigned NOT NULL default '0',
  G smallint(3) unsigned default NULL,
  W smallint(3) unsigned default NULL,
  L smallint(3) unsigned default NULL,
  PRIMARY KEY  (idxTeamsHalf)
) TYPE=MyISAM;

--
-- Table structure for table `Batting`
--

DROP TABLE IF EXISTS Batting;
CREATE TABLE Batting (
  idxBatting int(9) NOT NULL auto_increment,
  idxLahman int(9) NOT NULL default '0',
  stint int(2) NOT NULL default '0',
  idxTeams int(9) NOT NULL default '0',
  G smallint(3) unsigned default NULL,
  AB smallint(3) NOT NULL default '0',
  R smallint(3) unsigned default NULL,
  H smallint(3) unsigned default NULL,
  2B smallint(3) unsigned default NULL,
  3B smallint(3) unsigned default NULL,
  HR smallint(3) unsigned NOT NULL default '0',
  RBI smallint(3) unsigned default NULL,
  SB smallint(3) unsigned default NULL,
  CS smallint(3) unsigned default NULL,
  BB smallint(3) unsigned default NULL,
  SO smallint(3) unsigned default NULL,
  IBB smallint(3) unsigned default NULL,
  HBP smallint(3) unsigned default NULL,
  SH smallint(3) unsigned default NULL,
  SF smallint(3) unsigned default NULL,
  GIDP smallint(3) unsigned default NULL,
  PRIMARY KEY  (idxBatting),
  KEY idxBatting (idxBatting,idxLahman),
  KEY idxTeams (idxTeams,idxLahman),
  KEY idxLahman (idxLahman,stint,idxTeams)
) TYPE=MyISAM;

--
-- Table structure for table `Pitching`
--

DROP TABLE IF EXISTS Pitching;
CREATE TABLE Pitching (
  idxPitching int(9) NOT NULL auto_increment,
  idxLahman int(9) NOT NULL default '0',
  stint int(2) NOT NULL default '0',
  idxTeams int(9) NOT NULL default '0',
  W smallint(2) unsigned default NULL,
  L smallint(2) unsigned default NULL,
  G smallint(3) unsigned default NULL,
  GS smallint(3) unsigned default NULL,
  CG smallint(3) unsigned default NULL,
  SHO smallint(3) unsigned default NULL,
  SV smallint(3) unsigned default NULL,
  IPouts int(5) unsigned default NULL,
  H smallint(3) unsigned default NULL,
  ER smallint(3) unsigned default NULL,
  HR smallint(3) unsigned default NULL,
  BB smallint(3) unsigned default NULL,
  SO smallint(3) unsigned default NULL,
  BAopp decimal(5,3) unsigned default NULL,
  ERA decimal(5,2) unsigned default NULL,
  IBB smallint(3) unsigned default NULL,
  WP smallint(3) unsigned default NULL,
  HBP smallint(3) unsigned default NULL,
  BK smallint(3) unsigned default NULL,
  BFP smallint(6) unsigned default NULL,
  GF smallint(3) unsigned default NULL,
  R smallint(3) unsigned default NULL,
  PRIMARY KEY  (idxPitching),
  KEY idxPitching (idxPitching,idxLahman),
  KEY idxLahman (idxLahman,stint,idxTeams),
  KEY idxTeams (idxTeams,idxLahman)
) TYPE=MyISAM;

--
-- Table structure for table `Fielding`
--

DROP TABLE IF EXISTS Fielding;
CREATE TABLE Fielding (
  idxFielding int(9) NOT NULL auto_increment,
  idxLahman int(9) NOT NULL default '0',
  stint int(2) NOT NULL default '0',
  idxTeams int(9) NOT NULL default '0',
  POS char(2) NOT NULL default '',
  G smallint(3) unsigned default NULL,
  GS int(3) default NULL,
  Innouts int(5) default NULL,
  PO smallint(3) unsigned default NULL,
  A smallint(3) unsigned default NULL,
  E smallint(2) unsigned default NULL,
  DP smallint(3) unsigned default NULL,
  PB int(3) default NULL,
  ZR double(5,3) default NULL,
  PRIMARY KEY  (idxFielding),
  KEY idxFielding (idxFielding,idxLahman,stint,idxTeams),
  KEY idxLahman (idxLahman,stint,idxTeams),
  KEY idxTeams (idxTeams,idxLahman),
  KEY idxLahman_2 (idxLahman,POS),
  KEY idxTeams_2 (idxTeams,POS)
) TYPE=MyISAM;

--
-- Table structure for table `FieldingOF`
--

DROP TABLE IF EXISTS FieldingOF;
CREATE TABLE FieldingOF (
  idxFieldingOF int(9) NOT NULL auto_increment,
  idxFielding int(9) NOT NULL default '0',
  Glf int(3) default NULL,
  Gcf int(3) default NULL,
  Grf int(3) default NULL,
  PRIMARY KEY  (idxFieldingOF),
  KEY idxFielding (idxFielding,idxFieldingOF)
) TYPE=MyISAM;

--
-- Table structure for table `Salaries`
--

DROP TABLE IF EXISTS Salaries;
CREATE TABLE Salaries (
  idxSalaries int(9) NOT NULL auto_increment,
  idxLahman int(9) NOT NULL default '0',
  idxTeams int(9) NOT NULL default '0',
  salary double(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (idxSalaries),
  KEY idxLahman (idxLahman,idxTeams),
  KEY idxTeams (idxTeams,idxLahman)
) TYPE=MyISAM;

--
-- Table structure for table `Transactions`
--

DROP TABLE IF EXISTS Transactions;
CREATE TABLE Transactions (
  tranID int(9) NOT NULL auto_increment,
  date date NOT NULL default '0000-00-00',
  transTime varchar(55) default NULL,
  approxIndic char(2) default NULL,
  retroTranID int(9) NOT NULL default '0',
  retroID varchar(255) NOT NULL default '',
  type varchar(5) NOT NULL default '',
  teamIDfrom varchar(55) default NULL,
  lgIDfrom varchar(55) default NULL,
  teamIDto varchar(55) default NULL,
  lgIDto varchar(55) default NULL,
  draftType varchar(5) NOT NULL default '',
  draftRound int(3) NOT NULL default '0',
  pickNumber int(5) NOT NULL default '0',
  info varchar(255) default NULL,
  PRIMARY KEY  (tranID),
  KEY teamFrom (teamIDfrom,lgIDfrom),
  KEY teamTo (teamIDto,lgIDto),
  KEY date (date,teamIDto),
  KEY date2 (date,teamIDfrom)
) TYPE=MyISAM;

--
-- Table structure for table `Managers`
--

DROP TABLE IF EXISTS Managers;
CREATE TABLE Managers (
  idxManagers int(9) NOT NULL auto_increment,
  idxLahman int(9) NOT NULL default '0',
  idxTeams int(9) NOT NULL default '0',
  inseason smallint(1) NOT NULL default '1',
  G smallint(3) NOT NULL default '0',
  W smallint(3) NOT NULL default '0',
  L smallint(3) NOT NULL default '0',
  rank smallint(1) NOT NULL default '0',
  plyrMgr enum('Y','N') default 'N',
  PRIMARY KEY  (idxManagers),
  KEY idxTeams (idxTeams,inseason),
  KEY idxLahman (idxLahman,idxTeams,inseason)
) TYPE=MyISAM;

--
-- Table structure for table `ManagersHalf`
--

DROP TABLE IF EXISTS ManagersHalf;
CREATE TABLE ManagersHalf (
  idxManagersHalf int(9) NOT NULL auto_increment,
  idxManagers int(9) NOT NULL default '0',
  Half smallint(1) NOT NULL default '1',
  DivWin enum('Y','N') default NULL,
  G smallint(3) NOT NULL default '0',
  W smallint(3) NOT NULL default '0',
  L smallint(3) NOT NULL default '0',
  PRIMARY KEY  (idxManagersHalf),
  KEY idxManagers (idxManagers,idxManagersHalf)
) TYPE=MyISAM;

--
-- Table structure for table `Allstar`
--

DROP TABLE IF EXISTS Allstar;
CREATE TABLE Allstar (
  idxAllstar int(9) NOT NULL auto_increment,
  yearID smallint(4) unsigned NOT NULL default '0',
  lgID char(2) NOT NULL default '',
  idxLahman int(9) NOT NULL default '0',
  PRIMARY KEY  (idxAllstar)
) TYPE=MyISAM;

--
-- Table structure for table `AwardsVotes`
--

DROP TABLE IF EXISTS AwardsVotes;
CREATE TABLE AwardsVotes (
  idxAwardsVotes int(9) NOT NULL auto_increment,
  awardID varchar(25) NOT NULL default '',
  yearID smallint(4) NOT NULL default '0',
  lgID char(2) NOT NULL default '',
  idxLahman int(9) NOT NULL default '0',
  pointsWon int(4) default NULL,
  pointsMax int(4) default NULL,
  votesFirst int(4) default NULL,
  PRIMARY KEY  (idxAwardsVotes)
) TYPE=MyISAM;

--
-- Table structure for table `AwardsWinners`
--

DROP TABLE IF EXISTS AwardsWinners;
CREATE TABLE AwardsWinners (
  idxAwardsWinners int(9) NOT NULL auto_increment,
  awardID varchar(25) NOT NULL default '',
  yearID smallint(4) NOT NULL default '0',
  lgID char(2) NOT NULL default '',
  idxLahman int(9) NOT NULL default '0',
  tie char(1) default NULL,
  notes varchar(100) default NULL,
  PRIMARY KEY  (idxAwardsWinners),
  KEY idxLahman (idxLahman),
  KEY awardID (awardID)
) TYPE=MyISAM;