[AccessD] Re: question on normalization

Welcome fellow freak.  ;-)

Also, you should technically do this in addition:

tblCounty

CountyID    Autonumber  (PK)
CountyName  Text(30)  Unique Index

tblCountyRegion

CountyRegionID    Autonumber (PK)
CountyID          Long Integer
RegionID          Long Integer

Unique Index CountyID, RegionID

Change tblPostalCode to

tblPostalCode

PostalCodeID     AutoNumber (PK)
PostalCode       Text(10)
CityID           Long Integer
CountyRegionID   Long Integer

Unique index on PostalCode, CityID, CountyRegionID

That should take care of it.  ;-)

Robert

P.S.  When you look at some of the ISO standards
for this kins of information, it gets very interesting.


At 08:46 AM 3/12/2004 -0600, you wrote:
>Date: Fri, 12 Mar 2004 08:59:27 -0500
>From: "John Clark" <John.Clark at niagaracounty.com>
>Subject: [AccessD] Re: question on normalization
>To: <accessd at databaseadvisors.com>
>Message-ID: <s0517c56.071 at nebnov3.niagaracounty.com>
>Content-Type: text/plain; charset=US-ASCII
>
>This is actually what I was looking at--keeping the zips and the towns
>seperated.
>
>Maybe I'm a closet freak myself--hmmph!
>
>John W Clark
>
>
> >>> rl_stewart at highstream.net 3/12/2004 8:42:37 AM >>>
>
>John,
>
>I have a table for the US with all that
>in it.  Do you need it?
>
>For the normalization freaks (like me),
>here is a normalized design:
>
>tblPostalCode
>PostalCodeID  AutoNumber (PK)
>PostalCode    Text(10)
>CityID        Long Integer
>RegionID      Long Integer
>
>Unique index on PostalCode, CityID, RegionID
>
>tblCity
>CityID        Autonumber (PK)
>CityName      Text(30)
>
>Unique Index on CityName
>
>tblRegion
>RegionID      Autonumber (PK)
>RegionName    Text(30)
>CountryCode   Text(2)
>
>Unique Index on RegionName, CountryCode
>
>tblCountry
>CountryCode   Text(2) (PK)
>CountryName   Text(30)
>
>Unique index on CountryName
>
>
>Robert
>
>P.S.  The ISO standard is actually Division, if I remember
>correctly for the way I used region here.