Over normalization in database

Question

Currently I am working on creating a new database schema as part of a major new product. Our previous customer address records (in a previous product) look something like this in our current schema (not all columns shown):

AddressID, AddressLine1, AddressLine2, City, State. Zip, Country, Etc.

I want to normalize this so that an address actually breaks down to:

• 1 address to many address lines
• 1 address to a zip/postal code

So I sent out a communication saying that zip code will now be in its own table and that each "address" corresponds to one zip/postal code. In addition, addresses would be broken out so that we could support more than 2 address lines with a 1 to many relationship.

Some people in my group are now saying that I am over-normalizing the database. Am I?

Note, I am not a DBA but I feel that this is more logical and more efficient than the previous schema.

Update

Thx to everyone for their input. I'm not going to break out the address or zips to their own tables. My original plan was to have a zip code lookup and re-use for the addresses so I would only have to have one set of zips, but I'll just drop everything on the record itself, and have a separate table for zip lookups.

Answer 1

You are overnormalizing (and as a database specialist, I don't say that often).

Think of how you use this data and you will see it makes the most sense to be in one table. If each address related to multiple zip codes, a second table makes sense. Since the relationship is one-to-one there is no need at all to split out the table unless you have too wide a record which is rarely the case in an address table.

Also since your configuration is not the common way that this data is stored, you will create a maintenance issue as new devs will be confused by the structure. Really, each address should be completely contained in one record.

Answer 2

Yes and no. You can have a lookup table for the ZipCodes, however, I wouldn't use a key to relate the two. Use the actual ZipCode in the Address record.

And since AddressLine1, AddressLine2, etc are really quite arbitrary, rather than breaking it into it's own table, just change the field to AddressLines and have it contain line feeds where applicable.

It will make searching easier as it's in one field. And you always display them together anyhow. Since they are just free-text, you can't do anything meaningful with them split. Youre two AddressLine fields currently can contain anything from box numbers, appt numbers, street addresses, care of text, etc... and you never know which one will contain what. If you do know, they should be labeled better.

Answer 3

The table of zip codes isn't too unusual, and does make sense if you want other information to be tied to zip codes - either other columns in the zip code table or references from other tables (along the lines of salesmen have regions which are sets of zip codes).

I assume you want address lines in their own table because you want to follow first normal form. I've never seen anyone actually do this, probably because it's so unlikely that you will need more than a very small number of columns for address lines, ever.

I find it unlikely you will get any practical value out of it. Consider some of the other things you might do to rigorously model names and addresses:

1 Have a separate table for each name, instead of first, last, middle, since a person might have any number of names (and it's actually much more common than for address lines to have a large number of them).

1 Have address be many to many with entity, since in the real world this is actually how it works.

2 Use address standardization software to get the many to many mapping right.

3 Support international addressing for all countries.

4 Have name be many to many with address, because you might use more informal names when contacting someone at home.

5 Have name be many to many with entity, since some people might use nicknames only with certain people.

Now imagine writing a query to put a bunch of people's name and address on a report.

Are any of these ways of modelling the data wrong? No, not in and of themselves. But for most applications they are more work than they are worth. Your question didn't make any sort of case your design other than that it seemed logical to you. I would be very careful about having a good justification for the added complexity before making that change, because it's a drag joining 14 tables for a simple query, and those 14 tables can creep up on you.

Answer 4

Jeez.

At the relational level, there's no such thing a "over normalization" or "under normalization". Instead, there are formal specifications for first normal form, second normal form, and so on.

Mailing labels have lines. Addresses don't.

On the one hand, factoring ZIP codes into their own table doesn't necessarily improve data integrity. It might limit users to choosing an existing ZIP code, but it might also allow users to associate an Alabama ZIP code with San Francisco, Calif. On the other hand, a foreign key reference to a table of {city, state, ZIP} probably will improve data integrity in a table of US addresses.

Repeated data in a column doesn't violate any normal form. To be more specific, a table of addresses that has 3 million rows might have 400 addresses in ZIP code '90210'. Having 400 addresses that share the same ZIP code doesn't violate any normal form.

Substituting an ID number for a ZIP code doesn't change the normal form, and it doesn't improve data integrity. Ditto for cities and states.

Using ID numbers as surrogate keys requires joins. Setting a foreign key reference to a table of {city, state, zip} doesn't require a join. Queries are exactly the same after setting the foreign key reference; they don't need to change at all.

Each country has their own governing body that makes rules for addressing. Different governing bodies, different rules. Different rules, different constraints. Different contstraints, different domains. Different domains, different tables.

Answer 5

It's hard to say. Usually postal codes and zip codes are not normalized to a separate table (from most of the databases I've seen), but there may be cases where it makes sense. I don't know enough about your business domain to know if it does. I've seen reporting databases that had tables of zip/postal codes, and then also had other data (some codes - I don't know what they were for) associated with each zip/postal code. In that case, the addresses referenced the postal/zip codes through a combination of territory IDs and something else... it was not as simple as I had hoped!

Normalizing the address lines does seem excessive. How often are you going to reference the same Address Line 1 value (assuming I understand what you are doing)? I suppose it could happen where you have hundred of records in all sorts of tables referencing "123 Main Street" but it seems very unlikely to me (but if you do have that data then it might make sense).

Answer 6

I don't know enough about your application to know when this amount of data break down makes sense. If you require strong control over city-state-country-zip code combinations (voter registration, emergency vehicle response), you can have a table to manage this. You really need a strong understanding how this works, so you don't paint your users into a data corner. Cities can have multiple zip codes and some zip codes can have multiple cities or other entities. Make sure you can justify this additional complexiity. Other members of your firm disagree. You need a better arguement than normalization.

There is no need to break out address lines.

Answer 7

Another reason to split things is if you have to offer your application in multiple languages where the same city, state and country name can be different in each language.

I have seen the same address be different in one language than another. (think rue and street) but I agree that for most applications the address fields can all be in one table.

Breaking an address into multiple fields makes printing labels much easier and allows for the edge conditions where the address is: Around the bend, at the corner of x and y, down the street from here, and over there. North American address standards are not followed across the world so a few extra address fields helps out.

Answer 8

I'm not sure if this applies or not but I think I have a crash-and-burn case for your schema: I have owned two properties within a single zip code.

Answer 9

If you need to visualize large sets of geo based data (graphs, maps, ...) , report on it ,e tc, not normalizing your address data will be a real performance nightmare.

The problem I'm trying to overcome at my current project is "bad data", and "incomplete data".. We found people living in belgium with zip code: 'CHINA' in our clients legacy database. DEAR GOD.

Doing it right will require a lot of data cleanup, and should allow a flexible way to add new cities/zipcodes + merge zipcodes when cities merge. Across the world this happens a lot. Depending on the size of your project and the geographical scope you should onsider if it's worth by looking at the actual data you have.

Answer 10

Yeah, probably.

Do you have a specific business case/conceptual reason for separating them? Have you considered the impact on the application of making the queries more complex, both in terms of performance and application maintenance? Will someone now have to debug sql queries that now feature a bunch of joins, where before it was a simple select? (I'm not talking just simply selecting addressess, but when addressess need to be extracted along with other data).

Normalization is about storing data efficiency. But there's other important factors in software development which often trump the need for the maximum efficiency in storage.

Answer 11

Normalization for the sake of normalization is a bad idea. How far do you take it?

As others have said, normalizing the address might make sense, but it's probably just going to make it harder to work with.

For the zip codes, I don't see much of a point. They are only 5 digits long (9 with +4) and as such can be represented by a single 32-bit integer field. My suggestion would be to continue doing that, and if the need arises to split it into a separate table, just use the zip code as the PK and slap an FK on the existing column. This gives you the benefit of normalization, without implementing something before you need it (YAGNI)

Answer 12

The rule of thumb is to ask if your changes will allow you to use just a query to retrieve data that previously involved some manipulation in the software, or at least allow for a simpler query. The answer here is no. In fact, your changes will make some common existing queries more complex.

You're already able to retrieve a list of addresses by zip code. The only reason to split them out would be if you're using zip codes for another purpose than customer records, such as typing in a zip code to auto-populate the city and state. Even then, the easiest key for the zip code table is the zip code itself.