0

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.

Matt
  • 22,721
  • 17
  • 71
  • 112
Jimmy T.
  • 4,033
  • 2
  • 22
  • 38
  • I'm not sure you'll be able to find a one size fits all that's applicable internationally. Or do you want a solution just for one country? If so, please specify. – fvu Oct 07 '12 at 10:12
  • @fvu It should be for more than one country, but it don't have to use the same tables for every country. – Jimmy T. Oct 07 '12 at 10:20
  • why does no one on here read data model pattern books? – Neil McGuigan Oct 07 '12 at 18:37

2 Answers2

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

Borys
  • 2,676
  • 2
  • 24
  • 37
  • 1
    Please be aware that different cities are allowed to share the [same zip code](http://host.madison.com/wsj/news/local/ask/article_dd4efd2c-617a-11e0-876d-001cc4c002e0.html). – Dour High Arch Oct 16 '12 at 00:21
  • It depends on which country we are talking about. But thank you for the notice. – Borys Oct 16 '12 at 09:38
  • To avoid this problem one can add surrogate key to the zips table, then it'll be possible to write as many as necessary duplicated zip codes. – Borys Oct 16 '12 at 09:47
0

Anything can be stored in a normalized form. Tools like 5th Normal Form and the Principle of Orthogonal design can be used to eliminate certain kinds of problems in your database design. No normal form claims to eliminate all types of redundancy however and nor is it desirable or even achievable to do so (the information theoretic view is that redundancy is the useful information in a piece of data).

You may find the Party Data Model helpful: http://www.tdan.com/view-articles/5014/

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • Modeling itself is not the problem. It's more that I don't know what belongs together. For Example: What does the postal code identify? – Jimmy T. Oct 07 '12 at 11:34
  • Postal codes in my experience are of limited use in identifying anything. You can implement some software or database that verifies, enriches and even excludes inconsistent addresses but even then it's of very little use to assume that the code is a totally reliable way to determine some part of address. New codes are issued or withdrawn all the time and even the official post office databases don't remain consistent or up-to-date. – nvogel Oct 07 '12 at 11:55