Normalization: 'zip code' as a primary key

Question

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?

Answer 1

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.

Answer 2

If I read the question right, then zip code do makes sense as primary key; but only if in a table which ties zip code together with a city name. And I suspect for some countries, states/regions, can be put into that table as well as that information is actually rather static in most (western) countries. Changes do happen, but in my country so rarely that it's an event which is silly to plan for.

I would however not put street names into such a table, because a for each single zip code you will have many street names and then it does not really make sense from a database/normalization viewpoint.

So an address table, would have:

• Address (in various forms and fields, with address1, 2, street number, floor and so on).
• ZipCode (FK)
• other fields

And then you could have a ZipCode/City table:

• ZipCode (PK)
• City
• (State/Region/County - what else is relevant for your given country).

Zip code can easily be a primary key in such structure, and are often used as such for not only normalization but also validation so the user does not have to enter the city name which can reduce the number of errors happening due to various forms of spelling/spelling mistakes.

So - the Address table should not have zipcode as a primary key, but most likely a synthetic key as suggested by the other answer, which then can be used as foreign key on a user object, order object as delivery or billing etc. But the zip code in itself can be a primary key in a normalized structure.

Answer 3

An address is an address and should be in an address table.

A zip code is an American postal code, and should be in a postal code table.

An address is usually composed of some other info and a postal code, except in say Ireland, where they don't have postal codes. So a global, long-term solution does not use a postal code as a primary key for an address.

Postal / zip codes unfortunately are not unique globally. They are unique by country. So you can't use one as a primary key. You could use postal code + country as a primary key for a postal code table.