Solved

Best Practice for State and Country field in Dbase Design

Posted on 2010-09-06
4
807 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
  • 2
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now