CODEWITHSUNDEEP
mysql
PleaseHelpMe
PleaseHelpMe

Reputation: 739

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

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!

Upvotes: 3

Views: 467

Answers (4)

Piotr
Piotr

Reputation: 4963

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

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

You are looking for a second table joined in a many-to-many relationship. Check this post:
Many-to-Many Relationships in MySQL

The "Pros" are numerous. You are handling your data with referential integrity, which has incalculable benefits down the road. The issue you described will be followed by others in your application, and some of those others will be more unmanageable than this one.

The "Cons" are that

  1. You will have have to learn how referential data works (but that's ahead anyway, as I say)
  2. You will have more tables to deal with (ditto)
  3. You will have to learn more about CRUD, which is difficult ... but, just part of the package.

Upvotes: 1

code_burgar
code_burgar

Reputation: 12323

What you are currently using is not regarded as a good practice for relational database design, however, like with anything else, there are cases when that approach can be justified, albeit restrictive in terms of what you can accomplish.

What you could do is, like J V suggested, create a cross reference table that contains mappings of user relationships. This allows you to, among other things, skip unnecessary queries, make use of table indexes and possibly most importantly, it gives you far greater flexibility in the future.

For instance, you can add a field to the table that indicates the type/status of the relationship (ie. blocked, friend, pending approval etc.) which would allow a much more complex system to be developed easily.

Upvotes: 0

J V
J V

Reputation: 11946

You are looking for a cross reference table.

You have a table containing user IDs and "Blocked" user IDs, then you SELECT blockid FROM blocked WHERE uid=$user and you have a list of user ids that are blocked, which you can filter through a where clause such as WHERE uid NOT IN(SELECT blockid FROM blocked WHERE uid=$user)

Now you can block multiple users per user, and the other way round, with all the speed of an actual database.

Upvotes: 1

Related Questions