Mysql – Database structures for similar profiles

database-designMySQL

Please bare me if my question is too blunt

I am working on a social network where I need to show similar profiles to users

Every profiles has 40 parameters to match with other profiles.

I want to fetch results in a manner that users with maximum similarity with current logged-in user i.e if user has exact same values to other users then they must be shown at top.

After that users with lesser similarities should be shown i.e 39 similarities

I am planning to use 2 tables

[user][table] ->  id, name, email, password, username etc

[user_values][table] -> It will have 40 columns for all 40 parameters and user_id

Can this be done using single query ?

So we will have 40 values of current login user. Whenever they reach a matches page we should be able to hit a query which will fetch users in order to descending match of columns.

I can change database design/structure if needed.

Best Answer

I have limited to 10 user_values for this example. Replace the two instances of "1" with the user_id of the user that you are matching profiles to.

SELECT
    user_id,
    @score :=
        (user_values.value1 = cur_user_values.value1) +
        (user_values.value2 = cur_user_values.value2) +
        (user_values.value3 = cur_user_values.value3) +
        (user_values.value4 = cur_user_values.value4) +
        (user_values.value5 = cur_user_values.value5) +
        (user_values.value6 = cur_user_values.value6) +
        (user_values.value7 = cur_user_values.value7) +
        (user_values.value8 = cur_user_values.value8) +
        (user_values.value9 = cur_user_values.value9) +
        (user_values.value10 = cur_user_values.value10)
        AS score
FROM
    user_values JOIN (
        SELECT
            value1,
            value2,
            value3,
            value4,
            value5,
            value6,
            value7,
            value8,
            value9,
            value10
        FROM
            user_values
        WHERE
            user_values.user_id = 1
    ) cur_user_values
WHERE
    user_id != 1
ORDER BY
    score DESC;

This results in a response like so (I was testing with only 5 users):

enter image description here

In my case, score will be a number between 0 and 10. In your case it will be a number between 0 and 40.

Related Question