语言与一列中的序列化数组的多列

Question

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.

Answer 1

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.