5

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

enter image description here

  • Are you only planning on storing addresses for the US? There is no single standard for addresses. Some countries have provinces, some have states. Some have other structures entirely. Zipcode / Postalcode, etc. – datagod Aug 13 '15 at 20:12
  • @datagod US only. I only need address lines, city, county, state and zipcode. – Cristian Aug 13 '15 at 20:16
  • This is one of those things that normally kind of bugs me. ZIP codes are always 5 characters. Not 4, not 6 so just use a char(5). Use a FIPS code for the county. No reason to ever need a int if you have a state table - never going to get that large. Use a tinyint. – user1207758 Aug 13 '15 at 21:07
  • 2
    @user1207758 But shouldn't we account for people wanting to enter the whole 9 digits (excluding the dash between char5 and char6) like 28306-2412? – Cristian Aug 13 '15 at 22:43
  • What database are you using? – Evan Carroll Dec 30 '16 at 1:54
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.

  • thanks for your answer. I guess I was thinking it too much. One thing i would have to have is state normalization since I need to restrict users to do stuff only in their residence state. So I guess that normalizing zipcodes and states should be enough. – Cristian Aug 13 '15 at 20:58
  • @Cristian That makes sense to me. It might be helpful to get more details from others about the pain they experienced normalizing zip codes. It really feels like a good idea to normalize to me but their experience tells us that isn't alway true. – Erik Aug 13 '15 at 21:00
  • @Cristian I am guilty of over analyzing things sometimes too. :) – Erik Aug 13 '15 at 21:03
  • Based on your latest Edit regarding avoiding zipcodes normalizing I need to ask this then. How performant would be a query that looks through 200k (denormalized) records and checks for business addresses that are in a specific zipcode? By the way. Is there any site/tool where I can simulate this or I have to do it on my own? – Cristian Aug 13 '15 at 22:37
  • 1
    There is no point in normalizing ZIP codes. Normalization is for data integrity, not for lookup efficiency. There are no facts that are important to an address that are fully functionally dependent on a ZIP code, therefore there is no reason to create a table with ZIP code as its primary key. An index on ZIP is more than adequate for making retrieval by ZIP Code quick. – Joel Brown Aug 14 '15 at 10:43
3

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.

  • Well this looks simple and nice. Would a states lookup table be fine for managing user state resyriction? You said no to state normalization so I'm curious. – Cristian Aug 13 '15 at 21:03
  • I would not restrict state, if dealing with more than 5 countries. I tried that, but when we moved past US, Mexico, Canada, NZ, Australia we found the list of states to be problematic. The main bulk of the addresses were imported from another system. Many places do not record state in their addresses. – datagod Aug 14 '15 at 16:27
  • 1
    I will never have to deal outside US. However, user registration address restricts them to do business with the website only in their residence state. – Cristian Aug 14 '15 at 16:56
2

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.

  • Why not have a zip code table that you normalize against instead of a combined city, state, zip table or a combined city, zip table? – Erik Aug 13 '15 at 20:52
  • Well, that would be optimal, but what do you do with cites that have multiple zip codes? You have to have some way of differentiating them. To be in 3NF, you cannot have the city repeat in the table – stubsthewizard Aug 13 '15 at 21:12
  • True, but I'm not suggesting 3NF nor an optimal setup to support searching for all the zips in a city. My question was trying to figure out why you had pain trying to normalize zip codes. I definitely agree that normalizing zip codes and relating the zip code to the city would be a harder problem than normalizing the zip to an address record irrespective of the city. Based on what you're saying it seems like your pain was around linking one city to multiple zips. At the end of the day you're absolutely right the schema should be based on cost vs benefit for the OP. – Erik Aug 13 '15 at 22:15
  • City and ZIP are many-to-many. Some ZIP codes cover huge areas and some cover a single door. – Joel Brown Aug 14 '15 at 10:38
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.