How should I store city, state values for addresses, please help.

From: JBLi (newsgroup_at_lidesigners.com)
Date: 02/24/04 

Date: Tue, 24 Feb 2004 14:56:02 -0500

Hi All,

     I am writting an real estate website for a company. I need some
advices on how should I store fields like city, and state for addresses.

     Originally, I was thinking to store the city and state as numeric
fields (in the main Houses table) and having extra one table for cities and
one table for states; such that the city and state fields contains the
identity (column) values from the tables.

     The problem is that my client already has an old website database which
using the same idea as above. I need to of course, transfer all the
property records from the database to my new database. So if the cities
and states tables are recreated in my new database, the indentity column
values will not match those (tables) in the old database, which were already
in use in the property records of the old database (the Houses table). That
is, let say one property record's city field value is 123 (which is an
identity column value in the old database for the city, Brooklyn). And let
say I create the cities table in my new database and having a city record
for Brooklyn with value 456. If I transfer the property record to the new
database, as you can see, the city value 123 is not the same as 456. I
cannot really lookup by the city name at this point since the old database
may contain bad city name spellings.

     Thanks for any suggestions. I may consider to store Cities and states
as plain strings to avoid future problems.

Regards,

JB

Relevant Pages

  • Re: How should I store city, state values for addresses, please help.
    ... > advices on how should I store fields like city, ... > fields and having extra one table for cities ... > property records from the database to my new database. ... > in use in the property records of the old database. ...
    (microsoft.public.sqlserver.programming)
  • Re: What is Pick anyway?
    ... POSTALCODE, CITY and STATE. ... >>> the scenario as typical of database work. ... >> programs, Datastage, uses Universe as its engine. ...
    (comp.databases.theory)
  • RE: Birding Life List design
    ... clarified in the first post - I forgot there was region information in there. ... The database link now points to the updated database. ... the city table would be populated as I have sightings in that city. ... form that allows me to enter one or more photo records. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Query Question
    ... OR you could put each city in the table and associate the city with a ... Then you could use the RegionName field to extract the cities in a region. ... > I have a database of Texas companies with a lot of different information. ... > I'm trying to run a query pulling information from just Dallas & surrounding ...
    (microsoft.public.access.queries)
  • Re: How should I store city, state values for addresses, please help.
    ... >> advices on how should I store fields like city, ... >> property records from the database to my new database. ... >> say I create the cities table in my new database and having a city ...
    (microsoft.public.sqlserver.programming)