Best Practice for State and Country field in Dbase Design

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.
Who is Participating?
rockiroadsConnect With a Mentor Commented:
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 :
SivakatirswamiAuthor Commented:
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?  
rockiroadsConnect With a Mentor Commented:
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
SivakatirswamiAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.