Re: How should I type my data- ZIP codes and States/Regions

Remember that a zip code can be associated with a city which is in a county in a state. 5 zip codes could also be associated with the same city, county,state. Or, a zip could be a business/company in a county in a state. This might give you some background why a FK is needed and the State can't be included. As to a text code for the FK vs a system generated number I would go with the number because of the lang. that I use tend to be easier with numeric FKs.
At . http://www.usps.gov/ncsc/products/products.htm#publications are some postal documents that might help with your project. Unfortunately the documents on this web page all have pieces of what most people need with no one document covering in one place the simple definitions. Pub 28 for general standards I find is useful but Address information system (AIS) products technical guide has been very helpful. It does contain a data element definition section. Which clearly defines a Zip code and all it parts plus more. Under CH3 City state products you will find that to the post office a state abbreviation might not be a state but a "Non Postal Community Name, Former Postal, /Facility, or Place Name". A zip code can be classified as Unique or Non Unique. I hope this will point to some useful info.

Its to bad there is no data model of the US Postal Service data. Based on all these documents they could sure use it. My model so far for Address has 15 tables and I still don't have a clear definition yet. About once a year for the past 3 I have spend a week or so on this very thing. Anyone that has it please let me know. You will find when you model zip or zip+4 that the state is a minor reference table in the structure.

Have fun!
TomB
<chrisc_at_pittauto.com> wrote in message news:8esgve$iup$1_at_nnrp1.deja.com...
> In article <g5vbfsk9otqnajdhhg5r45qp6r79a97n6b_at_4ax.com>,
> Fernando <spamers_at_must.die> wrote:
> > On Thu, 13 Apr 2000 10:19:31 -0400, "Jon M." <Jonatmfr_at_eei.org> wrote:
> >
> >> In my experience it is far better to store zips as strings,
> >> especially if you ever intend to store international addresses
> >> (ever see a Canadian zip code?). I'm not sure I understand why
> >> you would even consider storing a state as a number.
> >
> > I guess he isn't sure if storing the states as strings (and repeating
> > n times the same string) or using a number: a foreign key from a table
> > consisting in state_id and state_name
> > // Fernando Rodriguez Romero

>

>

>
>
>