How to store postal addresses and political divisions in a normalized way?

Question

Is it possible to store an postal address and political divisions in a normalized way so that there is no redundancy? It should be applicable for any state, even if every state needs its own structure.

It's not just about storing addresses. I want to attach additional informations about towns, etc.

Answer 1

The most normalized way I can imagine is that you create tables like countries -> states -> cities -> zips -> addresses. Every of 'node' is in one to many relationship with the next table. Starting from countries which is in one to many relationship with states DDL gonna look like this:

CREATE TABLE countries (
    country_id integer NOT NULL,
    name character varying(80) NOT NULL,
    symbol_3 character(3),  -- i meant ISO-code
    symbol_2 character(2),  -- ISO -code as well
    citizenship character varying(50) -- not obligatory in my case
);

CREATE TABLE states (
    state_id integer NOT NULL,
    country_id integer NOT NULL,
    name character varying(50) NOT NULL
);

CREATE TABLE cities (
    city_id integer NOT NULL,
    state_id integer NOT NULL,
    name character varying(80) NOT NULL
);

CREATE TABLE zips (
    zip_id integer NOT NULL,
    city_id integer NOT NULL,
    number character(5) NOT NULL
);

CREATE TABLE addresses (
    address_id integer NOT NULL,
    zip_id integer NOT NULL,
    street text NOT NULL,
    notes text
);

Remember that (along with the best designing practices) the majority of the attributes in the database should be declared as NOT NULL, mainly because of performance.

And answering your next question (if you already didn't figure it out) zips should be stored as character type - not as one of several numeric data types supported by your db. Why? Because you don't wanna bother with filling with zero or ziros at the beginning of the postal code. Numeric types just truncate zeros at the front of a number.

Useful diagram showing what I meant below:

http://blog.blueage-software.com/pics/blog-images/ERD_country_province.jpg