1. The project that I am working on right now uses JSPs for the user interface, and Struts to connect the JSPs to Java classes running on the server. The Java classes use JDBC to query an Informix database through Stored Procedure Language (SPLs), which is an extension of SQL.
I have noticed in the almost three years that I have been working on this project that the SPLs never use boolean type parameters. Instead, we pass CHAR(1) parameters that are usually "Y" or "N". In addition, the vast majority of the tables defined in the Informix database that should be boolean are declared CHAR(1).
Why? It turns out that the JDBC class responsible for converting parameters in Java to the string to send to Informix did not translate Java boolean objects correctly; it was converting them to a SQL BIT type, and Informix apparently does not know about that type. It appears that the software engineers (and I use that term loosely) who hacked our current mess together could not figure out how to pass boolean types to SPLs, so they used CHAR(1) for boolean parameters--and did likewise in defining the tables in the database. It was a one line change to tell the class that formats the strings to convert Java boolean values to something that SPLs could recognize as a boolean parameter. Now I can pass Java boolean values to SPLs as BOOLEAN parameters.
Our database administrator tells me that Informix is more efficient in both storage space and processing time using booleans rather that CHAR(1). Every place that we use a CHAR(1) instead of a boolean means that Informix has to do a separate check to make sure that the field is "Y" or "N" (or whatever variant this particular table uses). These suboptimal uses of CHAR(1) occur in many hundreds of tables, and many of these tables have hundreds of thousands of rows, with vast numbers of transactions on a daily basis. (A user of our system accesses dozens of tables each time he or she loads an offender's records.) Pretty obviously, this is an area where changing the existing tables and SPLs to use booleans instead of CHAR(1) is likely to be a big gain for throughput.
2. I have spent much of the last year and a half working on something called the PreSentence Investigation module (PSI). After an offender has been convicted, Corrections does something called a PreSentence Investigation, which produces a report showing an offender's employment history, family history, previous criminal history, substance abuse history, medical history. The PSI report is provided to the judge so that he determine the appropriate sentence for this offender.
You read these reports and it is often quite difficult not to get angry at the bad parenting that clearly set some of these offenders up to fail. That doesn't mean that they don't need to be locked up--but it makes you realize that a society that cares not at all about values is destined for failure.
Anyway, a recurring problem is that there are dates associated with various events. When did you start using meth? When did you start using alcohol? When did you start using heroin? And the answers on many of these questions are pretty appalling. But these are necessarily approximations. The investigators are asking people to give them a date for events that happened ten or twenty years earlier, and these must necessarily be estimates.
The problem, unfortunately, is that the people who threw together the database definition some years ago assumed that all the dates in our system would be precisely known. For obvious reasons, the courts need to know which dates are dates, and which are guesses. But there's no easy way to specify this in an SQL DATE field.
So I came up with a very clever scheme for solving this problem, without redesigning all the tables: add a column called precision next to each date column in every table where there might be uncertainty. This value would be an enumeration identifying the precision of the corresponding DATE field: 0=precise date; 1 means, to the month; 2=year; 3=decade. On the input screens, the investigator can enter a date, and then select how precise this date is. When it comes time to print the PSI report, we can print various strings, depending on precision:
I think this is a very elegant solution to the problem of already having vast quantities of data in the system which you can't discard.