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.
2012-04-14
2012-04-11
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.
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.
2012-04-09
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:
Fortunately there are very few instances where ZIP code areas cross state boundaries.
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.
Subscribe to:
Posts (Atom)