Internationalization using DB - DB optimization, complexity

Hi all.

I have a web that is translated into 3 languages. But as I am affaid of heavy DB trafic, I do not use DB to store translations, but a simple associative array with cca 300 rows, each row contains 3 strings (for each language one string).

Now question. If I use DB, will it slow down my application? … Because if I make one SQL query per translated item, I will make many queries per page just to translate a few words plus queries to save and read data.

Example:




<table>

<tr>

<th><?php echo getMytranslation('username','en');?></th>

<th><?php echo getMytranslation('password','en');?></th>

<th><?php echo getMytranslation('email','en');?></th>

<th><?php echo getMytranslation('first_name','en');?></th>

<th><?php echo getMytranslation('surname','en');?></th>

...

...

<th><?php echo getMytranslation('i_agree_with_conditions','en');?></th>

</tr>

<tr>

... list of users, textboxes, checkboxes etc ...

</tr>

</table>

<th><?php echo getMytranslation('save','en');?></th>



As you can see this small table has 7 translated items = 7 queries to DB. Plus list of users that should be displayed in the table = 1 big query. And it is just a small table, nothing big.

Is this approach ok?

I18N in Yii is too complicated for me, I dont understand it but I think it must use very the same approach.

Is there a way how to read all translated items in 1 query? Or what is the most DB-friendly way to read so many small things from DB?

Hi there.

I can’t answer your for optimization, or access times for db translation at all, because I began using Yii::t() at first. But outside main menu, I ended up dropping that approach and creating duplicate views : if Yii webapp finds the view in a ‘en’ subfolder, it’ll display it instead of the master view in the parent folder.

I think it can even be mixed with partial renders in order not to repeat irrelevant parts of code (ie not needing translation).

Well I don’t know if I was clear enough or if it helps, but it’s really hot here these days B)

Hi.

Thanks, good idea. But problem is that in this case users can not translate web into another language. It must be done by programmer.

Nevertheless good idea that didn’t cross my mind before…

There’s surely a possibility to get the translations with one query, but as you don’t know which translations you need before, you must get all of them at the beginning and save them in an array. Your getMytranslation function returns then the value of that translation array.

In fact i18n in yii is not that difficult. You should read this section in the yii guide once more because in my opinion it is simplier to store your translations in a text file.

However if you want to try the database approach you should think of caching techniques.