At 11:20 PM 6/5/2008, [EMAIL PROTECTED] wrote:
Date: Thu, 5 Jun 2008 10:14:04 -0400
From: "Edward W. Rouse" <[EMAIL PROTECTED]>
To: <pgsql-sql@postgresql.org>
Subject: design resource
Message-ID: <[EMAIL PROTECTED]>
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
