I have database structure
2672
planing to extend this structure with products, news, blog, gallery etc…
so the question is not only performance, but and multilingual convenience.
Or it is better to have only 1 table with language prefix. but that will be a lot of work if need to add more languages in the time. also can be changed main language in time.
I’m working on a multilingual website that should drive heavy traffic here is my db structure and here is the view:
CREATE OR REPLACE ALGORITHM=MERGE VIEW `generalb2b`.`view_country` AS
SELECT
country.id,
country.iso2,
country.iso3,
country.numcode,
country.un_member,
country.calling_code,
country_translation.short_name,
country_translation.long_name,
country_translation.language
FROM `country` JOIN `country_translation` ON country.id=country_translation.country_id
these structure is use after so many works and research on possible structures and quite fit with any multilingual web sites needs.
as you can see we need to set some fields in translation table and in your models you can use this function to retrieve proper data:
public static function getCountryByLanguage($language = null) {
if ($language === null)
$language = Yii::app()->language;
if ($language !== 'en')
$rows = Yii::app()->db->cache(10000)->createCommand()
->select('short_name as name, calling_code')
->from('view_country')
->where('language=:language', array(':language' => $language))
->order('short_name')
->queryAll();
else
$rows = Yii::app()->db->cache(10000)->createCommand()
->select('short_name as name, calling_code')
->from('country')
->order('short_name')
->queryAll();
return $rows;
}
thank you a lot for sharing your idea how to work with multilingual system. I had this also in mind same like your structure but i refused it because of changing default language.