What is the most efficient method of keeping track of each user's "blocked users" in a MySQL Database?

Question

What is the most efficient method of managing blocked users for each user so they don't appear in search results on a PHP/MySQL-run site?

This is the way I am currently doing it and I have a feeling this is not the most efficient way:

Create a BLOB for each user on their main user table that gets updated with the unique User ID's of each user they block. So if User ID's 313, 563, and 732 are blocked by a user, their BLOB simply contains "313,563,732". Then, whenever a search result is queried for that user, I include the BLOB contents like so "AND UserID NOT IN (313,563,732)" so that the blocked User ID's don't show up for that user. When a user "unblocks" someone, I remove that User ID from their BLOB.

Is there a better way of doing this (I'm sure there is!)? If so, why is it better and what are the pros and cons of your suggestion?

Thanks, I appreciate it!

Answer 1

You are saving relationships in a relational database in a way that it does not understand. You will not have the benefit of foreign keys etc.

My recommended way to do this would be to have a seperate table for the blocked users:

create table user_blocked_users (user_id int, blocked_user_id);

Then when you want to filter the search result, you can simply do it with a subquery:

select * from user u where ?searcherId not in (select b.blocked_user_id from user_blocked_users where b.user_id = u.id)

You may want to start out that way, and then optimize it with queries, caches or other things if neccessary - but do it last. First, do a consistent and correct data model that you can work with.

Some of the pros of this approach:

1. You will have a correct data model of your block relations
   • With foreign keys, you will keep your data model consistent

The cons of this approach:

1. In your case, none that I can see

The cons of your approach:

1. It will be slow and not scalable, as blobs are searched binarily and not indexed
2. Your data model will be hard to maintain and you will not have the benefit of foreign keys