Multi language content

This solution is the best (for me) after many tests with MySQL requests. This tests were made to develop a website for Europe.

MySQL accepts requests for "big" tables but requests are faster when tables are smaller (said MySQL).
If a document is tranlated in 20 languages (I exaggerate purposely), there are 20 rows for 1 document.
When the request for select this document in 1 language is made, MySQL parsed all rows of tables to found the right. Rights indexes helps but indexes is bigger with big table than a small. MySQL spends 20 times more for 1 rows.

If a table is created for each language. You make your request only on the right tables, and time spent is smaller.

Moreover, if you want to display on the webpage links to the translation, you need parse a second time the big table. With a table which contains only parent id and language id (indexes is needed for the two fields), the search is very very faster, because there are no data but only keys.

With tests and experience, I note that MySQL is faster when tables contains no data but only keys, even if indexes are only with keys. I don’t know exactly why. I believe that is because they’re is no data in rows, only few caracters to parse.

I have make tests with Mysql 4.x and 5.x. Can be that have changed...

You’re making your point there.
So if I get this right, everytime there is a new translation language, the system needs to create a new table for that language. We also need another table containing primary content id with all the available translation content id being stored, for faster searches to look for available translation.

Now the only problem that I can see, considering we’re implementing subsites in the future, basically each subsites will have their own language. Let say I’m hosting 2 sites on one MODx installation. The first site will have the default language set to english, and the second site will have the default language set to france for example. Now, how do we use the current table formating that you propose? I know that I’m adding more requirement that just causing the implementation need to be solved differently. I’m using MODx to host multiple static page right now, and I find it really helpful to host small static websites on one MODx installation, so I can save space, maintenance time, and etc. I would like to know your response regarding about this. (FYI: this is not being planned yet for the next release, but if we can find the right solution on this so we can compare all difference solution available, we will be able to choose the best db design for MODx).

By the way, we need to do some benchmarking or at least we need to have a good number to be able to compare the performance. Because the performance can vary, and if you know about drawing a graph for speed of algorithm, some of them have a cruve line, some of them straight, and etc. So by considering this in more details, we can get a better picture on approaching the database design. I want to explain about this subject a little bit more in depth, but with my limited vocab on english, it’s kinda hard to explain it. Hope you guys understand what I’m trying to say. Basically the speed benchmark with 100 rows on database and 1000 rows is dffierent, and it will differ again from 1,000,000 rows. So whichever design that have a better performance by averaging the score from all different testing with different amount of rows num will be the champ.

We also need another table containing primary content id with all the available translation content id being stored, for faster searches to look for available translation.

In fact, one table is needed to contain all primary document id and its translation :

PrimaryDocId TransaltionDocId LanguageId
1 1 1
3 2 1
3 1 2

Another solution is to use the same id for all primary documents and all their transalations (parent id = 1, italian translation id = 1, french translation = 1):
PrimaryDocId LanguageId
1 1
3 1
3 2

Fot your 2), I think that I writed above responding at your question.
If not, I don’t understand well your question.

I’m not an expert in benchmark. To my knowledge, benchmark softwares have costs. I have tested MySql with our own benchmark
In fact, we have insert rows in tables with a script and after we have make requests 50 times to make average.
We have tested with one table wich contains a field with 255 caracters and 1 index, one table with a blob (field with approximately 65 000 000 caracters) and 1 index, another with half of this and 1 index and one with 2 fields (with id) with indexe.
Also, we have inserted in this tables 10 rows, 50 rows, 100 rows, 500 rows and 1000 rows.
With all this data, we have created Excel files to make charts and compare results easlier.
It was 3 years ago, so I forgot values
We have viewed that more fields are big, more requests spent times, and more there are rows, more requests spent time so.
When it is possible, it is prefered to use small field than blob. In the same way, it is prefered to fix length for each field (for exemple, if nickname must make 6 characters length, field must be set as "char" and fixed to 6 characters).
I remember that fixed field is faster than not fixed : when it is possible, it is better to use "char" than "varchar" => requests are faster (said MySQL).

I rember that we have choice the last version of MySQL because it manage better UTF-8 and requests were faster, although it is beta version. At the end of developement, MySQL was in stable version.

A good ressource for optimize database, tables and fields is http://dev.mysql.com/doc/refman/5.0/en/data-types.html
MySQL benchmarks are not download without inscription (it’s new >:( ): http://www.mysql.com/why-mysql/white-papers/performance.php

Other MySQL ressources :
[url=http://dev.mysql.com/doc/refman/5.0/en/optimizing-database-structure.htmlOptimizing Database Structure[/url] It is very important and make MySQL very faster. I have tested 3 years ago before and after application of this.

A solution for content with multi language is to create new tables. It is simply to make evolution when a new language must be added and also delete it.
I talk to create new tables for contents, metatags... every tables which contains localized string. I know it is not easy to do this but I think it’s the better way. Table name must contains a reference to the language like "modx_site_content_fr", "modx_site_content_en", etc... In this way, it is possible to translate the major part of string. It permits to select easly content localized, without weigh down default tables. Request on the database will be faster than one table with a field containing a reference to the language.

I like this approach. I only know one multilanguage solution and this is the one from joomla. This component needs lots of queries and isn´t very fast nor handy.

I think another advantage of your approach is the possibility to export the content to other applications, because the structure would be the same as for "normal" content.

Greetz Marc

I think another advantage of your approach is the possibility to export the content to other applications, because the structure would be the same as for "normal" content.

It is right. I did not think about this because my experience is based on application from scratch

Also, there is only one table added to make links between different translations. So it is easy to clear it if you delete a translation or if you want to export data with only 2 language on other website.

It’s my view...

I really like Guillaume’s view cause I used the same technique in my own custom webapplications before and it seems to work very well.
To create an example for the current modx document table this is how the modx_site_content table would be splitted in my view:

[table]
[tr]
[td]altered: modx_site_content
[tt]id
type
contentType
(alias)
published
pub_date
unpub_date
parent
isfolder
richtext
template
menuindex
searchable
cacheable
createdby
createdon
editedby
editedon
deleted
deletedon
deletedby
donthit
haskeywords
hasmetatags
privateweb
privatemgr
content_dispo
hidemenu[/tt]
[/td]
[td] new: modx_site_content_lang
[tt]docid
langid
pagetitle
longtitle
description
(alias)
introtext
content
menutitle[/tt]
[/td]
[/tr]
[/table]

All the localized fields are in the new table and all the "document configuration/attributes fields" are kept in the original table.
docid would be referencing to the document in the original table and the langid would be referencing to the language used.
The ’alias’ field would be a point of discussion of where to put it (do we want localized document paths also?) and maybe the fields such as ’editedby’ could be added to the translation table to track the editor responsable for the translation.

In the end all tables containing content which is in need of localizing would only have to be stripped from these fields and have them added to an extra table appended with "_lang" into the db.

The ’alias’ field would be a point of discussion of where to put it

Imho to the lang table because the alias is used for short URLs, so it has to be unique for each (possible) content...

Quote from: MadeMyDay at Mar 15, 2006, 06:19 AM

The ’alias’ field would be a point of discussion of where to put it

Imho to the lang table because the alias is used for short URLs, so it has to be unique for each (possible) content...

Agree... this way it also helps SEO.

This is great stuff guys! But I want to offer a little of the internal vision from within the core team. Here is a summary of the key features I believe are needed to implement this properly in MODx:

• Revisioning. This is key for multi-lingual content implementation and workflow, including translation workflow, and will also be generally useful for site maintenance, allowing easy rollbacks of just about anything that can be considered content. A standalone Revision data structure would simply store the actual content for that identified revision.
• Content by Culture. Culture defines language amongst other i18n attributes and will be used to find the most appropriate content revision for a document, chunk, snippet, TV, or anything else that might be storing content to be rendered.
• Content. A generic content data structure would be used to relate the data structure the content belongs to (i.e. site_content, site_snippets, etc.) with an appropriate Revision through a Content - Culture - Revision relationship. This simply involves moving the ’content’ field from each MODx table we want to enable revisioning and multi-cultural content on, out of that table and into this new structure
• Contexts. Contexts can then be used to split the site into subsites, and can define default Culture by ’subsite’, and make it easy to present the same document tree via language specific URLs, or simply allow a single document structure to access language specific content via user sessions, cookies, user preference settings, or custom logic.
• Metadata. A generic metadata structure would be used to store all metadata for documents (and could be later extended to snippets, TV’s, etc.). This metadata would represent all of attributes of the document and these would be isolated from the document and would use the same multi-cultural revisioning services discussed above. So your pagetitle, summary, alias, or even custom attributes you make up, could be easily described for each culture and protected by rollback capabilities. These custom metadata facilities can then also be used to describe workflow properties, making advanced publishing and translation management possible. (NOTE: alias would also go here, so it would be loaded appropriately as defined by the Context -- i.e. if the context is for a specific culture, you could have different alias’ for the same page, or a different subdomain with the same alias; it’d be totally up to how you wanted to structure your multi-cultural site and it’s content)

I originally posted these ideas when discussing workflow yesterday at http://modxcms.com/forums/index.php/topic,3402.msg24312.html#msg24312, for your reference.

I welcome any and all discussion on this before it get’s implemented, so speak now or forever hold your peace.