Document Home


Previous Revising the Database Structure

The New Event Record Structure


(This page has been modified from its original version.)

The new structure that will be used to store game event information explicitly recognizes that there are characteristics of any given at bat that are best recorded at that level, and that the descriptive capability of the system can best be preserved by associating with that record a stream of records that describe the events that happened during that at bat and after, should the batter get on base. As there almost certainly be multiple events associated with an at bat, there will be two main tables associated with recording these events, at_bat and ab_events, and a third table to record events that occur on base. The two tables related to the current at bat will be related by their primary keys, the primary key of at_bat forming the basis of the primary key for ab_events, while the table recording events on base will retain the key for the runner's at bat, and will include a foreign key associating the event with the current at bat should that at bat be the precipitating factor in the event



A general impression of the manner in which this structure will be utilized can be gained by anticipating the way in which a batter's participation in a standard 6-4-3 double play would be recorded. The at_bat table would indicate that the ab_result was out - double play, and the ab_events table would record that the ball was put in play. The ob_events table would include two new records, one indicating that the batter, now a runner, was out at first base and the second indicating that the runner on first was forced at second


There will, to be sure, be some subtlety involved in the appropriate application of the association between an at bat and the on-base event. For example, it is quite possible for two runners to run into a double play without the at bat being the precipitating factor in the play. I will discuss such considerations as I design the interface. Note also that it would be perfectly possible to combine the ab_events and the ob_events table. The assoc_ab column would be blank for any record associated with the at bat, but other than that the two table structures are identical. I decided to leave them distinct primarily to maintain the conceptual distinction between events occurring at the plate and those occurring on base.


This design can reasonably model most any offensive context. (I say most because I am sure that there is something that did not occur to me as I contemplated this design. It should be relatively easy, however, to modify one table or another to rectify any such oversights.) Constructing a defensive sequence from a set of records is, however, likely to be more problematic. A bit of reflection on the double play scenario I used as an example above should serve to illustrate the point. Just as the previous participant_game structure was adequate for reconstructing a narrative representation of the game but limited in its support for deeper analysis, so viewing game records in this at bat-oriented structure has limitations for providing a basis for analysis from a defensive perspective. It is better than relying on an interpretation of the participant_game narrative, but just as the at bat is the base event for most offensive analysis, so the fielding chance is the base event for most defensive analysis. The reader can probably see where this is going; it seems to me that making the next step to support defensive analysis would entail the development of a parallel structure behind the interface, recording events from a defensive perspective. That being said, I am not going to do that at this point. It might be instructive, however, to keep the subject in mind as the development of this interface proceeds, because it is not at all unlikely that I will address that at some point.


Now that I have the structure for the new database tables, I can turn my hand to the creation of the interface through which the much more rich level of detail embodied there can be reasonably managed.


Next: The New Entry Interface.