Loading ...
Sorry, an error occurred while loading the content.
 

Re: 2012 Database

Expand Messages
  • aidanshealy
    I run a database where we do a lot of information with splits. I figured I would share the basics of the database design here, and you can see how we handle
    Message 1 of 16 , Jan 9, 2013
      I run a database where we do a lot of information with splits. I figured I would share the basics of the database design here, and you can see how we handle things. Not everything is perfect here, but it really does allow for you to see just about any information set from a game, and gives you a fairly easy way to query all of that data in whatever way you want to look at it.

      Just throwing out some ideas...



      CREATE TABLE `splits` (
      `season` smallint(2) NOT NULL DEFAULT '18',
      `game_month` smallint(2) NOT NULL DEFAULT '0',
      `game_day` smallint(2) NOT NULL DEFAULT '0',
      `game_year` smallint(4) NOT NULL DEFAULT '0',
      `boxscore_id` bigint(13) NOT NULL DEFAULT '0',
      `org` int(1) NOT NULL DEFAULT '0',
      `type` int(5) NOT NULL,
      `ballpark_name` varchar(50) NOT NULL DEFAULT '',
      `batter_id` smallint(5) NOT NULL DEFAULT '0',
      `batter_team_id` smallint(2) NOT NULL DEFAULT '0',
      `batter_position` char(2) DEFAULT '',
      `batter_bat` char(1) DEFAULT '',
      `home_away` char(1) NOT NULL,
      `pitcher_id` smallint(5) NOT NULL DEFAULT '0',
      `pitcher_team_id` smallint(2) NOT NULL DEFAULT '0',
      `boxscore_ordinal` int(3) NOT NULL DEFAULT '0',
      `inning` int(2) NOT NULL DEFAULT '0',
      `outs` mediumint(1) NOT NULL DEFAULT '0',
      `hitting_batting_order` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_pa` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_ab` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_s` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_db` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_tr` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_hr` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_bb` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_iw` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_k` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_hbp` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_sh` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_sf` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_rbi` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_ro1b` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_ro2b` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_ro3b` tinyint(1) NOT NULL DEFAULT '0',
      `hitting_strikes` int(1) NOT NULL DEFAULT '0',
      `hitting_balls` int(1) NOT NULL DEFAULT '0',
      `hitting_pitch_count` int(2) NOT NULL DEFAULT '0',
      `hitting_description` varchar(200) DEFAULT NULL,
      `original_notation` char(30) DEFAULT NULL,
      `baserunning_run_scored` tinyint(1) NOT NULL DEFAULT '0',
      `baserunning_sba` int(1) NOT NULL DEFAULT '0',
      `baserunning_sb` int(1) NOT NULL DEFAULT '0',
      `baserunning_cs` int(1) NOT NULL DEFAULT '0',
      `baserunning_pickoff` int(1) NOT NULL DEFAULT '0',
      `baserunning_base` char(1) NOT NULL DEFAULT '',
      `pitching_outs` smallint(2) DEFAULT NULL,
      `pitching_inn` double(3,1) NOT NULL DEFAULT '0.0',
      `pitching_h` int(2) NOT NULL DEFAULT '0',
      `pitching_r` int(2) NOT NULL DEFAULT '0',
      `pitching_er` int(2) NOT NULL DEFAULT '0',
      `pitching_bb` int(2) NOT NULL DEFAULT '0',
      `pitching_k` int(2) NOT NULL DEFAULT '0',
      `pitching_pch` int(3) NOT NULL DEFAULT '0',
      `pitching_str` int(3) NOT NULL DEFAULT '0',
      `pitching_era` double(6,2) NOT NULL DEFAULT '0.00',
      `pitching_started` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_finished` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_win` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_loss` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_hold` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_save` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_blown_save` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_cg` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_shutout` tinyint(1) NOT NULL DEFAULT '0',
      `pitching_order` tinyint(2) DEFAULT NULL,
      `fielder_pitcher` varchar(40) DEFAULT NULL,
      `fielder_catcher` varchar(40) DEFAULT NULL,
      `fielder_first` varchar(40) DEFAULT NULL,
      `fielder_second` varchar(40) DEFAULT NULL,
      `fielder_third` varchar(40) DEFAULT NULL,
      `fielder_short` varchar(40) DEFAULT NULL,
      `fielder_left` varchar(40) DEFAULT NULL,
      `fielder_center` varchar(40) DEFAULT NULL,
      `fielder_right` varchar(40) DEFAULT NULL,
      )
    Your message has been successfully submitted and would be delivered to recipients shortly.