I am using django framework with mysql as database. we are trying to build a social network like platform, our application has a followers/following system, we facing some difficulty in choosing the right database schema, we thought of using one of the following two database concepts,
First one would make one table for users and one table for relationships. The relationship table would look like:
id | follower | following
1 | 23 | 20
2 | 58 | 20
3 | 84 | 20
4 | 20 | 11
This way adding new relationships is simply an insert, and removing relationships is a delete. It's also much easier to roll up the counts to determine how many followers a given user has. But it has a problem that the number of rows will grow exponentially. i.e for 100 users following other 100 users may need 10000 rows.
To avoid this we can use the following table structure (second)
id | user_id | followers | following
1 | 20 | 23,58,84 | 11,156,27
2 | 21 | 72,35,14 | 6,98,44,12
... | ... | ... | ...
here we are storing the user's follower's/following in string format of user_id's separated my coma's. For finding user's follower's/following we need to fetch the string and extract the information. For deleting, inserting and for fetching user's follower's/following we need to process the string present in the database. But this method will take n-rows for n-users.
Now the question is which schema design is good for using??
Is it ok to use second schema design by processing the string present in the database or using first design will ok??
