Re: design resource

At 11:20 PM 6/5/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Thu, 5 Jun 2008 10:14:04 -0400
>From: "Edward W. Rouse" <erouse(at)comsquared(dot)com>
>To: <pgsql-sql(at)postgresql(dot)org>
>Subject: design resource
>Message-ID: <0e9c01c8c716$6f5db800$143c520a(at)ntc2s(dot)comsquared(dot)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