[AccessD] Re: question on normalization

Hurray for the normalization freaks!  
Robert, I would like to have your zip-code database for the US.  I did 
download some files from the USPS that I can combine into the tables I 
need, but if you have that already done, it would save me quite a bit of 
work.  Thank you.
Tina
P.S. I did get the naming conventions zip - although I had to get it out 
of quarantine at my ISP - seems there have been some viri running around 
lately masquerading as zips.
T

Robert L. Stewart wrote:

> 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.
>
>
>