Re: design resource

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