Re: design resource - Mailing list pgsql-sql

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