проектирование БД

Есть скрипт каталога товаров с базой данных примерно следующей структуры:

Id (ид товара)
name (наименование)
desc (описание)
rating (рэйтинг)

Рэйтинг соответствует количеству просмотров данного товара за всё время. Т.е. при каждом клике на изображение товара, рейтинг увеличивается на единицу.

Добавил в таблицу дополнительные поля:
r_1 (количество просмотров за последний месяц)
r_2 (количество просмотров за последние 6 месяцев)
r_3 (количество просмотров за последний год)

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

Кто нибудь решал подобную задачу?

Можно просто создать таблицу:
id
id-товара
ye - год
me - месяц
de - день
h - час
i - минуты
s - секунды

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

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

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

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

Тем, что Ваш вариант по сути, можно и правильнее сделать так: создать таблицу, куда записывать только одно поле - дату клика (вместе со временем). А при выводе пользователю считать COUNT по этой таблице. Конечно, такое построение даст возможность считать рейтинг за любой промежуток времени.
Но. Представьте себе, что у нас хотя бы 100000 кликов в день.. Это будет 36500000 записей за год. И самое главное, что нет ни одного поля, по которому бы можно было создать индекс (поле по сути одно и оно индекс по нему будет иметь слишком высокое значение CARDINALITY). И если мы захотим выбрать COUNT из такой таблицы, это приведет к жутко тормозному FULL SCAN (так как нет индексов).
Поэтому лучше хранить данные в уже агрегированном виде, когда достаточно знать число посещений за месяц. Правда, в этом случае мы будем знать число посещений именно за календарный, а не астрономический, месяц. (То есть если сейчас, к примеру, 6-е число, то мы не сможем узнать число посещений с 6-го мая по 6-е июня, а только с 1-го мая по 1-е июня).

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

Тогда может проще будет сделать
Id – ид товара
timestamp – дата просмотра


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



Про каленадрные периоды тоже думал. В принципе это устраивает. Т.е. когда пользователь кликает на ссылку "лучшие товары за 3 месяца", он получит выброку лучших товаров за 3 предыдущих месяца, без учёта текущего. Например, если сегодна 6-е июня, то выборка будет произведена за период с 1-е марта по 31 мая.

Сейчас вот думаю, може быть лучше привести таблицу к следующему виду:
Id (ид товара)
name (наименование)
desc (описание)
rating (рейтинг)
rm_1 (количество просмотров за январь)
rm_2 (количество просмотров за февраль)
rm_3 (количество просмотров за март)
...
rm_12 (количество просмотров за декабрь)

Тогда для того что бы выбрать лучшие товары за 3 месяца запрос будет выглядеть следующим образом

Увеличение числа полей ничем не плохо для СУБД, но для чтения человеком излишнее может быть вредно. Хотя в данном случае, не думаю, что это так. Альтернативой можно посоветовать отдельную таблицу. То есть id товара, номер месяца, рейтинг в этом месяце. Правда, тогда уже агрегация данных будет выполняться несколько дольше (нужно будет суммировать данные из присоединяемой таблицы)
Суммирование - простая арифметическая операция, так что это точно не приведет к падению производительности.
Чем плохо записывать таймштамп каждого клика - я, кажется, уже объяснил.

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

EuGen
Дык при таких нагрузках, можно снизить точность до одного дня, т.е. получится в год примерно 365 записей в таблице! А мы сможем вычислить с 6-го мая по 6-е июня спокойно количество посещений или кликов, и то и другое можно записывать туда же.
Если делать с точностью до одного часа, то выйдет примерно 8760 записей в год. Думаю такая четкость и нагрузка довольна хороша! Тем более, что можно организовать кэширование на вычисление периодов больших чем за месяц.

То есть в итоге, все сводится к агрегируемой таблице, как я и описал. Только Вы сейчас предлагаете сделать интервал, равный 1 дню. А идейно разницы нет никакой.

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

EuGen Тут любой вариант - это дело точности вывода данных, даже мой первый вариант можно оптимизировать и давать с точность до секунды результат!

Да и поправка - "с точность до часа" - можно спокойно организовывать!!! Выйдет только 8760 в год примерно.

(Отредактировано автором: 06 Июня, 2009 - 18:00:44)

И как Вы будете оптимизировать вариант "с точностью до секунды?"
Приведите, пожалуйста, аргументы, почему Ваш вариант лучше. Пока что я вижу лишь большое число восклицательных знаков. К тому же в последнем случае, повторюсь, идейно нет никакой разницы.

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

EuGen
1) А зачем вычислять постоянно колличество?
2) Вариант с точностью до часа - я бы делал именно его, записей в год получается не много, а точность вывода можно организовать до часа. Я считаю самый оптимальный вариант.

1) Если делать "с точностью до секунды" - предложите вариант, как Вы будете вычислять
2) С точностью до часа - второй раз повторюсь, это такое же точно агрегирование. Автору темы было нужно с точностью до месяца, и поэтому был предложен наибольший интервал - месяц.
Наше с Вами обсуждение уже ушло в сторону от темы, я полагаю.

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

EuGen
Не вижу смысла делать с точностью до секунды, тогда лучше подсчитывать количество уникальных кликов.
А вот вариант с Вашими месяцами, лучше записывать с точностью до дня, чтоб можно было выводить в середине месяца не за 15 дней, а точно за 30 или 31 день.
Т.к. тогда за последний месяц если, как Вы говорите 100 000 кликов в стуки, то в месяц их примерно 3 000 000 , а посетитель увидит например в первый день 100 000 и тогда по статистике будут очень сильные скачки.

Только представьте, что 30 числа пользователь видит 3 000 000 скачиваний, а через день - два - уже 100 000

(Отредактировано автором: 06 Июня, 2009 - 19:03:24)

Так ведь не я посоветовал делать с точностью до секунды, а календарные периоды вполне устраивают автора.
P.S. Я правда не понимаю, о чем речь и спор.

-----
Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.