Postgres storing multiple IDs

Question

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?

Normalize your database. Serialized values in the DB should be your absolute last resort. PostgreSQL arrays can be OK if you need them to save disk space, but have trade-offs too. — Craig Ringer
– Craig Ringer, Commented Jul 14, 2014 at 15:32

Sal00m · Accepted Answer · 2014-07-14 11:25:50Z

2

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:

An user register in your site => insert into users table
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
An user makes one post only for friends => Then insert into posts with value FRIENDS (for example) in post_visualization field.
You want to know all the post that should see one friend => join the four tables and you will get that value
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

edited Jul 14, 2014 at 11:25

answered Jul 14, 2014 at 11:19

Sal00m

2,9143 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Craig Ringer Over a year ago

The only issue here is Pg's huge 28 byte row overhead. If they're dealing with "millions" of entries...

user2982550 Over a year ago

What is the difference between your way and the second example that I wrote ? ("This method would be good but the table could have millions of records")

Sal00m Over a year ago

There's no difference, no matter the table have millions of records, is all about normalization.

Collectives™ on Stack Overflow

Postgres storing multiple IDs

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related