Store full address in a database

table design brainstorming

[moltar]

I am working on a database design. One table of it will store client's location (address). I was wondering what table structure do you use for address?

I've done some research and found out that in Canada there are serveral formats for addresses [canadapost.ca]:

• Civic Addresses (Street Addresses)
  
• Post Office Box Addresses
  
• Rural Route Addresses
  
• General Delivery
  

And I am sure it also varies from country to country, but I am mostly conserned about Canada at the moment.

Do you ignore all the types, except the Civic Addresses or you try to accomodate to everyone? I'd like to make it work for everyone, but I can't see how to make it easy...

I don't want to reinvent the wheel. Can anyone share their ideas?

[txbakers]

bascially an address has four parts:

the street address or PO box or Carrier route
The city
the province or state
the postal code (zip in the US)

Sometimes there is a second line for street address.

If you ever want to be able to sort/select by province or postal code, then those should be separate fields.

[Krapulator]

This has never failed me:

Address Line 1
Address Line 2
Suburb
State
Postcode
Country

[choster]

International Mailing Address Formats:
[upu.int...]
[bitboost.com...]

[john_k]

You might also want to store all addresses in a separate Address table. You would then add a field for the address type (shipping, billing, mailing) and another for the CustomerId. Then you can add any number of addresses to a customer.