SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 30.03.2013 19:06:45

skolozhabskiy
Участник
Зарегистрирован: 14.08.2011
Сообщений: 24

Оптимальная структура таблиц для rating и comments для оценки продукта

добрый день,

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

1.
есть три таблицы: ratings, comments и products

2.
моя предложенная схема этих таблиц следующая:


products
-------------------
id
name
description
price
и так далее

comments
------------------
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`product_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`rating_id` INT(11)  UNSIGNED NOT NULL DEFAULT 0,
`author` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`email` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`user_ip` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`avatar` VARCHAR(128) COLLATE utf8_unicode_ci NOT NULL,
`topic`  VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`content` TEXT COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`whenadded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`approved` TINYINT(1) NOT NULL DEFAULT 1,
`is_spam` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'clean',
PRIMARY KEY ( `id` ),
UNIQUE KEY (`product_id`, `user_ip`),
FOREIGN KEY ( `product_id` )
REFERENCES products( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY ( `rating_id` )
REFERENCES ratings( `id` )
ON UPDATE CASCADE ON DELETE CASCADE

ratings
-------------------------------
`product_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`comment_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`member_ip` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`rating` DECIMAL (8,2) NOT NULL DEFAULT 0,
 PRIMARY KEY ( `product_id`, `comment_id` ),
FOREIGN KEY ( `product_id` )
REFERENCES products( `id` )
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY ( `comment_id` )
REFERENCES comments( `id` )
ON UPDATE CASCADE ON DELETE CASCADE
 


Привязка к пользователю идет через его IP-адрес, регистрация пользователя не предусматривается из-за ее бесполезности в моем конкретном случае.

Важное замечание: оценку продукту пользователь дает при создании комментария по пяти-бальной шкале.

Вопросы:

1. насколько правильно составлены таблицы с точки зрения их оптимальности?
2. правильно ли определены поля для PRIMARY KEY и UNIQUE KEY.
3. может оптимальнее создать одну таблицу comments с полем `rating`?

Спасибо за любые идеи.

Неактивен

 

#2 30.03.2013 22:04:07

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2422

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

1) Говорить про оптимальность можно только в контексте определенных запросов.
2)
а) is_spam я бы сделал tinyint.

б) UNIQUE KEY (`product_id`, `user_ip`) - почему Вы не допускаете ситуации, что весь, допустим, научный институт сидит за одним прокси-сервером (такое бывает). И двое сотрудников прокомментировали один продукт.

3) Да, не вижу смысла хранить рейтинг в отдельной от комментариев таблице. Вы же не храните - кто именно повысил/понизил  рейтинг.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#3 30.03.2013 22:16:27

skolozhabskiy
Участник
Зарегистрирован: 14.08.2011
Сообщений: 24

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

deadka

1. сейчас речь только о правильности структур таблиц, насколько я понял они правильно составлены, кроме уникальных ключей в таблице comments
2. вы считает, что в таблице comments стоит удалить UNIQUE KEY (`product_id`, `user_ip`)  и оставить только PRIMARY KEY ( `id` ). Я правильно вас понял?
3. суть хранения рейтинга в отдельной таблице в том, что при выводе комментария покупателя о конкретном продукте выводится его оценка в виде рейтинга, который он оставлял при заполнении формы комментария. т.е. комментарии покупателей показаны с теми оценками в виде пяти звездочек, которые они давали при написании отзыва. надеюсь, что я правильно объяснил ситуацию. если такая ситуация имеет место, то в этом случае отдельная таблица для рейтинга оправдана?

какие еще мнения и замечания есть по данной задаче?
может я что еще упустил или может есть иной путь реализации задачи, главное, чтобы все три таблицы products, comments, ratings были сопряжены (связаны) друг с другом.

спасибо за помощь и внимание.

Неактивен

 

#4 30.03.2013 23:08:29

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2422

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

1) ИМХО есть что улучшить идейно (допустим, хранить аватарку для незарегистрированных пользователей ну абсолютно незачем ), но таблицы вполне корректные на вид.
2) Да, этот уникальный ключ я бы удалил. Другие уникальные ключи в голову не приходят.
3) Рейтинг покупателя (если я правильно понял) - величина вычисляемая из нескольких полей - ну так и вычисляйте ее, когда надо. Или приведите тестовые данные и пример.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#5 30.03.2013 23:35:20

skolozhabskiy
Участник
Зарегистрирован: 14.08.2011
Сообщений: 24

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

deadka

наверное я все же не совсем четко описал ситуацию. реально она выглядит так:

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

теперь я правильно описал ситуацию?
я еще раз продумал все что вы отметили и могу предположить, что вы правы - можно не делать отдельную таблицу ratings, так как все равно каждому занесенному комментарию, который имеет связь с конкретным идентификатором продукта и IP-адресом покупателя будет соответствовать как раз данный последним рейтинг при вводе своего коммента. В дальнейшем при запросе можно не только вывести вместе с конкретным комментарием рейтинг, но и выбрать с помощью функции AVG среднее значение рейтинга по всем, кто оставил для данного product_id. Я правильно строю логику своих рассуждений? Таким образом, нужно построить только одну таблицу comments? связав ее с таблицей products:


comments
------------------
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`product_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`author` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`email` VARCHAR(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`user_ip` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`avatar` VARCHAR(128) COLLATE utf8_unicode_ci NOT NULL,
`topic`  VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`content` TEXT COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`rating` DECIMAL (8,2) NOT NULL DEFAULT 0,
`whenadded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`approved` TINYINT(1) NOT NULL DEFAULT 1,
`is_spam` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'clean',
PRIMARY KEY ( `id` ),
FOREIGN KEY ( `product_id` )
REFERENCES products( `id` )
ON UPDATE CASCADE ON DELETE CASCADE
 


мне очень интересно ваше мнение по-поводу моих рассуждений

спасибо

Отредактированно skolozhabskiy (30.03.2013 23:38:25)

Неактивен

 

#6 31.03.2013 00:31:13

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2422

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

Да, именно это я и имел в виду - в нужных случаях с помощью avg считать рейтинг.
Посоветовал бы еще придумать возможность однозначной идентификации пользователя - ибо IP-адрес, это не показатель ни разу smile. Вот e-mail  - хороший кандидат на однозначную идентификацию пользователя.

Тогда если в таблице создать индекс на (product_id,email) - то avg будет как молния считаться. Это к слову про оптимизацию smile.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#7 31.03.2013 00:44:15

skolozhabskiy
Участник
Зарегистрирован: 14.08.2011
Сообщений: 24

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

deadka

Если Вы имеете ввиду добавить в последнюю таблицу comments UNIQUE KEY (`product_id`, `email`) или просто PRIMARY KEY ( `id`, `product_id`, `email` ), но в таком случае пользователь может не всегда оставить свой электронный адрес, а вот считать IP-адрес я могу без ведома пользователя, может тогда UNIQUE KEY (`product_id`, `email`, `user_ip`) или PRIMARY KEY ( `id`, `product_id`, `email`, `user_ip` ) ?

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

Извините с ключами еще плохо освоился.

спасибо за помощь

Отредактированно skolozhabskiy (31.03.2013 00:59:12)

Неактивен

 

#8 31.03.2013 01:50:26

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2422

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

skolozhabskiy написал:

Если Вы имеете ввиду добавить в последнюю таблицу comments UNIQUE KEY (`product_id`, `email`)

Именно так.

skolozhabskiy написал:

или просто PRIMARY KEY ( `id`, `product_id`, `email` )

Нет, в этом случае на эту таблицу будет очень неудобно ссылаться. Primary Key на id и уникальный ключ на связку (product_id и email).

skolozhabskiy написал:

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

Почему? На уровне базы данных можно сделать требование, чтобы поле email не было пустым, а если сильно заморочиться, то можно добавить триггер, который будет проверять, является ли введенный e-mail валидным email'ом.
Но лучше конечно сделать это на уровне интерфейса - то есть не давать писать комментарий, пока человек e-mail не укажет.

skolozhabskiy написал:

а вот считать IP-адрес я могу без ведома пользователя.

Как я уже указывал выше, IP-адрес, штука не очень надёжная.

skolozhabskiy написал:

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

Если создать уникальный индекс (product_id, email), то тогда один пользователь не сможет два раза один товар комментировать. Если же Вы хотите дать ему такую возможность (то есть два раза комментировать один товар), тогда идекс надо делать неуникальным.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#9 31.03.2013 02:01:52

skolozhabskiy
Участник
Зарегистрирован: 14.08.2011
Сообщений: 24

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

deadka

Если создать уникальный индекс (product_id, email), то тогда один пользователь не сможет два раза один товар комментировать. Если же Вы хотите дать ему такую возможность (то есть два раза комментировать один товар), тогда индекс надо делать неуникальным.

т. е. если я вас правильно понял лучше все таки дать такую возможность (комментировать товар многократно) и тогда в таблице comments следует прописать:

PRIMARY KEY ( `id`), KEY(`product_id`, `email`) или лучше PRIMARY KEY ( `id`), INDEX(`product_id`, `email`)?

спасибо за помощь

Неактивен

 

#10 31.03.2013 11:56:16

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2422

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

Я не знаю, необходимо ли Вам давать возможность комментировать один товар одному пользователю несколько раз smile.
Бывает ведь, когда под товаром такое обсуждение начинается... А бывает, что и каждому дают возможность высказаться только по разу. Исходите из Вашей задачи, под нее уже база данных строится.

Если возможность многократного комментирования одним юзером одного товара надо дать, то индекс надо сделать неуникальным, иначе - лучше уникальным. И avg, если будете считать запросом типа SELECT avg(rating) WHERE product_id=АЙДИ_ПРОДУКТА AND email='ЕМЭЙЛ_ПОЛЬЗОВАТЕЛЯ' будет отрабатывать очень быстро.

> PRIMARY KEY ( `id`), KEY(`product_id`, `email`) или лучше PRIMARY KEY ( `id`), INDEX(`product_id`, `email`)?
То же самое smile, KEY и INDEX означают одно и то же. Не забудь добавить слово UNIQUE перед INDEX, если откажетесь от многократного комментирования, приведенного выше.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#11 31.03.2013 13:22:18

skolozhabskiy
Участник
Зарегистрирован: 14.08.2011
Сообщений: 24

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

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

Неактивен

 

#12 01.04.2013 14:05:30

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Оптимальная структура таблиц для rating и comments для оценки продукта

Тоже не советую с IP связываться. Корпорация сидящая за прокси-сервером - будет для вас одним пользователем. И HTTP_X_FORWARDED_FOR не все прокси отдают. Если не предвидится регистрация, то лучше всего выдавать пользователю куку на год при первом заходе на сайт и ее считать идентификатором. Число пользователей отключающих куки стремится к нулю

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson