CiviCRM Community Forums (archive)

Hello sir,
              I've made my proposal for civiCRM organization for improving multilingual scalability and I've been working on it. As the current database model uses one big table per entity, with an extra column for each language. But there are disadvantages
1.Hard to maintain - works in easy way for 2-3 languages, but it becomes a really hard when you have a lot of columns or a lot of languages
2.Hard to add a new language - adding new language requires schema changes (and special access rights for db user) for each table with multilanguage content
3.Store empty space - if not all translations are required (e.g. at some places default language should always be used) it may cause redundant data or empty db fields

my approach:(Please see the attachment below)

<?php
// Retrieve titles for all languages
$sql = "SELECT p.*, l.name as language_name, te.field_text as title
        FROM `app_product` p
        INNER JOIN `app_translation_entry` te ON p.title = te.translation_id
        INNER JOIN `app_language` l ON te.language_code = l.code
        WHERE p.id = 1";
if($result = mysql_query($sql)){
    while($row = mysql_fetch_assoc($result)){
        echo "Language (".$row["language_name"]."): ".$row["title"]."<br>";
    }
}

// Retrieve appropriate title according to the chosen language in the system
$sql = "SELECT p.*, l.name as language_name, te.field_text as title
        FROM `app_product` p
        INNER JOIN `app_translation_entry` te ON p.title = te.translation_id
        INNER JOIN `app_language` l ON te.language_code = l.code
        WHERE p.id = 1 AND
              te.language_code = '".$_SESSION['current_language']."'";
if($result = mysql_query($sql)){
    if($row = mysql_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>

1.this way makes adding another another language(Major issue) easy
2.All translations in one place more easily readable.
3.Doesn't require altering the database schema for new languages (and thus limiting code changes)
4.Doesn't require a lot of space for unimplemented languages or translations of a a particular item
5.Provides the most flexibility
6.don't end up with sparse tables

Is this approach in a right way  for making this more dynamic and more scalable.?
Please correct me if any mistakes.
I would be thank full for any suggestions.
Thank you