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`
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`
