MySQL - Private Posts Display

Question

So I have 3 tables: users, posts, private. In this example, lizzy created a private post 'Dating' and only wants her girlfriends to see it 'authorized_user_ids' in the private table, and herself 'user_id' in posts table.

users
user_id   user_name
   1        tony
   2        steph
   3        lizzy
   4        adam
   5        lara
   6        alexa


posts
post_id   user_id   post_name   private (0 is public, 1 is private to authorized users)
   1         1       Coding        0
   2         3       Dating        1
   3         3       Show Me       0

private
private_id   post_id   authorized_user_ids
    1           2             2,5,6

Only the private poster user_id and the authorized_user_ids should see the 'Dating' post by lizzy, but everyone should be able to see the 'Show Me' post by lizzy. Logged out users and users not authorized should not see anything. Is that the most efficient way to go about it, or should it be:

private
private_id   post_id   authorized_user_id
    1           2               2
    2           2               5
    3           2               6

That's my first question. My second is what would be the best way to tackle it in php (display to authorized_user_ids) when pulling the data from a mysql db? Do we do a second query based on the 'private' flag in the posts table? If it is 1, we query in the while loop to get the 'authorized_user_ids' from the private table, and if it matches the $logged_in_id of the logged in user, then we display 'Dating'? Or should there be a join somewhere in the main query with the 'authorized _user_ids' using the 'post_id' in the private table and the posts table? Any help on how to most efficiently do this is appreciated.

score 2 · Accepted Answer · answered Jan 22 '13 at 00:11

First Q: I would definitely go with the second version. Storing authorized_user_id as a CSV will cause both DB read and write operations to become much less efficient as you will end up splitting the CSV values all the time in your application code and generating bad dynamic SQL statements in order to process updates. You should never have to write PHP code to help with finding the intersection of different sets of data like that, use SQL.

Second Q: Use joins, no while loops for example

SELECT DISTINCT p.post_id, p.post_name FROM users as u
JOIN posts as p on p.user_id=u.user_id 
LEFT JOIN private pv on pv.post_id=p.post_id
WHERE (p.private=0) OR  (authorized_user_id={$logged_in_id} and p.private=1)

The above (or very similar) will list all posts $logged_in_id is 2 but only public ones when $logged_in_id is 4 etc.

In terms of efficient indexing, private_id as primary index, and post_id as index, or post_id AND authorized_user_id as composite index? Thanks for the quick response btw. — Maverick, Jan 22 '13 at 00:37
I think composite index on post_id and authorized_user_id would work but if unsure run the queries from the command line using EXPLAIN to see how the indexes you add are being used effectively. — pipe-devnull, Jan 22 '13 at 07:28

MySQL - Private Posts Display

1 Answers1