Monday, December 13, 2010

Addresses in databases


Whenever I see something like this:

  • Address Line 1:
  • Address Line 2:
  • Address Line 3:
  • City:
  • Country:
  • Post Code:

I want to find the database designer and smack them.

What is it about addresses that make people think that they don't need normalising?

No! Of course! The solution to storing addresses is to create a table and force all addresses to fit into five lines plus a postal code. Brilliant. Really smart.

There is one mandatory field in the address: country. That's the only one. Everyone lives in a country. I don't want to get into stupid arguments like "Wales isn't a country it's a principality", etc., when you put it in an address it's a country.

You know something people know? How many lines there are in their address. So don't force them to have 3, 4, 5, xty mumble-jillion, or however many you think is sufficient.

This is what I want to see from now on:

Address


If you do the post / zip / whatever code search thing, then great, but be sure to store the address lines in a sensible manner.

address_id line_id text
1 1 My House Name
1 2 My Street Name
1 3 My City Name
1 4 My Post Code
1 5 My Country

2 comments:

Peter Coulton said...

I hate DBAs as much as the next developer, and addresses are frustrating, but ultimately what are they used for? That's right, labels on envelopes, and this constraint alone means that 4-8 lines is all you're gonna be able to use anyway.

Mojo said...

Labels? Print directly onto the envelop, that gives you more than enough space for any reasonable address. Moonpig do it and they sent loads of cards each day.