Подтвердить что ты не робот

База данных - проектирование таблицы "События"

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

У меня есть четыре типа пользователей, смоделированных в структуре Class Table Inheritance, в основной "пользовательской" таблице хранятся данные, общие для всех (id, username, password, несколько flags,...) вместе с некоторыми полями TIMESTAMP (date_created, date_updated, date_activated, date_lastLogin,...).

Чтобы процитировать совет № 16 из статьи Nettuts +, упомянутой выше:

Пример 2: у вас есть "last_login" поле в таблице. Он обновляет каждый время входа пользователя на сайт. Но каждое обновление таблицы приводит к кэш запросов для этой таблицы очищено. Вы можете поместить это поле в другую таблицу, чтобы минимальная таблица пользователей.

Теперь это становится еще сложнее, мне нужно отслеживать статистику пользователей, например

  • сколько уникальных раз просмотрен профиль пользователя
  • сколько уникальных раз было нажато объявление от определенного типа пользователя
  • сколько уникальных раз было опубликовано сообщение от определенного типа пользователя
  • и т.д.

В моей полностью нормированной базе данных это составляет примерно от 8 до 10 дополнительных таблиц, но это не так много, но я хотел бы сохранить все просто, если бы мог, поэтому я придумал следующую таблицу "events" 

|------|----------------|----------------|---------------------|-----------|
| ID   | TABLE          | EVENT          | DATE                | IP        | 
|------|----------------|----------------|---------------------|-----------|
| 1    | user           | login          | 2010-04-19 00:30:00 | 127.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 1    | user           | login          | 2010-04-19 02:30:00 | 127.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | created        | 2010-04-19 00:31:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | activated      | 2010-04-19 02:34:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | approved       | 2010-04-19 09:30:00 | 217.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | login          | 2010-04-19 12:00:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | created        | 2010-04-19 12:30:00 | 127.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | impressed      | 2010-04-19 12:31:00 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:01 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:02 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:03 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:04 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 15   | user_ads       | clicked        | 2010-04-19 12:31:05 | 127.0.0.2 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | blocked        | 2010-04-20 03:19:00 | 217.0.0.1 |
|------|----------------|----------------|---------------------|-----------|
| 2    | user           | deleted        | 2010-04-20 03:20:00 | 217.0.0.1 |
|------|----------------|----------------|---------------------|-----------|

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

Из-за растущего характера таблицы events я также подумал о некоторых оптимизации, таких как:

  • # 9. Поскольку существует только конечное число таблиц и конечное (и предопределенное) количество событий, столбцы TABLE и events могут быть установлены как ENUM вместо из VARCHAR, чтобы сохранить некоторое пространство.
  • # 14. Сохраните IP как UNSIGNED INT s с помощью INET_ATON() вместо VARCHAR s.
  • Сохранить DATE как TIMESTAMP s вместо DATETIME s.
  • Используйте ARCHIVE (или CSV?) вместо InnoDB/MyISAM.
    • Поддерживаются только INSERT и SELECT, а данные сжимаются на лету.

В целом, каждое событие будет потреблять только 14 (несжатых) байтов, которые, по моему мнению, подходят для моего трафика.

Плюсы:

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

Минусы:

  • Не реляционные (все еще не так плохо, как EAV):
    • SELECT * FROM events WHERE id = 2 AND table = 'user' ORDER BY date DESC();
  • Нагрузка на 6 байт на событие (id, TABLE и EVENT).

Я больше склоняюсь к этому подходу, поскольку профессионалы, похоже, намного перевешивают минусы, но я все еще немного неохотно... Я что-то упустил? Что вы думаете об этом?

Спасибо!

@coolgeek:

Одна вещь, которую я делаю немного иначе нужно поддерживать entity_type и использовать его идентификатор в столбец object_type (в вашем случае, столбец "ТАБЛИЦА" ). Вы хотели бы делать то же самое с event_type таблица.

Чтобы быть понятным, вы имеете в виду, что я должен добавить дополнительную таблицу, которая отображает, какие события разрешены в таблице, и использовать PK этой таблицы в таблице событий вместо пары TABLE/EVENT?

@ben:

Это все статистические данные, полученные из существующие данные, не так ли?

Дополнительные таблицы в основном связаны со статистикой, но я их еще не существует, некоторые примеры:

user_ad_stats                          user_post_stats
-------------                          ---------------
user_ad_id (FK)                        user_post_id (FK)
ip                                     ip
date                                   date
type (impressed, clicked)

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

Я согласен, что он должен быть отдельным, но больше, потому что это принципиально разные данные. Что есть кто то, что кто-то делает, два разных вещи. Я не думаю, что волатильность важно.

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

Я думаю, что тебе не хватает леса для деревья, так сказать.

Предикат для вашей таблицы будет "Идентификатор пользователя из IP IP в момент DATE EVENTed to TABLE", который кажется разумно, но есть проблемы.

То, что я подразумевал для "не так плохо, как EAV", состоит в том, что все записи следуют линейной структуре, и их довольно легко запросить, нет иерархической структуры, поэтому все запросы могут выполняться с помощью простого SELECT.

Что касается вашего второго заявления, я думаю, вы поняли меня неправильно здесь; IP-адрес не обязательно связан с пользователем. Структура таблицы должна читать примерно следующее:

IP-адрес (IP) сделал что-то (EVENT) к PK (id) table (TABLE) на дату (DATE).

Например, в последней строке моего примера выше следует прочитать, что IP 217.0.0.1 (некоторый администратор), удалил пользователя # 2 (последний известный IP-адрес 127.0.0.2) в 2010-04-20 03: 20:00.

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

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

Еще одна вещь, которая учитывается в этом аргументе, заключается в том, что я буду хранить гораздо больше событий, и каждое событие будет более чем в два раза по сравнению с оригинальным дизайном, имеет смысл использовать механизм хранения ARCHIVE здесь, единственный Дело в том, что оно не поддерживает FK (ни UPDATE, ни DELETE s).

4b9b3361

ОТВЕТЫ

Ответ 1

Я очень рекомендую этот подход. Поскольку вы, вероятно, используете одну и ту же базу данных для OLTP и OLAP, вы можете получить значительные преимущества в производительности, добавив некоторые звезды и снежинки.

У меня есть приложение для социальных сетей, которое в настоящее время находится в 65 таблицах. Я поддерживаю одну таблицу для отслеживания объектов (блоги/сообщения, форум/поток, галерея/альбом/изображение и т.д.), Другой для рекомендаций по объектам и третью таблицу для суммирования активности вставки/обновления в десятке других таблиц.

Одна вещь, которую я делаю несколько иначе, - это сохранить таблицу entity_type и использовать ее идентификатор в столбце object_type (в вашем случае столбец "TABLE" ). Вы хотели бы сделать то же самое с таблицей event_type.

Разъяснение для Alix. Да, вы поддерживаете справочную таблицу для объектов и справочную таблицу для событий (это будут ваши таблицы измерений). Ваша таблица фактов будет иметь следующие поля:

id
object_id
event_id
event_time
ip_address

Ответ 2

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

В моей полностью нормализованной базе данных это добавляет до 8-10 дополнительных таблицы

Это все статистические данные, полученные из существующих данных, не так ли? ( Обновить: хорошо, это не так, так пренебрегайте следующим.) Почему бы просто не быть представлениями или даже материализованными представлениями?

Может показаться медленной операцией для сбора этих статистических данных:

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

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

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

Я не думаю, что волатильность так важна. СУБД уже должна позволить вам поместить файл журнала и файл базы данных на отдельные устройства, что и то же самое, и утверждение не должно быть проблемой при блокировке на уровне строк.

Не реляционные (все еще не так плохо, как EAV)

Я думаю, что вам не хватает леса для деревьев, так сказать.

Предикат для вашей таблицы будет "Идентификатор пользователя из IP-IP в момент DATE EVENTed to TABLE", который кажется разумным, но есть проблемы. (Обновление: ладно, так вроде вроде как.)

Вы по-прежнему можете присоединяться к пользовательским событиям пользователям, но вы не можете реализовать ограничение внешнего ключа. То, что EAV обычно проблематично; независимо от того, является ли это что-то точно EAV, не имеет большого значения. Обычно это одна или две строки кода для реализации ограничения в вашей схеме, но в вашем приложении это могут быть десятки строк кода, и если одни и те же данные доступны в нескольких местах несколькими приложениями, это может легко умножить на тысячи строки кода. Итак, в общем случае, если вы можете предотвратить плохие данные с ограничением внешнего ключа, вам гарантировано, что это приложение не сделает.

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

Дальнейший комментарий

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

И с некоторыми предостережениями вы можете сделать очень успешную систему. С надлежащей системой ограничений вы можете сказать: "Если какое-либо приложение, работающее с базой данных, не знает, что он делает, СУБД отметит ошибку". Для этого может потребоваться больше времени и денег, чем у вас, поэтому что-то более простое, что вы можете иметь, вероятно, лучше, чем нечто более совершенное, чего вы не можете. C'est la vie.

Ответ 3

Я не могу добавить комментарий к ответу Бена, так что две вещи...

Во-первых, было бы одним делом использовать представления в автономной базе данных OLAP/DSS; это совсем другое, чтобы использовать их в своей базе данных транзакций. Высокопроизводительные пользователи MySQL рекомендуют не использовать представления, в которых важны показатели производительности

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