Post Closed as "Needs more focus" by mustaccio, Colin 't Hart, Vérace, LowlyDBA, Max Vernon
4 added 125 characters in body
source | link

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)

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?

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)

3 added 29 characters in body
source | link

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 tabletables for storing users that like a topic (user_id, topic_id), with naming: users_of_topic_id
  • N tabletables 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_topics_of_[user1] and topics_of_topics_of_[user2]

If we want to give suggested topics, we can intersect random row from users_of_users_of_[friend'stopictopic_id]

If we want to suggest a mutual friend, we can union all relations_relations_[friendsfrienduser_id] then sum it.

Is this design efficient?

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 table for storing users that like a topic (user_id, topic_id), with naming: users_of_topic_id
  • N table 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_topic

If we want to suggest a mutual friend, we can union all relations_friend then sum it.

Is this design efficient?

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?

2 added 304 characters in body
source | link

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 table for storing users that like a topic (user_id, topic_id), with naming: users_of_topic_id
  • N table 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_topic

If we want to suggest a mutual friend, we can union all relations_friend then sum it.

Is this design efficient?

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 table for storing users that like a topic (user_id, topic_id), with naming: users_of_topic_id
  • N table 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]

Is this design efficient?

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 table for storing users that like a topic (user_id, topic_id), with naming: users_of_topic_id
  • N table 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_topic

If we want to suggest a mutual friend, we can union all relations_friend then sum it.

Is this design efficient?

1
source | link