Countries & currencies & Languages & Culture Info for Developers

Country and language Table RelationshipsRequirement

I searched the internet for possible information/properties for countries and their currencies and Languages and their Culture info for developers/programmers, and got multiple sources and tables, then I began linking them to make a much more informative table, so it came out with the following fields, and created a simple application to show them, using .Net 4.5 + SQL server localDB 2012:

This article can be seen in

http://www.codeproject.com/Articles/1030981/Countries-currencies-Languages-Culture-Info-for-De

Languages 3Languages 2

 

 

Download

The database script and the .Net application with the database is in this link:
http://1drv.ms/1FNPqyP

The Excel sheet for the collected Languages and Culture Info:
Languages b 2015-09-22

The Excel sheet for the collected Countries and currencies Info:
Nationality Country & Dialing Codes b – 2015-09-22

The Excel sheet for the collected Nationality Country & Dialing Codes:
Nationality Country & Dialing Codes – 2015-09-16

Requirments

Microsoft® SQL Server® 2012 Service Pack 2 (SP2) Express:
http://www.microsoft.com/en-sa/download/details.aspx?id=43351

 

Language Columns:

# Field Description عربي Size Current maximum data size
0 # Language number رقم اللغة Integer Integer
1 orderNo Ordering number ترتيب Integer Integer
2 eName Language English name الاسم عربي nvarchar(100) 51
3 aName Language Arabic name الاسم انجليزي varchar(100) 58
4 NativeName Language native name الاسم بنفس كتابة اللغة nvarchar(150) 57
5 LanguageFamily Language family name عائلة اللغة varchar(100) 19
6 code_639_1 ISO 639 1 رقم ثقافة اللغة بالايسو varchar(2) 2
7 code_639_2_T ISO 639 2 T varchar(3) 3
8 code_639_2_B ISO 639 2 B varchar(3) 3
9 code_639_3 ISO 639 3 varchar(16) 8
10 code_639_6 ISO 639 6 varchar(6) 4
11 Notes ملاحظة nvarchar(150) 75

.

 

 

language-culture.

 

Language Culture Columns:

# Field Description عربي Size Current maximum data size
0 languageCultureNo language Culture No رقم ثقافة اللغة Integer
1 code_639_1 ISO 639 1 رقم ثقافة اللغة بالايسو Integer Integer
2 LCID_CultureIdentifier LCID Culture Identifier رقم ثقافة اللغة الحرفي varchar(6) 6
3 cultureName Language Culture Name ثقافة اللغة varchar(16) 11
4 locale_Language_Country_Region locale Language Country Region اسم اللغة مع المنطقة او الدولة nvarchar(150) 50
5 eLanguageName english Language Name اسم اللغة انجليزي varchar(150) 33
6 aLanguageName arabic Language Name اسم اللغة عربي nvarchar(150) 48
7 nativeName Language Name in its native form اسم اللغة بكتابتها nvarchar(150) 50
8 ANSI_codepage ANSI code page كود انسي Integer Integer
9 OEM_codepage OEM code page كود او اي ام Integer Integer
10 countryRegion_abbreviation country Region abbreviation مختصر الدولة او المنطقة varchar(3) 3
11 languageName_abbreviation language Name abbreviation مختصر الاللغة varchar(3) 3

.

 

 

Languages 4

 

Languages 5

 

 

Countries & currencies

.

This will help in giving nationality / country / currency for applications.

 

Country & Currency Table fields:

 

# Field Description عربي Size Current Data max size
0 ISO3166_1_numeric Numeric codes from ISO 3166-1 (synonymous with UN Statistics M49 Codes) رقم أيزو الخاص بالدولة Integer Integer
1 eName Country’s official English short name الاسم انجليزي varchar(150) 44
2 aName Country’s official Arabic short name الاسم عربي nvarchar(150) 36
3 frName Country’s offical French short name الاسم فرنسي nvarchar(150) 46
4 ISO3166-1-Alpha-2 Alpha-2 codes from ISO 3166-1 ايزو حرفين varchar(2) 2
5 ISO3166-1-Alpha-3 Alpha-3 codes from ISO 3166-1 (synonymous with World Bank Codes) ايزو ثلاث حروف varchar(3) 3
6 ITU Codes assigned by the International Telecommunications Union رمز الاتحاد الدولي للاتصالات varchar(3) 3
7 MARC MAchine-Readable Cataloging codes from the Library of Congress رمز مكتبة الكونغرس varchar(15) 14
8 WMO Country abbreviations by the World Meteorological Organization رمز البلاد من قبل المنظمة العالمية للأرصاد الجوية varchar(2) 2
9 DS Distinguishing signs of vehicles in international traffic العلامات المميزة للمركبات في حركة المرور الدولي varchar(3) 3
10 dialingCode Country code from ITU-T recommendation E.164, sometimes followed by area code رمز البلد من ITU-T توصية E.164، وجاء في بعض الأحيان رمز المنطقة varchar(30) 17
11 FIFA Codes assigned by the Fédération Internationale de Football Association رموز فيفا varchar(30) 15
12 FIPS Codes from the U.S. standard FIPS PUB 10-4 رموز من PUB FIPS القياسية الولايات المتحدة varchar(50) 26
13 GAUL Global Administrative Unit Layers from the Food and Agriculture Organization طبقات الوحدة الإدارية العالمية من منظمة الأغذية والزراعة Integer Integer
14 IOC Codes assigned by the International Olympics Committee رمز اللجنة الأولمبية الدولية varchar(3) 3
15 currency_numeric_code ISO 4217 currency numeric code ISO 4217 العملة رمز رقمي Integer 3
16 currency_alphabetic_code ISO 4217 currency alphabetic code ISO 4217 العملة رمز أبجدي varchar(3) 3
17 currency_minor_unit ISO 4217 currency number of minor units ISO 4217 عدد العملات وحدات صغيرة Integer Integer
18 currency_EName ISO 4217 currency english name ISO 4217 اسم العملة varchar(150) 29
19 currency_AName currency Arabic name اسم العملة بالعربي nvarchar(150) 29
20 currency_ESign currency english sign رمز العملة بالانجليزي varchar(6) 3
21 currency_ASign currency arabic sign رمز العملة بالعربي nvarchar(6) 3
22 capital_CityNo Capital City number رقم العاصمة Integer Integer
23 GPSPoint_Latitude GPS Latitude خط العرض varchar(16) 7
24 GPSPoint_Longitude GPS Longitude خط الطول varchar(16) 7
25 exchangeRateToBaseCurrency exchange Rate To Base Currency قيمة التحويل من العملة الحالية إلى عملة البرنامج Decimal(18,6) Decimal
26 decimalPlaces number of decimals after the point عدد الخانات العشرية smallint 1
27 decimalSeparator the character used for specifing the decimal الرمز المستخدم للتفرقة بين الرقم العشري والرقم الصحيح nvarchar(5) 1
28 thousandsSeparator the character used for specifing the thousand seperator الرمز المستخدم للتفرقة بين الألاف nvarchar(5) 1
29 CR_CurrencyPositionType Later smallint 1
30 CR_NegativeType Later smallint 1

.

currencies 2

 

Countries & currencies 2

 

Fields for the Country Capital Table:

 

# Field Description عربي Size
0 CountryCityNo city number رقم المدينة Integer
1 CountryNo Numeric codes from ISO 3166-1 (synonymous with UN Statistics M49 Codes) رقم أيزو الخاص بالدولة Integer
2 eName City official English short name الاسم انجليزي varchar(150)
3 aName City official Arabic short name الاسم عربي nvarchar(150)
4 GPSPoint_Latitude GPS Latitude خط العرض varchar(16)
5 GPSPoint_Longitude GPS Longitude خط الطول varchar(16)

.

currencies 4

 

The Sql Code for creating the tables:

/****** Object:  Table [dbo].[Country]    Script Date: 9/17/2015 1:06:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Country](
[countryNo] [dbo].[AddressNationality_Country_Currency_Dom] NOT NULL,
[eName] [dbo].[eName_150_sDom] NOT NULL,
[aName] [dbo].[aName_150_sDom] NOT NULL,
[frName] [dbo].[aName_150_sDom] NULL,
[ISO3166_1_Alpha_2] [dbo].[TypeChar02_Dom] NULL,
[ISO3166_1_Alpha_3] [dbo].[TypeChar03_Dom] NULL,
[ITU] [dbo].[TypeChar03_Dom] NULL,
[MARC] [dbo].[TypeVarChar15_Dom] NULL,
[WMO] [dbo].[TypeChar02_Dom] NULL,
[DS] [dbo].[TypeChar03_Dom] NULL,
[dialingCode] [dbo].[TypeVarChar30_Dom] NOT NULL,
[FIFA] [dbo].[TypeVarChar30_Dom] NULL,
[FIPS] [dbo].[TypeVarChar50_Dom] NULL,
[GAUL] [dbo].[TypeIntegerShort_Dom] NULL,
[IOC] [dbo].[TypeChar03_Dom] NULL,
[currency_numeric_code] [dbo].[AddressNationality_Country_Currency_Dom] NULL,
[currency_alphabetic_code] [dbo].[TypeChar03_Dom] NULL,
[currency_minor_unit] [dbo].[TypeIntegerShort_Dom] NULL,
[currency_EName] [dbo].[eName_150_sDom] NULL,
[currency_AName] [dbo].[aName_150_sDom] NULL,
[currency_ESign] [dbo].[TypeVarChar06_Dom] NULL,
[currency_ASign] [dbo].[TypeVarChar06_Dom] NULL,
[capital_CityNo] [dbo].[AddressCityNo_Dom] NULL,
[GPSPoint_Latitude] [dbo].[TypeVarChar16_Dom] NULL,
[GPSPoint_Longitude] [dbo].[TypeVarChar16_Dom] NULL,
[exchangeRateToBaseCurrency] [dbo].[ExchangeRate_Dom] NULL,
[decimalPlaces] [dbo].[TypeIntegerShort_Dom] NULL,
[decimalSeparator] [dbo].[TypeVarChar06_Dom] NULL,
[thousandsSeparator] [dbo].[TypeVarChar06_Dom] NULL,
[CR_CurrencyPositionType] [dbo].[TypeIntegerShort_Dom] NULL,
[CR_NegativeType] [dbo].[TypeIntegerShort_Dom] NULL,
[accountNo] [dbo].[TreeAccountNo_aDom] NULL,
[row_timestamp] [timestamp] NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[countryNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’
عملتها هي في حقل:
currenyCode1′ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’TABLE’,@level1name=N’Country’
GO
/****** Object:  Table [dbo].[CountryCity]    Script Date: 9/17/2015 1:08:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CountryCity](
[countryCityNo] [dbo].[AddressCityNo_Dom] NOT NULL,
[countryNo] [dbo].[AddressNationality_Country_Currency_Dom] NOT NULL,
[eName] [dbo].[eName_150_sDom] NOT NULL,
[aName] [dbo].[aName_150_sDom] NOT NULL,
[GPSPoint_Latitude] [dbo].[TypeVarChar16_Dom] NULL,
[GPSPoint_Longitude] [dbo].[TypeVarChar16_Dom] NULL,
[row_timestamp] [timestamp] NULL,
CONSTRAINT [PK_CountryCity] PRIMARY KEY CLUSTERED
(
[countryCityNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CountryCity]  WITH CHECK ADD  CONSTRAINT [FK_CountryCity_R_484_Country] FOREIGN KEY([countryNo])
REFERENCES [dbo].[Country] ([countryNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CountryCity] CHECK CONSTRAINT [FK_CountryCity_R_484_Country]
GO

 

 

 

References:

http://www.loc.gov/standards/iso639-2/php/English_list.php

https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes

https://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx

http://timtrott.co.uk/culture-codes

http://data.okfn.org/data/core/country-codes

https://www.unc.edu/~rowlett/units/codes/country.htm

http://www.nationsonline.org/oneworld/country_code_list.htm

https://en.wikipedia.org/wiki/ISO_3166-1_numeric

http://www.worldatlas.com/aatlas/ctycodes.htm

https://www.countries-ofthe-world.com/world-currencies.html

http://www.nationsonline.org/oneworld/currencies.htm

http://www.tripmondo.com/magazine/facts-and-statistics/list-of-capitals-and-countries

http://www.yallaforex.net/school-yallaforex-pages/5.htm

https://goo.gl/i9rzwV

https://www.microsoft.com/resources/msdn/goglobal/default.mspx

 

HELLO in eight different languages

 

Versions:

1.2 Database normalization on table “Language”, and extracted: “LanguageCulture”, and made new form for it

1.0 Started the article

 

 

 

 

 

 

 

About Reader Man حسين ناجي الصفافير

حسين ناجي الصفافير Hussain Naji Al-Safafeer Info: https://readerman1.wordpress.com/aboutme
This entry was posted in Development and tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

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