Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!rutgers!rochester!pt.cs.cmu.edu!cadre!pitt!amanue!oglvee!norm
From: norm@oglvee.UUCP (Norman Joseph)
Newsgroups: comp.databases
Subject: Foreign address formats & representations
Message-ID: <485@oglvee.UUCP>
Date: 21 Jun 89 16:30:46 GMT
Distribution: na
Organization: Oglevee Computer Systems, Connellsville, Pa
Lines: 56


[The application in question is written with Progress, but the
question involves more general principles.]

I'm writing a database application that has to be able to handle
foreign (Canada and Europe mostly) addresses as well as domestic.
A straightforward and simplistic implementation for a customer
record in this database, for example, might look like this:

        table: customer
                customer-name      char(25)
                addr-line-1        char(25)
                addr-line-2        char(25)
                addr-line-3        char(25)
                phone-number       ...etc.

As you might suspect, I need something a little more elaborate for
storing and presenting the address information than a free-form
character array.  I've come up with the following:

        table: customer
                customer-name      char(25)
                addr-line-1        char(25)
                addr-line-2        char(25)
                city               char(20)
                state              char(2)
                province           char(20)
                country            char(20)
                zipcode            char(10)

but I'm not at all sure that I've accounted for most common foreign
addresses.

In addition to their representation, I need to be able to display
these addresses for reports, etc.  The problem is, if I define a
generic address "form" that includes all of the fields,  I will
end up with a lot of unappealing white spaces and gaps where
fields make no sense (domestic addresses usually don't specify
`province' or `country', for example).  More complex displays
-are- possible, but before I commit to the extra work, I want to
be certain that the table layout is reasonably solid.

So, my quandary really boils down to two questions:

    1)  What is a reasonable scheme for representing both foreign
    and domestic addresses in the same table?

    2)  What is a reasonable scheme for displaying said addresses?

I'm certain that I'm not the first person who has had to solve this
problem, and I'm hoping the net can give me some pointers at least.
E-mail would probably be more appropriate and greatly appreciated.
-- 
Norm Joseph - Oglevee Computer System, Inc.
  UUCP: ...!{pitt,cgh}!amanue!oglvee!norm
    /* you are not expected to understand this */