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.