Normalization for postal addresses in US (address, county, city, state, zipcode)?

Question

I've been trying to understand for the last few years which way is good for storing addresses. I've been getting "normalize all the way" but also "denormalize as much as you can" and I just cannot get my head on deciding what is good for my project.

Shortly, my project would involve lots of users (100k+) and all users will have 1-3 addresses stored (personal, business and billing). That means that I can have 100k+ * 3 records for addresses. Also, I will be doing a lot of look-ups by zipcodes (get users that have addresses registered into a zipcode). I will only have U.S addresses.

I am happy with the user-to-address tables and their relationship for my project. However, the tables without relationships is what drives me nuts.

(My tables displayed in the image are like that just for me to get a better view on what I need and how do to it. I know there are a lot of redundant fields so please don't take them as they are.)

Does anyone has any tips on how should this be designed?
Does anyone has a link or something to a schema or a similar schema of what big companies use (UPS, USPS, etc)?

Answer 1

I think that @datagod's answer is good, but I would tweak it a little based on your stated requirements:

Address Table

AddressLine1 varchar(255) -- If using SQL Server I would go with NVARCHAR instead. You don't seem to need unicode support but why not support it since things will often be converted to unicode in the application layer by default anyway, and storage is cheap.
AddressLine2 varchar(255)
City varchar(50)
ZipCodeID int -- FK to PostalCode table
County varchar(50)
State      varchar(50)

As you can see my recommendation is very similar to @datagod's. I changed two things:

1. I got rid of the Country FK since you stated you only need addresses for the United States.
2. I made ZipCode/PostalCode a FK. I think this will allow you to index/query zip codes more effectively.

Furthermore I feel like you don't need to upload an external master list of zip codes unless you want to use that list for data validation purposes... You can check if that zip code exists when the address is inserted and insert it into the zip code table if it doesn't exist. This will add some overhead on insert but I don't think it will be that much since common zip codes will be inserted pretty quickly.

If you are going to move international then I would definitely add the Country table as suggested by @datagod.

Normalizing the database down to cites/counties/streets etc. seems like overkill to me at this point unless any of the following apply:

• You find yourself querying by those data points often and would benefit from the indexes/normalization
• You have to do some sort of region based security, ie Sales located in Atlanta can't access information outside of these three counties.
• You want to use those lists as data validation to make sure people aren't giving you bad data. (This seems like it will be a mess to implement though depending on how far you want to validate the data.)
• Some other reason that I haven't thought of that makes further normalization to make your life easier.

I don't have @datagod's experience with millions of address records so my advice might be plain wrong, but it is the approach I would take.

Edit: Two answers are eschewing normalizing the zip code now so I might be overlooking a pain point because I haven't experienced it.

Answer 2

I deal with millions of existing international addresses. The following design works for my project:

Address Table

AddressLine1 varchar(255)
AddressLine2 varchar(255)
City varchar(50)
PostalCode varchar(20)
State      varchar(50)
CountryID  int  (FK to Country table)

Avoid the temptation to normalize postal codes and states, unless you really do have a master list that is updated frequently.

Countries are easier to manage, so they belong in their own lookup table. Master lists can be found online readily.

Answer 3

I tried a while back to normalize zipcodes, and it does not really work, because there can be multiple zip codes for a city. So, while you can normalize City and State, Zip code just has to be put in the table.

I suppose you could normalize on city+zipcode, such that you might have a table with something like 1 | "Indianapolis 46422", and 2 | "Indianapolis 46421" in it.

What you have to look at, though, is cost vs benefit. Believe me, I am a big stickler for 3NF databases in my shop, but normalizing based on city and zip code would be so tedious, that I can perceive no benefit that would outweigh the cost.

Answer 4

For what it's worth, the USPS has databases available which can be used to disabiguate and check addresses in this country -- which zipcodes cover which towns in which state, which streets (and which street addresses) exist within each zipcode, all the way down to which side of the street the address is on and within which block, and these days probably lat/long approximation. These are the databases companies use to check mailing addresses, and they're part of the information that mapping systems such as car GPS units use to determine where an address actually is.

I don't know whether this data is available for free, or if there's a fee to purchase and update it; last time I looked at this they were still mailing it out on tapes, and obviously the economics are very different now. It wasn't unreasonably expensive even then; the USPS doesn't really want to deal with stuff mailed to garbled addresses.

Answer 5

This is not so much an answer as some comments about why your question is so difficult to get right. Note that I am in Australia, where the addressing is similar, but not exactly the same.

The key to database is that it represents data, not information, which is much more complex. Data follows simplistic rules which don’t always reflect real life.

Take, for example towns, states and postal codes. States are unique, but towns are not. Postal codes are supposed to solve that problem, but, at least in Australia, multiple towns can have the same postal code, and, occasionally, one town can have multiple postal codes. And, occasionally, towns can have postal codes from the range in adjacent state.

That means that each town is really part of a town/state/postal code combination. Although the postal service has some sort of notion of the relationship between them, it’s not a very strict one, and there are many (partial) exceptions.

For this reason, I think it is best to represent towns as a separate table, and regard the town, state and postal code as distinct columns of data, and overlook the relationship between them which would defy normalisation.

You can regard the combination as a Primary Key, though I always prefer a distinct primary key, and simply make a unique key out of the combination of the rest.

I don’t know where counties come in, as we don’t use them here.