-1

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

closed as too broad by Doc Brown, BobDalgleish, Robert Harvey Jul 12 at 19:19

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    You did not give any clear criteria how you define "best", which makes your question prone to be closed as "too broad". See explanation here. – Doc Brown Jul 12 at 17:08
  • That is the answer I am looking for, if I knew the definition of best that would mean I also knew what approach was better. I am looking here for you guys to say what you considered better of these two approaches and why, that way I can get another point of view and learn. Normally the answer should have clear arguments, like for example solution a needs less storage space, but you might have more code to write etc etc – Kristi Jorgji Jul 12 at 17:29
  • I think what @DocBrown is saying here is that there is no way to determine what is "best" without knowing for what we are optimizing. That said, I would look closely at these options... Your two tables share the same schema. How can it be that you are storing the exact same information for each approach yet they require a different number of rows in order to glean the same information? – king-side-slide Jul 12 at 17:52
1

You're dealing with a symmetric relation.

There are a number of ways for modeling symmetric relationships, and here's a third approach for you adapted from here.

CREATE TABLE people (
    person_id int IDENTITY(1,1) PRIMARY KEY,
    ...other columns...
)

CREATE TABLE matches (
    match_id int IDENTITY(1,1) PRIMARY KEY,
    ...other columns, if any...
        ...such as the date of the match...
)

CREATE TABLE people_matches (
    person_id int NOT NULL REFERENCES people(person_id)
    match_id int NOT NULL REFERENCES matches(match_id)
    superliked tinyint(1) NOT NULL,
    PRIMARY KEY (person_id, match_id)
)

What I like about this solution is that since it maximizes normalization, it makes the queries simpler — in the sense that there is only one column to name for certain things: for example, there is only one superliked column, not two.

On the downside, it adds a join to certain queries compared with the other approaches.  And it also means that if you have a constraint that a match is only between two people, you'll have to enforce externally.

  • Thank you for this nice idea. This is the reason why I asked here to get other ideas. This one seems good to me for keeping the design clean, and only one id for the match. I accept this answer – Kristi Jorgji Jul 14 at 10:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.