Posted by Toby A Inkster on 05/25/07 18:27

Jeff North wrote:

> Initially the client only wants state by state franchisees but I can
> see that this could eventually be scaled to districts/areas.
>
> To compound matters, my client is wanting this to work in any country.

Firstly, not all countries have "states" -- certainly the UK doesn't. Some
countries have regional units similar to states -- e.g. France has
"departments". Other countries (small ones mainly) don't have any regional
subdivisions.

For encoding administrative areas in a database, I'd recommend using
ISO 8859, which provides two character codes for every country. Most
countries then define subcodes within ISO 8859 for regions within their
country.

For example, here are a few codes:

Australia: AU
United Kingdom: GB
Brighton & Hove: GB-BNH
New South Wales: AU-NSW

There is more information, and a full list of ISO codes at:
http://en.wikipedia.org/wiki/ISO_8859-1

However, the US local codes only go down to state level, so if you think
you're likely to need to subdivide further than ISO 8859 allows, I'd
advice encoding location in two or more fields -- one or more to contain
the ISO code, and one to contain a more specific locale *within* the area
of that ISO code.

Some places might have some sort of standards for how to encode these
districts, but you'll mostly find that they don't. Postal codes are an
option, but are probably too specific for your needs. (One franchisee per
postal code? This might be OK in Australia, where postal codes cover
fairly large populations, but less so for the UK, where they tend to cover
a dozen or so buildings.)

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.12-12mdksmp, up 91 days, 1:56.]

Non-Intuitive Surnames
http://tobyinkster.co.uk/blog/2007/05/25/non-intuitive-surnames/

[Back to original message]