Best practice Joomla multilingual country list database tables?

Question

What is the best practice to design multilingual database tables which contains translated country specific data depending on the users language in Joomla?

I can not add a language key row like used in other Joomla database tables like for com_content because some rows are and must be unique.

This is the current database table

Image 1: Current database table content

Image 2: Current database table structure

Image 3: Current database table Indexes

This is the result I need.

Data for language A

ID| Name     | Code3 | Code2 | TaxRateID | EU
- - - - - - - - - - - - - - - - - - - - - -
12  Aruba       ABW     AW         1       0
13  Australia   AUS     AU         1       0
14  Austria     AUT     AT         2       1

Data for language B

ID | Name     | Code3 | Code2 | TaxRateID | EU
- - - - - - - - - - - - - - - - - - - - - - -
12  Aruba        ABW     AW         1       0
13  Australien   AUS     AU         1       0
14  Österreich   AUT     AT         2       1

Should I add extra tables with an language suffix for each language?

prefix_table_LANGUAGE

Or is there an other and better way? Thanks in advance!

Answer 1

Since you're creating your own component, you don't need to use Joomla's native multilingual feature.

I'd keep your table as is, using the name as fallback for missing translations. Add another table, country_names, with language and code3 as primary key, to keep the translated country names.

So you'll end up with (simplified)

#__countries {
    id,
    name,
    code3,
    code2,
    tax_rate_id,
    eu
}

and

#__country_names {
    code3,
    language,
    name
}

Your query would look like this:

   SELECT a.id, COALESCE(b.name, a.name) AS name, a.code3, a.code2, a.tax_rate_id, a.eu
     FROM #__countries AS a
LEFT JOIN #__country_names AS b ON (a.code3=b.code3 AND b.language=$lang)

(not tested - take it as a sketch).