Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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. Also 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 thier input. I'm not going to break out the address or zips to thier 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.

share|improve this question
3  
I don't understand why is this line 1,2 business - why not just use a column AddressLines with TEXT data type? –  Codism Aug 29 '11 at 21:17
    
@Codism: Many older systems seem to like to have to distinct address lines (though one I saw had 4, but #3 and #4 were almost never used). Newer systems seem to have this to maintain better compatibility. I am not sure if the Address Line 1..2 is done to make printing mailing labels easier or if it originated in some field-size limit, but it's quite common. –  FrustratedWithFormsDesigner Aug 29 '11 at 21:21
    
Are you going to prepopulate the zip, city, and state data? –  JeffO Aug 29 '11 at 21:22
    
@FrustratedWithFormsDesigner: I was not really asking but thanks for your explanation. Although we cannot question the old systems, a db designer who puts this design for a new system must be brain-dead. –  Codism Aug 29 '11 at 21:30
    
Please take a look at this post [enter link description here][1] [1]: stackoverflow.com/questions/310540/… –  Emmad Kareem Aug 29 '11 at 23:11

12 Answers 12

up vote 16 down vote accepted

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.

share|improve this answer
8  
Nitpicking developer comment - address to zip code is many to one, since many addresses share a zip code. It's just that usually nobody cares - if you have a person table you almost never make person many to one with name and make the first name field a foreign key. –  psr Aug 29 '11 at 21:46
2  
@Jon Raynor, who cares if there are repeats in city, state, zip? What benefit do you expect to gain normalizing this? –  CaffGeek Aug 30 '11 at 17:01
1  
@Chad: Lots. For one, having a separate table populated with a database of ZIP or other postal codes helps to validate input and clean up incorrect city and state data. For another, with all three correctly tied together as tables, you can cook up all sorts of queries to make your marketing more effective. If you see a lot of sales from, say, Los Angeles and want to target more customers there, non-normalized address data will lose rows with typos like "Los Anglees." –  Blrfl Aug 30 '11 at 23:33
1  
@Blrfl, I have used many databases that try to keep a complete record of addresses and such for validation purposes. Ultimately, they break down and you end up having to loosen your restrictions. City names can change. There are lots of duplicated city names. And no list is complete, so you need to allow free text anyway. And in some areas of the world, you can't even get an accurate address. And restricting it to what's in your database could mean a customer can't enter a valid shipping address, and won't receive your product. I'd rather miss a customer during marketing, than sales. –  CaffGeek Aug 31 '11 at 13:48
1  
Never said to restrict it. If the costs of offering clean-up suggestions at the point where the data enters system are less than the costs of having it wrong in the system and acting on it, it's worth doing. You can always accept fishy-looking data, create records to hold it that are flagged as such and use that to drive the process of checking and correcting it. –  Blrfl Aug 31 '11 at 15:02

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
this kind of stuff makes my developer "god complex" tickle so much. there shouldn't be a "Allemagne" in any database imo, Deutschland, Great-Britain, België, United States, 中國 for all I care. If only we could have a 'metric system' for addresses around the world, and a standard on how to format it. :D –  Mvision Jun 21 '13 at 19:20
    
I think this is being worked on for the millions of people who do not have an address at all. Their GPS co ordinates are being considered given that they are offer precision to within 30 feet –  kevinsky Jun 21 '13 at 20:32

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
I agree creating a report would be harder. But this is a OLTP schema, reporting would be separate database and most likely using cubes. I also could use views to make the queries easier (hide the joins) for other developers. –  Jon Raynor Aug 30 '11 at 2:33
    
I've worked with databases where people used views to make a very normalized database easier, but really if you can fully hide it with a view you don't need it in the first place. Either people use the view and break on the edge case or there is no edge case to justify the more complicated structure. –  psr Aug 30 '11 at 3: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)

share|improve this answer
    
Are all postal codes numerical? stackoverflow.com/questions/893454/… –  JeffO Aug 29 '11 at 21:35
1  
@Jeff O, no they are not. In Canada a postal code is A9A 9A9 –  CaffGeek Aug 29 '11 at 21:40
    
We would be using postal codes, so its not an int since the application is used in Europe and countries outside of the US. –  Jon Raynor Aug 30 '11 at 2:24

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.

share|improve this answer

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.

share|improve this answer
    
What do you do when a zip code is in more than one city? –  JeffO Aug 29 '11 at 21:32
    
@Jeff O, Who cares if the zip code is in more than one city? I don't see what the issue with that is? You may have some lookup tables which you use to validate a zip code is valid for a given city, but that doesn't change the address record. –  CaffGeek Aug 29 '11 at 21:39
    
I didn't realize you were going to have individual lookup tables for: zip, city and zip/city. That's why I asked; sorry I bothered you. –  JeffO Aug 29 '11 at 23:32

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).

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.