Solved

Best Practice for State and Country field in Dbase Design

Posted on 2010-09-06
4
811 Views
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.
0
Comment
Question by:Sivakatirswami
[X]
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
4 Comments
 
LVL 65

Accepted Solution

by:
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 : http://databases.about.com/od/specificproducts/a/normalization.htm
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
0
 

Author Comment

by:Sivakatirswami
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?  
0
 
LVL 65

Assisted Solution

by:rockiroads
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
0
 

Author Comment

by:Sivakatirswami
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.
0

Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This video teaches users how to migrate an existing Wordpress website to a new domain.
Wufoo.com 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.

728 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