I am trying to create a table in a more accurate and professional manner.
I need two fields TITLE and DESCRIPTION for two languages, but I'm not quite sure if I won't add a third language after a while.
Which one would you prefer?
-----------------------------------------------------------
id | title_en | title_ge | description_en | description_ge
-----------------------------------------------------------
...|..........|..........|................|................
-----------------------------------------------------------
or
---------------------------
id | titles | descriptions
---------------------------
...|serialized| serialized
---------------------------
where serialized is like this
array(
'en' => 'title for english',
'ge' => 'title for georgian'
)
Or any other suggestions? Thanks for future recomendations.
I would do it in a combination of two tables.
Your first table should include titles and description in just english. Similar to what you have right now.
Your secondary table will include translations. With a structure something like:
-----------------------------------
doc_id | lang | title | description
-----------------------------------
You would put a primary key that would include both doc_id and lang and fulltext indexes on title and description.
This way you could construct a query that would fallback to the original english text if a translation for the language you are looking for does not exist, as well as limit your searching to one specific language or another.
I would create a table with four fields (id,title,description,language)
--------------------------------------
id | titles | descriptions | language
---------------------------------------
1 |title in en| desc in en | en
----------------------------------------
2 |title in ge| desc in ge | ge