As part of the project I am working on right now I need to implement a database structure to maintain countries with their corresponding states. I found a few websites/blogs that helped a little, but nothing really suited my needs, although the country and states/provinces codes are based on the ISO format: http://www.maxmind.com/app/iso3166. I though posting on this as it might help someone else out there!
After creating your database, open a new query window in SQL Server Management Studio and create a table for the countries…
USE YOUR_DATABASE_NAME; GO
CREATE TABLE Countries(
CountryID INT NOT NULL IDENTITY (1,1),
CountryName VARCHAR(256) NOT NULL,
CountryCode VARCHAR(256) NULL,
CountryDescription VARCHAR(256) NULL
CONSTRAINT CountryPK PRIMARY KEY (CountryID));
GO
then a second table containing the states.
CREATE TABLE Country_States(
StateID INT NOT NULL IDENTITY (1,1),
CountryID INT NOT NULL,
StateName VARCHAR(256) NOT NULL,
StateCode VARCHAR(256) NULL,
StateDescription VARCHAR(256) NULL
CONSTRAINT StatePK PRIMARY KEY (StateID),
CONSTRAINT Country_StatesCountryFK FOREIGN KEY (CountryID) REFERENCES Countries (CountryID));
GO
Once the tables are created, stored procedures are used to fill in the tables. I left the country description blank.
Then populate the country_states table…
Populate Country_States Script
There are some drawbacks with this structure.; There can be states in different countries having the same state code. For example, WA stands for Washington state in the US but is also used to denote WesternAustralia state in Australia. When building your application or importing data from that needs to compare the statecode, make to consider the country in which the state is or simply change the state code.
