Как организовать структуру БД для мультиязычия CMS?

Question

[Анатолий]

Доброго времени суток, тостерчане!
Вопрос собственно такой.
Как максимально удобно организовать структуру БД для написания мультиязычной CMS?
Цель такая: Нужно как то хранить переводы названий разделов, переводы новостей, страниц, и чтоб потом можно было организовать поиск по сайту а конкретно по переводам того или иного языка.
Я организовал вот так (структура таблицы с переводами новостей и разделов в данном случае):

----------------------------------------------------------------
| id | section_id | news_id | lang | title | description | ... |
----------------------------------------------------------------

Но при таком подходе когда нужно выбрать переводы на любом языке, то все супер если такая запись есть, а если нет, то нужно выбрать запись на языке который заведен по умолчанию. Все как бы супер, но если выбираю например Новости, которых 1000 то это +1000 запросов на получение переводов.
Плюсы в таком подходе несколько:

1. не нужно лезть в БД и изменять структуру если добавляется новый язык
   
2. Очень удобно реализовывать поиск
   
3. Все переводы хранятся в одной таблице
   

Из минусов только то, что куча запросов, но может это у меня не хватает ума правильно организовать выборку.
Тогда может подскажете как правильно это разрулить?
Пример запроса ниже:

$news = News::get();
    foreach ($news as $nws) {
        $data[] = [
            'id' => $nws->id,
            'good' => $nws->good,
            'translates' => $nws->translations()->where('lang', App::getLocale())->get()->toArray()
        ];
    }

На хабре есть статья, но я подумал, может тут кто поделится еще своим опытом.

Comments

[ой всё]

а вы сразу показываете новости на всех языках? у вас нет деления сайта по language/locale?

[Анатолий]

Нет, конечно выбираю новости только на одном языке, там в пример чуть не дописал, должно быть вот так примерно исключая все остальные проверки

$nws->translations()->where('lang', App::getLocale())->get()->toArray()

Поправлю сейчас в вопросе

[Дмитрий]

Вы не правильно выбираете, выбирать нужно уже с переводом. Что то типа News::with('translate')->get(); Тогда и не будет 1000 запросов. Вообще для организации переводов есть очень удобная библиотека dimsav/laravel-translatable

Answer 1

[Muhammad]

Для каждой сущности создаются две таблицы: основная и таблица с переводами. Еще должна быть таблица со списком языков (languages) Например у нас есть сущность Post (публикация), для нее будет следующая структура БД:

posts

id, slug, published_at

posts_texts

id, post_id, language_id, title, content

Answer 2

[xmoonlight]

Я делал прямоугольником: одна запись - это одна публикация/статья, а переводы - в колонках.

id | section_id | news_id | title[en-en] | title[ru-ru] | description[en-en] | description[ru-ru] |...

просто, что будет больше 4-5 языков у вас - это вряд ли, а на скорость на поиск(фильтрацию) по нужному языку и по нужным колонкам - это сильно влияет в лучшую сторону. Не забывайте указывать чётко те колонки, которые нужно отображать в результатах запроса (и LIMIT тоже иногда) и всё.

Comments

[Анатолий]

Идея не плохая, не додумался сам, подумаю над этим, Спасибо!

[Muhammad]

Анатолий, плохая идея

[xmoonlight]

Muhammad, я как бы понимаю, что правильнее разносить:
материалID | языкID | contentID | titleID
и для каждого ID - держать свою таблицу.
Вначале ищем по ID, возвращаемся снова, отсеиваем и т.д. прыгаем, пока не отсеем всё ненужное. Это как раз и будет 3ДНФ.
Но затраты на выборку через связи по ID - будут куда значительнее (ИМХО).
Хотелось бы для себя прояснить этот вопрос...

[Muhammad]

xmoonlight, на каждую сущность по две таблицы - таблица с общими полями и таблица с данными для перевода. Во второй каждая строка является переводом на какой-либо язык для конкретной сущности. Например, если у нас есть один пост и пять языков, в первой таблице будет одна запись, а во второй 5 - тексты для каждого языка для данного поста. Кстати, slug тоже можно перенести в таблицу с переводами, если планируется собственный адрес для каждого языка.

[xmoonlight]

Muhammad, а в чём преимущество отделения переводов от пары-тройки общих полей (это 5-8 колонок максимум: время модификации, флаги состояния публикаций и т.д.)?

[Muhammad]

xmoonlight, а как еще хранить переводы для множества языков?

[xmoonlight]

Muhammad, ну вот так, как я предложил в своём ответе - чем мой метод хранения хуже?

[Muhammad]

xmoonlight, тем, что столбцов в таком случае становится не меренное количество, что как минимум неудобно при просмотре. И еще один момент - при выборке из этой таблице придется постоянно указывать поля, которые нужно загрузить, иначе будут грузиться ненужные данные.

[xmoonlight]

Muhammad, ни 1-е, ни 2-е утверждение - никак не препятствуют снижению производительности в моём варианте.

Answer 3

[Владимир Скибин]

У Вас по факту получается, что одна новость, это набор записей в БД. Если прибегать к 1-й нормальной форме, то получится что то вида
id | lang_id | title | text
Причем первичным ключом в этом случае выступает уже связка id + lang_id. При входящем запросе уже определяется язык, который нужен браузеру и просто ко всем значениям подставляется
WHERE `lang_id`='$lang_id'
Но тогда придется самостоятельно организовывать работу с AUTO_INCREMENT, поскольку уникальность уже идет на уровне id+lang_id

Comments

[Анатолий]

Одна новость - это одна новость, которая имеет поля
| id | onOff | created_at | updated_at |
Этой новости со значением "id" соответствуют записи в таблице с переводами
| id | news_id | lang | title | content |
тут реализована обычная связь "Один ко многим"

[Владимир Скибин]

А зачем Вы дублируете news_id ? Это по факту суррогатный ключ, нуждны в котором нет. Собственно Ваше решение - это как раз то, о чем я описывал - что за сущность отвечает одна таблица, а за "языкозависимые данные" отвечает вторая. Если сделать запрос на подчиненную, то через нее по внешнему ключу можно выйти на главную. Единственное я бы в Вашем случае объединил id и news_id и первичный ключ на news_id + lang

[Анатолий]

Владимир Скибин, Я понял ваше предложение, Спасибо большое за совет.

[Анатолий]

Владимир Скибин, Еще такой момент, я в этой таблице использую одни и те же поля для переводов из разных таблиц. Так например у меня поле "title" используется для перевода как названия новости так и для названия раздела сайта. Только выборка проходит по разным полям, в одном случае по полю news_id в связке с lang, в другом по полю section_id в связке с тем же lang.
Так у меня все переводы в одной таблице и таблица не увеличивается по кол-ву столбцов. Может вы предложите свой вариант?

[Владимир Скибин]

Анатолий, Я бы все таки не выделял "перевод" как сущность, а он был бы атрибутом у текущих сущностей. Так конечно количество таблиц увеличится, но все будет по полочкам. Т.е. `news` и `news_data`, `pages` и `pages_data`. При добавлении языка в этом варианте - нужно просто добавить новую запись в `langs` и склонировать во всех таблицах `*_data` данные с языка по умолчанию. Данную схему реализовывал когда то давно на "свой велосипед / своя CMS" и он был довольно рабочий. В свое время тоже задавался этим вопросом и рассматривал разные варианты. В итоге решил выбрать тот, что советую.