Steve Midgley wrote:
> However, normalizing country is generally pretty smart (as is
> normalizing state/admin region within countries where you do a lot of
> business). This can be generally handled on the front-end with a
> pull-down menu of choices, but you would probably be happiest enforcing
> this on the back-end as well - possibly by having a "country" look up
> table:
>
> country_id|iso2|iso3|full_name|short_name|full_accents|short_accents...etc
>
> I keep the country names with and without accents to make searching
> easier across keyboards/locales.
Good point. You'll have to make sure you keep the country table up to
date, though, if you make it a foreign key for address entries.
Countries change more than you might expect.
The other option is to use it as an advisory table the client can use to
get additional information on a country, with the country stored as a
string field in addresses. The client would be expected to warn users
loudly if it couldn't find the address in the advisory table.
I think it's better to break countries out into a lookup table and use a
foreign key constraint (to reduce data entry errors) to reference them
by generated integer ID or ISO code if you have more than a trivial
number of customers in countries other than your own. However, I'd want
to provide easy to use administrative options in the client to update
the country list. Validation error messages should probably mention how
to update the country list (even if it's just a manual section number
reference).
For some things, like states/provinces, you may want to validate them
strongly for addresses some countries (say, where you have most
customers) but permit unvalidated input for other places. This is a good
use for a PL/PgSQL trigger. My address schema in the app I'm working on
right now has a state/province field that a trigger forces to be a valid
Australian / US state if the address is in Australia or the US,
respectively, but it otherwise accepts any string or a null value.
Argh. I loathe addressing almost as much as I loathe printers and
printer drivers.
-
Craig Ringer
