3

I'm looking for a common pattern that will store and access global addresses in database. Components or other technologies can be used. The following criteria must be adheard to...

  • Every line of the address is saved for every country
  • Postal codes are tested with a regular expression before being saved
  • Country of original is saved in it's own field When the data is displayed, the [address is formatted] (http://en.wikipedia.org/wiki/Postal_address) in the style of that country
  • When the data is input using a form the label fields are as descriptive as possible, so the labels ned to be dynamic to the country of origin.
  • The addresses take up the minimum space possible
animuson
  • 53,861
  • 28
  • 137
  • 147
digiguru
  • 12,724
  • 20
  • 61
  • 87

1 Answers1

0

How about storing the addresses as text (allowing newlines). The postal code will have to be extracted from the address with a regex (selected based on a country dropdown), and should be stored in a separate column.

This doesn't deal with the "as descriptive as possible" requirement, but in general, enforcing more constraints about the format of the data will result in a percentage of valid addresses being rejected. It will also take more space than a single varchar column. Therefore, there will always be a compromise between the requirements you listed.

deemer
  • 1,142
  • 8
  • 10
  • 1
    Its an interesting solution, but when you start giving people a free text input box is they start entering weird information like "It's just over the train lines next to the funny green postbox. We have the house with the red door". That's something we're trying to avoid. – digiguru Sep 16 '08 at 08:07