SQL - get records from many-to-many relations by the user itself -OR- his group

Question

I have two database tables, one as the main table and the other as the relation table. The first table is a table of contents and the second table is a table that connects to users or groups. Some data may also be modified in this second table.

I'm not sure about the structure and performance.

for example, we have User Id 160 which is under group id 7

So for the first, we have a post Table.

id  |  title  |  content    |   cover  | status
------------------------------------------------
1   | first   |  content 1  | /img/... |   1
2   | second  |  content 2  | /img/... |   1
3   | another |  content 3  | /img/... |   1
4   | four    |  content 4  | /img/... |   1
5   | five    |  content 5  | /img/... |   1

and for the second we have a post_rel Table:

id  | group_id | user_id | post_id |  title   |   cover  | sort | status
---------------------------------------------------------------------------
1   | 7        | NULL    | 1       | g title  | img/...  | 1    | 1
2   | NULL     | 160     | 1       | u title  | NULL     | 2    | 1     ***  selected for user_id
3   | 7        | NULL    | 2       | NULL     | img/...  | 6    | 0
4   | NULL     | 160     | 2       | NULL     | img/...  | 4    | 1     ***  selected for user_id
5   | NULL     | 160     | 3       | some     | img/...  | 3    | 1     ***  selected for user_id
6   | 7        | NULL    | 4       | NULL     | img/...  | 9    | 1     ***  selected for group_id
7   | NULL     | 165     | 5       | NULL     | img/...  | 5    | 0

This is the basic query we have.

select 
    `post_rel`.`title` as `custom_title`, 
    `post_rel`.`cover` as `custom_cover`, 
    `post_rel`.`group_id`, 
    `post_rel`.`user_id`, 
    `post`.* 
from 
    `post` 
        inner join `post_rel` on `post`.`id` = `post_rel`.`post_id` 
        where 
            `post`.`status` = 1 
            and `post_rel`.`status` = 1 
            and ( 
                `post_rel`.`user_id` = 160 
                or ( 
                    `post_rel`.`group_id` = 7 
                    and `post_rel`.`post_id` not in (
                            select 
                                `post_rel`.`post_id` 
                            from 
                                `post_rel` 
                            where 
                                `post_rel`.`user_id` = 160
                    )
                )
            )
order by 
    `post_rel`.`sort` asc

So, what you think about the basic query? Especially in the subquery, won't performance drop in a large table? Is it possible to write a better and simpler query or change the structure?

Edit: this is sqlfiddle example of my code and structure http://sqlfiddle.com/#!9/ed9d4b/1

Answer 1

I would change it to use "not exists" instead of "not in" and would use aliases so I could pull it off like so:

select 
    b.`title` as `custom_title`, 
    b.`cover` as `custom_cover`, 
    b.`group_id`, 
    b.`user_id`, 
    a.* 
from 
    `post` a
        inner join `post_rel` b on a.`id` = b.`post_id` 
        where 
            a.`status` = 1 
            and b.`status` = 1 
            and ( 
                b.`user_id` = 160 
                or ( 
                    b.`group_id` = 7 
                    and not exists (
                            select 
                                'x' 
                            from 
                                `post_rel` c
                            where 
                                c.`user_id` = 160 and c.`post_id`=b.`post_id`
                    )
                )
            )
order by 
    b.`sort` asc

Typically when managing users and group, there's this notion of an exception user who directly can get assigned to assets just like the whole group. This seems to be an example of that.

From a modeling-only perspective, there are 2 ways to deal with that:

1. Ensure that every user exists in a group and that you only assign assets to groups. For the exception user, create a group. You could even enforce that every user belongs to only one group. This way your post_rel table deals with only groups. Unfortunately, the relationship between group and user is not understood well enough to weigh in appropriately.

2. Driven only by the need to eliminate null values towards a good model which also reduces overhead, the other option is to use name value pairs and allows the User and Group to exist in the same field with another field besides it, denoting Group or User. These are the SQL Fiddle:

NOT EXISTS version: http://sqlfiddle.com/#!9/1af8cf/2

NOT IN version: http://sqlfiddle.com/#!9/1af8cf/1

Some reading on nulls https://dev.mysql.com/doc/refman/5.6/en/data-size.html

Specifically:

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.