Many to many relationship and MySQL

Question

If I wanted to make a database with subscribers (think YouTube), my thought is to have one table containing user information such as user id, email, etc. Then another table (subscriptIon table) containing 2 columns: one for the user id and one for a new subscriber's user id.

So if my user id is 101 and user 312 subscribes to me, my subscription table would be updated with a new row containing 101 in column 1 and 312 in column 2.

My issue with this is that every time 101 gets a new subscriber, it adds their id to the subscription table meaning I can't really set a primary key for the subscription table as a user id can be present many times for each of their subscribers and a primary key requires a unique value.

Also in the event that there's a lot of subscriptions going on, won't it be very slow to search for all of 101's followers as all the rows will have to be searched and be checked for every time 101 is in the first column and check the user id (the subscriber to 101) in the second column?

Is there's a more optimal solution to my problem?

Thanks!

Answer 1

In your case, the pairs (user_id, subscriber_id) are unique (a user can't have two subscriptions for another user, can they?). So make a compound primary key consisting of both fields if you need one.

Regarding the speed of querying your subscription table: think about the queries you'll run on the table, and add appropriate indexes. A common operation might be "give me a list of all my subscribers", which would translate to something like

SELECT subscriber_id FROM subscriptions WHERE user_id = 123;

(possibly as part of a join). If you have indexed the user_id column, this query can be run quite efficiently.

Answer 2

A Primary Key can be made of two columns, subscribe and subscriber in your case. And since search will only be on integer value, (no text search) it will be fast.

more informations here : https://stackoverflow.com/a/2642799/1338574