Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > СУБД, общие вопросы > Проектирование базы данных

Автор: FiMa1 4.1.2010, 20:19

Ребята, привет всем! Нужен ваш совет и помощь. Необходимо спроектировать базу данных mysql для словаря. Схема базы получается пока совершенно небольшой, но, так как нет опыта, сомнения в правильности проектирования не дают продвинуться дальше   Пытался читать Р. Яргер, Дж. Риз, Т. Кинг "MYSQL и MSQL Базы данных для небольших предприятий и интернета", сомнения развеяло несильно. Итак, на простом примере базы данных словаря (отношение один ко многим): Вопросы: 1. Как физически (на примере mysql) связать между собой две приведенные выше сущности? 2. Какую СУБД выбрать (MyISAM или InnoDB)? 3. Какие потенциальные проблемы вы можете спрогнозировать для такой БД на будущее (с ростом размеров БД и пр.)? 4. Какую доходчивую литературу для нубов почитать на тему проектирования и оптимизации баз данных? Полагаю, ответить на вопросы для знатоков дела сложности не составит, а я был бы очень признателен за помощь. Заранее спасибо!

Автор: FiMa1 5.1.2010, 00:41

Похоже вопросы 1 и 2 сняты, кажется, наконец-то нашел толковое описание http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html (InnoDB не поддерживает мой хостер). Все еще интересуют ответы на вопросы 3 и 4...

Автор: LSD 5.1.2010, 17:10

Для начала, было бы неплохо объяснить, что за сущности Term и Dict. Мне например это не очевидно.

Автор: Kesh 14.1.2010, 23:50

Я бы не стал хранить русский и английский вариант в одной строке таблицы... Для одного слова на русском могут существовать несколько эквивалентов на английском. Как собственно и наоборот...

Автор: Deniz 15.1.2010, 06:46

FiMa1, объясни поподробнее. Если я правильно понял, то поддерживаю Kesh, и такой же вопрос про синонимы и антонимы (к одному слову может быть несколько синонимов/антонимов)

Автор: FiMa1 15.1.2010, 11:04

Ребята, спасибо за участие! Цитата(Kesh) Я бы не стал хранить русский и английский вариант в одной строке таблицы... Для одного слова на русском могут существовать несколько эквивалентов на английском. Как собственно и наоборот... А как правильно поступить? Для случая, когда одно слово имеет несколько вариантов перевода, пример приведен ниже - Вид содержимого таблицы "Cловарь1". Цитата(Deniz) FiMa1, объясни поподробнее. Если я правильно понял, то поддерживаю Kesh, и такой же вопрос про синонимы и антонимы (к одному слову может быть несколько синонимов/антонимов) Для синонимов и антонимов решил создать отдельные таблицы, чтобы в основной таблице словаря не дублировать для одного и того же термина синонимы и антонимы. Потом, при поступлении запроса на перевод слова, например "dog", искать по таблицам синонимы и антонимы все совпадения для данного термина. Но не уверен наиболее ли это оптимальный вариант... Структура таблицы "Список словарей в направлении En-Ru-En" (для каждого списка словарей по направлениям перевода создается отдельная таблица) Код mysql> DESCRIBE enruen_dicts; +-----------+--------------+------+-----+-------------------+-------+ | Field     | Type         | Null | Key | Default           | Extra | +-----------+--------------+------+-----+-------------------+-------+ | dictid    | varchar(255) | NO   | PRI |                   |       | | dictname  | varchar(255) | NO   |     | NULL              |       | | creatdate | timestamp    | NO   |     | CURRENT_TIMESTAMP |       | | comment   | varchar(255) | NO   |     | NULL              |       | +-----------+--------------+------+-----+-------------------+-------+ 4 rows in set (0.02 sec) Вид содержимого таблицы "Список словарей в направлении En-Ru-En" Код mysql> SELECT * FROM enruen_dicts; +----------+---------------+---------------------+----------------+ | dictid   | dictname      | creatdate           | comment        | +----------+---------------+---------------------+----------------+ | dict1    | общий словарь | 2010-01-11 00:38:56 | общая тематика | +----------+---------------+---------------------+----------------+ 2 rows in set (0.00 sec) Структура таблицы "Словарь1" (для каждого словаря создается отдельная таблица) Код mysql> DESCRIBE dict1; +---------+--------------+------+-----+-------------------+----------------+ | Field   | Type         | Null | Key | Default           | Extra          | +---------+--------------+------+-----+-------------------+----------------+ | termid  | int(11)      | NO   | PRI | NULL              | auto_increment | | uid     | int(11)      | NO   | PRI | NULL              |                | | eng     | varchar(255) | NO   |     | NULL              |                | | rus     | varchar(255) | NO   |     | NULL              |                | | comment | varchar(255) | NO   |     | NULL              |                | | class   | varchar(20)  | NO   |     | NULL              |                | | date    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                | +---------+--------------+------+-----+-------------------+----------------+ 7 rows in set (0.02 sec) Вид содержимого таблицы "Cловарь1" Код mysql> SELECT * select dict1; +---------+-----+------+--------+-----------------+-------+---------------------+ | termid  | uid | eng  | rus    | comment         | class | date                | +---------+-----+------+--------+-----------------+-------+---------------------+ |   1     |  0  | dog  | собака | млекопитающее   | сущ.  | 2010-01-15 10:40:36 | |   2     |  0  | dog  | пёс    | млекопитающее   | сущ.  | 2010-01-15 10:41:58 | +---------+-----+------+--------+-----------------+-------+---------------------+ 2 rows in set (0.00 sec)

Автор: Kesh 15.1.2010, 11:33

Цитата(FiMa1 @  15.1.2010,  11:04 ) А как правильно поступить? Две (если есть поле язык) или три (для каждого сзыка своя) таблички, одна из табличек служит для связки слов. например  Таблица 1. 1 - rus - собака 2 - eng - dog 3 - rus - пес Таблица 2. re - 1 - 2 re - 3 - 2 er - 2 - 1 er - 2 - 3 re - rus -> eng er - eng -> rus

Автор: FiMa1 15.1.2010, 11:37

Цитата(Kesh @ 15.1.2010,  11:33) Цитата(FiMa1 @  15.1.2010,  11:04 ) А как правильно поступить? Две (если есть поле язык) или три (для каждого сзыка своя) таблички, одна из табличек служит для связки слов. А мой приведенный выше вариант в одной строке термин-перевод в чем проигрывает?

Автор: Kesh 15.1.2010, 11:43

1. Как вы расширять французским будете? В моем варианте - новые записи, в вашем - изменение структуры таблиц. 2. А если вас надо добавить связки синонимов. в Моем случае Таблица 2. записи syn - 1 - 3 syn - 3 - 1 в вашем - изменение структуры бд 3. Ну и по размеру данных

Автор: FiMa1 15.1.2010, 11:57

Цитата(Kesh) 1. Как вы расширять французским будете? В моем варианте - новые записи, в вашем - изменение структуры таблиц. Нее, без изменения структуры. Как описано выше, при добавлении нового направления перевода (напр. Fr-Ru-Fr) происходит:  создание пустой таблицы "Список словарей в направлении Fr-Ru-Fr"; При создании нового словаря в направлении Fr-Ru-Fr происходит:  добавление записи о словаре в таблицу "Список словарей в направлении Fr-Ru-Fr" (dictid, dictname, creatdate, comment);  создание таблицы для словаря (в последующем будет заполняться терминами) Цитата(Kesh) 2. А если вас надо добавить связки синонимов. в Моем случае Таблица 2. записи syn - 1 - 3 syn - 3 - 1 в вашем - изменение структуры бд Изменения структуры также не предполагается. Поиск перевода термина:  получить направление перевода в котором ищет пользователь;  из таблицы "Список словарей в направлении XXX-Ru-XXX" получить список всех словарей (таблиц) из данного направления перевода;  искать по всем словарям (таблицам) указанного направления перевода все соответствия rus-XXX для заданного термина;  из таблицы "Синонимы" извлечь все совпадения для термина;  из таблицы "Антонимы" извлечь все совпадения для термина; Цитата 3. Ну и по размеру данных Пока не вижу из-за чего должна сильнее распухнуть база по сравнению с вашим предложением. Единственное только, слово "dog", в моем случае, будет присутствовать и в таблице словаря и в таблицах синонимы и антонимы, это да, согласен. Единственное, но очень весомое преимущество, которое я вижу для вашего предложения по сравнению с моим - это то, что в вашем случае соответствия термин-синоним, термин-антоним заданы численными id, по которым, насколько я знаю, поиск в базе будет осуществлен быстрее, чем поиск по id термина, представленному в текстовом виде. Хотя уверенности по всем пунктам, приведенным мной нет абсолютно

Автор: Deniz 15.1.2010, 12:53

Цитата(FiMa1 @  15.1.2010,  14:57 ) Нее, без изменения структуры. создание таблицы (индексы, связи и т.д.) это уже изменение структуры БД. Вариант, предложенный Kesh, достаточно хорош (можно конечно еще подумать). Цитата(FiMa1 @  15.1.2010,  14:57 ) Пока не вижу из-за чего должна сильно пухнуть база по сравнению с вашим предложением. весь вопрос в слове "сильно".  Во-первых, посчитать сколько байт хранит целое и строка (в примере varchar(255)), то можно прикинуть разницу в объемах. Во-вторых, никто не отменял нормализацию, если слово "dog" набрано с ошибкой и его нужно исправить, то придется делать update, который затронет много(в Вашем примере 2) записей в таблице и использованием неуникального индекса и с перестройкой его, а в примере Kesh update только 1 записи, причем по первичному ключу. В-третьих, правильно подмечено, что поиск по integer будет в разы быстрее, чем поиск по varchar(...), причем, чем длиннее varchar и больше записей в таблице, тем больше integer будет выигрывать.

Автор: FiMa1 15.1.2010, 12:55

Цитата(Deniz @ 15.1.2010,  12:53) Во-первых, посчитать сколько байт хранит целое и строка (в примере varchar(255)), то можно прикинуть разницу в объемах. Во-вторых, никто не отменял нормализацию, если слово "dog" набрано с ошибкой и его нужно исправить, то придется делать update, который затронет много(в Вашем примере 2) записей в таблице и использованием неуникального индекса и с перестройкой его, а в примере Kesh update только 1 записи, причем по первичному ключу. В-третьих, правильно подмечено, что поиск по integer будет в разы быстрее, чем поиск по varchar(...), причем, чем длиннее varchar и больше записей в таблице, тем больше integer будет выигрывать. Ага, это я понял, спасибо!

Автор: Kesh 15.1.2010, 16:39

Цитата(FiMa1 @  15.1.2010,  11:57 ) создание таблицы для словаря (в последующем будет заполняться терминами) Уже изменение структуры данных... А для работы с табличкой надо будет еще и програму подправить, чтобы она могла из этой новой таблицы читать. Теперь смотрите, если у вас слово dog будет переводится на английский и французский, то у вас это слово будет сидеть во всех словарях (англо-русском и франко-русском), тут вставет проблема с тем, чтобы правильно занести дополнительную информацию даже к русскому слову. Что если в одном словаре одно описание, в другом - другое. Это уже некорректность в основных данных. теперь синонимы антонимы... Создавая дополнительную таблицу вы потихоньку приближаетесь к тому, что предложил я.  Взгляните на это с точки зрения сущностей. Слово у вас это одна сущьность. Язык на котором оно написано, его класс и т.п. - это его свойства. Логично все сущности хранить в одно таблице. (table1) Перевод слова, его синонимы и антонимы - по сути это связи меду словами (сущностями) - принимаем связь за сущность (в ее тип: перевод, синонимия и т.п. за свойство) и вот у нас вторая табличка со связями. (table2) Словари - тоже сущьности, которые характеризуются направлением перевода и содержат в себе набор сущьностей в виде слов и их переводов. Т.е. отдельная табличка для словарей (table3) и табличка включения слова в словарь (table4) (т.к. в одинаковых словарях могут быть одни и те же слова).   Возможно также подключение связей слов в переводе (table2) к словарям(table3), если например вы хотите объявить, что в словаре содержатся не все возможные переводы слова, а только тематические. (table5)

Автор: FiMa1 15.1.2010, 16:44

Цитата(Kesh @ 15.1.2010,  16:39) Уже изменение структуры данных... А для работы с табличкой надо будет еще и програму подправить, чтобы она могла из этой новой таблицы читать... Как только переварю и переосмыслю, составлю новый вариант таблиц и напишу чтобы показать, что получилось. Спасибо!

Автор: FiMa1 16.1.2010, 00:52

Думал, смотрел, появились вопросы: ВОПРОС 1 Цитата 2. А если вам надо добавить связки синонимов. в Моем случае Таблица 2. записи syn - 1 - 3 syn - 3 - 1 А почему только запись в таблице 2 "Связи между словами (сущностями)"? А как насчет случая, если для слова "собака" нужно вести синоним "пёс", а "пса" в таблице 1 "Термины" еще нет по факту? Как построить такую связь? Похоже, что syn - кандидат на добавление в таблицу 1 "Термины" в качестве "языка" (наравне с русским, английским и пр.). Тогда получим в таблице 1 запись: Код ---|------|--------|-------| id | lang | term   | part  | ---|------|--------|-------| 98 | rus  | собака |  сущ. | ---|------|--------|-------| 99 | syn  | пёс    |  сущ. | ---|------|--------|-------| А в таблице 2 в соответствие имеем: Код ---------|----------|-------------|---------| term_id1 | term_id2 | lang_direct | dict_id | ---------|----------|-------------|---------|    99    |    98    |   syn-rus   |   N     | ---------|----------|-------------|---------| Хотя это уже, по-моему, бред, но вот такой вопрос возник   ... ВОПРОС 2 Цитата Возможно также подключение связей слов в переводе (table2) к словарям(table3), если например вы хотите объявить, что в словаре содержатся не все возможные переводы слова, а только тематические. (table5) Эту фразу, честно, просто не понял. Можно чуть поподробнее, что имелось в виду, что значит "только тематические". Очень интересно. Ну и, наконец, результаты моего творчества. Ребята, посмотрите, пожалуйста. ЯЗЫКИ 1 – русский 2 – английский 3 – французский … ЧАСТИ РЕЧИ 1 – прилагательное 2 – наречие 3 – союз 4 – междометие 5 – числительное 6 – частица 7 – предлог 8 – существительное 9 – глагол 10 – порядковое числительное 11 – местоименное прилагательное 12 – местоименное наречие 13 – местоименное существительное … НАПРАВЛЕНИЯ ПЕРЕВОДА 1 – русско-английский 2 – русско-французский 3 – англо-французский 4 – англо-русский … В принципе уже видно, что Направление перевода дублируется, присутствует и в таблице TermsRelations и в таблице Dicts. Надо бы, видимо, откуда-то удалить (скорее всего из Dicts).

Автор: Kesh 16.1.2010, 03:42

Цитата(FiMa1 @  16.1.2010,  00:52 ) А почему только запись в таблице 2 "Связи между словами (сущностями)"? А как насчет случая, если для слова "собака" нужно вести синоним "пёс", а "пса" в таблице 1 "Термины" еще нет по факту? Как построить такую связь? Похоже, что syn - кандидат на добавление в таблицу 1 "Термины" в качестве "языка" (наравне с русским, английским и пр.). Тогда получим в таблице 1 запись: Я бы не стал так делать... Если есть у пес синоним слова собака, значит пес - тоже слово и тоже должно быть в списке слов... Другое дело, что это слово может не иметь перевода... Цитата(FiMa1 @  16.1.2010,  00:52 ) Эту фразу, честно, просто не понял. Можно чуть поподробнее, что имелось в виду, что значит "только тематические". Очень интересно.  Например у вас есть русско-английский словарь общей лексики и математической лексики. И переводы этого слова Zero (общий) и Null (математический). Соответственно связи будут принадлежать к разным словарям, а может и к обоим сразу... Цитата(FiMa1 @  16.1.2010,  00:52 ) В принципе уже видно, что Направление перевода дублируется, присутствует и в таблице TermsRelations и в таблице Dicts. Надо бы, видимо, откуда-то удалить (скорее всего из Dicts). Я бы может быть даже и наоборот сделал. В итоге при поиске перевода я бы сначала делал выборку по активным словарям (подходящим под направление перевода), а потом с номерами словарей поиск по переводам.Хотя предложенный вами вариант искал бы скорее всего быстрее. В итоге я бы сказал очень и очень неплохо... На мой взгляд остается открытым маленький вопрос по поводу того, как быть если у вас два словаря для перевода en->ru и в обоих есть перевод dog->собака... Но с такими "дубликатами" можно смириться...

Автор: Kesh 16.1.2010, 13:35

Вот тут есть еще готовый примерчик.  http://www.databaseanswers.org/data_models/dictionaries_for_foreign_languages/index.htm

Автор: FiMa1 16.1.2010, 20:10

Цитата(Kesh) Я бы не стал так делать... Если есть у пес синоним слова собака, значит пес - тоже слово и тоже должно быть в списке слов... Другое дело, что это слово может не иметь перевода... Да, речь как раз о случае, когда "пёс" не имеет перевода. Тогда, при поиске перевода для термина "пёс" я буду вынужден определять, а есть ли для этого слова перевод, т.к. выдать только синоним для термина было бы нелогичным по самому назначению словаря. Такие дополнительные проверки (а есть ли перевод, а не только синоним) снизят производительность. Таким образом, проще, наверное, при добавлении синонима проверять присутствуют ли оба слова в словаре. Неудобно, но зато соответствует основному предназначению сервиса... Кстати, вариант по ссылке, приведенной вами как раз подразумевает обязательное наличие обоих слов образующих синонимичную пару на этапе добавления новой связки для синонимов. Цитата(Kesh) Я бы может быть даже и наоборот сделал. В итоге при поиске перевода я бы сначала делал выборку по активным словарям (подходящим под направление перевода), а потом с номерами словарей поиск по переводам. Хотя предложенный вами вариант искал бы скорее всего быстрее. Мой вариант, видимо, будет быстрее при типичном варианте работы со словарем: задан термин, поиск перевода с попутным определением словаря в которой данная пара термин-перевод находится. Но вот если сделать поиск настраиваемым для пользователя (расстановка приоритетов для поиска по словарям и т.п.), то как раз более выигрышным получается ваш вариант. Но это уже вопрос о проблеме выбора: скорость против объема базы, т.к. можно оставить и последний вариант, где колонка "Направление перевода" присутствует и в таблице Dicts и в таблице TermsRelations, а дальше уже уточнять поиск согласно настройкам пользователя. Но вот здесь уже дублирования как-то не хочется, да еще и обработка усложняется. Цитата(Kesh) На мой взгляд остается открытым маленький вопрос по поводу того, как быть если у вас два словаря для перевода en->ru и в обоих есть перевод dog->собака... Но с такими "дубликатами" можно смириться...  Да, похоже нужно смириться. Тем более, что это не полные дубликаты по строке, поле dict_id будет отличаться . Самое забавное, что в первоначальном варианте структуры базы, из моего последнего примера, я как раз имел дополнительную табличку TermsRelationsDictsRelations, т.е. не было получения словаря для пары слово-перевод непосредственно из колонки dict_id (этой колонки просто не было), а было извлечение всех словарей для данной пары из таблицы TermsRelationsDictsRelations. Я посмотрел на этот вариант базы с разных сторон и стер эту табличку, посчитав, что сойдет просто завести колонку dict_id в TermsRelations. Но теперь, когда вы упомянули о дубликатах, понял в чем минус такого построения. Но и это все тот же вопрос о скорости против объема. Если задуматься, то таких дубликатов не должно быть повально много, да и сами дубликаты значения int, хранить которые не столь накладно. Зато при поиске посетить нужно меньше таблиц для разрешения связей, пожалуй оставлю все-таки последний свой вариант. Хотя, если вы несогласны, то дайте знать  Пример по ссылке тоже посмотрел, спасибо! Во многом напоминает наш вариант

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)