Re: 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 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.

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