HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

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

Submitted by: @import:stackexchange-dba··
0
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)?

Solution

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.

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.