Добрый день, на данный момент передо мной стоит задача разработки бд для мультиязычного словаря. Подскажите, пожалуйста, насколько правильно будет данное решение?

Условия задачи:
1. Имеется 5 языков.
2. В будущем предполагается добавление новых языков.
3. В каждом языке более 10 000 слов.
4. В будущем предполагается добавление новых слов.
5. Каждое слово может иметь несколько значений-переводов.
6. Каждое слово с большой вероятностью имеет перевод на все доступные в системе языки.



Будет использоваться SQLite или MySQL.

Сокращения:
PK - первичный ключ.
FK - внешний ключ.

words word_id - PKword_value language_id - FK

languages language_id - PKlanguage_valuelanguage_code

translations translate_id - PK first_word_id - FK second_word_id - FK

Если насчет первых двух таблиц у меня не возникает особых сомнений, то как поступить с таблицей translations пока не знаю.
Как правильно заполнять данную таблицу, чтобы увеличить скорость поиска перевода:
Любое слово, если оно имеет перевод, должно встретиться в левой колонке (first_word_id), даже если оно уже было в правой колонке (second_word_id) или это приведет к черезмерному учеличению объема бд и как следствие замедлению поиска? Или же наоборот скорость поиска увеличится, т.к. будет осуществляться только по одному столбцу.

Пример: (вместо, id слов я подставил значения слов, чтобы было нагляднее)

translate_id first_word_id second_word_id1 table таблица2 table стол3 таблица table4 стол table

Спасибо за ответ!
А если появится необходимость добавить такую функциональность как синонимы, антонимы, категории слов (пример: фрукты, предметы быта, причем каждое слово также может состоять в разных категориях). То лучше добавить новые таблицы как, например:

(Вместо значений нужно использовать word_id, но так нагляднее)

Synonyms
word_id synonym_idБежать мчатьсяБежать нестись

Antonyms
word_id antonym_idБежать стоятьБежать ползти

Categories
category_id - PK category_value1 фрукты2 продукты3 еда

Categories_entries
word_id - FK category_id - FKгруша фруктыгруша продуктыгруша едахлеб продуктыхлеб еда

Кот МатроскинDimitry Sibiryakovпропущено...

Выкинуть её. Убрать с таблицы Words первичный ключ и валить все переводы в неё, объединяя
по id. При этом table (который стол) и table (которая таблица) будут разными словами. В
качестве бонуса получишь возможность переводить с любого языка на любой.

Очень сомнительный способ, подразумевающий обязательную транзитивность перевода.
Возможность перевода с любого языка на любой есть и в изначальной схеме автора.

Я тоже немного сомневаюсь в транзитивности перевода. Знакомый лингвист говорит, что исключения есть и если есть возможность, то лучше не завязываться на каком-то эталоне (id в нашем случае).

С другой стороны, если предположить что языков как минимум 5, в каждом 10 000 слов, и для каждого слова 5 значений-переводов на каждый другой язык, то таблица translations будет состоять из как минимум:
10 000 (слов) * 5 (значений-переводов) * 5 (языков изначального слова) * 4 (языков для слова - перевода) ~ 1 000 000 записей в таблице переводов.

Собственно, если я хочу найти переводы слова table, исходя из предложенной мною схемы, я должен:
1. сначала найти в таблице words значение word_id по условию word_value = 'table' и language_id = 1 (допустим это код english).
2. Найти в таблице translations значение second_word_id (это код слова-перевода) по условию first_word_value = word_id (результат первого запроса)
3. По полученным значениям сделать join на таблицу words, чтобы получить значения слов и выбрать только нужные языки.

Не имею опыта работы с таблицами таких размеров (1 000 000 записей) , поэтому интересует скорость поиска в ней. Скорость вставки и редактирования таблицы переводов не волнует совсем, т.к. это будет происходить очень редко.

Кот МатроскинМиллион записей - это немного, при наличии индексов поиск будет вполне шустрым.
Спасибо за ответ!
А в данном случае (таблица translations ) как лучше поступить с индексами:
1. Сделать составной первичный ключ (first_word_id, second_word_id).
И заполнять таблицу переводов как я уже писал выше, то есть с повторениями:

first_word_id second_word_id1 (table) 10 (стол)1 (table) 15 (таблица)10 (стол) 1 (table)15 (таблица) 1(table)

Т.е. чтобы при поиске перевода для определенного word_id (например 1 (table)), происходил поиск значения только в одном столбце.
Но при этом будет значительное дублирование данных.

2. Или же кроме составного первичного ключа (first_word_id, second_word_id) добавить еще индекс по тем же полям, но в обратном порядке (second_word_id, first_word_id). Но в таком случае для поиска перевода нужно будет задавать условия вроде WHERE first_word_id = word_id || second_word_id = word_id. Тогда в таблице не будет дублирующих записей, но вот сколько будет занимать времени такой поиск и последующая выборка значения слова и фильтрация по необходимом языку в таблицу words, не совсем представляю.

Пример таблицы переводов в таком случае:
(Возможен поиск переводов в обоих направлениях с меньшим количеством записей)

first_word_id second_word_id1 (table) 10 (стол)1 (table) 15 (таблица)

Кот МатроскинЯ бы не делал дублирований.
Только поиск в таком случае лучше оформлять не как

, а


Обычно оптимизаторы лучше понимают такие конструкции

Спасибо за совет!

guest_20040621> добавить такую функциональность как синонимы, антонимы, категории слов

Casemaster, вы вообще представляете, что такое словарь? Словарь Ожегова, например, в руках держали? Понимаете, чем он отличается от тезауруса? Понимаете, почему у слов не может быть никакой категоризации?

> Знакомый лингвист

Это хорошо, что у вас есть знакомый лингвист. Заплатите ему немного денег, пусть он вам расскажет, как строятся словари.

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

К сожалению, в техническом условии категоризация есть. Более того даже в реальных словарях категоризация встречается.
Пример - словари с тематическими разделами. Там есть такие категории, как еда, одежда, и прочее. Там тоже одно слово может встречаться сразу в нескольких категориях.

А можно более конкретно сказать, почему предложенная мной структура совершенно не подходит под заданные требования?

guest_20040621> К сожалению, в техническом условии категоризация есть.

Это означает, что задание писал человек, который в принципе ничего не знает о словарях. В сообщении выше я уже сказал, что рекомендовал бы уволить этого человека за профнепригодность.

> Более того даже в реальных словарях категоризация встречается.

Например? Не общие названия, а конкретные примеры академических изданий?

> Там есть такие категории, как еда, одежда, и прочее.

Нет и не может быть.

> А можно более конкретно сказать, почему предложенная мной структура совершенно не подходит под заданные требования?

Можно, конечно. Только давайте сначала разберемся с задачей. Что именно вы хотите спроектировать и для какой цели? Словарь длиной 10 тыс. слов вызывает... хм... настороженность. И - до начала обсуждения - пожалуйста, прочтите о том, что такое тезаурус и почему невероятно сложно построить мультиязычный тезаурус.

Ок, назовем тогда это не словарь, а некой справочной системой.

Необходимо построить систему, которая позволяла бы пользователю ввести слово. После этого система выдавала бы перевод или переводы (если значений много) данного слова на определенный заранее язык. После этого пользователь мог бы воспользоваться функцией поиска синонимов или антонимов для определенного перевода.
Кроме того, в системе есть некий каталог, в котором некоторые слова расположены по категориям (еда, одежда и т.д.). Таким образом, пользователь может также воспользоваться функцией, которая определяла бы входит указанное им слово в одну из категорий в системе.

Пример, такой системы в книжном варианте - Русско-немецкий визуальный словарь. Единственное там отсутствует функция поиска синонимов, антонимов.

guest_20040621> Ок, назовем тогда это не словарь, а некой справочной системой.

Словарной единицей является слово или термин?

> Необходимо построить систему

То есть что-то типа translate.google.com + thesaurus.com в одном флаконе? Чем ограничен набор словарных единиц? Откуда цифра 10 тысяч?

> Русско-немецкий визуальный словарь

Не видел, ничего не могу о нем сказать. Более распространенные примеры есть? Ваше описание похоже на тезаурус. Но это не тезаурус. Вы задачу в целом можете описать? Для чего это нужно? С чем связаны словарные единицы?

Да, что-то похожее на translate.google.com + thesaurus.com. Это система служит для образовательных целей, а точнее для людей изучающих иностранные языки. На данный момент для каждого языка предполагается около 10 000 слов, т.к. система рассчитана на начальный уровень языка. Соответственно для этого и нужны категории, чтобы пользователь мог посмотреть другие слова данной тематики. Набор словарных единиц может быть и увеличен.

Cane Cat Fisher,

стол

1) (мебель) table

письменный стол — writing table / desk; (с выдвижными ящиками тж.) bureau; (лабораторный, рабочий) bench

2) (питание) board; diet; meals pl; (кухня) cooking, cuisine [kwɪ'ziːn]

стол и квартира — board and lodging
диетический стол — diet, dietary meals pl

3) (отдел в учреждении) desk, department, office

стол заказов — order placement desk
адресный стол — address bureau
паспортный стол — passport office
стол справок — information (desk)


причем Хиругический стол может называться как surgical [operating] table, так и patient plate.

Я бы сделал так, но еще скорей бы всего добавил в свою структуру БД таблицы словосочетаний и поле (к таблице Words) или отдельную таблицу Context, где описал бы возможные трактования слов.

Languages
language_id language_value1 Русский2 English3 Italiano

Words
word_id word_value language_id1 стол 12 мебель 13 питание 14 отдел 15 table 26 board 27 diet 28 стул 1

Translations
first_word_id second_word_id1 51 61 7

Synonyms
word_id synonym_word_id5 6

Для соотнесения слов по категориям:

Categories_entries
word_id category_id1 (Стол) 2 (Мебель)5 (table) 2 (Мебель)8 (Стул) 2 (Мебель)

Cane Cat Fisher, с board немного промашка вышла, но с другой стороны один из переводов - это же "табло, информационная таблица" :)