What is the best design for this use case regarding bidirectional relationship?

by Kristi Jorgji   Last Updated July 12, 2019 17:05 PM

For this question let's consider a system in which the users can match with each other (let's say for simplicity exactly like the dating apps).

User1 matches User2 (and the other way around) only in one of the following cases 1. they both like each other 2. one of them special likes the other one and the other one just likes 3. both of them special like each other

The match will be stored in a SQL database, and when the users log in the app they need to fetch their matches, also the information if they were special liked, or special liked, or both.

The design question here is regarding the match entity storage and concept, whether it should be considered as two entities or one.

The first approach is as follows:

create table matches
    id                     char(36)                            not null,
    first_user_id          int unsigned                        not null,
    second_user_id         int unsigned                        not null,
    first_user_superliked  tinyint(1)                          not null,
    second_user_superliked tinyint(1)                          not null,
    created_at             timestamp default CURRENT_TIMESTAMP not null
    PRIMARY KEY (id)

The id will be an unique uuid, so one match will have only this one entity for both users. The fetch query will have to query both first_user_id and second_user_id fields. Example, user with id 4 logs in and need to see his matches, query will be like:

SELECT * FROM matches WHERE first_user_id = 4 OR second_user_id = 4

The second approach requires two rows for one match, each obviously will have different ids. The structure:

create table matches
    id                     char(36)                            not null,
    user_id                int unsigned                        not null,
    matchee_id             int unsigned                        not null,
    did_superlike          tinyint(1)                          not null,
    was_superliked         tinyint(1)                          not null,
    created_at             timestamp default CURRENT_TIMESTAMP not null
    PRIMARY KEY (id)

With this approach if we want to get the matches of user with id 4, we have to query only based on the field user_id If someone unmatches the other user, we have to delete both rows (or disable via some flag)

As software engineers, what do you think is the best approach for this scenarios ?

I provided a match example, but my question is in general for bidirectional relationships of this type as I see them quite often and would like other opinions in order to improve myself

Related Questions

Persisting dynamic form data

Updated December 20, 2017 12:05 PM

Design for syncing data from remote API

Updated September 23, 2017 19:05 PM

Designing hotel room persistence layer

Updated June 23, 2017 12:05 PM