Best Practice for State and Country field in Dbase Design

Posted on 2010-09-06
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 500 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 :

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 500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common. provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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