CREATE TABLE `participant` (
  `participant_id` int(50) NOT NULL auto_increment,
  `lname` varchar(50) NOT NULL default '',
  `fname` varchar(50) NOT NULL default '',
  `street` varchar(50) default NULL,
  `city` varchar(50) default NULL,
  `state` varchar(50) default NULL,
  `zip` varchar(50) default NULL,
  `home_phone` varchar(50) default NULL,
  `day_phone` varchar(50) default NULL,
  `email` varchar(50) default NULL,
  `age` tinyint(50) default NULL,
  `shirt_size` varchar(50) default NULL,
  `entering_grade` varchar(50) default NULL,
  `home_coach` varchar(50) default NULL,
  `year` year(4) default NULL,
  `camp_session` int(50) default NULL,
  `school` varchar(50) default NULL,
  `team_id` int(50) NOT NULL default '0',
  `height_feet` int(50) default NULL,
  `height_inches` int(50) default NULL,
  `status` varchar(50) default NULL,
  PRIMARY KEY  (`participant_id`),
  KEY `indexlname` (`lname`),
  KEY `indexlnameSes` (`lname`,`camp_session`),
  KEY `indexfname` (`fname`),
  KEY `indexfnameSes` (`fname`,`camp_session`),
  KEY `indexcity` (`city`),
  KEY `indexcitySes` (`city`,`camp_session`),
  KEY `indexstate` (`state`),
  KEY `indexstateSes` (`state`,`camp_session`),
  KEY `indexshirt` (`shirt_size`),
  KEY `indexshirtSes` (`shirt_size`,`camp_session`),
  KEY `indexgrade` (`entering_grade`),
  KEY `indexgradeSes` (`entering_grade`,`camp_session`),
  KEY `indexschool` (`school`),
  KEY `indexschoolSes` (`school`,`camp_session`)
) TYPE=MyISAM

CREATE TABLE `resident` (
  `resident_id` int(50) NOT NULL auto_increment,
  `participant_id` int(50) NOT NULL default '0',
  `room_ext` varchar(50) default NULL,
  `assigned_roommate_id` int(50) default NULL,
  `room_number` varchar(50) default NULL,
  `dorm` varchar(50) default NULL,
  `roommate_pref_lname_1` varchar(50) default NULL,
  `roommate_pref_fname_1` varchar(50) default NULL,
  `roommate_pref_lname_2` varchar(50) default NULL,
  `roommate_pref_fname_2` varchar(50) default NULL,
  `roommate_pref_lname_3` varchar(50) default NULL,
  `roommate_pref_fname_3` varchar(50) default NULL,
  `pair_id` int(50) default '0',
  `camp_session` int(50) default '0',
  PRIMARY KEY  (`resident_id`)
) TYPE=MyISAM

CREATE TABLE `health` (
  `health_id` int(50) NOT NULL auto_increment,
  `participant_id` int(50) NOT NULL default '0',
  `insurance_policy` varchar(50) default NULL,
  `emergency_contact_phone` varchar(50) default NULL,
  `insurance_company` varchar(50) default NULL,
  `conditions` mediumtext,
  `medications` mediumtext,
  `notes` mediumtext,
  `insurance_id` varchar(50) default NULL,
  PRIMARY KEY  (`health_id`)
) TYPE=MyISAM 

CREATE TABLE `financial` (
  `financial_id` int(50) NOT NULL auto_increment,
  `deposit_paid` date default NULL,
  `payment` double default NULL,
  `balance_due` double default NULL,
  `participant_id` int(50) NOT NULL default '0',
  PRIMARY KEY  (`financial_id`)
) TYPE=MyISAM

CREATE TABLE `league` (
  `league_id` int(50) NOT NULL auto_increment,
  `league_name` varchar(50) NOT NULL default '',
  `commissioner` varchar(50) default NULL,
  `camp_session` int(50) default NULL,
  PRIMARY KEY  (`league_id`)
) TYPE=MyISAM

CREATE TABLE `team` (
  `team_id` int(50) NOT NULL auto_increment,
  `league_id` int(50) NOT NULL default '0',
  `team_name` varchar(50) default NULL,
  `wins` varchar(50) default NULL,
  `losses` varchar(50) default NULL,
  `coach1` varchar(50) default NULL,
  `coach2` varchar(50) default NULL,
  `camp_session` int(50) default NULL,
  PRIMARY KEY  (`team_id`)
) TYPE=MyISAM

CREATE TABLE `system_vars` (
  `id` int(50) default NULL,
  `resident_cost` int(50) default NULL,
  `day_cost` int(50) default NULL,
  `discount` int(50) default NULL,
  `both_discount` int(50) default NULL,
  `deposit` int(50) default NULL,
  `discount_date_1` date default NULL,
  `discount_date_2` date default NULL
) TYPE=MyISAM