How to structure database for Tinder functionality [closed]

Question

I'm building a functionality similar to Tinder. People can 'like' or 'skip' photo's of someone else, if both people 'like' each other then there is a match.

What is the best approach of a database structure for this functionality? I want to be able to get a list of all matches and all matches per Person.

Approach 1:

Person | JudgedPerson | Like
------ | ------------ | ----
1      | 2            | yes
2      | 1            | yes
1      | 3            | yes
3      | 1            | no
2      | 3            | yes

This looks like a logical approach, but it is difficult to create a MySql query to discover matches. Or is there a simple way to discover it?

Approach 2

Person1 | Person2 | P1LikesP2 | P2LikesP1
------- | ------- | --------- | ---------
1       | 2       | yes       | yes
1       | 3       | yes       | no
2       | 3       | yes       | null

It's easy to create queries to get matches, but the datamodel might be not the best.

What is the best approach? If approach 1 is the best approach, what mysql queries can I use to discover the matches?

Answer 1

I don't have a formal reason for why I prefer the first option, but it is clear that the second option is not completely normalized.

To query the first table and find pairs of people who like each other, you can try the following self join:

SELECT DISTINCT LEAST(t1.Person, t1.JudgedPerson) AS Person1,
                GREATEST(t1.Person, t1.JudgedPerson) AS Person2
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.JudgedPerson = t2.Person AND
       t1.Person       = t2.JudgedPerson
WHERE t1.Like = 'yes' AND
      t2.Like = 'yes'

Note: I added DISTINCT along with LEAST/GREATEST to the SELECT clause because each match will actually come in the form of a duplicate. The reason for this is that, e.g. 1 -> 2, 2 -> 1 would be one matching record, but also 2 -> 1, 1 -> 2 would also be a second record.