The specs:
- there would be about 1 billion users
- there would be probably 1 million topics
- each topics has subtopics stored at the same level as topics
- user has many to many relationship with topics (whether they like the topics or not)
- user can chat with another user
The table design:
- 1 table for
users
- 1 table for
topics
- N tables for storing users that like a topic (
user_id
,topic_id
), with naming: users_of_topic_id
- N tables for storing topics that liked by a user (
user_id
,topic_id
), with naming: topics_of_user_id
- NxM tables for
chats
(loweruser_id
, higheruser_id
), with naming: chats_[loweruser_id
]_[higheruser_id
] - N+M tables for chat reference tables, storing table names of the
chats
and their relationships), with naming: relations_[user_id
]
So if we want to match topics between users, we can use intersect
between topics_of_[user1
] and topics_of_[user2
]
If we want to give suggested topics, we can intersect
random row from users_of_[friend'stopic_id
]
If we want to suggest a mutual friend, we can union all
relations_[friendsuser_id
] then sum it.
Is this design efficient?
EDIT apparently this is a job for graph database (such as dgraph or neo4j)
1 billion users
? For real? I admire your optimism. Have you used a search engine for "chat app schema" or similar? – Vérace Jan 21 at 15:51