Re: design resource

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