I've seen frequently that you should not have all address fields (zip, street, city, state, country, etc) in the same table as your user info table (pk_id, name, email… ).
This would be in violation of the 3rd normalization. That makes sense, all the address fields are not based on the user (or user's pk).
However, I have seen more than once that a zip code makes a good primary/foreign key to fix this? Huh? Example: Normalization of Database
Obviously more than one person can live in the same zip code so this does not make sense to me. It seems odd to me to keep the zip code in the user table but leave the rest of the address in another table.
What is the standard best way to have an address table separated, but still abiding by the rules of normalization?
Best Answer
The page you linked to is, quite simply, wrong.
It makes no sense for a ZIP code to be a primary key in an address table. You would expect to have multiple addresses in the same ZIP code, as you said. ZIP codes cross city, county, and state lines which makes it uniquely poor as a key (postal codes in other countries may not be as problematic but data modelers always have to worry about the worst case). Plus, ZIP codes change over time for an address which is not something you want in your primary keys.
In general, given that addresses tend not to have anything that works as a natural primary key, your address table will need a synthetic primary key (
address_id) that has no meaning and simply acts as a primary key. Databases have different ways to generate synthetic primary keys, sequences and auto-incrementing columns are common approaches.