Re: Generalised approach to storing address details

In message <1165492572.390698.246080_at_j72g2000cwa.googlegroups.com>, Frank Millman <frank_at_chagford.com> writes
>
>Bernard Peek wrote:
>> In message <1165470808.701320.264240_at_79g2000cws.googlegroups.com>, Frank
>> Millman <frank_at_chagford.com> writes
>> >Hi all
>> >
>> >This is really a database design question. I am not sure if this is the
>> >correct forum. If not, please can someone recommend a more appropriate
>> >one.
>> >
>> >I want to set up a generic way of entering names and address details.
>> >Address details could be a multi-line address, a telephone number, an
>> >email address, or whatever communication medium may arise in the
>> >future.
>>
>> A few questions for you:
>>
>> Are you expecting to use this for international addresses? Note that
>> different countries expect postal address fields in different orders.
>>
>
>I am developing a midrange business/accounting system. I want to make
>it as generalised and as flexible as possible. Therefore, yes, it could
>well be used for international addresses. One of the benefits of my
>concept is that different users can set up their own definitions, with
>their own fields, in their own sequence, so it should be easy to meet
>the above requirement without any changes to the database.

In which case I suggest that you save yourself a lot of effort and keep things as simple as possible. Store addresses as Line1, Line2 etc. That's not only the easiest format to use it's also the most useful.

When someone writes an address down they will write it in the right format for their own postal system. If you try to be clever and analyse it into named fields you then have to have an algorithm that puts the named fields back in the same order that they started in, two complex and completely unnecessary operations.

The only thing you need to do is to make sure that the country is in the right place for the sender's postal system, the last line of the address for the US/UK. That gets the mailing to the right country and after that it's SEP - Someone Else's Problem.

-- 
Bernard Peek
back in search of cognoscenti