How to design a subscription system on a website?

Question

Database design

PHP + MySQL There is a users

table with the following structure: user_id user_subscribes_count user_subscribers_count ... There is a subscribes table with the following structure: subscribe_id subscribe_author_id subscribe_object_id ... Consider working with tables. With the addition and removal of single records to the subscribes table, everything is clear - added (deleted) a record, increased (decreased) the counter. But what about, for example, mass deletion of records if the user has deleted his account? Then after all, I need to delete all subscriptions and all subscribers of the user, simultaneously reducing the necessary counters in the users table





. What if there are more than 1,000 of them, while there are more than 10,000,000 records in the subscribes table? This is not done with a single request from a php script, right? I have 2 options before my eyes: 1) Link the users and subscribes tables with the “Links” tool so that when a user is deleted, all his subscriptions are deleted. And make a trigger that reduces the number of subscriptions every time a record is deleted. 2) Remove the user_subscribes_count column from the users table so as not to reduce anything and link the users and subscribes tables



the "Connections" tool so that when a user is deleted, all his subscriptions are deleted. In this case, the number of subscriptions will be obtained through a COUNT query . I don’t know how correct this is, because the number of subscriptions will need to be received quite often.

Is it correct to use one of these options (I'm leaning towards the first one) or should I come up with something else? Am I digging in the right direction? How do social networks implement such functionality?

Answer 1

one subscribes table with the following structure:
subscribe_id
subscribe_author_id
subscribe_object_id
and, of course, throw out the counters from the users table
, nothing more is needed
for the rest to learn basic SQL. All subscriptions of a user are deleted with a single request. All subscribers - too.
If we leave aside the ridiculous questions "Do you need some other SQL for a request to delete a thousand records?", and return to the design of subscriptions, then, as Slava Rozhnev correctly suggests ,
Firstly, two composite indexes must be added to the subscription table,
subscribe_author_id , subscribe_object_id
subscribe_object_id, subscribe_author_id
and then the terrible count(*) query will no longer be a nightmare, but will be executed instantly
. And secondly, you can add foreign keys to it that will refer to the users table, with the option of cascading deletion. Then a separate request to delete subscriptions will not have to be written by hand at all, it will be enough to delete only the user.
There is no need to be afraid of a request for deletion. This request will only mark the necessary records as deleted, no one will shorten the file on the disk. The database then uses those same cells for other subscriptions.