Online Date Entry

One of the most frequently encountered problems is getting users to enter dates correctly. (This is only intended for dates which cover a large number of years.) To mitigate the problems and to simplify the process, I suggest that designers proceed as follows:
Limit the dates to a finite number of years – specify both the earliest acceptable year and the latest acceptable year. Ask the user to enter the year as a 4-digit number (YYYY). Immediately check if it is within your acceptable range. If not ask the user to reenter it.
Once an acceptable year has been entered, get the user to pick the month from a drop-down list that looks like this:

01	January
02	February
03	March
04	April
05	May
06	June
07	July
08	August
09	September
10	October
11	November
12	December

At this point the user only has 4 lists of numbers from which to choose a correct day of the month. The shortest list will be for February when the year entered is NOT a Leap Year (i.e. 1 to 28). When the year entered is a Leap Year and the month is February the list will be from 1 to 29. The other months are either 30-day months or 31-day months. The list from 1-30 can be presented for all 30-day months (April, June, September and November), and the list from 1 to 31 for the 31-day months (January, March, May, July, August, October and December). These should all be simple drop-down lists.

So, What is a Leap Year?

In every 400 years there are 97 leap years, into which an extra day is added. The Leap Year Day added is always February 29th. They were introduced to stop religious holidays from creeping through the seasons. If the year is evenly divisible by 400 it is a leap year. Any other year evenly divisible by 4, but not also evenly divisible by 100, is a leap year.

Database Corruption

The contents of many databases become corrupt over time. There are two main reasons for this: improper normalization and improper checking of users’ inputs. There are many cases when asking users to enter data will invite corruption. Two of the worst offenders are calendar dates and street addresses.

Calendar Dates

By soliciting a date correctly it is possible to preclude errors (without having to check them after they have been entered). Start by asking for the year. The context usually makes it possible to constrain the valid years to a small range. For example, if what you are asking for is the year of birth of a living adult the range can be restricted to, the present year – 130 to the present year – 18.

Once the year is entered and the month is chosen from a list – January to December – all that remains is to let the user select the day of the month. Depending on the month chosen and whether or not it is a leap year, the user can be asked to select the day of the month from a constrained list.

There are four different situations: 1 – 28 for Februaries in non leap years; 1 – 29 for Februaries in leap years; 1 – 30 in 30-day months (April, June, September and November); and 1 – 31 in 31-day months (January, March, May, July, August, October and December).

In the middle of the sixteenth century Luigi Lilio, the Vatican librarian, worked out how to keep the calendar more or less in synch with the seasons of the year. He had us slip an extra day into 97 years of a 400-year cycle. These 97 years are what we call leap years and the extra day is at the end of February.

His algorithm for determining if it is a leap year is straight forward. If the year is evenly divisible by 400, and if not a multiple of 400, then if the year is evenly divisible by 4 but not also evenly divisible by 100, then the year is a leap year.

Street Addresses

Another large contributor to database corruption is the street address. A company, Melissa Global Intelligence, provides a service which makes it possible to insure that all addresses entered are correct.

If the addresses are global start by asking the user for a country. If all the addresses are in the same country this step may be omitted. Now solicit the postal code (in the US that would be the zipcode). As complete a postal code as possible narrows the number of possibilities in the next step.

Each postal code only has a relatively small number of possible street names. Present the user with a sorted list of these street names and have them select the correct one. If you allow the user to enter the name it is subject to being misspelled, peculiarly abbreviated, or otherwise corrupted. All deliverable street numbers on the chosen street are known, and whether or not a suite or unit number is needed is also known. Allow the user to select a street number, and if necessary a unit number; - once more from constrained lists. Now there will be no need to check the input, and the database will not be corrupted.

You can either use a service like Melissa’s or construct your database to handle this.

If the addresses in your database are all in a small geographical area this becomes more simple. In my own work I also geocode each address, i.e. I include its geographic coordinates, making it very easy to find on any mapping system (Google Earth or Google Maps, etc.).

The use of domain tables reduces the size of the database considerably. For example, there are some 20 or more towns/cities in the US named Jackson. So by having a domain-table with City Names, the name Jackson only occurs in the database once regardless of which Jackson you are talking about. Similarly, County Names are repeated from state to state, so these too are stored in a domain-table. In the United States there are three different ways of spelling the County Name that sounds like ‘Allegany’ so there are three entries in the County Name domain table with the correct spelling connected to each state. Here is the phonetic query followed by the result from interrogating my own database:

select
   distinct `dm_countyname`.`countyname` AS `countyname`,
   `postalcode`.`state` AS `state`
from
   (`postalcode` join `dm_countyname`
   on((`postalcode`.`county` = `dm_countyname`.`county_id`)))
where
   (soundex(`dm_countyname`.`countyname`) = soundex('Alleghany'))
order by
   `dm_countyname`.`countyname`

Describing a Database

The reason that databases are incorrectly designed has a lot to do with the Data Description Languages (DDLs). Data description in COBOL, SQL, and XML all omit some crucial elements.

A database description names and identifies collections of similar things and what it is that you want to know about them. Ontologically, for a thing to exist in a database, something about that thing must be explicitly stored; that is, at least one property of that thing must be stored in the database. It could be as simple as an arbitrarily assigned identifier. For example, each person in a collection of people might be assigned a unique identifying number. In Mealy's terms, an example would be: 'this person (object) has an identifier (attribute) with a (value) of 3097.' Databases consist of primary objects, the sets of objects that are the reason for the database's existence; temporal objects, the sets of data about the primary objects that change over time; the data domains, the sets of values (or the sets of other objects) that specific attributes of the primary and temporal objects are defined over; and views, the user-oriented objects that make the database accessible to the end users.

Taking the attribute date of birth (DOB) as an example, there are many questions that must be asked:

Must every person have a DOB?
May one person have more than one DOB?
May two people have the same DOB?
What is the largest number of DsOB a single person may have?

More generally, three simpler questions may be used to determine these characteristics:

What is the minimum number of properties with this attribute that a single object may have?
What is the maximum number of properties with this attribute that a single object may have?
Must the properties with this attribute be unique for every single object in the set?

Simply stating that the minimum number of properties with a specific attribute is 1 tells us that every person must have at least one property with that attribute. On the other hand if the minimum number of properties with a specific attribute is 0, then we know that properties with that attribute are not required. Similarly, the maximum number of properties with an attribute is needed. Clearly, the maximum number of properties must be at least as large as the minimum number of properties.

If the maximum number of properties is greater then the minimum number of properties then some ordering discipline needs to be specified.

Simply stating that properties with a specific attribute are unique, means that no two people may have the same value for a property with that attribute.

Lastly, the domain of the values needs to be declared. A domain may simply be a list of values, or it may be one or more sets of objects.

Terminology

In 1967 George Mealy lamented that we had no common terminology for discussing data. Fortunately, the Computer History Museum provides a copy of his paper, Another look at data, here. Quoting from Mealy:

...we speak of some set of things, attributes of those things and values of attributes. Attributes are the same as relations, being a correspondence between the things and the values (which may also be things)...

...The notion of attribute should be distinguished from that of property. To say that something has a given property is to say that some attribute of that something has a certain value. Thus, when I say that a house is red, I mean that the value of its color attribute is red, not that I intend to identify the house with the universal concept of redness. Properties may be combined using the usual logical connectives to form new properties, unlike values. Thus, the tall, red house has a property not shared by the long, red house, except by accident. Its color attribute has the value red and its height attribute has the value tall.

Bill Anderson of the University of Texas has summarized and diagrammed Mealy's theories here. In the absence of anything more cogent I find Mealy's proposed terminology eminently useable and have used it since his paper appeared. Unfortunately, few people writing about databases use as well-defined a set of terms. His appeal for the need for Representation Independence goes completely unheeded by both database software and language developers. I urge all readers of this blog to download and read Another Look at Data.

Value-based and object-based properties

Properties having the same attribute should take their values from a very specific domain. There are two types of properties: those whose value-domain is simply a set of values represented by a specific data-type found in the computing environment – strings, integers, floating-point numbers, etc.; and those whose value-domain is the identifiers of a specific set of objects in the computing environment. I'll refer to these as value-based and object-based properties, respectively.

Every attribute must specify whether its properties are value-based or object-based and exactly which domain they take their 'values' from. For example, an attribute of a car might be its wheelbase and the corresponding value-based property might be (wheelbase, 162 inches). That same car might have an object-based property whose attribute is owned-by and the corresponding property might be (owned-by, person #3564) where person #3564 is an object in the database. The object-based property implies the existence of a reciprocal property in the set of people – with an attribute owns-car. Only one of the two properties, owned-by and owns-car, requires explicit representation in the database; the other can be derived easily from the index of the one that is stored.

Associative database access

Feldman and Rovner designed and implemented An ALGOL-based Associative Language (1968). They called it LEAP. They wanted to have an associative memory that would make it possible to -

...access data through a partial specification of its contents,...

Their associations all consisted of triples (and were implemented using a hash coding technique):

attribute of object is value

In essence this is very similar to what Mealy described:

object has property (attribute, value)

Wither databases?

In my paper Sets as a Model for Database Representation: Much Ado about Something, which I was invited to present at ACM Pacific 1975, I employed essentially the same structure. I proposed that two representations of a database be kept; one that stored every object that had a particular property (attribute, value —› object), and the other which stored every property for each object (object —› attribute, value). The utility of this representation isn't immediately evident: firstly, there are two complete representations of the database, each of which may simply be recreated from the other; secondly, each representation serves a different purpose, the mappings from properties to objects facilitate access to the contents of the database, and the mappings from objects to properties allow all of the properties of an object to be rapidly retrieved.

NoSQL databases try to achieve something similar but do not have - Atomicity, Consistency, Isolation, and Durability – they don't pass the ACID test! (In 1984 my company, symphysis incorporated, implemented an object-oriented, multi-processor-based, distributed database management system, for a Minneapolis-based company. It worked exceedingly well. Unfortunately the client decided to move to a different hardware platform. They decided to use the database management system provided by their new hardware supplier, rather than have us implement our system for the new hardware.)

Temporal data

Many tables in a database house what I will call temporal data. This data is related to the main objects of the database and changes over time. For example, in a database about people it includes: data about where a person lives, information about who a person works for, and what a person's name is. Each of these data may be different at different moments in time. In this modern age even a person's gender might change during their lifetime. It is necessary that temporal data be maintained as such, if a database is to model reality.

The places where people live shouldn't go away when a person moves; the companies that people work for don't go out of existence when the only person in the database who works for them, leaves them. If these places and companies exist in the real world, and are ever associated with the objects for which the database exists, they should be represented in the database. Even when they do cease to exist, the fact that at some time they did exist, should be reflected in a database. If these entities exist only once in the real world then they should exist only once in the database.

Consider the data stored about people: only a person's date of birth and date of death are static. Everything else about a person is temporal. Each person only requires an identifier and a date of birth! A person's names are temporal; at birth the person's parents usually give them names, but over a life-time the person's names may change. A name history is needed; a table storing the person's names for the duration of their time spent on this planet. Not only will this track legal changes of name but it may also be used for aliases. Each name-history entry will have to include a start date and optionally, a stop date. Again, we are trying to model reality – and people's names do change. People's names are also ordered and in some cultures they take a large number of names.

If your database is conscientiously updated all the time, and doesn't have a name history, then how will you find that undergraduate named Maxine Smythe who was in the class of 1972? She has had two husbands since you were class-mates, is now single, and has legally changed her name to something quite different. Keep your database up to date, but don't throw away the history. These temporal data can be very valuable.

The excuse used by most database designers is that for all practical purposes the data in their database may be considered static. I have yet to see one instance of a database, that has a lifespan of more than a few weeks, for which this is true! There is very little static data in most databases. Most of the data stored in most databases is temporal data.

Domain tables

How should City Names, State Names and Postal Codes be represented in a database being built for the USA? Let’s assume that we have a table representing the zip codes comprised of just zip-code, state, and city. The zip-code has all the right properties to be a primary key (but must be treated as text because some start with a zero). The two character state abbreviation should suffice to identify a state. There are fewer than 4,000 zip codes in any 1 state and no state has more than 200 different city names. Large numbers of the zip codes will have the same city name. In reality each city (or rather, city name) should exist only once. For this reason the city names should be removed from the table and should be stored in a table of their own. This new table has a unique property, it serves simply to identify which city name from among many city names is intended. This table defines a domain, the entire set of city names that might be used in zip codes. The primary key for this domain table (as I'll call such tables) is simply an integer and the set of primary keys is simply an index set viz. {1, 2, 3, 4, 5, … n}. Similarly, the state names can be stored in a domain table where the primary key is the two-character abbreviation for the state and the full state name is the other value. A third domain table that is made up of an index set primary key and the county names should be included for completeness. There are two kinds of domain table; those that are not user-extensible and those that are. These three domain tables need not be user-extensible.

Many states have cities with identical city names: for example, 20 states have a city called Jackson and 12 states have a city named Alexandria. By eliminating the duplicates in the domain table, the domain table will become much smaller and much more readily accessible. If a city name is found to be wrong then there are two possible cases: either it is indeed misspelled or two cities have phonetically similar names with different spellings. (An interesting case can be found in county names—Allegany in NY and MD, Alleghany in VA and NC, and Allegheny in PA.) In either case fixing the problem is simple, and once fixed, applies to all the uses of those city names in the database. The potential for bad data arises if the city name is stored in each row of a table. (Remember, our goal is to only have a single representation of everything; that includes cities' names and counties' names.)

Defining a view consistent with the needs of database programmers will make it simple for them to access the database. This entire database for the USA, including all indices, is less than 10 MB and the same tables may be plugged into any larger database. When a database is constructed like this it does truly represent reality and is not straight-jacketed. Programmers and users need never be aware of the underlying complexity. Below is a schema diagram of these tables (including some additional fields and a couple of views).

The referential integrity of this sub-schema is ensured by the three foreign keys associated with the zipcode table. They are all many-to-one relationships that will allow the domain contents to be shared by multiple zipcode rows. Each domain table has a specific context and need not be user-extensible. When a new state, county or city is created the database administrator can add the data.

The integrity of this small part of the database is assured and there will be no need for any user to alter any of the data in it. All entries in the domain tables should be case sensitive and should use both uppercase and lowercase letters as needed. A canonical form for entries into all fields in all tables should be established and enforced. The two views both include the city state and zip code; one (v_citystzip) uses the abbreviated state name while the other uses the complete state name.

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.