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 '19 at 15:51
  • optimist indeed XD thanks for the suggestion – Kokizzu Jan 21 '19 at 16:45

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.

  • even when using cockroachdb? – Kokizzu Jan 20 '19 at 20:20
  • ah they said it would slowdown after 1k-10k tables using cockroachdb – Kokizzu Jan 20 '19 at 20:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.