Creating database structure to support countries and their states

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.

Populate Countries Script

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s