CODEWITHSUNDEEP
phppostgresql
user2982550
user2982550

Reputation: 23

Postgres storing multiple IDs

I'm starting to design a big project - similar to Facebook. I got into a situation where I need to solve the following problem.

When you take Facebook at Posting choose who can see the post. (You can check friend who can see the post, I need work with this friends)

And I do not want to deal with similar style

Very bad way (way of example only) 

post_id | visible_user_ id 
5       | 15,25,156,489,21,56,41,56,21,56,1465 ...

This method would be good but the table could have millions of records 

post_id | visible_user_id 
5       | 15 
5       | 25 
5       | 156

The question is: which way do I move in or do not have experience with something similar? I want to find fastest solution, what do you think about serialized string?

Upvotes: 0

Views: 175

Answers (1)

Sal00m
Sal00m

Reputation: 2916

Serialized string with all ids is a very bad idea

Why?

Well, in this string you will have all the ids of every user that can see the post right? If you remove one of them, then you need to recover again all the friends of that user in order to update that field right? too much work to remove one user, and the same if you want to add another user (one more friend)

Another thing , that field type should be of type TEXT in order to store users with lots of friends.

In a first look i can tell you how i'll do it:

First, i will make for tables, users, users_friends, posts and users_posts

  • users: user_id
  • users_friends: user_id, friend_id (friend_id will be a foreign key of the users table)
  • posts: post_id, post_visualization
  • users_posts: user_id, post_id

With this four tables you will solve that problem:

  1. An user register in your site => insert into users table
  2. An user add one friend => afaik the friend should be in your users table, so insert in the users_friends table with the id of the user and the id of the friend
  3. An user makes one post only for friends => Then insert into posts with value FRIENDS (for example) in post_visualization field.
  4. You want to know all the post that should see one friend => join the four tables and you will get that value
  5. Remove one friend => Delete from table users_friends

I think this should give you an idea to start with your project, so good luck

If anything is unclear just comment it

Upvotes: 2

Related Questions