I'm trying to build a social networking database schema. I have certain restrictions inorder to design it perfectly even if it contains billions of users in future.
1. Friends list should not save directly since duplications will occur and will occupy lot of memory spaces.
Consider, Ram have 200 friends in his list say Sita, Laxman.
In Sita's friends list Ram will be there. Here duplications will occur. So ignored this method.
2. Inorder to overcome the previous method. I built like this,
If Ram sends friend request to Sita, Ram will be added in UserID1 and Sita will be added in UserID2 and in IsMutual lets add it as '0'.
'0' represents friendship is not mutual. If the request is accepted, it'll be changed to '1'.
* If Ram has 300 friends, then Ram's ID will be repeated for 300 times.
* Imagine if there is 10 Lakh users with 300 friends. Its really hard to retrieve from 30 Crore datas saved in just a single table. It may decrease the speed and efficiency.
3. To overcome the repitition of the UserID to save the friends list, I used a delimter to save all the friends.
Here Ram's Id will be saved in UserID and in Friends column all the IDs will be seperated using the delimiter ',' (comma).
While fetching, we have to split the delimiter and show the friends list.
* Each and every time it needs to split the datas to show the friends list.
* If any issues irrived in splitting, the entire model will stuck.
My problem is:
>> User ID should not repeat each and everytime it saves the friends list.
>> Avoid Seperating friends list with delimiter and saving it in blob.
>> Should not save all the friends list in just a single table, inorder to reduce the table weight.
Please provide me a good solution for this in which i'm confused for a very long time.