Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
I can see how you could get a very specifically formatted address out of it and it might be useful for bar-coding. Otherwise it seems a bit overboard to me. – Kenneth Fisher Mar 28 at 17:24
4  
Just as a snarky comment - tell your modelers they are missing about 35 possible post mail fields. If they are going to do this, they should be doing it right. :) – Karen Lopez Mar 28 at 17:36
1  
And keep in mind some addresses still won't fit your template -- I've seen some real street addresses along the lines of "down the street from the cement factory" from developing countries. – duskwuff Mar 28 at 17:47
1  
Show me the regex which enforces a street name requirement within a line of arbitrary text and I'll show you why it's easier to have a separate database field for street name alone :) – MonkeyZeus Mar 29 at 12:03
3  
up vote 5 down vote accepted

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?

share|improve this answer
3  
"They're not going to tell you to just dump it in a BLOB, are they?" - I was just going to model my application after Vision. It's like the Diet Coke of software. Just one table. Oh look. An angry mob of DBAs is assembling outside my office. I wonder what they're so upset about... – Greg Burghardt Mar 29 at 12:49
1  
Splendid plan! For extra performantizationess implement it as just a single BSON column. :-) – Michael Green Mar 29 at 15:28
2  
I think this is a highly underrated answer. Most answers address the many problems that can arise from splitting addresses into columns, but I think this answer does the best job of summing up what problems are solved. I might post a similar question asking about the problems that are introduced. Every solution has benefits and drawbacks. Your answer addresses the benefits best. – Greg Burghardt Mar 30 at 12:26

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.

share|improve this answer
    
What a great answer based on your expert experience. Nice +1. – JonH Mar 29 at 14:04
    
Interesting follow up for leading zeroes in street numbers: The HTML number INPUT element will post leading zeroes back to the server: <input type="number">. I was afraid that it wouldn't (at least it does in Firefox anyhow). – Greg Burghardt Mar 29 at 15:59
    
So why is it useful to split at all? What about just providing 3 string "lines" for the address? – usr Mar 29 at 19:57
    
And there's also the 137 SE Chestnut Ave SW pattern, common from IN to WI. – Ross Presser Mar 29 at 23:19
    
@usr Not every address fits in three lines - just use a varchar and a free-form multi-line text field already! – immibis Mar 30 at 3:17

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.

share|improve this answer
    
"you don't need to have international business operations to have to support international data" -- very true. And on top of that we are physically located near the border of another country. The modeling team did give a solution for international addresses, which is to provide line 1, line 2 and line 3 fields in the database. – Greg Burghardt Mar 28 at 18:56
    
Though you said this "is a gross generalization" the one-side-fits-all solution for addresses we have enterprise wide makes your answer all the more applicable. – Greg Burghardt Mar 28 at 18:57
    
"mailing discount"??? "volume discount" would seem to fit; but even with autocorrupt I'm not sure how you could get from one to the other. – Dan Neely Mar 28 at 19:47

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.

share|improve this answer
1  
+1 "a couple of days after that you will figure out how to parse it correctly" I wish I could upvote this more than once :) – David Mar 29 at 12:18
    
@David: Same here. That line gave me a good chuckle. – Greg Burghardt Mar 29 at 12:29
    
"What is an address, anyway? ... you could make an equally good case that it's delivery instructions" - Very good point. I think the "location" aspect of an address and the "delivery instructions" aspect should be separate fields in the database in this case. – Greg Burghardt Mar 29 at 13:24

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 and so on. It's nice to be able to update that kind of information without parsing the addresses as a single string.

share|improve this answer

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!

share|improve this answer
    
"But then when you start adding “town”, “area” etc it gets questionable..." - While our solution is focused on "approved postal addresses" things like neighborhoods are very important to people other than the USPS. Good point. – Greg Burghardt Mar 29 at 12:28
2  
Amazon.uk has the best system I have seen, when I type in address, they give me the OPTION of using the "approved" address the matches best. However often the approved address is for a different company in the building, or does not include the "floor" etc, as the post office only caress about were the letter box is, not where to take something to get it signed for. – Ian Ringrose Mar 29 at 12:59

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.

share|improve this answer
1  
Your 1980s example is a wonderful illustration of my point about parsing out whatever dimensions you need to manipulate, and "...store them or you're getting into declension" is a good example of why it's vital to keep the source text. It inevitably contains all sorts of non-functional things that nevertheless must be preserved. And speaking of irrelevant but interesting things, boulevard means "promenade built on top of demolished defensive ramparts". – Peter Wone Mar 30 at 23:53
  • Street pre-directional: N (North)
  • Street name: Smith
  • Street type: ST (Street)
  • Street post-directional: SW (Southwest)

What's the actual purpose of splitting these up? The number could be useful as a separate field (e.g. if you have a map database that gives the physical locations of ranges of numbers but not individual numbers), but splitting up the street name into parts.

EDIT: It occurs to me that if these fields are defined by the US Postal Service, which I had not realized until seeing your link, then a problem solved by splitting them up likewise in your application could be the ability to match addresses to databases provided by the Postal Service, for example, for validation, or to automatically fill in ZIP+4 or delivery point numbers not provided by the user.

I do notice, however, that the USPS does not define a separate field for a "fractional part" of the primary address number, and that an example is provided of an address whose address number contains a hyphen: "289-01 MONTGOMERY AVE". Someone else noted in a comment that Portland has some addresses with a significant leading zero in the address number, so treating any part of the address number as an integer seems like a lost cause.

Looking at your USPS source in more detail, you also may need to handle Rural Routes, Highway Contract routes, PO Boxes, and General Delivery... some of which may not be usable for purposes other than mail delivery.

share|improve this answer
    
Just out of curiosity, do you live in the U.S.A.? According to the United States Postal Service, 123 Smith North DR would be an invalid address if the "North" was the post directional. If it were the post directional, the proper format, according to the USPS would be 123 Smith DR N. International addresses aren't covered in the parsed columns where I work, and instead are added as additional "line 1, 2 and 3" columns (like people normally store addresses as). – Greg Burghardt Mar 29 at 15:47
    
@GregBurghardt I had never heard of there being "post directionals" as an official thing at all rather than just being part of a street name. I assumed you were making it up. Do you have a link to a source on this? – Random832 Mar 29 at 15:59
    
The USPS has a pretty exhaustive list of rules. Specifically about post directionals: pe.usps.gov/text/pub28/28c2_014.htm#ep526395 - but again, this is for the U.S.A. I can't speak for other countries. – Greg Burghardt Mar 29 at 16:03
    
Okay, what's the point? – Random832 Mar 29 at 16:15
    
I guess that sounded confrontational. Apparently the Postal Service thinks there's a problem solved by splitting these fields (and I wonder if this might be stated somewhere). If the fields you are using match theirs exactly, then the problem solved in your application could be the ability to match addresses with USPS databases (for validation, or to look up the ZIP+4 code when not provided by the user). – Random832 Mar 29 at 16:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.