Take the 2-minute tour ×
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.

Take the representation of an Address, here below is a complete and very detailed implementation:

address representation

here instead is a quick implementation (which more or less contains the same fields, imagine all fields in the former are contained also in the second)

address representation quick

If I were to decide which is the closer to a normalized and academically correct one I'd say the first one, however if I were to start a project I would go with the second.

Do you agree with this consideration? And if yes, how does one deal with this fact?

  1. start with an easy database and as soon as it is time improve it into a more normalized/academical database.
  2. start with something as close as possible to the academical database
  3. stick with the quick and dirty solution
share|improve this question
1  
Are you sure that you will need all of that information? I don't know your requirements but both of those designs seems heavily overengineered. –  Peter Henell Feb 20 at 11:42
    
Can you give an example of an address that has multiple boroughs? Or a Borough that has multiple municipalities? I think both designs are incorrect. –  sa555 Feb 20 at 21:16
    
I agree with @sa555. You don't need those many-to-many linking tables. All of these objects roll up in a one-to-many fashion, so you can simply have a foreign key on the more specific table, linking to the less specific. That would simplify your design by removing half of the tables. –  siride Feb 21 at 4:19

3 Answers 3

up vote 8 down vote accepted

Trying to normalize addresses is generally a bad idea. There isn't a lot of value to normalizing addresses. Both of your designs are inappropriate for the vast majority of systems.

There are two things you typically do with addresses:

  1. Use them to send mail or packages to that location.
  2. Use them to do geospatial analysis on that location.

Since you are using states, provinces, and boroughs in your design, and not prefectures, for example, I'm assuming that you are working in a North American context. If that's true, then you have well established postal authorities (USPS, CPC) with very well regulated postal data and readily available address data quality tools. Even if you are working outside of US/Canada, there are probably data quality tools that will do what you need.

With validation and standardization of your address data, you can make sure that you are able to meet your first goal.

Using ZIP+4 in the US and Postal Code in many other countries, you can get everything you need for your second goal.

A lot of people are really tempted to break addresses down into granular fields. This is a reaction to how bad address data typically is when all you have is "address_line_1, address_line_2,...". However, breaking out lousy, unvalidated city names into its own field only mean you've got a smaller pile of garbage instead of a larger pile. The only way to solve this is to use an address data quality tool to validate and standardize your addresses. If you attempt to normalize your address data you end up with a big pile of many-to-many associations. This is because addresses in real life don't fit into the neat hierarchies that you would see in a textbook.

Unless you have some really specialized need for addresses, just keep your tables simple (a few address lines, with maybe the postal code broken out) and get a good address data quality tool to scrub the data on the way in.

share|improve this answer
    
+1 Decomposing addresses is sufficiently difficult that even end users, who only have to do it to a single address, will often make mistakes in the process. Then you get the City in Street, while the Street is in StreetNumber along with the Number (because you had to make that field long enough to deal with weird numbering schemes like Tokyo) and in the end what you get is garbage in, garbage out. It's far easier to treat the address as a heap of data, breaking out only the postal code. Scrub & validate the heap as needed, and let the code do the heavy lifting. –  Jonathan Van Matre Feb 20 at 15:53
    
I deal with hundreds of millions of addresses... all I can say is definitely this. More importantly you're highly unlikely to ever get address data of the "quality" implied by the OP - it's all likely to be more like you describe. –  Ben Feb 20 at 22:45

My preference would be something in the middle. Because states/provinces and countries are well established entities that don't change over time you can pull those out into separate tables. However, trying to normalize street and city-level data whilst relying on human input is error-prone at best, and at worst you'll end up with some very poor information in your database.

share|improve this answer

I think the "academically correct way" is not to provide functionality for every detail that could present itself in the real-life object you are modeling. I think it simply means that -if- you need that level of detail, -that- is now you should normalize it.

Going in the same direction of "solution 1", you might as well start creating tables for places that have gotten a new name over time, or regions that got absorbed in other surrounding regions over time. You could implement details to infinity.

So, the question is -always-, what functionality do you need, and what is the simplest way to implement it in a normalized form. The "quick and dirty" solution seems perfectly normalized to me, if that is the functionality you are looking for.

share|improve this answer

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.