Jason Irwin dot Net

Helping Out With Some (My)SQL

February 23, 2009 Development, Technology

How many times have you had the luxury to create a database from scratch?  If you’re like me, the answer is probably “not enough.”  But, regardless of how often we can flex our mental muscles with the complete design and implementation of a database solution, there are some pretty mundane aspects that go along with the work.  I speak, of course, about “Entering the Default Data.”

I am a firm believer in offering complete data to the people who will use my applications.  This means that my default database installations often go above the call of duty by containing way more information than may seem necessary at first.  For example, when creating a table called “country”, I don’t just put the few countries that an organization might actually deal with … I put all of the countries that currently exist in there.  From there, the countries that need to be seen are filtered out with simple column flags.  Is this absolutely necessary in all databases?  Heck, no.  But I prefer to give people as much base information as possible to avoid confusion or excessive setup later when an organization needs to deal with a new market.

Here is a basic example of a table named “Country”, along with the default nations:

DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
`Code` char(2) COLLATE utf8_bin NOT NULL,
`DefaultName` varchar(100) COLLATE utf8_bin NOT NULL,
`SortOrder` smallint(6) NOT NULL DEFAULT '50',
`isDefault` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N',
`isDeleted` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N',
`UpdateDTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`UpdateBy` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT 'dbo',
PRIMARY KEY (`Code`),
KEY `CountryCodesDDX` (`Code`,`SortOrder`,`isDefault`,`isDeleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Country List';

INSERT INTO `Country` (`Name`, `Code`)
VALUES ('AFGHANISTAN', 'AF'), ('ÅLAND ISLANDS', 'AX'), ('ALBANIA', 'AL'), ('ALGERIA', 'DZ'), ('AMERICAN SAMOA', 'AS'), ('ANDORRA', 'AD'), ('ANGOLA', 'AO'), ('ANGUILLA', 'AI'), ('ANTARCTICA', 'AQ'), ('ANTIGUA AND BARBUDA', 'AG'), ('ARGENTINA', 'AR'), ('ARMENIA', 'AM'), ('ARUBA', 'AW'), ('AUSTRALIA', 'AU'), ('AUSTRIA', 'AT'), ('AZERBAIJAN', 'AZ'), ('BAHAMAS', 'BS'), ('BAHRAIN', 'BH'), ('BANGLADESH', 'BD'), ('BARBADOS', 'BB'), ('BELARUS', 'BY'), ('BELGIUM', 'BE'), ('BELIZE', 'BZ'), ('BENIN', 'BJ'), ('BERMUDA', 'BM'), ('BHUTAN', 'BT'), ('BOLIVIA', 'BO'), ('BOSNIA AND HERZEGOVINA', 'BA'), ('BOTSWANA', 'BW'), ('BOUVET ISLAND', 'BV'), ('BRAZIL', 'BR'), ('BRITISH INDIAN OCEAN TERRITORY', 'IO'), ('BRUNEI DARUSSALAM', 'BN'), ('BULGARIA', 'BG'), ('BURKINA FASO', 'BF'), ('BURUNDI', 'BI'), ('CAMBODIA', 'KH'), ('CAMEROON', 'CM'), ('CANADA', 'CA'), ('CAPE VERDE', 'CV'), ('CAYMAN ISLANDS', 'KY'), ('CENTRAL AFRICAN REPUBLIC', 'CF'), ('CHAD', 'TD'), ('CHILE', 'CL'), ('CHINA', 'CN'), ('CHRISTMAS ISLAND', 'CX'), ('COCOS (KEELING) ISLANDS', 'CC'), ('COLOMBIA', 'CO'), ('COMOROS', 'KM'), ('CONGO', 'CG'), ('CONGO, THE DEMOCRATIC REPUBLIC OF THE', 'CD'), ('COOK ISLANDS', 'CK'), ('COSTA RICA', 'CR'), ('CÔTE D''IVOIRE', 'CI'), ('CROATIA', 'HR'), ('CUBA', 'CU'), ('CYPRUS', 'CY'), ('CZECH REPUBLIC', 'CZ'), ('DENMARK', 'DK'), ('DJIBOUTI', 'DJ'), ('DOMINICA', 'DM'), ('DOMINICAN REPUBLIC', 'DO'), ('ECUADOR', 'EC'), ('EGYPT', 'EG'), ('EL SALVADOR', 'SV'), ('EQUATORIAL GUINEA', 'GQ'), ('ERITREA', 'ER'), ('ESTONIA', 'EE'), ('ETHIOPIA', 'ET'), ('FALKLAND ISLANDS (MALVINAS)', 'FK'), ('FAROE ISLANDS', 'FO'), ('FIJI', 'FJ'), ('FINLAND', 'FI'), ('FRANCE', 'FR'), ('FRENCH GUIANA', 'GF'), ('FRENCH POLYNESIA', 'PF'), ('FRENCH SOUTHERN TERRITORIES', 'TF'), ('GABON', 'GA'), ('GAMBIA', 'GM'), ('GEORGIA', 'GE'), ('GERMANY', 'DE'), ('GHANA', 'GH'), ('GIBRALTAR', 'GI'), ('GREECE', 'GR'), ('GREENLAND', 'GL'), ('GRENADA', 'GD'), ('GUADELOUPE', 'GP'), ('GUAM', 'GU'), ('GUATEMALA', 'GT'), ('GUERNSEY', 'GG'), ('GUINEA', 'GN'), ('GUINEA-BISSAU', 'GW'), ('GUYANA', 'GY'), ('HAITI', 'HT'), ('HEARD ISLAND AND MCDONALD ISLANDS', 'HM'), ('VATICAN CITY', 'VA'), ('HONDURAS', 'HN'), ('HONG KONG', 'HK'), ('HUNGARY', 'HU'), ('ICELAND', 'IS'), ('INDIA', 'IN'), ('INDONESIA', 'ID'), ('IRAN, ISLAMIC REPUBLIC OF', 'IR'), ('IRAQ', 'IQ'), ('IRELAND', 'IE'), ('ISLE OF MAN', 'IM'), ('ISRAEL', 'IL'), ('ITALY', 'IT'), ('JAMAICA', 'JM'), ('JAPAN', 'JP'), ('JERSEY', 'JE'), ('JORDAN', 'JO'), ('KAZAKHSTAN', 'KZ'), ('KENYA', 'KE'), ('KIRIBATI', 'KI'), ('KOREA, DEMOCRATIC PEOPLE''S REPUBLIC OF', 'KP'), ('KOREA, REPUBLIC OF', 'KR'), ('KUWAIT', 'KW'), ('KYRGYZSTAN', 'KG'), ('LAO PEOPLE''S DEMOCRATIC REPUBLIC', 'LA'), ('LATVIA', 'LV'), ('LEBANON', 'LB'), ('LESOTHO', 'LS'), ('LIBERIA', 'LR'), ('LIBYAN ARAB JAMAHIRIYA', 'LY'), ('LIECHTENSTEIN', 'LI'), ('LITHUANIA', 'LT'), ('LUXEMBOURG', 'LU'), ('MACAO', 'MO'), ('MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF', 'MK'), ('MADAGASCAR', 'MG'), ('MALAWI', 'MW'), ('MALAYSIA', 'MY'), ('MALDIVES', 'MV'), ('MALI', 'ML'), ('MALTA', 'MT'), ('MARSHALL ISLANDS', 'MH'), ('MARTINIQUE', 'MQ'), ('MAURITANIA', 'MR'), ('MAURITIUS', 'MU'), ('MAYOTTE', 'YT'), ('MEXICO', 'MX'), ('MICRONESIA, FEDERATED STATES OF', 'FM'), ('MOLDOVA', 'MD'), ('MONACO', 'MC'), ('MONGOLIA', 'MN'), ('MONTENEGRO', 'ME'), ('MONTSERRAT', 'MS'), ('MOROCCO', 'MA'), ('MOZAMBIQUE', 'MZ'), ('MYANMAR', 'MM'), ('NAMIBIA', 'NA'), ('NAURU', 'NR'), ('NEPAL', 'NP'), ('NETHERLANDS', 'NL'), ('NETHERLANDS ANTILLES', 'AN'), ('NEW CALEDONIA', 'NC'), ('NEW ZEALAND', 'NZ'), ('NICARAGUA', 'NI'), ('NIGER', 'NE'), ('NIGERIA', 'NG'), ('NIUE', 'NU'), ('NORFOLK ISLAND', 'NF'), ('NORTHERN MARIANA ISLANDS', 'MP'), ('NORWAY', 'NO'), ('OMAN', 'OM'), ('PAKISTAN', 'PK'), ('PALAU', 'PW'), ('PALESTINIAN TERRITORY, OCCUPIED', 'PS'), ('PANAMA', 'PA'), ('PAPUA NEW GUINEA', 'PG'), ('PARAGUAY', 'PY'), ('PERU', 'PE'), ('PHILIPPINES', 'PH'), ('PITCAIRN', 'PN'), ('POLAND', 'PL'), ('PORTUGAL', 'PT'), ('PUERTO RICO', 'PR'), ('QATAR', 'QA'), ('RÉUNION', 'RE'), ('ROMANIA', 'RO'), ('RUSSIAN FEDERATION', 'RU'), ('RWANDA', 'RW'), ('SAINT BARTHÉLEMY', 'BL'), ('SAINT HELENA', 'SH'), ('SAINT KITTS AND NEVIS', 'KN'), ('SAINT LUCIA', 'LC'), ('SAINT MARTIN', 'MF'), ('SAINT PIERRE AND MIQUELON', 'PM'), ('SAINT VINCENT AND THE GRENADINES', 'VC'), ('SAMOA', 'WS'), ('SAN MARINO', 'SM'), ('SAO TOME AND PRINCIPE', 'ST'), ('SAUDI ARABIA', 'SA'), ('SENEGAL', 'SN'), ('SERBIA', 'RS'), ('SEYCHELLES', 'SC'), ('SIERRA LEONE', 'SL'), ('SINGAPORE', 'SG'), ('SLOVAKIA', 'SK'), ('SLOVENIA', 'SI'), ('SOLOMON ISLANDS', 'SB'), ('SOMALIA', 'SO'), ('SOUTH AFRICA', 'ZA'), ('SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS', 'GS'), ('SPAIN', 'ES'), ('SRI LANKA', 'LK'), ('SUDAN', 'SD'), ('SURINAME', 'SR'), ('SVALBARD AND JAN MAYEN', 'SJ'), ('SWAZILAND', 'SZ'), ('SWEDEN', 'SE'), ('SWITZERLAND', 'CH'), ('SYRIAN ARAB REPUBLIC', 'SY'), ('TAIWAN, PROVINCE OF CHINA', 'TW'), ('TAJIKISTAN', 'TJ'), ('TANZANIA, UNITED REPUBLIC OF', 'TZ'), ('THAILAND', 'TH'), ('TIMOR-LESTE', 'TL'), ('TOGO', 'TG'), ('TOKELAU', 'TK'), ('TONGA', 'TO'), ('TRINIDAD AND TOBAGO', 'TT'), ('TUNISIA', 'TN'), ('TURKEY', 'TR'), ('TURKMENISTAN', 'TM'), ('TURKS AND CAICOS ISLANDS', 'TC'), ('TUVALU', 'TV'), ('UGANDA', 'UG'), ('UKRAINE', 'UA'), ('UNITED ARAB EMIRATES', 'AE'), ('UNITED KINGDOM', 'GB'), ('UNITED STATES', 'US'), ('UNITED STATES MINOR OUTLYING ISLANDS', 'UM'), ('URUGUAY', 'UY'), ('UZBEKISTAN', 'UZ'), ('VANUATU', 'VU'), ('VENEZUELA', 'VE'), ('VIET NAM', 'VN'), ('VIRGIN ISLANDS, BRITISH', 'VG'), ('VIRGIN ISLANDS, U.S.', 'VI'), ('WALLIS AND FUTUNA', 'WF'), ('WESTERN SAHARA', 'EH'), ('YEMEN', 'YE'), ('ZAMBIA', 'ZM'), ('ZIMBABWE', 'ZW')

This isn’t the only way to build a country table, and I’m sure there are far more efficient means of doing so for an organization that has a very clear target but, when dealing with a multinational organization, this is one option that could save a good deal of time.

Need some states, provinces, and other base information to go along with the countries?  No problem.  Here are the complete state/province/prefecture details for the United States, Canada, Japan, China, South Korea, and North Korea.  Let me know if you need some other country’s details, as I have most of these done.

DROP TABLE IF EXISTS `state`;
CREATE TABLE `state` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Code` varchar(10) COLLATE utf8_bin NOT NULL,
`DefaultName` varchar(80) COLLATE utf8_bin NOT NULL,
`CountryCode` char(2) COLLATE utf8_bin NOT NULL,
`isDefault` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N',
`isDeleted` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N',
`UpdateDTS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`UpdateBy` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT 'dbo',
PRIMARY KEY (`id`),
KEY `StateCodesDDX` (`Code`,`CountryCode`,`isDefault`,`isDeleted`)
) ENGINE=InnoDB AUTO_INCREMENT=187 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Region List';

Prefecture List for Japan:
INSERT INTO `State` (`CountryCode`, `Code`, `DefaultName`)
VALUES ('JP', '01', 'Hokkaido'), ('JP', '02', 'Aomori'), ('JP', '03', 'Iwate'), ('JP', '04', 'Miyaga'), ('JP', '05', 'Akita'), ('JP', '06', 'Yamagata'), ('JP', '07', 'Fukushima'), ('JP', '08', 'Ibaraki'), ('JP', '09', 'Tochigi'), ('JP', '10', 'Gunma'), ('JP', '11', 'Saitama'), ('JP', '12', 'Chiba'), ('JP', '13', 'Tokyo'), ('JP', '14', 'Kanagawa'), ('JP', '15', 'Niigata'), ('JP', '16', 'Toyama'), ('JP', '17', 'Ishikawa'), ('JP', '18', 'Fukui'), ('JP', '19', 'Yamanashi'), ('JP', '20', 'Nagano'), ('JP', '21', 'Gifu'), ('JP', '22', 'Shizuoka'), ('JP', '23', 'Aichi'), ('JP', '24', 'Mie'), ('JP', '25', 'Shiga'), ('JP', '26', 'Kyoto'), ('JP', '27', 'Osaka'), ('JP', '28', 'Hyogo'), ('JP', '29', 'Nara'), ('JP', '30', 'Wakayama'), ('JP', '31', 'Tottori'), ('JP', '32', 'Shimane'), ('JP', '33', 'Okayama'), ('JP', '34', 'Hiroshima'), ('JP', '35', 'Yamaguchi'), ('JP', '36', 'Tokushima'), ('JP', '37', 'Kagawa'), ('JP', '38', 'Ehime'), ('JP', '39', 'Kochi'), ('JP', '40', 'Fukuoka'), ('JP', '41', 'Saga'), ('JP', '42', 'Nagasaki'), ('JP', '43', 'Kumamoto'), ('JP', '44', 'Oita'), ('JP', '45', 'Miyazaki'), ('JP', '46', 'Kagoshima'), ('JP', '47', 'Okinawa');

For Canada:
INSERT INTO `State` (`CountryCode`, `Code`, `DefaultName`)
VALUES ('CA', 'AB', 'Alberta '), ('CA', 'BC', 'British Columbia '), ('CA', 'MB', 'Manitoba '), ('CA', 'NB', 'New Brunswick '), ('CA', 'NL', 'Newfoundland and Labrador '), ('CA', 'NS', 'Nova Scotia '), ('CA', 'ON', 'Ontario '), ('CA', 'PE', 'Prince Edward Island '), ('CA', 'QC', 'Quebec '), ('CA', 'SK', 'Saskatchewan '), ('CA', 'NT', 'Northwest Territories '), ('CA', 'NU', 'Nunavut '), ('CA', 'YT', 'Yukon Territory ');

For the United States (Including Protectorates):
INSERT INTO `State` (`CountryCode`, `Code`, `DefaultName`)
VALUES ('US', 'AL', 'Alabama'), ('US', 'AK', 'Alaska'), ('US', 'AS', 'American Samoa'), ('US', 'AZ', 'Arizona'), ('US', 'AR', 'Arkansas'), ('US', 'CA', 'California'), ('US', 'CO', 'Colorado'), ('US', 'CT', 'Connecticut'), ('US', 'DE', 'Delaware'), ('US', 'DC', 'District of Columbia'), ('US', 'FM', 'Federated States of Micronesia'), ('US', 'FL', 'Florida'), ('US', 'GA', 'Georgia'), ('US', 'GU', 'Guam'), ('US', 'HI', 'Hawaii'), ('US', 'ID', 'Idaho'), ('US', 'IL', 'Illinois'), ('US', 'IN', 'Indiana'), ('US', 'IA', 'Iowa'), ('US', 'KS', 'Kansas'), ('US', 'KY', 'Kentucky'), ('US', 'LA', 'Louisiana'), ('US', 'ME', 'Maine'), ('US', 'MH', 'Marshall Islands'), ('US', 'MD', 'Maryland'), ('US', 'MA', 'Massachusetts'), ('US', 'MI', 'Michigan'), ('US', 'MN', 'Minnesota'), ('US', 'MS', 'Mississippi'), ('US', 'MO', 'Missouri'), ('US', 'MT', 'Montana'), ('US', 'NE', 'Nebraska'), ('US', 'NV', 'Nevada'), ('US', 'NH', 'New Hampshire'), ('US', 'NJ', 'New Jersey'), ('US', 'NM', 'New Mexico'), ('US', 'NY', 'New York'), ('US', 'NC', 'North Carolina'), ('US', 'ND', 'North Dakota'), ('US', 'MP', 'Northern Mariana Islands'), ('US', 'OH', 'Ohio'), ('US', 'OK', 'Oklahoma'), ('US', 'OR', 'Oregon'), ('US', 'PW', 'Palau'), ('US', 'PA', 'Pennsylvania'), ('US', 'PR', 'Puerto Rico'), ('US', 'RI', 'Rhode Island'), ('US', 'SC', 'South Carolina'), ('US', 'SD', 'South Dakota'), ('US', 'TN', 'Tennessee'), ('US', 'TX', 'Texas'), ('US', 'UT', 'Utah'), ('US', 'VT', 'Vermont'), ('US', 'VI', 'Virgin Islands'), ('US', 'VA', 'Virginia'), ('US', 'WA', 'Washington'), ('US', 'WV', 'West Verginia'), ('US', 'WI', 'Wisconsin'), ('US', 'WY', 'Wyoming'), ('US', 'AE', 'Armed Forces Africa'), ('US', 'AA', 'Armed Forces Americas'), ('US', 'AE', 'Armed Forces Canada'), ('US', 'AE', 'Armed Forces Europe'), ('US', 'AE', 'Armed Forces Middle East'), ('US', 'AP', 'Armed Forces Pacific');

For China:
INSERT INTO `State` (`CountryCode`, `Code`, `DefaultName`)
VALUES ('CN', 'AH', 'Anhui'), ('CN', 'BJ', 'Beijing'), ('CN', 'CQ', 'Chongqing'), ('CN', 'FJ', 'Fujian'), ('CN', 'GS', 'Gansu'), ('CN', 'GD', 'Guangdong'), ('CN', 'GX', 'Guangxi Zhuang'), ('CN', 'GZ', 'Guizhou'), ('CN', 'HI', 'Hainan'), ('CN', 'HE', 'Hebei'), ('CN', 'HL', 'Heilongjiang'), ('CN', 'HA', 'Henan'), ('CN', 'HB', 'Hubei'), ('CN', 'HN', 'Hunan'), ('CN', 'JS', 'Jiangsu'), ('CN', 'JX', 'Jiangxi'), ('CN', 'JL', 'Jilin'), ('CN', 'LN', 'Liaoning'), ('CN', 'NM', 'Nei Mongol'), ('CN', 'NX', 'Ningxia Hui'), ('CN', 'QH', 'Qinghai'), ('CN', 'SN', 'Shaanxi'), ('CN', 'SD', 'Shandong'), ('CN', 'SH', 'Shanghai'), ('CN', 'SX', 'Shanxi'), ('CN', 'SC', 'Sichuan'), ('CN', 'TJ', 'Tianjin'), ('CN', 'XJ', 'Xinjiang Uygur'), ('CN', 'XZ', 'Xizang'), ('CN', 'YN', 'Yunnan'), ('CN', 'ZJ', 'Zhejiang');

For North Korea:
INSERT INTO `State` (`CountryCode`, `Code`, `DefaultName`)
VALUES ('KP', '01', 'Chagang-do '), ('KP', '17', 'Hamgyŏng-bukto '), ('KP', '03', 'Hamgyŏng-namdo '), ('KP', '07', 'Hwanghae-bukto '), ('KP', '06', 'Hwanghae-namdo '), ('KP', '08', 'Kaesŏng-si '), ('KP', '09', 'Kangwŏn-do '), ('KP', '18', 'Najin Sŏnbong-si'), ('KP', '14', 'Namp''o-si '), ('KP', '11', 'P''yŏngan-bukto '), ('KP', '15', 'P''yŏngan-namdo '), ('KP', '12', 'P''yŏngyang-si '), ('KP', '13', 'Yanggang-do ');

For South Korea:
INSERT INTO `State` (`CountryCode`, `Code`, `DefaultName`)
VALUES ('KR', '10', 'Busan'), ('KR', '05', 'Chungcheongbuk-do'), ('KR', '17', 'Chungcheongnam-do'), ('KR', '15', 'Daegu'), ('KR', '19', 'Daejeon'), ('KR', '06', 'Gangwon-do'), ('KR', '18', 'Gwangju'), ('KR', '13', 'Gyeonggi-do'), ('KR', '14', 'Gyeongsangbuk-do'), ('KR', '20', 'Gyeongsangnam-do'), ('KR', '12', 'Incheon'), ('KR', '01', 'Jeju-do'), ('KR', '03', 'Jeollabuk-do'), ('KR', '16', 'Jeollanam-do'), ('KR', '11', 'Seoul'), ('KR', '21', 'Ulsan');

Let me know if I’ve forgotten any states/provinces/prefectures/territories/etc., as I’ll try to keep this up to date. As you can see, both of these tables are using the Romanized characters, however, it wouldn’t take much to convert them to the local language. One thing that I often do for my employers (or customers) is to have secondary tables that contain the localized names. This makes localization a snap and can shave a good amount of time off of presentation layer development, as well as the creation of localized reports.

Have questions about localizing a database? Feel free to leave a comment here or you can email me directly through the contact form.  Enjoy!

Tags: country, database, defaults, MySQL