Re: [GENERAL] Database Best Practices
От | David Warnock |
---|---|
Тема | Re: [GENERAL] Database Best Practices |
Дата | |
Msg-id | 37942BA4.696EEB13@sundayta.co.uk обсуждение исходный текст |
Ответ на | Database Best Practices (ghoffman@ucsd.edu (Gary Hoffman)) |
Список | pgsql-general |
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@aol.com Regards Dave -- David Warnock Sundayta Ltd
В списке pgsql-general по дате отправления: