Blacklist for list for each user

Just go for it. You will learn if it won't work. There is no right or wrong!

I have a field in almost all of the non-reference tables, userID. This contains the id's value from the User table. There are other ways to accomplish this, but I have used this approach for years with many different apps/platforms, and it has

BTW, I also have an additional index (unique) on the non-reference tables (lists, list_user), the table's userID & id fields. This allows for the query to use the index when qualifying on the User's ID.

So, in your case, you could add a field in list_user (userID which is the User's id value for the users table). This same value would also be stored in list_user_blacklist.

As for the list_user and list_user_blacklist. What if you were to put all the lists in list_user, adding a field such as blacklist. This cuts down on the number of tables for maintenance and joins.

Okay, I don't know if I get it right. I tried it on my way:

users

lists

list_member # with use pivot table named MemberList

• id
• user_id
• list_id

list_member_blacklist

• id
• user_id (is the user who want to blacklist a member)
• list_member_id

My User model contains this relation:

public function membersBlacklisted()
{
    return $this->belongsToMany(MemberList::class, 'list_member_blacklist', 'user_id', 'list_member_id')
        ->withTimestamps();
}

It works to get the users like User::find(1)->membersBlacklisted()->byList(1)->get() but there must be an mistake because if I try to attach a member, it fails.

SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: insert into `list_member_blacklist` (`created_at`, `list_member_id`, `updated_at`, `user_id`, `0`, `1`) values (2020-11-29 20:32:16, 12, 2020-11-29 20:32:16, 1, user_id, 1))