Re: [GENERAL] Database Best Practices

Gary Hoffman

> For example, I'd like to know, in designing a name-and-address table,
> - should a title field be provided? (Mr., Dr., etc.)
> - how about a suffix (Jr., III, etc.)
> - how many address lines should I have for a home address?
> - should all the fields be type 'text'? Should Zip be 'text' or 'int4'?
> - what schema handles Zip-plus-four best (including the blasted hyphen)?
> - what is the best database schema for a universal, international
> address table that includes individuals and companies?

If you are serious about name and address tables then you have chosen
one of the most complex things to model in a useable way.

We have developed a fundraising system for charities, the first
customers are Bible Societies around the world and the system replaces
one written by me nearly 10 years ago.

We use the following

Titles table. Has format strings to create Salutation prefix,
salutation, addressee from the title, any part of the name and also any
constants. Each person has their own salutation prefix, saluation and
addressee which are initially generated from the title but which can be
changed. Some countries have titles that span more than 1 line (eg
Austria, Italy) and it is common to require a diferent name presentation
for the addressee (top of address) and salutation (top of letter). In
many languages the Salutation prefix (usually "Dear" in English is
diferent for men and women. In some countries titles are almost never
used (eg Denmark) in others they are essential and there are 1000's of
variations (Austria).

Names. We have Lastname, firstname, initials and honours (eg BSc (hons)
or Jr). This is still not ideal for some cultures which dom not have
western naming conventions (eg India at least traditionally).

Addresses. There are lots of complications. Basically we have an address
format for each country and dynamically rearrange the address
presentation according to that (eg house number on right in most of
Europe on left in UK, postcode before city in most of europe and on a
new line after county in the UK).

We have 5 address lines plus postcode, state (from a lookup list for the
selected country), and country. We enter addresses backwards ie country,
state, postcode so that we can check correctly and adjust the formats as
we go (plus automatic address completion from the postcode). For example
UK does not have states but Spain and USA do. The postcode format is
kept with the country. The address format has descriptions for each
address line which are displayed so that the same line is used for City
in all UK addresses. The address format also controls whether particular
address lines are "Not used", "Optional" or "Required".

NB House number is not a short or simple column. In many countries where
lots of people live in apartments it will combine the floor number and
apartment number.

Obviously you also need country specific formats for phone numbers (eg
Denmark does not have area codes).

When you support finding people you need to remember things like

- postcode finding is useless in much of the world eg Denmark has 1
postcode for a whole town. UK has 1 postcode for 17 houses on average.
- Surname finding is useless in many countries eg (Smith or Patel in UK,
Hansen or Jensen in Denmark

In terms of column types we use varchar with a unicode character set
otherwise you may not be able to have an address in Moscow in the same
dbms as one in Portugal, one in Finland, Latvia, Malta, Jordan etc

Also in countries like Norway you need a summer and winter address for
people as many move out of the city in the summer.

There is a book "Guide to worldwide Postal-Code & Address formats" from
Marian Nelson/Nelson Intersearch Company tel +1 (212) 580-4819 fax +1
(212) 362-9855 email MarNelson(at)aol(dot)com

Regards

Dave

--
David Warnock
Sundayta Ltd