Multi-languages website : problem of architecture

[BLob]

Hi,

I am building a multi languages website. To simplify let's say that I need
to deal with news, projects, partners, all of them having some information
depending on the language (title, introduction, ...) and some not depending
on the language (date_online, date_offline, published, ...). The main point
is that I don't want to have to touch the structure of the database or to
modify my scripts if one or more languages were to be added. I came up with
something like this :

My architecture:
----------------------------------------------------------------------------
------
'news' table: with all information not depending on the language
news_id (primary key)
date_online
date_offline
published
...
'news_translations' table: with all information depending on the language
news_id (primary key)
lang (primary key)
title
introduction
...
The (news_id, lang) is linking the second table to the first one.

Same kind of structure for all other content that needs to be translated
(projects, partners, etc.).
Is there a better approach: something using only one table with all the
information that has to be translated, coming from any table (news,
projects, partners, etc.) ? or something else ?

Thank you,

BLob

[Markus Ernst]

BLob schrieb:

Hi,

I am building a multi languages website. To simplify let's say that I need
to deal with news, projects, partners, all of them having some information
depending on the language (title, introduction, ...) and some not depending
on the language (date_online, date_offline, published, ...). The main point
is that I don't want to have to touch the structure of the database or to
modify my scripts if one or more languages were to be added. I came up with
something like this :

My architecture:
----------------------------------------------------------------------------
------
'news' table: with all information not depending on the language
news_id (primary key)
date_online
date_offline
published
...
'news_translations' table: with all information depending on the language
news_id (primary key)
lang (primary key)
title
introduction
...
The (news_id, lang) is linking the second table to the first one.

Same kind of structure for all other content that needs to be translated
(projects, partners, etc.).
Is there a better approach: something using only one table with all the
information that has to be translated, coming from any table (news,
projects, partners, etc.) ? or something else ?

I use a table 'strings' with the fields:
- stringnumber
- language
- string

and in the 'news' table I have all the fields necessary, but all
multilanguage fields contain the stringnumber instead of the text itself.

I could not say if this approach was better than yours, it is just
different. The advantage of it is that you have access to any string
regardless of the information about what object it belongs to. This
makes it easy to make fallbacks for the case some info is not present in
the desired language, somehow like

function get_string($str_nr, $lang, $main_lang) {
$string = [get string for $lang here];
if ($string == "" || $string == NULL) {
if ($lang != $main_lang)
return get_string($str_nr, $main_lang, $min_lang);
else
return false
}
else return $string;
}

HTH
Markus

[BLob]

I use a table 'strings' with the fields:
- stringnumber
- language
- string
I thought about this structure, but I don't know if it is better or worse
than the one I proposed in my first post. 'string' has to be a 'text' field
in the database in order to hold both data from what would otherwise be
varchar and text. Isn't this a problem ? (I don't know).
and in the 'news' table I have all the fields necessary, but all
multilanguage fields contain the stringnumber instead of the text itself.
OK.
I could not say if this approach was better than yours, it is just
different. The advantage of it is that you have access to any string
regardless of the information about what object it belongs to. This
makes it easy to make fallbacks for the case some info is not present in
the desired language, somehow like

Yes, it makes it easier for the 'translation' process. But on the other hand
I guess it makes it more complicated to search within information (search
engine), as you don't know what type of data you have without making links
to other tables. That's why I chose the other approach, but maybe I am wrong
and there is a pretty work around do do efficient searches with the
structure you described. What do you think ?

BLob

[Markus Ernst]

BLob schrieb:

I use a table 'strings' with the fields:
- stringnumber
- language
- string
I thought about this structure, but I don't know if it is better or worse
than the one I proposed in my first post. 'string' has to be a 'text' field
in the database in order to hold both data from what would otherwise be
varchar and text. Isn't this a problem ? (I don't know).

According to the MySQL manual a "text" field requires as much disk space
as the size of the text it holds (plus 1 byte). That's why I think it is
not a problem to use the "text" data type also for very short strings; I
actually don't understand the reason why most developers use varchar
fields with specified string lengths. But maybe I am wrong here.
Yes, it makes it easier for the 'translation' process. But on the other hand
I guess it makes it more complicated to search within information (search
engine), as you don't know what type of data you have without making links
to other tables. That's why I chose the other approach, but maybe I am wrong
and there is a pretty work around do do efficient searches with the
structure you described. What do you think ?

I built a special search index table where I store all information for
every object that has to be searchable centrally. This is extra
programming work, but I see several advantages in it:

- As the contents of a page is centralized in one field you can use the
MySQL fulltext search and take advantage of its boolean and ranking
capabilities
- When storing the contents to the search index you can pre-process it,
for example remove punctuation and convert accented characters and
whatever, which can improve the performance and quality of the search
process
- If you need to add another type of objects - say "products" - you
don't have to edit your search process, but just add the appropriate
"store to search index" method to the "products" administration.

--
Markus