Как структурировать базу данных MYSQL на мультиязычном сайте?

Question

[Svet Valentin]

Доброго всем времени суток!
Не справляюсь с поставленой задачей где то остро туплю!
Нужно зделать мултиязычной базу данных на MYSQL (codeigniter)
раньше таблицу писал так (прим. blog post)

id
title_ru
title_en
short_ru
short_en 
...

но это слишком грамозтко сейчас понимаю что хавает много памяти при выводе из базы

есть идея такая

id
lang
title
short
...

где при выводе из базы просто выводим все посты с маркером lang где lang это аббревиатура языка по типу en,ru
- но вот такая штука есть поля которые не нужно переводить такие как image,category_id и.т.д.

Подскажите юниору как поступить ?

Answer 1

[Иван]

В CMS с которой я иногда работаю реализуется XML'ем. Поля, которые не нужно искать хранятся в поле content и парсятся через extractvalue. Т.е. все поля , в том числе разноязычные версии. Также можно парсить уже на php или вообще на клиенте.

Answer 2

[Игорь Воротнёв]

Для мультиязычной архитектуры есть несколько подходов. Описывать их не стану - в сети информации достаточно. Первый подход самый простой, но не самый эффективный. В вашем случае я бы предложил обойтись 2мя таблицами (условно, точное разделение без ТЗ сложно определить, здесь важен принцип). Даю на примере того же блога, архитектура близка к WordPress:

# Таблица 1 - "Посты"
id
translation_of | 0 или id оригинала
language
title
short
full
...

# Таблица 2, вариант 1 - метаданные постов (любые)
id
post_id | id поста(ов) из таблицы 1
key
value

# Таблица 2, вариант 2 - метаданные постов (конкретные)
id
post_id | id поста(ов) из таблицы 1
image
author
...

Важный момент - post_id во второй таблице. Можно держать один ID, но тогда данные будут дублироваться (2 одинаковые картинки для поста на 2х языках), что не дает никакого выигрыша. Можно держать массив ID, тогда одну и ту же картинку можно назначить разным постам. А можно обойтись 1м ID, а переводам назначать ту же картинку программно - в поле post_id всегда держать только ID поста на языке оригинала, для вывода проверять, если есть картинка для перевода (по его ID) - брать ее, если нет - брать по ID из поля translation_of. Такая система будет более гибкая, в том плане что много полей из 1й таблицы можно вынести во 2ю, и появляется возможность работать "и так, и сяк" - если есть перевод поля, используем его, если нет - используем из оригинала. Это так, в общих чертах.

Данный подход не является "правильным", "лучшим" и тд. Это один из рабочих вариантов, со своими плюсами и минусами. Оптимальное решение выбирается на основе ТЗ.

Answer 3

[ThunderCat]

После того как попробовал всякие методы, в своей разработке пришел к выводу который сейчас опишу. В моей стране 2 языка являются основными, распределение нечеткое т.к. многие свободно говорят на 2 языках, но предпочитают какой-то один, "родной". Тем не менее русскоговорящих порядка 30-40%. При создании платформы встал вопрос о мультиязычности контента, т.к. 90% местных заказчиков естественно хотят иметь минимум 2 языка. В результате организовалось нечто следующее:
В базе есть таблицы:

language{
  id (int 2),
  name (varchar 200),
  shortname (varchar 3),
  prefix (varchar 2),
  default (int 1)
};
localization{
  id (int 6)
  alias (varchar 200)
  value (blob)
  langid
};

Все материалы помимо id имеют groupid и languageid, что собсно позволяет объединять их по материалам и языкам. В таблице принадлежностей (например картинок) всё хранится используя groupid, соответственно товар всегда будет иметь одинаковые картинки для всех языков. С информационными материалами сделали проще - картинки загружаются админом в аплоадную папку и прикрепляются в пост по прямому прописанному линку, т.е.
в базе не хранятся, не очень красиво, зато избавляет от проблем привязки изображений с разным лексическим контекстом. Таблица localization для перевода статического контента, во вьюшке прописывается хелпер вывода языкового алиаса <?=Locale::get("some_alias")?>