Best Practice for State and Country field in Dbase Design

Posted on 2010-09-06
Medium Priority
Last Modified: 2012-05-10
I'm a bit new to dBase design. I'm currently doing my "homework" and going through the tutorials and documentation for PostGreSQL, but this is certainly not going to provide me with "best practices" knowledge that comes from real world experience. So please forgive me for asking some basic database design questions. and if anyone know of a good forum or place dedicated to discussion data base schema, "normalization" etc. please include URL's in your response.

Today I will start with a basic question on the need for separate tables in a simple contacts dBase for Country or State. I had one experience person build a PostGreSQL dbase for us some time back. He had a lot of experience and wanted to have a separate table for countries, to make sure values were constrained to the values in the table based in the ISO standards for country names and abbreviations.

But there is another "school" of thought (our own office staff who program in 4D and not PostgreSQL, and they also have years of experience with ecommerce web apps where names and addresses are coming in ... says that, there is never a need for the ISO country abbreviations, you always need the full country name; and if you constrain the data at the input level (use JS script or old fashion <option> lists) on the web forms, that's good enough, and you don't clutter your back end Dbase with another table you don't need. True, in the future, if you for example, change the spelling of a country name, then you might have old data, in the dBase, and you could just update the spelling in a relational country table and you would fix it, but how often is that going to happen? you could just as easiily update the record themselves. (that have the old spelling)

Similarly I'm being told *not* to bother with a separate table for the the "state" because, since the whole world will be entering their addresses, you would need a separate table that contained all the states for all countries in the world, but that's just "insane" so, again, just use a simple varchar(30) and constrain-validate data entry at the GUI input level.

Again, I'll be posting things here that are more about mentoring a newbie in dBase design, at least for now, and later get into the nitty gritty of queries.  But I want to build the dbase right to start with.
Question by:Sivakatirswami
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 65

Accepted Solution

rockiroads earned 2000 total points
ID: 33613750
The way I think is if you have static data like your list of countries then they should be held in its own table. The primary key of that is then held in your other tables as a secondary/foreign key.
Main advantage of that is if you decide to change the name then all tables that link to it are affected. If you did not have a separate table then you have to remember to update all tables that use it. There is more maintenance work in that.

I have even created it for small lookups like title when entering addresses. I think over time you develop your own practices.

Other advantage is database space, your not using large text fields (though I guess nowadays its not so important). To generate a unique list of countries it is easy to query the lookup table. If you did not have a lookup table you have to generate a list somehow, which to me sounds like hardcoding - which is always a no no imo.

some urls : http://databases.about.com/od/specificproducts/a/normalization.htm

Author Comment

ID: 33613857
OK, thank you... the links to normalization are useful. The "easy way out" (not using another table) is probably not a wise habit pattern to establish at the beginning of my novice journey into this world. I think I will try to opt for proper normalization from the start. So, that settles it, will set up a table called "Country"

But what about "State" where we will have users from all over the world registering?  
LVL 65

Assisted Solution

rockiroads earned 2000 total points
ID: 33613912
Is your design going to be truly global? or mostly american based

if the former then just have a address line field
if the latter then have a states table with all the us states plus one record saying non us or something like that

Author Comment

ID: 33614601
yes, our users will be truly global. So, I will use a plain field and handle the input for US states at the <option> name=value</option> level to get the US states entered as 2 letter abbreviations, with the "other" option on the form, but both inputs going to the same field in the dBase. I believe there are plenty of JS widgets that will hide the US options list if someone enters country other than US.

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this article, we’ll look at how to deploy ProxySQL.
This video teaches users how to migrate an existing Wordpress website to a new domain.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question