Thursday, November 1, 2007

Numeric vs. text fields in application design


By Mick Moignard

One thing I see all too often in application design, and this isn't just Lotus Notes application design, is the horrible misuse of the Numeric data type.

Indeed, I had an example of this just recently. We had a field that was defined all the way through the application as a numeric field, and it had "numeric" values. We needed to make a change to allow the user to select a field value from a keyword list, and as I'm sure you all know, keyword list fields are, by definition, text.

"That one usually comes home to roost about a week after going in to production."

That this field was really a text field was drummed in by the fact that the selections the end-user was making were indeed textual descriptions -- actually of the levels of health-care required in a given situation -- and the "numeric" value stored was an internal coded value, a synonym, for that.

Back in the day

When I was just starting out in programming, it was pointed out to me in no uncertain terms that the various numeric data types that mainframe PL/I supported were all there to enable arithmetic operations on their values. That there were many different representations of numbers, fixed-point decimal and various sizes of binary fields wasn't particularly relevant to the issue.

They all existed to provide a balance between number size, scale, precision, and, if you were doing decimal operations, accuracy -- that last because binary fields only generate approximate decimal values at the far right-hand end. But they all stored numeric values that allowed for computation.

Nowadays, we have in Lotus Notes far fewer choices of numeric fields, but the same rule should apply in application design and programming: that number fields should only be used to contain data that is to be manipulated arithmetically, and for no other purpose. There are a whole pile of good reasons for this.

Understanding emptiness

Firstly, conventional numbers don't really have the concept that a string does, of the value "". In Lotus Notes, a string containing "" means that the field has no value. Strictly, that is not the same as "blank". "Blank" implies that the field contains at least one space character, or " ".

Some programming languages like PL/I have the concept of fixed-length fields, and such a string field actually cannot contain the "" value at all. With a fixed length, assigning "" to it will generate a string of all blanks, and that's not the same as "empty". But more importantly for this argument, a number field containing 0, zero, has the numeric value zero, which is a real value and is most definitely not the same as "empty".