Форум пользователей MySQL

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

В продолжение темы хотелось бы задать еще несколько вопросов.
1. Необходимо реализовать поиск по параметрам (марка, модель, тип двигателя, дополнительные опции: кондиционер, круиз-контроль и т.д.) Изначально думал об использовании модели EAV, но услышал много нелестного и решил отказаться. Хотелось бы услышать и ваше мнение. [в моей задаче набор параметров конечен и добавление новых возможно только модератором]
2. Параметры хранятся в отдельных таблицах car_ad_param_values (для авто), tyres_ad_param_values (для шин) и т.д.

Вопрос состоит в следующем: как оптимальней хранить булевы значения в виде BITS(M) или аналога или для каждого заводить отдельное поле TINYINT(1)

zales, модель EAV предоставляет большую гибкость, но проигрывает в скорости на абстрактных тестовых данных. Плюс, гемор в реализации. Если параметры могут добавляться администратором, то значит набор не конечен априори Я бы для Вашего случая не брал бы вчистую эту схему. Я бы сделал таблицу возможных параметров и свзяь с таблицей объекта вида "один объект ко многим параметрам". Судя по всему, Вы так и сделали, хотя фраза "Параметры хранятся в отдельных таблицах..." без структуры таблицы не дает полной уверенности
По поводу boolean. Я предпочитаю не испоьзовать boolean вообще. В свое время на Почте России оптимизировал базу, где у двух миллионов записей об отправлениях сделали boolean поле "Доставлено". Запрос "получи что-то среди недоставленных" выполнялся больше 30 секунд. После этого boolean поля недолюбливаю

Вот примерная схема автообъявлений (без запчастей и без разделов пока)

---

Прикрепленные файлы:
auto.png, Размер: 22,695 байт, Скачано: 777

Первая значительно лучше второй, но я бы не делал ни так, ни так Если не говорить о глобальной реструктуризации, то я бы посоветовал следующее (по первой схеме):
1. Я бы не рекомендовал навешивать на БД реализацию бизнес-логики. Обязательность/необязательность каких-то атрибутов при заполнении - это именно бизнес-логика, которая может меняться со временем при неизменности самих данных. Это относится как к автомобилю, так и к пользователю. Связей "один к одному" существовать без особой необходимости не должно, а у Вас такой необходимости нет.
2. Вы уверены, что информацию о пользователе (телефонах и т.д.) нужно дублировать? У Вас возникнут проблемы, если пользователь поменяет номер телефона.
3. Я бы убрал country_id из car_ad_param_values - мне кажется, что запрос типа "жигули по всей России" или "Вольво в любом городе Украины" маловероятны, а страну Вы всегда получите через город.

Отредактированно Magz (24.06.2009 11:55:05)

1. В первом случае разделение таблицы объявлений на две (car_ad, car_ad_stats) было вызвано следующими соображениями: использоваться будет MyISAM, поэтому статистика просмотров страниц объявлений будет изменяться только в таблице car_ad_stats (last_update, hits), (я не большой специалист в БД), но так рекомендуют делать (может я слишком доверчивый . По поводу user_info я согласен.
2. Что касается  информации в объявлении смысл следующий: если пользователь не заполнил поля при создании объявления, то берутся данные из профиля, в противном случае те, которые он ввел при оставлении объявления. Так как часто пользователь хочет оставить информацию с др.телефонами.
3. согласен, избыточная информация.
4. Magz, если не так и не так, то если не секрет как?

to Magz: и еще вопрос. Почему вам второй вариант нравится меньше, чем первый?

Таблица Autos - данные об автомобиле, таблица Options - дополнительные опции. Разделение простое: если про что-то можно сказать "есть или нету" (например, магнитола, тонировка, литые диски, сигнализация) - то это в Options. Все остальное - в Autos. Соединяются через связь "многие-ко-многим" (AutoOptions). Выбор обусловлен тем, что подавляющее большинство запросов не будет использовать опции вообще, а будут ограничиваться данными из таблицы Autos. А положительный эффект для проекта от простоты реализации данной схемы перекроет отрицательный из-за сложных запросов.
По повод цены - ИМХО, никто до копеек или центов цену указывать не будет, поэтому предлагаю цену делать целым числом.
Таблица Adverts - данные об объявлении: кто подал, когда. Выделил отдельно от Autos (помимо более точного соответствия предметной области), так как сортировать наверняка придется по дате выдачи. Если эту дату "запихнуть" в Autos, то будут пробмы с индексами. К тому же, мы делаем независимым текущее состояние рынка (пул всех объявлений) и историю рынка (пул всех автомобилей). Это нам позволит строить аналитику по изменению цен, отследить путь машины (например, одну и ту же машину могут продавать несколько раз), оценивать машины путем сравнения.
При добавлении объявлений по запчастям, я бы в таблицу "Adverts" добавил бы поле id_запчасть и отдельную таблицу по аналогии с Autos. Необходимости получения сразу объявлений и по запчастям, и по машинам нет - в поиске мы четко разграничиваем области, а в управлении зависит от интерфейса. Я бы тоже разгарничил. В любом случае, ориентироваться стоит на поиск - он происходит в сотни раз чаще, чем операции по управлению объявлениями.
Теперь про пользователей и телефоны. Все телефоны выносим в отдельную таблицу. Пользователь добавляет нужное количество телефонов (например, 5). Потом дает 5 объявлений - каждое на свой телефон. Реализуется с помощью бизнес-логики, а хранятся ссылки на телефон. Это позволяет избежать дублирования информации при созранении возможности давать объявления на разные номера. Так же, позволит избежать повторных регистраций одного и того же человека - под разными логинам не может быть одинаковых телефонов.

Теперь про запросы. Самый сложный - это выбрать машины с дополнительными опциями. На самом деле, все достаточно просто и работает в пределах разумного.

mysql> EXPLAIN
    -> SELECT a.id, a.Description
    -> FROM Autos a FORCE INDEX (PRIMARY)
    ->  JOIN AutoOptions ao ON (a.id = ao.id_auto AND ao.id_option = 2)
    ->  JOIN AutoOptions ao2 ON (a.id = ao2.id_auto AND ao2.id_option = 4)
    ->  JOIN AutoOptions ao3 ON (a.id = ao3.id_auto AND ao3.id_option = 3)
    ->  JOIN Adverts av ON av.id_auto = a.id
    -> WHERE  ao.id_auto = ao2.id_auto AND ao.id_auto = ao3.id_auto AND a.id_auto_brand = 1
    -> ORDER BY av.AddDate;
+----+-------------+-------+--------+-------------------+----------+---------+---------------------------+------+-----------------------------+
| id | select_type | table | type   | possible_keys     | key      | key_len | ref                       | rows | Extra                       |
+----+-------------+-------+--------+-------------------+----------+---------+---------------------------+------+-----------------------------+
|  1 | SIMPLE      | av    | index  | id_index,NewIndex | NewIndex |      12 | NULL                      |    4 | Using index; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY           | PRIMARY  |       4 | db_alco.av.id_auto        |    1 | Using where                 |
|  1 | SIMPLE      | ao2   | eq_ref | PRIMARY           | PRIMARY  |       8 | db_alco.a.id,const        |    1 | Using index                 |
|  1 | SIMPLE      | ao3   | eq_ref | PRIMARY           | PRIMARY  |       8 | db_alco.a.id,const        |    1 | Using index                 |
|  1 | SIMPLE      | ao    | eq_ref | PRIMARY           | PRIMARY  |       8 | db_alco.ao2.id_auto,const |    1 | Using where; Using index    |
+----+-------------+-------+--------+-------------------+----------+---------+---------------------------+------+-----------------------------+

Но, повторюсь, я думаю, что такой запрос будет редким.

Статистику просмотра объявления Вы правильно выделили в отдельную таблицу, я ее не стал показывать на схеме, но сделал бы так же.

Отредактированно Magz (24.06.2009 22:43:48)

---

Прикрепленные файлы:
autos.jpg, Размер: 85,174 байт, Скачано: 831

Как сказал один киноактер: "Приятно общаться с умными людьми".
Все лаконично, по делу и с примерами. Magz, спасибо, что потратили свое время на это.

Я только не понял, что вы имели в виду, когда говорили о запчастях:
>> "При добавлении объявлений по запчастям, я бы в таблицу "Adverts" добавил бы поле id_запчасть и отдельную таблицу по аналогии с Autos. Необходимости получения сразу объявлений и по запчастям, и по машинам нет - в поиске мы четко разграничиваем области, а в управлении зависит от интерфейса."

а как же избыточность и нормальные формы? (или вы о моей идее хранить все объявления в одном месте) Может все-таки лучше несколько таблиц для объявлений: одну для авто, вторую для запчастей, третью для дисков, четвертую для колес. Тогда и поиск объявлений будет вестись по таблицам меньших размеров и ваше предложение "разделяй и властвуй" работает.

to Magz:
>> Таблица Adverts - данные об объявлении: кто подал, когда. Выделил отдельно от Autos (помимо более точного соответствия предметной области), так как сортировать наверняка придется по дате выдачи. Если эту дату "запихнуть" в Autos, то будут пробмы с индексами. К тому же, мы делаем независимым текущее состояние рынка (пул всех объявлений) и историю рынка (пул всех автомобилей). Это нам позволит строить аналитику по изменению цен, отследить путь машины (например, одну и ту же машину могут продавать несколько раз), оценивать машины путем сравнения.

Здесь связь между таблицами будет "один-к-одному", т.к. пользователь при подаче объявлений не может выбирать автомобиль из оставленных ранее. Они могут быть абсолютно идентичны по параметрам и даже быть одним автомобилем, но продаваться в разное время разными людьми. Конечно, логично было бы разделить сущности "машины" и "объявления". Может тогда лучше сделать структуру "звезда" вида:

ОБЪЯВЛЕНИЕ (кто, когда, доп.инфо)
--СВЯЗЬ--АВТОМОБИЛИ (параметры для этого автомобиля)--СВЯЗЬ--ОПЦИИ
--СВЯЗЬ--ЗАПЧАСТИ (параметры для этой запчасти)
--СВЯЗЬ--ДИСКИ
--СВЯЗЬ--ШИНЫ

Если бы объявление подавалось по VIN-коду, то тогда можно было бы говорить о соответствии "один-ко-многим".

zales, извините, сейчас полный загруз с работой, отпишусь сегодня-завтра.