Как спроектировать систему подписок на сайте?

Question

[IvanIF]

PHP + MySQL

Есть таблица users с вот такой структурой:

user_id
user_subscribes_count
user_subscribers_count
...

Есть таблица subscribes с вот такой структурой:

subscribe_id
subscribe_author_id
subscribe_object_id
...

Рассмотрим работу с таблицами. С добавлением и удаление единичных записей в таблицу subscribes всё ясно — добавил (удалил) запись, увеличил (уменьшил) счётчик. А как быть, например, при массовом удалении записей, если пользователь удалил свой аккаунт? Тогда ведь мне надо удалить все подписки и всех подписчиков пользователя, попутно уменьшив необходимые счётчики в таблице users. А если их больше 1.000, в то время как всего в таблице subscribes больше 10.000.000 записей? Одним запросом из php-скрипта это ведь не делается, верно? У меня перед глазами 2 варианта:

1) Связать таблицы users и subscribes инструментом «Связи», чтобы при удалении пользователя, удалялись все его подписки. И сделать тригер, уменьшающий количество подписок, при каждом удалении записи.

2) Убрать из таблицы users столбец user_subscribes_count, чтобы ничего не уменьшать и связать таблицы users и subscribes инструментом «Связи», чтобы при удалении пользователя, удалялись все его подписки. В таком случае количество подписок будет получаться через запрос типа COUNT. Не знаю, на сколько это правильно, ведь количество подписок нужно будет получать достаточно часто.

Правильно ли использовать один из этих вариантов (я склоняюсь к первому) или надо придумывать что-то ещё? В том ли направлении я копаю? Как подобный функционал реализовывают социальные сети?

Comments

[Ипатьев]

Одним запросом из php-скрипта это ведь не делается, верно?

Не понял, а сколько по вашему должно быть запросов ? 10.000.000?

Вообще вам бы букварь по SQL прочитать, а не социальные сети на сто тыщ пятсьсот миллиардов пользователей проектировать

[IvanIF]

Роман Юрьевич Ипатьев, я знаю, что это делается 1 запросом. Вопрос в том, что правильно ли писать из скрипта запрос на удаление 1.000 записей в 10.000.000 таблице...

[Ипатьев]

А что с этим запросом не так?
Вот откуда берутся эти фантазии?
Социальной сети нет
Офигиллиарда пользоваталей нет
Подписки не то что тыщи - и одной нету.
И никогда не будет.
Но страхов уже полные штаны. А вдруг для таблицы в тыщу записей какой-то другой SQL нужен?!

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

Answer 1

[Ипатьев]

одна таблица subscribes с вот такой структурой:

subscribe_id
subscribe_author_id
subscribe_object_id

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

Если оставить в стороне нелепые вопросы "а для запроса на удаление тыщи записей какой-то другой SQL нужен?", и вернуться проектированию подписок, то, как правильно подсказывает Slava Rozhnev,

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

А во-вторых, в неё можно добавить внешние ключи, которые будут ссылаться на таблицу users, с опцией каскадного удаления. Тогда отдельный запрос на удаление подписок и вовсе не придётся писать руками, достаточно будет удалить только юзера.

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

Comments

[Slava Rozhnev]

Не забываем про индексы и внешние ключи

Answer 2

[jazzus]

Второй вариант, без префиксов subscribe_ итак понятно, что от subscribers. И связь полиморфную, чтобы подписываться на кучу объектов с одним функционалом через трейт модели. для соцсетей актуально. В дальнейшем можно оптимизировать коунтерами итд