Наилучшая практика для структуры базы данных, чтобы отслеживать, если пользователь открыл определенный контент?

Question

Наилучшая практика для структуры базы данных, чтобы отслеживать, если пользователь открыл определенный контент?

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

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

Сначала я подумал о таблице базы данных с тремя столбцами: «идентификатор пользователя», «идентификатор содержимого», «статус». Где «статус» — это true для активного содержимого и false для неактивного содержимого. «Идентификатор пользователя» и «идентификатор контента» являются первичным ключом, а «статус» — внешним ключом из таблицы контента. Но потом я подумал о размере этой таблицы. В некоторые годы (надеюсь) может быть 2 миллиона узлов и 250 тысяч пользователей, так что может быть 500 миллиардов строк … Я думаю, что это сильно снизит производительность.

Поэтому я подумал о другом способе: я создаю две таблицы базы данных, одна называется «content_opened_by_users», а другая — «user_opened_content». Первый будет содержать два столбца: «идентификатор контента» и «пользователи». Где «пользователи» — это сериализованный массив, содержащий все идентификаторы пользователей, которые открыли соответствующий идентификатор контента в той же строке. Вторая таблица будет содержать три столбца: «идентификатор пользователя», «активный контент», «неактивный контент». Где «активный контент» и «неактивный контент» также являются сериализованными массивами, содержащими идентификаторы контента активного / неактивного контента, который открыл соответствующий пользователь.
Таким образом, всякий раз, когда контент открывается пользователем, сервер загружает соответствующий массив «users» из таблицы «content_opened_by_users» и соответствующий массив «active content» из таблицы «user_opened_content». Если идентификатор пользователя еще не существует в массиве «users», он будет добавлен, а если идентификатор содержимого еще не существует в массиве «active content», он также будет добавлен. Тогда оба массива будут перезаписаны в базе данных.
Теперь, если я опрашиваю всех пользователей, открывших определенный контент, я получаю массив из таблицы «content_opened_by_users». И если я запрашиваю все активные / неактивные узлы, которые были открыты определенным пользователем, я получаю массивы «активное содержимое» и «неактивное содержимое» из таблицы «user_opened_content». Затем я проверяю, содержит ли массив «активный контент» какие-либо идентификаторы контента, которые пока что неактивны, и переносит их в массив «неактивного контента» и передаю их обратно в базу данных.

Я знаю, что я создаю избыточные данные в этих двух таблицах, но я надеюсь, что это повысит производительность.

Ну, это будет удобный способ для отслеживания пользователей? Или может быть другой, более эффективный способ?

Буду рад каждому совету!
Большое спасибо.
Даниил

0

database mysql php user-tracking

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Во-первых, вы можете прочитать о нормализация базы данных.

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

table    | columns
-------------------
users    | id, login, ...
content  | id, title, active, ...
access   | id, user_id, content_id, timestamp, ...

Затем вы можете хранить пользователей и элементы контента независимо от того, кто открыл какой элемент. access Таблица содержит записи о том, какой пользователь открыл какой элемент контента в какое время. Это позволяет вам отслеживать многочисленные статистические данные без необходимости хранить какую-либо информацию о пользователе не открытие предмета

Основными запросами будут:

Какие пользователи открыли элемент

SELECT DISTINCT users.login FROM access JOIN users ON access.user_id = users.id JOIN content ON access.content_id = content.id WHERE content.title LIKE '%test%'

Активный контент, открытый пользователем

SELECT DISTINCT content.title FROM access JOIN users ON access.user_id = users.id JOIN content ON access.content_id = content.id WHERE content.active = true AND user.login = 'testuser'

Неактивный контент, открытый пользователем
```
SELECT DISTINCT content.title FROM access JOIN users ON access.user_id = users.id JOIN content ON access.content_id = content.id WHERE content.active = false AND user.login = 'testuser'
```
Предполагая, что доступ может происходить только с активным контентом. Чтобы отследить время изменения статуса, добавьте еще одну таблицу, которую можно использовать для поиска отметки времени последнего изменения и сравнения с записями доступа.

Обратите внимание, что эти запросы вообще не оптимизированы. Вы также должны добавить соответствующие Ограничения FOREIGN KEY обеспечить согласованность данных и рассмотреть возможность добавления дополнительных индексов для повышения производительности.

0