International Location Field(s)

[NMiller007]

I am building a database that needs to store international locations and I'm curious about how to best handle this. Right now, I have it set up as a text field with "Brooklyn, NY" or "Dublin, Ireland", but I'm sure at some point, I'll want to sort records by state or country. Should I just use three fields: city, state, country? What about Canadian provinces or other countries that don't quite fit ("Holywell, Wales, UK") that mold?

Is there some standard way these are handled that I am just missing? I searched Google for every word combination I could think of, so I'm wondering if I'm missing something simple.

Thank you,

Neil

 

[lespaul]

you definite don't want 'Brooklyn, NY' - that breaks normalization rules by having two pieces of data in the same field.

You could use 'Region' instead of State and still be able to capture three pieces of information.

Check out the fundamentals document below for more on the rules of normalization.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[NMiller007]

Thanks for the quick reply.

I didn't like the text field, but since I'm not really working with that field right now, I let it slide to get the information in.

I guess I'll go with the City, Region, Country combination to get the job done.

Thanks.

 

[IGPCS]

you could use 1 field and then you can make a query with the instr(Right(string,,)," ",1) and mid(right(String,1),1,sintr) function

to cut out the country or state from the field

or just use 3 fields

hope you get it

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY