Thread: design resource

Edward W. Rouse wrote:
> I was wondering if there were any resources that have some table designs for common problems. Since that isn't very
clearI will
 
> give an example.
>  
> We have an internal app from years back that needs to be updated. One of the problems is that when it was originally
created,the
 
> company only had US customers. We now have international customers and need to support international addresses and
phonenumbers.
 
> For the phone numbers that means adding a new column for international code or expanding the data field so that it's
bigenough to
 
> hold the international prefix (still not sure which approach is best). But I haven't a clue as to how to set up for
international
> addresses. 

If you want to remain happy and sane, don't try to enforce too strict a 
structure. Modelling addressing in a truly flexible, international way 
is a *massively* complex problem.

I'd personally stick to having plenty of space for a general address - 
think unit, street, etc etc etc. It could either be divided into 
"address lines" or stored as freeform text, but the point is not to try 
to break it down too finely.

On top of your general address field you can probably afford to 
specifically track:

- A postcode/zip code (unvalidated, any alphanumeric  & symbolic, long)
- A suburb/town/city/shire/region/whatever name (unvalidated string)
- A state/region name (unvalidated string, DO NOT JUST ASSUME US STATES)
- A country

... but I'd be prepared to accept null values in most of them 
(indicating that it's not needed, not known, or expressed in the 
freeform address field).

Nothing stops you doing application-level or trigger-level validation in 
more specific cases. Think: "Addresses in the USA must have a non-null, 
valid ZIP code and state" and "Australian addresses must include an 
assigned 4-digit postcode and include a city/town/shire and a state".

I'd avoid trying to make sense of street-level addresses if at all 
possible. You're in for a world of pain if you try to properly 
denormalize and model those. For example: In rural New Zealand, delivery 
locations are identified by a person's name, the nearest town, and a 
region designation like "Rural Delivery Area 3". Many systems just 
pretend that there's no street/unit number and the "street" name is 
"rural delivery area 3" ... but that's just kludging wrong data into 
your schema, so I'd instead avoid forcing that level of structure on 
things at all.

That's just my personal opinion on a possibly good way to do it. Your 
needs may vary.

As for design patterns for common database problems - I know they're out 
there, and I'd be interested in specific references myself if anyone 
knows any ISBNs. I wouldn't be at all surprised to see books on 
addressing alone.

--
Craig Ringer

At 11:20 PM 6/5/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Thu, 5 Jun 2008 10:14:04 -0400
>From: "Edward W. Rouse" <erouse@comsquared.com>
>To: <pgsql-sql@postgresql.org>
>Subject: design resource
>Message-ID: <0e9c01c8c716$6f5db800$143c520a@ntc2s.comsquared.com>
>
>I was wondering if there were any resources that have some table 
>designs for common problems. Since that isn't very clear I will
>give an example.
>
>We have an internal app from years back that needs to be updated. One 
>of the problems is that when it was originally created, the
>company only had US customers. We now have international customers and 
>need to support international addresses and phone numbers.
>For the phone numbers that means adding a new column for international 
>code or expanding the data field so that it's big enough to
>hold the international prefix (still not sure which approach is best). 
>But I haven't a clue as to how to set up for international
>addresses.
>
>So I was hoping there would be a resource that I could check where 
>these kinds of data sets have been 'solved' to ease the effort. I
>have several books on design patterns for programming but I've not 
>seen a design patterns book for common database problems. Thanks.

Hi,

In addition to Craig's excellent answer, I'll give an additional 
nuance. I think that free-form and flexible/re-usable fields are the 
way to for handling addresses.

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.

I hope this helps too -- I think Craig has given you the lion's share 
of good advice for sure - and I definitely follow the practices more or 
less as he laid them out as well.

Sincerely,

Steve

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