What problems are solved by splitting street addresses into individual columns?

Question

We have a team who designs the tables and relations for software developers. In our organization, they are pretty strict about enforcing 3NF normalization - which to be honest, I agree with given the size of our organization and how the needs or our clients change over time. There is only one area I'm not clear about the reasons behind their design decision: addresses.

While this mostly focuses on addresses in the United States, I think this could apply to any country that does this. Each piece of an address gets its own column in the addresses table. For instance, take this gnarly U.S. address:

Attn: Jane Doe
485 1/2 N Smith St SW, APT 300B
Chicago, IL 11111-2222

It would get split up in the database like this:

• Street number: 485
• Street fraction: 1/2
• Street pre-directional: N (North)
• Street name: Smith
• Street type: ST (Street)
• Street post-directional: SW (Southwest)
• City: Chicago
• State: IL (Illinois)
• Zip code: 11111
• Zip4 Code: 2222
• Country (assumed to be U.S.A.)
• Attention: Jane Doe
• P.O. Box: NULL
• Dwelling type: APT (Apartment)
• Dwelling number: 300B

And there would be a few other columns related to rural routes and contract routes. Furthermore, our specific application will likely have a few international addresses in it. The data modelers said they would add columns specific for international addresses, which would be the normal line 1, line 2 fields.

At first I thought this was WAY overboard. Researching online repeatedly refers to using address line 1, 2, 3 and possibly 4, then splitting out city, region and postal code. We do have one use case for our new application where this granularity is beneficial. We have to validate that the user is not creating a duplicate business, and checking the address is one of the validations. We can get it to work with address line 1 and 2, but it would be more difficult.

As for our specific application, we need to store multiple kinds of addresses for businesses and people (physical, mailing, shipping, etc). We might need to generate printable form letters, but that requirement hasn't been discussed so far.

Some other things applications in our organization need to support:

• Auditing (with full history tables)
• Printing mailing labels
• Generating printed forms
• Reporting (for national and regional governments)

While our application might not be doing everything that every other application is doing, splitting addresses into multiple components is an enterprise standard where I work. Regardless of whether our application would benefit from it, we are forced to do this.

Semi related StackOverflow question: Where is a good Address Parser which was closed, but illustrates how difficult parsing addresses can be.

In order for me to better understand their design decision, and to sell our client on the idea...

What problems are solved by splitting the street address into individual columns?

Bonus points for anyone who has implemented a system like this, because they ran into problems.

Answer 1

Problems that can be solved by splitting include

Validation Any one part of the name can be compared to a master list. Those which do not match can be rejected. Postcode / zipcode is an obvious example. These are issued and maintained by an independent authority. The only valid ones are those issued by that authority.

Sorting and Selection I have seen cases where postal charges are reduced if mail is handed to the delivery service already organised to some extent. Having the corresponding columns produces tangible business value.

Analysis It can be useful to know where your orders are going, in a geographically hierarchical way. This may drive sales initiatives, product development or commission payments etc.

Code Duplication By having all applications in an organisation adopt the same data model (that of the most complex consumer), a single code base can be adopted enterprise-wide and maintained consistently. Endlessly duplicated hair splitting can be avoided, or at least delegated to the propellerheads. Addresses held by different parts of the organisation can be updated consistently. Customer service and satisfaction can be increased. Development effort can concentrate on the unique, high value parts of a system.

Legal Issues Laws and taxes vary by jurisdiction. By capturing the detailed address values separately it is easier to cross-reference transactional data to compliance requirements.

Duplication It is simple to spoof addresses held as text by moving one element to the next line or resequencing some parts. Fully parsed addresses are easier to compare. This may be a simple data quality issue, or may have compliance or credit implications if, say, multiple shell companies make large orders to the same delivery address, or a credit card is used to deliver to many dispersed locations in a short period.

Formatting Parts held separately can be combined in whatever fashion suits the current need. If, say, long thin print labels become cheap you can reformat to use them.

Of course none of these may apply to any specific application. Data of this type is much easier to parse and validate at source, when collected, than it ever will be in post analysis. So even if YAGNI it may be better to put the extra effort in up front for little cost and a potential large future saving.

Finally, I wouldn't dismiss the human factor. The data model is produced by data modellers. It's what they do. That's their profession. They're not going to tell you to just dump it in a BLOB, are they?

Answer 2

I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists. It is useful to split up addresses into distinct fields, but you can never, EVER design for every possible pathological aberration of address formats and components the human brain can devise.

Every locality can have its quirks, and that's just in the US. Throw in other countries and things get unmanageable very quickly for any approach that wants to parse every address. Just two examples:

In Spain, the street number always comes after the street name and a comma, and many addresses contain a floor number ordinal, such as 1° or 3ª, along with abbreviations for "left" ("Izda" meaning left-hand-door after you get up the stairs), "right" ("Dcha") or other possibilities. Now multiply that quirkiness by the number of different countries and areas with different historical customs for addresses... (Japan? Rural England? Korea? China?)

In Portland, OR, there are N-S and E-W axes that divide the city into NW, NE, SW and SE quadrants (as well as a N "quadrant", but I digress). N-S streets are numbered incrementally East and West from this axis, and addresses on E-W streets are dictated by the N-S street number being the "hundred block" of the number (i.e. a house on an E-W street between 11th and 12th avenues would have a number like 1123). Pretty standard stuff for US addresses.

Every so often you run into a Portland address like 0205 SW Nebraska St. A leading zero? WTF? There goes my integer column for house "number".

When the grid was set up, the N-S axis was defined by the Willamette river. Everything to the East of the river was NE or SE, and West of the river NW or SW. As the city grew south they ran into the inconvenient fact that the river meanders to the East, so projecting the axis South you have this problematic area that's on the "West" side of the river but East of the axis. The solution was to add a leading zero, in effect a minus sign, with the numbers incrementing towards the East from the axis line.

If I were you I'd give up hope of designing the ultimate system. You cannot cover all possibilities, and new ones will be created as humanity pushes into previously undeveloped land.

For US addresses, take a look at what the USPS has already done in address standardization, and remember to make the house_number column a varchar. While you're at it figure out how you're going to parse 1634 E N Fort Lane Ave.

For the rest of the world, I'd probably try to abstract additional fields to cover 80-90% of what is likely to come up, and provide a set of uninterpreted fields that can handle everything else when necessary. I.e. if your parser fails to handle an address, save it unparsed and flagged as such. If you do manage to parse an address, make sure you remember the order in which you found the various fields so you can reassemble it into something deliverable.

I was going to say that the most important field is going to be post code, but even that is not a given in many places.

Good luck. This can be a fun and extremely frustrating endeavor but the key to sanity is to know when to quit trying and just store the input unparsed, or partially parsed with the original input as backup.

Answer 3

Like all design questions, there's a hugely qualified "it depends". It depends on your data story - how the data is collected, how it is used, how it gets updated, etc. All my comments should be taken as discussion points, not how-to answers.

It sounds like* you could benefit more from using an address validation service than trying to build one for yourself. While they are costly, many such services come with significant mailing discounts.

Of course, there is a compromise here, for certain data stories. You can persist the parsed out address pieces and create a computed column (set of columns, likely) for the combined address. This is an implementation answer, with all the normal caveats implied.

I have implemented the parsed out address design. We absolutely needed this for data quality AND data processing needs. But that was a business that had physical addresses, postal addresses, virtual addresses, etc.

The other issue that can come up is that different postal services require the same information to be presented in different formats/orders/etc. So having the parts modeled out supports presenting the same info in a variety of formats and layouts.

Finally, you don't need to have international business operations to have to support international data. Even US-based businesses need to support international addresses. It's a huge data mistake to assume you will never have that. Customers move, vendors change HQs, vendor contact info can be international even if they have a US HQ. Even if your current systems made that mistake, you don't want to carry this one forward.

I highly recommend the writings and blogging by Graham Rhind. He's the expert in the data field about addresses of all kinds and the trade-offs associated with them.

---

_{*All I've said here is a gross generalization. There are so many questions I'd have to help come to a design solution that it might take a few hours of chatting. Likely some pictures and some data profiling, too. And then a lot of really quirky data stories about addresses.}

Answer 4

Totally leaving aside the enormous challenge of correctly parsing the unpredictable gibberish that people supply, the benefit of parsing is it gives you dimensions for grouping and sorting. Postcode, for example. However, there is no payoff from parsing out a specific dimension until you need to group or sort on that dimension.

What is an address, anyway? You could make a good case that it's a location identifier, but you could make an equally good case that it's delivery instructions - "Down the street from the cement factory". In Australia, people think post codes are location identifiers, but they aren't, they're routing codes - delivery instructions. 4702 is Rockhampton Mail Centre, a major distribution node servicing a region stretching from the sea to Emerald, a mining town 300km inland.

If you want to identify locations then Bing and Google can geocode directly from the unparsed string into GPS coordinates, which can be stored in a small, simple table along with the unparsed string. They use the only general approach with any chance of consistently good results: ranked weighted partial matching with a colossal database of validated results.

If you want delivery instructions you're still well advised to keep the unparsed string because it could contain anything.

Notice that in both cases I have recommended keeping the unparsed string. That's because

• it's useful in its own right
• one day you will figure out how to parse it
• a couple of days after that, you will figure out how to parse it correctly
• this never ends

Arguably an address is always delivery instructions, containing at least one location identifier. A letter addressed to "123 Main st, Emerald 4702" encodes three locations: RMC in the north part of Rockhampton, Emerald, and a street address. Rockhampton post office will simply send it to RMC. RMC will send it to Emerald post office, and Emerald post office hopefully knows where to find 123 Main street.

Answer 5

Separating out postcode/zip code, building name, road name can make sense. But then when you start adding “town”, “area” etc it gets questionable, compared to just line1, line2 etc. The issue is that even I and my wife can’t agree on the name of the town we live in! Is the “village” name to be put in the town field, or does it go in the line below the road name, with the local city being put in the town fields? (Some people get offended if you call where they live a village instead of a town, other people living in the same location get offended if you call it a town instead of a village!)

Therefore trying to do anything fancy is no better than the address verification system you use. But it gets even worse. In the UK ALL addresses should have a post code, but yet the post code is not allocated until sometime after a house is built…… So a system has to allow every rule about address to be broken!

Answer 6

I have implemented a system like this before, albeit in the Netherlands. The thing is, this kind of information can change in more ways than you think. Streets are renamed, cities are merged, postal codes are updated and so on. It's nice to be able to update that kind of information without parsing the addresses as a single string.

Answer 7

In addition to the problems already mentioned in other answers, in some languages -- Germanic in particular -- street names tend to be compound. For example, it's common in many German towns / cities to have a "Bahnhofstrasse", the street that goes to the railway station ("Bahnhof" meaning railway / train station, "Strasse" meaning street). Certainly you could separate these two components out, but now if you want to put them back together (programmatically) you're getting into questions of declension.

Or, in the "romance" or Latinate languages, you frequently have street names of the form "Rue de la Pais" or "Boulevard des Champs-Élysées". Now you have a preposition ("de") and a definite article ("le" or "la") in the mix -- and they may be combined. Do they represent part of the street type or street name? (You probably need to store them somewhere, otherwise you're getting into declension again.)

---

I did once model something like this. But it was a very small application, for the residential properties maintenance office of a medium-sized university (in the US). I made the addresses very granular for the following reasons:

• There were streets in the area with the same name but a different street "type" (e.g. "Woods Avenue" vs "Woods Court").
• The users wanted to be able to optimize maintenance work e.g. if there were two or more service requests on the same block those could be handled at the same time.
• The users wanted to be able to correlate issues between different units (apartments) in the same building -- e.g. if more than one apartment reported cold temperatures or insufficiently hot water.

... and other reasons which I no longer remember. (This was in the late 1980s.)

And again, this only made sense because there was a reasonably small number of addresses (and address formatting rules) to deal with. I don't believe this approach would scale, even if limited to US addresses, for reasons already given in other answers.