A new project has me writing up the same old country/state/province reference tables. My feeling is that these (fairly) static entities should be normalized and referenced by foreign key. I had asked a Stack Overflow question on whether other developers had this prebuilt set of country/state/province create scripts in their toolbelt.
Create The Schema
Create your Country and State tables with this SQL script. As always, name the State table whatever you like (
tblState, whatever). Some folks don't like table names to be the same as reserved keywords.
Here is a collection of insert scripts to get the data populated quickly for you.
How Far To Normalize?
Some developers go further down the normalization path by creating a City table, but I usually pass on that. As always, that decision is largely dependant on the problem domain or task at hand.
Yippee for me - that'll be fun trying to reconcile data when someone enters their city location incorrectly as "St. Paul" / "Tuscon" / "Pittsburg" instead of Saint Paul / Tucson / Pittsburgh.
If you would like to contribute an insert script or two for a country that you would like to see here, just tweet at me with your script!