players birthdate, on career level
players height, weight, bathand, throwhand, on season level
players for each game, with status (active, d7, d15, etc)
coachrosters was only table that Access (thru ODBC) asked for unique key - I gave gameID, teamID, coachID
in coaches, new or no uniform number created new coachID - uniform number should go under coachrosters, as that can change from game to game (or be unavailable) one record for each gamedayID, use most commonly used name
try to parse umpire names into first & last?
team abbrev can repeat - must use code for unique identifier (other than user auto_increment)
rosters - add player status
add birthdate to players
pitchers - which team, home or road?
hits - distance isn't scaled, is still in pixels not feet
games - gameTime comes into Access with date imported before actual game start time
gameconditions - gameLength same as above in Access, perhaps can be converted to minutes
batters - playing for home or road?
batterhand is on season level, which is fine - would add throwhand, height, weight, primary position - make it look like a team's season roster
atbats - move batterHeight to team's season roster
umprosters only has 29 entries - doesn't seem to be populated correctly - I would make it same layout as coachrosters, with gameID, umpID, position
stadiums - insert venue_w_chan_loc?
teams - in dsl & vsl (rok) team abbrev up to 7 char
winter league dates wrap around - games in mon1 & mon2 go to season=year-1
When loading rok there were a lot of error messages, I think all for long team abbrev - but after there are several tables that Access (thru ODBC) says it can't find anymore to link to. I don't know if there might be a problem with the tables. I may delete the db and reload rok with correct seting to avoid error.
FLD_ID codes, showing which player is at each defensive position for each record in the atbats table
boxscore.xml contains the info to determine the starting lineup. and thus each team's fielders at the beginning of the game
<batter id="543173" pos="SS" bo="100">
is the last two digits of bo are "00" then that player started the game. In this case FLD6_ID=543173 when the away team is batting
These codes, for positions 2 thru 9, are stored for every record in the atbats table. When a defensive substitution is made, a new ID is entered in that field.
PROPER SEASON FOR WINTER LEAGUE
(New seasons starting March 1)
set season = year(gameDate);
IF year(gameDate) <=2 set season = year(gameDate)-1;
IF year(gameDate) >2 set season = year(gameDate);
Working on REGEXP to determine fielding from play description
If you are a great Reg Exp guy then maybe you can make something of the
descriptions like "Grady Sizemore grounds out, second baseman Brandon
Phillips to first baseman Joey Votto."
"second baseman" would key the FLD_CD, "Brandon Phililips" would preferably be replaced with the EliasID.
If I remember correctly from AWK, you can search if there is an occurence, but this example would be positive for both 2b and 1b. If you ask for the position at which the searched for string starts in the target, it returns the numerical position, zero if not found. Then the one with the smallest index would be the one the ball was hit to. The string between the position and the "to" would be the name.
I think the best thing would be for Gameday to add the fielder to the
Coding it in for us would make life simpler.
Yes, the summing fields are definitely there for that reason, to make
filtering by type easier and summing that type. But both should be
available. All of the 1/0 fields are derived from some description using a
regular expression for the most part. If you are good at those you should
be able to do anything you like.
4 fields - BATTEDBALL_CD (see Retro glossary for list), BATTEDBALL_TYPE (LD, FB, GB, PU), BUNT_FL & FOUL_FL can give you all the combinations.
I have no problem following the logic of the REGEXP tests in your SQL.