patternMinor
Normalization for postal addresses in US (address, county, city, state, zipcode)?
Viewed 0 times
citycountyaddressaddressesforstatezipcodenormalizationpostal
Problem
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)?
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)?
Solution
I deal with millions of existing international addresses. The following design works for my project:
Address 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.
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.
Code Snippets
AddressLine1 varchar(255)
AddressLine2 varchar(255)
City varchar(50)
PostalCode varchar(20)
State varchar(50)
CountryID int (FK to Country table)Context
StackExchange Database Administrators Q#110963, answer score: 3
Revisions (0)
No revisions yet.