Multi-country website coding

Posted on 2009-12-19
Last Modified: 2012-05-08
Hey guys,

I have a client who wants a website to sell products but the products would be different depending on whether the user was in, e.g. USA, UK, Africa.

How would this be done coding wise? Could you use one website but just get products from a different database depending on the country, or would you have t have say three separate websites for each country and update them separately?

Could smoeone point me in the direction to get some scope and ideas on how this would be done?

Question by:andoneknight
    LVL 34

    Accepted Solution

    This can be quite a complicated issue. Look up internationalisation localisation. Given the length of those words they are normally shortened to I18N and L10N and there are links on Wikipedia which is a useful background

    In practice you need to design it in to your database. Start with a country table

    Country ( country_id, country_name, country_code, currency, etc.... )

    and then anything which be multicountry (like a product) can needs a record storing with the country code, so instead of

    product ( product_id, name, price..... etc )

    you would design

    product( product_id, country_id, name, price, ....etc )

    this allows you to host products in multiple countries and is a common way of doing things especially if there are slight differences between products. If you have one product that you sold across various countries then you could use a cross reference table to match a product up against the country it is sold in.

    product_sells_in ( country_id, product_id )

    If there was a variation in price then that could be stored in the cross reference

    product_sells_in ( country_id, product_id, local_price )

    Another issue is the headings on the webpage. Normally you would display a heading or text like so

    echo "Product name"

    What you need to do is to have a table or set of arrays keyed by either country code or country ID from your country table and an id for the string, so you might have a table like this

    webpageText ( country_code, heading_name, heading_text )

    which would have entries like

    "EN", "productName", "Enter the product name"
    "FR", "productName", "Entrez le nom du produit"
    "DE", "productName", "Geben Sie den Produktnamen"

    and then you would write a little function that given the country code and keyname would return the approriate string

    echo getHeading("EN", "productName");

    There is a lot to it. Good luck.
    LVL 5

    Author Comment

    Thanks for your response. Could you explain what the "cross reference" table would be? If the product table itself contains a product and country id, amongst other fields, then why would you want another "cross-reference" table with just those details in?

    In terms of the language, that isn't a problem in this case,  it was more building an international site and how datbases etc would be structured etc... (which youve answered) :)

    In terms of having a shopping cart, how would you suggest starting this - again any documentation much appreciated. Would the fact that each product has an id be sufficient to make the cart work?
    LVL 107

    Assisted Solution

    by:Ray Paseur
    You may want to know the country codes.  This should help.  Best regards, ~Ray
    <?php // RAY_country_codes.php
    class CountryNames
    function __construct()
    	if (!isset($can))
    		$can = array();
    		$this->can["af"] = "Afghanistan";
    		$this->can["ax"] = "&Aring;land Islands";
    		$this->can["al"] = "Albania";
    		$this->can["dz"] = "Algeria";
    		$this->can["as"] = "American Samoa";
    		$this->can["ad"] = "Andorra";
    		$this->can["ao"] = "Angola";
    		$this->can["al"] = "Anguilla";
    		$this->can["ag"] = "Antigua and Barbuda";
    		$this->can["ar"] = "Argentina";
    		$this->can["am"] = "Armenia";
    		$this->can["aw"] = "Aruba";
    		$this->can["au"] = "Australia";
    		$this->can["at"] = "Austria";
    		$this->can["az"] = "Azerbaijan";
    		$this->can["bs"] = "Bahamas";
    		$this->can["bh"] = "Bahrain";
    		$this->can["bd"] = "Bangladesh";
    		$this->can["bb"] = "Barbados";
    		$this->can["by"] = "Belarus";
    		$this->can["be"] = "Belgium";
    		$this->can["bz"] = "Belize";
    		$this->can["bj"] = "Benin";
    		$this->can["bm"] = "Bermuda";
    		$this->can["bt"] = "Bhutan";
    		$this->can["bo"] = "Bolivia";
    		$this->can["ba"] = "Bosnia and Herzegovina";
    		$this->can["bw"] = "Botswana";
    		$this->can["bv"] = "Bouvet Island";
    		$this->can["br"] = "Brazil";
    		$this->can["io"] = "British Indian Ocean territory";
    		$this->can["bn"] = "Brunei Darussalam";
    		$this->can["bg"] = "Bulgaria";
    		$this->can["bf"] = "Burkina Aso";
    		$this->can["bi"] = "Burundi";
    		$this->can["kh"] = "Cambodia";
    		$this->can["cm"] = "Cameroon";
    		$this->can["ca"] = "Canada";
    		$this->can["bs"] = "Cape Verde";
    		$this->can["cv"] = "Cayman Islands";
    		$this->can["cf"] = "Central African Republic";
    		$this->can["td"] = "Chad";
    		$this->can["cl"] = "Chile";
    		$this->can["cn"] = "China";
    		$this->can["cx"] = "Christmas Island";
    		$this->can["cc"] = "Cocos (Keeling) Islands";
    		$this->can["co"] = "Colombia";
    		$this->can["km"] = "Comoros";
    		$this->can["cg"] = "Congo";
    		$this->can["cd"] = "Congo, Democratic Republic";
    		$this->can["ck"] = "Cook Islands";
    		$this->can["cr"] = "Costa Rica";
    		$this->can["ci"] = "C&ocirc;te d'Ivoire (Ivory Coast)";
    		$this->can["hr"] = "Croatia (Hrvatska)";
    		$this->can["cu"] = "Cuba";
    		$this->can["cy"] = "Cyprus";
    		$this->can["cz"] = "Czech Republic";
    		$this->can["dk"] = "Denmark";
    		$this->can["dj"] = "Djibouti";
    		$this->can["dm"] = "Dominica";
    		$this->can["do"] = "Dominican Republic";
    		$this->can["ec"] = "Ecuador";
    		$this->can["eg"] = "Egypt";
    		$this->can["sv"] = "El Salvador";
    		$this->can["gq"] = "Equatorial Guinea";
    		$this->can["er"] = "Eritrea";
    		$this->can["ee"] = "Estonia";
    		$this->can["et"] = "Ethiopia";
    		$this->can["fk"] = "Falkland Islands";
    		$this->can["fo"] = "Faroe Islands";
    		$this->can["fj"] = "Fiji";
    		$this->can["fi"] = "Finland";
    		$this->can["fr"] = "France";
    		$this->can["pf"] = "French Polynesia";
    		$this->can["tf"] = "French Southern Territories";
    		$this->can["ga"] = "Gabon";
    		$this->can["gm"] = "Gambia";
    		$this->can["ge"] = "Georgia";
    		$this->can["de"] = "Germany";
    		$this->can["gh"] = "Ghana";
    		$this->can["gl"] = "Gibraltar";
    		$this->can["gr"] = "Greece";
    		$this->can["gl"] = "Greenland";
    		$this->can["gd"] = "Grenada";
    		$this->can["gp"] = "Guadeloupe";
    		$this->can["gu"] = "Guam";
    		$this->can["gt"] = "Guatemala";
    		$this->can["gn"] = "Guinea";
    		$this->can["gw"] = "Guinea-Bissau";
    		$this->can["gy"] = "Guyana";
    		$this->can["ht"] = "Haiti";
    		$this->can["hm"] = "Heard and McDonald Islands";
    		$this->can["va"] = "Honduras";
    		$this->can["hk"] = "Hong Kong";
    		$this->can["hu"] = "Hungary";
    		$this->can["is"] = "Iceland";
    		$this->can["in"] = "India";
    		$this->can["id"] = "Indonesia";
    		$this->can["ir"] = "Iran";
    		$this->can["iq"] = "Iraq";
    		$this->can["ie"] = "Ireland";
    		$this->can["il"] = "Israel";
    		$this->can["it"] = "Italy";
    		$this->can["jm"] = "Jamaica";
    		$this->can["jp"] = "Japan";
    		$this->can["jo"] = "Jordan";
    		$this->can["kz"] = "Kazakhstan";
    		$this->can["ke"] = "Kenya";
    		$this->can["ki"] = "Kiribati";
    		$this->can["kp"] = "Korea (north)";
    		$this->can["kr"] = "Korea (south)";
    		$this->can["kw"] = "Kuwait";
    		$this->can["kg"] = "Kyrgyzstan";
    		$this->can["la"] = "Lao People's Democratic Republic";
    		$this->can["lv"] = "Latvia";
    		$this->can["lb"] = "Lebanon";
    		$this->can["ls"] = "Lesotho";
    		$this->can["lr"] = "Liberia";
    		$this->can["ly"] = "Libyan Arab Jamahiriya";
    		$this->can["li"] = "Liechtenstein";
    		$this->can["lt"] = "Lithuania";
    		$this->can["lu"] = "Luxembourg";
    		$this->can["mo"] = ">Macao";
    		$this->can["mk"] = "Macedonia (FYROM)";
    		$this->can["mg"] = "Madagascar";
    		$this->can["mw"] = "Malawi";
    		$this->can["my"] = "Malaysia";
    		$this->can["mv"] = "Maldives";
    		$this->can["ml"] = "Mali";
    		$this->can["mt"] = "Malta";
    		$this->can["mh"] = "Marshall Islands";
    		$this->can["mq"] = "Martinique";
    		$this->can["mr"] = "Mauritania";
    		$this->can["mu"] = "Mauritius";
    		$this->can["yt"] = "Mayotte";
    		$this->can["mx"] = "Mexico";
    		$this->can["fm"] = "Micronesia";
    		$this->can["md"] = "Moldova";
    		$this->can["mc"] = "Monaco";
    		$this->can["mn"] = "Mongolia";
    		$this->can["ms"] = "Montserrat";
    		$this->can["ma"] = "Morocco";
    		$this->can["mz"] = "Mozambique";
    		$this->can["mm"] = "Myanmar";
    		$this->can["na"] = "Namibia";
    		$this->can["nr"] = "Nauru";
    		$this->can["np"] = "Nepal";
    		$this->can["nl"] = "Netherlands";
    		$this->can["an"] = "Netherlands Antilles";
    		$this->can["nc"] = "New Caledonia";
    		$this->can["nz"] = "New Zealand";
    		$this->can["ni"] = "Nicaragua";
    		$this->can["ne"] = "Niger";
    		$this->can["ng"] = "Nigeria";
    		$this->can["nu"] = "Niue";
    		$this->can["nf"] = "Norfolk Island";
    		$this->can["mp"] = "Northern Mariana Islands";
    		$this->can["no"] = "Norway";
    		$this->can["om"] = "Oman";
    		$this->can["pk"] = "Pakistan";
    		$this->can["pw"] = "Palau";
    		$this->can["ps"] = "Palestinian Territories";
    		$this->can["pa"] = "Panama";
    		$this->can["pg"] = "Papua New Guinea";
    		$this->can["py"] = "Paraguay";
    		$this->can["pe"] = "Peru";
    		$this->can["ph"] = "Philippines";
    		$this->can["pn"] = "Pitcairn";
    		$this->can["pl"] = "Poland";
    		$this->can["pt"] = "Portugal";
    		$this->can["pr"] = "Puerto Rico";
    		$this->can["qa"] = "Qatar";
    		$this->can["re"] = "R&eacute;union";
    		$this->can["ro"] = "Romania";
    		$this->can["ru"] = "Russian Federation";
    		$this->can["rw"] = "Rwanda";
    		$this->can["sh"] = "Saint Helena";
    		$this->can["kn"] = "Saint Kitts and Nevis";
    		$this->can["lc"] = "Saint Lucia";
    		$this->can["pm"] = "Saint Pierre and Miquelon";
    		$this->can["vc"] = "Saint Vincent and the Grenadines";
    		$this->can["ws"] = "Samoa";
    		$this->can["sm"] = "San Marino";
    		$this->can["st"] = "Sao Tome and Principe";
    		$this->can["sa"] = "Saudi Arabia";
    		$this->can["sn"] = "Senegal";
    		$this->can["cs"] = "Serbia and Montenegro";
    		$this->can["sc"] = "Seychelles";
    		$this->can["sl"] = "Sierra Leone";
    		$this->can["sg"] = "Singapore";
    		$this->can["sk"] = "Slovakia";
    		$this->can["sl"] = "Slovenia";
    		$this->can["sb"] = "Solomon Islands";
    		$this->can["so"] = "Somalia";
    		$this->can["za"] = "South Africa";
    		$this->can["gs"] = "South Georgia and the South Sandwich Islands";
    		$this->can["es"] = "Spain";
    		$this->can["lk"] = "Sri Lanka";
    		$this->can["sd"] = "Sudan";
    		$this->can["sr"] = "Suriname";
    		$this->can["sj"] = "Svalbard and Jan Mayen Islands";
    		$this->can["sz"] = "Swaziland";
    		$this->can["se"] = "Sweden";
    		$this->can["ch"] = "Switzerland";
    		$this->can["sy"] = "Syria";
    		$this->can["tw"] = "Taiwan";
    		$this->can["tj"] = "Tajikistan";
    		$this->can["tz"] = "Tanzania";
    		$this->can["th"] = "Thailand";
    		$this->can["tg"] = "Togo";
    		$this->can["tk"] = "Tokelau";
    		$this->can["to"] = "Tonga";
    		$this->can["tt"] = "Trinidad and Tobago";
    		$this->can["tn"] = "Tunisia";
    		$this->can["tr"] = "Turkey";
    		$this->can["tm"] = "Turkmenistan";
    		$this->can["tc"] = "Turks and Caicos Islands";
    		$this->can["tv"] = "Tuvalu";
    		$this->can["ug"] = "Uganda";
    		$this->can["ua"] = "Ukraine";
    		$this->can["ae"] = "United Arab Emirates";
    		$this->can["gb"] = "United Kingdom";
    		$this->can["us"] = "United States of America";
    		$this->can["uy"] = "Uruguay";
    		$this->can["uz"] = "Uzbekistan";
    		$this->can["vu"] = "Vanuatu";
    		$this->can["ve"] = "Venezuela";
    		$this->can["vn"] = "Vietnam";
    		$this->can["vg"] = "Virgin Islands (British)";
    		$this->can["vi"] = "Virgin Islands (US)";
    		$this->can["wf"] = "Wallis and Futuna Islands";
    		$this->can["eh"] = "Western Sahara";
    		$this->can["ye"] = "Yemen";
    		$this->can["zm"] = "Zambia";
    		$this->can["zw"] = "Zimbabwe";
    	if (!isset($this->cna))
    		$this->cna = array_flip($this->can);
    public function get_country_name($abbr)
    	$abbr = strtolower($abbr);
    	if (!isset($this->can["$abbr"])) return FALSE;
    	return $this->can["$abbr"];
    public function get_country_abbr($name)
    	if (!isset($this->cna["$name"])) return FALSE;
    	return strtoupper($this->cna["$name"]);
    // TEST DATA
    echo "<pre>\n";
    $ca   = new CountryNames;
    $abbr = $ca->get_country_abbr("United States of America");
    $name = $ca->get_country_name("GS");
    $xerr = $ca->get_country_name("XX");

    Open in new window

    LVL 34

    Expert Comment

    by:Beverley Portlock
    The point of a cross reference table is to allow you to relate different items in a flexible way. For example, say I have one product that I want to sell in 3 countries and I will be using the same language and the same price and the same currency

    so I have a record like this in the product table

    product_id=1234, name=Left handed widget, price=12.34

    and I am going to sell it in UK, France and Germany (which are allin my country talbe) then my cross reference would have

    product_id, country

    If I then change the price from 12.34 to 13.00 then I only need to change the product record - 1 change - for all three countries. It is also flexible, say I want to sell it in the US and Canada, I just add two more records to the cross reference


    and the job is done. Now let us say that the French and Germans insist on local prices in Euros. By adding a extra "override" field you can determine that this product has to be sold in EUROs

    product_id, country, override_currency

    In practice currency things like this would likely be done at the "country record" level but I am trying to illustrate how a cross reference file can add enormous flexibility to an application and database.

    Finally if you are going to have an eCommerce shop then don't waste time writing one - use either ZenCart or Magento. Both are very well supported and extremely flexible and ZenCart does mutilple currencies as well. I'm sure that magento does as well, but I know ZenCart a lot better.

    I think I might write an article on cross-refs as it does pop up on here fairly often.
    LVL 34

    Expert Comment

    by:Beverley Portlock
    Actually, re-reading that after I posted it, I realise that I have missed out a MAJOR advantage of the cross-reference. Searching.

    Even with that very simple cross reference I can answer the following questions with simple queries

    1. How many countries do we sell product 1234 in?

    SELECT count(country) FROM  crossRef WHERE product_id = 1234

    2. What are the countries do we sell product 1234 in?

    SELECT countryName FROM  crossRef
        INNER JOIN countryTable on country=country_code
        WHERE product_id = 1234

    3. How many countries have an override currency?

    SELECT count(country) FROM  crossRef WHERE product_id = 1234 and override_currency <> ''

    4. What products do we sell in france?

    SELECT product_id, product_name
         FROM crossRef as c
         INNER JOIN products as p on c.product_id = p.product_id
         WHERE country='FR'

    and so on...
    LVL 5

    Author Comment

    Ok. Many many thanks for your explanation. In summary:

    You might construct this using a "products" table and a "country" table.

    The "products" table would have in it, e.g. product id, name, price, size, etc..etc.. (without a country code)
    The "country" table would have in it, e.g. product id, country code.

    This means that the products are independant of the country until they are stored in the cross reference table where each product is assigned a country code dependant on where it is sold. That way, if anyone is using the site in FRANCE, the query would pick out all the products in the Cross reference table where the country code was for france, and then join the product data onto the results.

    It confused me as in your earlier post you said
    and then anything which be multicountry (like a product) can needs a record storing with the country code, so instead of

    product ( product_id, name, price..... etc )

    you would design

    product( product_id, country_id, name, price, ....etc )
    which confused me as you were still putting the country_id in with all of the product details, but i get it now!

    Ray - many thanks for that country list!
    LVL 107

    Expert Comment

    by:Ray Paseur
    You're welcome!  Good luck with your project, ~Ray
    LVL 6

    Assisted Solution

    I see there has been a lot of talk specific to custom coding a solution. I wonder however why you would be wanting to re-invent the wheel. Have you thought of building your solution on an existing framework that already has features such as those you are talking about, eg:

    - Dan
    LVL 6

    Expert Comment

    LVL 5

    Author Comment

    Thankyou - will take a look at those two links! :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
    Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
    The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
    The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now