Is it efficient to design chat/likes/relationship database in separate table per user? [closed]

Question

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)

Answer 1

No. That won’t scale. Every user and every topic in a separate table would cost an enormous overhead. Smarter would be a users table that has all user id’s and a topics table that has all topics is’s. Next to that you would probably want to have user properties like names and topic properties. They all simply link by their id. The indexes would give quick access. If joins are not fast enough for your use case, you might want to use materialized views or snapshots that present the joins for even quicker accesss but don’t be to quick with pre-optimizations, they might not be needed and shoot you in the foot at the end.