contradiction of 3rd normal form on address table

Question

I have an Address table and its columns are address_id, address_1, address_2, city, state, country, pin code .Its primary key is address_id . According to 3rd normal form no non-key attribute should depend on other non key attribute. But in this table the non key attribute state is depending on other non key attribute country that means if we know state we don't really require address_id(primary key) to find which country it belongs. So, how should i design the Address table so that it follows 3rd normal form. Also, is 3rd normal form really needed to be applied on database tables? I have attached the image of Address table schema.

Answer 1

You should note that not every country has the same rules for addresses. I would believe that address_id (as an integer) is just a surrogate key for an entry in the Address table. It is not a reference to the country.

If you want to look into the variations in addresses you might find the following interesting.

FRANK'S COMPULSIVE GUIDE TO POSTAL ADDRESSES

Regarding City/State/Country you will not always have all of these values available to you. What about countries that do not have states?

If you have a comprehensive data source for every city, state (including NULL cities and states), and country that is maintained, then you could avoid the extra column.

However, you will need to determine your workaround when some elements are missing. (As you can find examples in Frank's web page.)

My personal opinion (not shared by everyone) is that a state and country column are often better than maintaining a large data set.

If you need is extensive enough you could subscribe to a service to maintain you address information. Just do a web search to find a service that fits your budget.