RE: Address fields and nulls
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 4 Feb 2008 11:16:02 -0800
There are different schools of thought on the legitimacy of NULL in a column
position in a row in a table, even amongst the great and the good. Date
considers them illegitimate, as only legitimate values of the attribute type
are allowed, and NULL is the absence of a value. Codd, as Date himself
points out, implied support for their use by his advocacy of a 3-way logic
(NULL = 'maybe').
At a more practical level whether you can allow Nulls in columns such as
your County column for instance depends on to what degree the tables are
normalized. This very question came up for a contact of mine in Paris who
was developing a database of members of an international organisation. In
some countries there are regional geographical units between city and
country, states in the USA, départements in France, cantons in Switzerland
etc, but in very small countries there is no regional structure.
A set of correctly normalized tables in this context would include Cities
(in this database all addresses included a 'city' even though it often might
better have been described as a town or village), Regions and Countries, but
if the RegionID foreign key column in a row in the Cities table is left Null
for a city in a country with no regional structure, then there is no way of
knowing which country the city is in, as the key of Countries is referenced
by a CountryID column in Regions, not by any column in Cities.
The answer is to define the Region column in Cities NOT NULL ('required' in
Access table design view) and in the Regions table have rows for each country
without a regional structure with a value of N/A in the region column and the
relevant value for the country in question in the CountryID column. So by
entering the relevant RegionID value in Cities for the N/A region of the
country in question the country in which the city is located is known.
So, in answer to your question, it partly depends on whether you are in the
Chris Date camp or, like most of us, are more cavalier in your adherence to
the principles of the relational model, and partly on whether in the context
any information is lost by the use of Nulls. In your case if your database
is restricted to addressees in Connecticut (assuming it is Hartford CT you
refer to) then obviously nothing is lost by allowing Nulls, but if your
database covers other states and the tables are correctly normalized, then
allowing a Null CountyID in a Cities (or whatever local unit you use) table
would be inappropriate.
Ken Sheridan
Stafford, England
"vlh" wrote:
Hello everyone,
Being new I have a basic question. In setting up a table that includes
addresses should I set the default value to "unknown" (or some other default
value) if a complete address is not given? For example, some address will be
provided without the county or without a second line for the address such as
apartment number or suite number.
I am hesitant to leave nulls as I may be asked to search for all addresses
that do NOT include Hartford county for example and don't want to miss all
those nulls for the participants that did not provide a county.
Thanks,
VLH
.
- Prev by Date: RE: move records from table to new table
- Next by Date: Re: Import and append a column from excel to an existing db
- Previous by thread: Re: Address fields and nulls
- Next by thread: Re: How do I move a column in Access to another position
- Index(es):
Relevant Pages
|
