Need help deciding here?

[McDonald]

I want to be able to have cities states zipcodes and counties all fill in
automatically. Should I create:

A table for City
One for State (With full names and abbreviations)
one for counties
one for zipcodes
and create a relationship between them.
Or should I create them all into one table (CSaZC).
Cant really decide what I want to do.

 

[Larry]

-----Original Message-----
I want to be able to have cities states zipcodes and counties all fill in
automatically. Should I create:

A table for City
One for State (With full names and abbreviations)
one for counties
one for zipcodes
and create a relationship between them.
Or should I create them all into one table (CSaZC).
Cant really decide what I want to do.


.
If your going to be repeating state in one table you

should create seperate tables
Tbl_State
SID - state id (Primary Key)
SAbb - state abbreviation
SName - full name

Tbl_City
CID - City ID (Primary Key)
SID - State ID
CAbb - City abbreviation
CName - full name
ZipCOde - Zip Code

Create a relationship between the two tables

 

[John Vinson]

should create seperate tables
Tbl_State
SID - state id (Primary Key)
SAbb - state abbreviation
SName - full name

Actually, the two-letter state abbreviations make a perfectly good
Primary Key: they are unique, short, and stable. No need for an
autonumber ID.

Tbl_City
CID - City ID (Primary Key)
SID - State ID
CAbb - City abbreviation
CName - full name
ZipCOde - Zip Code

Note that many cities have multiple zip codes, and many zipcodes cover
multiple cities.

I'd suggest instead using

tblZip
ZIP - Primary Key
CityName - (USPS preferred city for this zip)
Sabb - state abbreviation, link to tbl_State

tblAltCity
ZIP - link to tblZip
CityName - alternate city names <joint 2-field Primary Key>