Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

3 Answers
3

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.

I gave you the answer because of your first sentence. I believe it was a mistake and the 'Street' was possibly meant to be in the 'Student_Detail' table, thereby making the 'zip code' table a different relation. Thanks everyone for your answers. Everyone seems to be correct!
–
JonathanNov 26 '13 at 4:04

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.

At least in the United States, ZIP codes do change pretty regularly. And the ZIP code for an address will change somewhat commonly as subdivisions are built out. But more importantly, ZIP codes cross city, county, and state lines. So if you wanted to have something like this, you'd need zip_code, city, and state to be the composite primary key of your mapping table and have those three columns in the address table as well. That might still make sense for validation purposes, but ZIP code is not part of the geography hierarchy. oseda.missouri.edu/jgb/ZIP.resources.html
–
Justin CaveNov 25 '13 at 7:15

2

In a number of European countries at least, the same is not true. There, zip-codes are rather static information and (major) change is rare.
–
Allan S. HansenNov 25 '13 at 7:36

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.