Design Tables to hold multi-Country data

Question

We're becoming an international company and I have been tasked with designing how we're going to store the different countries' specific data. Such as language, postal code regex, telephone number regex, country calling codes, currency, etc.

Currently we have tables like these: Countries, Languages, CountryLanguages, CountryCallingCodes. What about currencies like British Pound and Euro for the UK, where would their definitions be stored? We're are not constrained to existing tables as we haven't implemented any of them.

Can anyone point me to online resources to help me design the tables to store this international country info, please?

Answer 1

If you're dealing with foreign currencies then you need to know how your company will handle conversion rates. For example, if you sell products online and have a catalog then you probably don't want the prices to change every day just because the Euro rose by 0.01% against the US Dollar. If you're dealing in online stock trades though, then maybe you do want to use the latest conversion rates.

I know that I've seen some DB design repositories online, but my Google-fu this morning is lacking. I found this website, but I didn't immediately see any example revolving around internationalization.

Looks like a good read from Sun on the issue: http://java.sun.com/blueprints/guidelines/designing_enterprise_applications_2e/i18n/i18n5.html

Answer 2

International Features in Microsoft SQL Server 2005

Answer 3

The CLDR (Common Locale Data Repository) http://cldr.unicode.org/ is probably the most comprehensive online resource for localization information. You can download a zip of the latest release unicode.org/Public/cldr/1.7.2/.