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
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.
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.
Addresstable, e.g., how it is going to be employed by its end users), you might attract more accurate answers. – MDCCL 8 hours ago