What's the most flexible design for any table of physical addresses in certain number of SQL? I suppose there's something much better than home address, building address, city, condition/province/region, country, local zip code . Are also there standard names for a number of aspects of addresses, especially worldwide standard names? Further, what kind of provision ought to be made for the similar location getting multiple addresses? With what conditions could this occur?

http://www.upu.int has got the format standards for worldwide addresses. Publication 28 at http://usps.com has got the U.S. format standards. CASS software like http://semaphorecorp.com validates and standardizes U.S. addresses.

The USPS wants the next unpunctuated address components concatenated on one line:

  • house number
  • predirectional (N, SE, etc)
  • street
  • suffix (AVE, BLVD, etc)
  • postdirectional (SW, E, etc)
  • unit (APT, STE, etc)
  • apartment/suite number

Eg, 102 N Primary ST SE APT B.

Should you keep your entire address line like a single area inside your database, input and editing is simple, but searches could be harder (eg, within the situation East LANE may be the street EAST as with S EAST LN or perhaps is it LANE as with SE LANE ST?).

Should you keep your address parsed into separate fields, looks for components like street title or flats become simpler, but you need to append everything together for output, you'll need CASS software to parse properly, and PO boxes, rural route addresses, and APO/FPO addresses have particular parsings.

An actual location with multiple addresses at this location will be an multiunit building, by which situation letters/amounts after models like APT and STE designate the address, or it is a Commercial Mail Receiving Agency (eg, UPS store) along with a maildrop/private mailbox number is appended (like 100 Primary ST STE B PMB 102), or it is a business with one USPS delivery point and mail is routed after USPS delivery (which often takes a separate mailstop area which the organization may need however the USPS will not want around the address line).

A message using more than one home address is generally a business or person having a home address along with a PO box. Observe that it is common for every address to possess a different Zipcode.

The correct answer is typical that certain business transaction may have a shipping address along with a billing address (again, with various ZIP codes). The data I keep for every address is:

  • title prefix (DR, MS, etc)
  • name and initial
  • surname
  • title suffix (III, PHD, etc)
  • mail stop
  • company title
  • address (one line only per Pub 28 for USA)
  • city
  • condition/province
  • ZIP/postal code
  • country

I typically print mail stops approximately the individual's title and company since the country consists of the condition/ZIP which consists of the town which consists of the address which consists of the organization which consists of the mail stop which consists of the individual. I personally use CASS software to validate and standardize addresses when joined or edited.

Probably the most generic ADDRESS table I built incorporated the next posts:

  • ADDRESS_1 - c/o line
  • ADDRESS_2 - RR/PO Box
  • ADDRESS_3 - suite/apt
  • ADDRESS_4 - home address
  • ADDRESS_TYPE_CODE - foreign answer to ADDRESS_TYPE_CODES table
  • CITY
  • Condition_PROVINCE
  • POSTAL_CODE
  • COUNTRY

The ADDRESS_TYPE_CODES could be business, home, mailing/shipping, etc.

There is no way to be aware what address particulars you are getting, so make it flexible.

One possibility is, a text area might be used to ensure that the address might be formatted by any means needed. However, then your text will have to be parsed for that areas of the address helpful for supplying links to maps and directions. Possibly there must be a text full_address area after which another group of posts which will contain parsed (instantly or by hand) address pieces helpful for links to maps and directions. When the address couldn't be parsed, a flag could be set saying it could not be recognized. (Possibly what this means is the address is within a nation that the parser does not be aware of address format for, or the address was joined incorrectly.)