To go one record per two users and avoid consuming extra memory that the proposed methods suggest (twice as much as needed, since there are two records for each user), you can do the following:
Table structure:
USER_RELATIONSHIP {
user_first_id,
user_second_id,
type
primary key(user_first_id, user_second_id)
}
Ensure: user_first_id < user_second_id
The most interesting part - type
: for all of the possible states of a relationship, you create the corresponding values. For exmaple:
pending_first_second
pending_second_first
friends
block_first_second
block_second_first
block_both
What you have:
- The
user_first_id < user_second_id
ensures that there is only one
record of a relationship between two given users, since this and primary key constraints
won't allow placing it otherwise.
- By proper switching between the relationship states, you determine how each of two users relate to each other. If there is no relationship between two users, there is no record.
To find out the relationship between two users (as well as update), you simply go by a single query:
select * from USER_RELATIONSHIP where
user_first_id = user1_id and
user_second_id = user2_id;
without an or
statement that would check this columns vice versa, which is faster.
Example scenario:
no record
: are not in a relationship
pending_first_second
: the first made a friend request to the second
friends
: the second approved the friend request
no record
: one of the users removed the other from his firends
This solution is efficient in terms of both memory and speed, because you create, store and update only one single record.