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