I know that the readers out there who have gotten to this point are an incredibly savvy bunch. Almost by definition. Anyone who has gotten to this point must have an extraordinarily fine appreciation for the "real deal", and a razor-sharp mind. Therefore, it comes as absolutely no surprise to me that I can sense a growing unease about the ability of the information stored in the event and event_result tables to adequately represent the game detail implicit in the new structure. Clearly, the events recorded for the results of an at-bat and those that should be recorded for a runner on base are considerably different. At a minimum, it makes absolutely no sense to scroll through a long series of options when only a relative few apply to any given context. Therefore, before I launch into deeper work on the interface I want to put into place a set of codes that both makes sense and can be readily extended to cover events that have not occurred to me.

The strategy I am going to employ in assigning these codes will involve nesting the classes under which individual items will be grouped within the codes themselves. This will allow me to store the codes and their descriptions in a single table, selecting those appropriate to a given context by passing the appropriate value to a placeholder in a prepared database statement. In that sense, this will be something of a model for the manner in which a single database statement can serve multiple purposes.

The basic structure that I will employ here will therefore be something like this, albeit somewhat more sophisticated:

The value of a structure like this lies primarily in its ability to force the creation of unique codes, and thus to help promote the integrity of the information stored in the system. The code itself becomes the primary key for the table, and specification that it be unique will prevent the insertion, inadvertent or otherwise, of a second row with the same code. A second benefit of this layout lies in the potential for streamlining both queries and the manipulation of the resultant result sets. As the code value incorporates all of the pertinent levels of detail, a query to return all of the on base events that fall into a certain class need not require a link to the source table holding the codes. In sql, such a link is known as a join, and every join added to a query results in slower response and the consumption of more computer resources, because the database server must assemble the composite record before it can return the result. (While more sophisticated database servers employ a variety of techniques to minimize the toll required by this process, a query requiring joins still takes longer than one that does not.) There will be times in any reasonably complex application when a join is required to return the desired information. After all, virtually all database engines are relational databases; establishing relationships between tables is inherent in the design of a relational system. At the same time, measures such as this can reduce the number of joins required, thus optimizing overall system response and reducing the complexity of those statements that do require joins.

The actual assignation of the codes is the kind of drudge work that makes everything else easier if done adequately. As is the case with virtually everything, the first 80% takes 20% of the effort and the last 20% requires 80%. Anticipating the set of events that will be recorded in the ab_results table is not all that taxing:

The final versions of the codes assigned will be more finely-grained, in that they will, for example, include the position that committed an error. It is worth restating, however, that this is just enriching the detail of the events recorded. In the absence of the more detailed codes, more specific information could simp[ly be recorded in the notes associated with that record.

The events that will be recorded in the ab_event table essentially represent the stream of balls and strikes that will be associated with an individual at bat. This description, however, belies the underlying detail required to automatically tabulate the state of the at bat.

The exception to which I referred earlier is the context in which a batter with two strikes attempts a bunt which goes foul. To prevent batters from bunting pitch after pitch foul to wear out the pitcher, this is recorded as a strikeout. Again, the specific context of the strikeout would be recorded in the associated text box.

Defining the events to be recorded for runners on base is also more difficult than it might seem. The following provides a start.

A little reflection reveals that these codes should encapsulate somewhat more detail. As an example, during a season fans of any given team might complain that their team is playing "station to station" baseball, which means that the team's runners have gotten into a patterm in which they advance only when there is a base hit, and then often only by the number of bases that the hitter was able to garner from the hit. Potentially, of course, the actions of runners on the basepaths are much more fluid than that. In at least half of the circumstances in which a batter singles with a runner on first, for example, that runner will advance to third. There is no intermediate event to associate with the move from second to third; the runner advanced from first to third as a result of the hit. Therefore, the table of on base event codes must have seperate values distinquishing the potential locations of the runner as a result of the play. I gave consideration to two potential formulations of these codes, in the first specifying the number of bases advanced and in the second specifying the endpoint of the runner, in the end adopting the latter to ease the load on the server by reducing the contexts to which the server must respond. If I were to implement the first type of code values, the task of automatically registering key events, such as a run scored and a RBI credited to the batter, would be complicated by the necessity of comparing the nature of the recorded event with the state of the bases at the time of the event. In contrast, by specifying the endpoint of the runner's advance many of those items can be registered in a straightforward manner. It is worthwhile, however, to briefly engage in what Albert Einstein used to call a "thought experiment". Imagine, if you will, <nod to The Twilight Zone> that the number of bases runners advanced as a result of a given individual's at bats was a key piece of baseball information. (While this may seem to be important, it is likely to primarily reflect the relative speed of the individuals preceding the subject individual in the batting order, so two individuals of equivalent effectiveness at the plate would have significantly different values for this statistic.) If, however, this were important information, the first form of code value assignment would better lend itself to the accumulation of those values.

In any event, the initial form of the ob_events codes will look something like this:

For those who may question my concern with maintaining straightforward logic on the server, in the past several weeks I have been experimenting with a couple of collaborative environments that can be run an an apache server, specifically twiki and scoop. Each is interesting, but as a result of this experimentation the additional load on the server exacted by the construction of an environment capable of responding to a wide range of request became very obvious. in both cases, the machine I was employing to host the environment was several times as powerful as the root ralphzilla machine, yet the application was less responsive. This should not be interpreted as a criticism of those packages, the process of building an environment that can be customized to represent a free-form collaborative environment is a daunting one, requiring that the server respond to a wide range of requests. It does, however, illustrate the load that such entails. As the application I am building here can be constructed in such a manner as to be very specific in the manner in which requests are handled, the server's responses will be much quicker. The price that is required is the kind of specificity I am employing in the development of these codes.

Note the final code in the table above, described as "Advance on Play". This will be used to describe a circumstance in which a user advances, or attempts to, as another play is taking place. A common example of this often occurs when a batter hits a sacrifice fly with more than one runner on base. If an attempt is made to throw the lead runner out, generally at the plate, the remaining runner(s) may attempt to advance a base. Such plays are independent actions within the context of the game, not associated with other events beyond the fact that they happen while a play is being made on another runner. It is therefore my intent that such events will be tied to the events around them by simply including a short description in the associated text box.

When I wrote the draft of this chapter I had intended to simply link to a text file containing the first version of the codes I am going to run into the relevant table (Release 1.0 <grin>), rasoning that there was no point in including a big table with more than 200 seperate rows in the document itself. As I have finalized the chapter, however, I have come to believe that such a listing will have real value, that a quick scan down a formatted listing of the codes can impart a visceral understanding of their structure that will serve to augment the written description.

Before I do that, however, I want to explicitly specify the internal structure of the code values.

Again, it is worth noting that this structure is not at all normalized. While some degree of normalization would reduce the number of codes in this table, that benefit would be far outweighed by the overhead engendered by adding one or two joins to any statements accessing records that met specific criteria. If this system were part of a larger system, and one of the non-normalized elements of this table represented the link between parts of the system, that overhead would be justified.

Given that introduction, here are the code values:

In the next section I will begin to fill out the structure of the data entry interface.