It’s a question of normalization. Say there are two rows in a table
as follows. This is simplified of course as in reality there would be
more columns:
LastName FirstName AddressLine1 City
_____________________________________________
Brown Gordon 10 DowningStreet London
Darling Alistair 11 DowningStreet London
Strictly speaking this table is not fully normalized on several
counts:
1. The AddressLine1 column contains tow separate attributes, the
house number and the street name. Each column should represent only
one attribute type, so really there should be separate PropertyNumber
and StreetName columns (or in fact numeric StreetID as street names
can be duplicated).
2. The City column is redundant as the once the StreetID is known the
City is known (or again the CityID in fact as city names are also
duplicated).
3. Having the names of the people also introduces redundancy as they
might have multiple addresses; the examples I've used, the British
Prime Minister and Chancellor of the Exchequer, both have official
country houses as well as their official London addresses.
So to fully normalize the above table it would be decomposed into
People, Addresses, Streets, and Cities tables. In Adddresses the
person would be represented by a PersonID column referncing the key of
the people table, and would be selected from a combo box's list.
Similarly the street would be represented by a SteetID column
referencing the key of Streets, and also selected from a combo box's
list. In each case the combo box would show the person's and street's
names City need not be included as this is determined by StreetID and
is known via the relationship form Streets to Cities by means of a
CityID column in Streets.
By enforcing referential integrity in the relationships between the
tables only valid people, and streets can be entered in Addresses,
i.e. the PersonID and StreetID values must exist in People and
Streets, so, in my simplified example, the only value to be typed in
would be the property number and/or a property name in another column
to allow for named properties with or without a number (there's also
Gina's point about apartments of course).
So that's how data integrity is protected in a relational database,
and deals with the sort of possible inconsistencies you've raised.
Here in the UK we'd often take it a step further as our Post Codes are
very specific (mine is one side of my street) so property addresses
can be stored as PostCode and HouseNameOrNumber (plus apartment number
where necessary). Online forms here often have an 'auto-fill address'
option in which you enter only your post code and then select your
house-name-or-number from a correlated combo box. This is not so easy
in many other countries, however, where the postal codes (at least
that part of the code in common use) is less geographically specific.
It has to be said, however, that many databases containing address
data are not normalized to the extent described above and contain
redundancy, e.g. including both the CityID and State in the address
table although the latter is determined by the former and therefore
redundant. It’s a matter of judgement how far you want to normalize
such a table. A common approach is to rely on the interface to
protect against inconsistencies, e.g. using correlated state and city
combo boxes so that, having selected a state the user is presented
with a list of cities in that state only. Reliance on the interface
in this way is not ideal, however, and it is in fact possible to
select locations (or any other similarly hierarchical values) in this
'top down' way while maintaining a normalized set of tables. I posted
a demo of various means of doing this at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
It uses the local administrative units of County, District and Parish
in my area, but the principle is the same for any similar hierarchy.
Ken Sheridan
Stafford, England
