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.