Why make your data live in a straight jacket?

Database designers have spent the past fifty years designing straight jackets for their data. I find this puzzling since designing a database to reflect the reality of a situation seems so much simpler. I will explain what I mean as we examine different snippets of databases. Most contemporary databases are confined to straight-jackets similar to the punched-card data record. The data record concept has been perpetuated by COBOL and other fixed-length, fixed field, data representations. I will discuss concepts that allow the real-world to be modeled.

There are many advantages to modeling reality. For one thing, relational database normalization is very nearly automatic. Having entities that exist once in the real-world, also exist only once in a database, ensures that bad data will seldom be stored in that database. (If erroneous data does slip in, it is very quickly found. As a corollary, the incorrectly entered data is quickly corrected.) Entire subsets of a schema may be reused in other databases because it is reality that’s being modeled.

When asked to illustrate what I mean by a real-world model, it is often sufficient to make a simple query in the context of an existing database. I ask to find all the different ways in which some specific entity is represented. In one university alumnus database I simply asked for all the unique, (or in SQL parlance distinct) City/State combinations in alumni’s home addresses with a specific postal code. The result was horrific! The query yielded more than 600 City/State combinations, only one of which was correct. This is the result of allowing each user to submit (or enter) this data. No database user should ever be asked to enter that sort of data!

When the data environment is correctly represented, data entry becomes a process of selecting from sets of alternatives. For instance, in the United States the postal codes partition (in a mathematical sense) the country. This means that once the postal code is known there is no reason to ask the user to supply the city name or the state name. Feedback to the user, in the form of a simple statement, is all that is needed to ensure that the correct postal code has been entered. For example; if the postal code is solicited and you enter 55604 when you meant to enter 55406, then popping Hovland, Minnesota (which has zip code 55604) up on the screen adjacent to the zip code entry is enough feedback to alert the user to correcting the postal code. If what the user was expecting was Minneapolis, Minnesota he/she would look at, and correct their postal code entry. (In an orally driven system it could be phrased as a question - Is that Hovland, Minnesota? When you respond No! the system would ask for the postal code again.)

Unfortunately there are a few postal codes in the United States that straddle state boundaries. When one of these is encountered disambiguation will be required. See Wikipedia:

Because ZIP codes are intended for efficient postal delivery, there are unusual cases where a ZIP code crosses state boundaries, such as a military facility spanning multiple states or remote areas of one state most easily serviced from an adjacent state. For example ZIP code 42223 spans Christian KY and Montgomery TN, and ZIP code 97635 spans Lake OR and Modoc CA.

Fortunately there are very few instances where ZIP code areas cross state boundaries.