MySQL structure :: Friendship

Question

Many say that I should create a friendship table (userid , friendid).

My question is:

Will there be a double entry for each friendship ? , eg:
12 - friend with 5
+
5 - friend with 12

Is there any way to prevent this ?

| OR |

Should I just ignore this one ?

Answer 1

There are at least two approaches you could use:

• Make sure userid always less than friendid .
• Keep each ratio in both directions (i.e. keep both a, b, and b, a).

The second approach costs twice as much storage and is redundant, but this means that some queries can be much simpler and will work better. For example, consider selecting all of the common friends of user "a" and "b" with each approach:

Approach 1:

 SELECT friendid FROM ( SELECT friendid FROM friendships WHERE userid = 'a' UNION SELECT userid FROM friendships WHERE friendid = 'a' ) T1 JOIN ( SELECT friendid FROM friendships WHERE userid = 'b' UNION SELECT userid FROM friendships WHERE friendid = 'b' ) T2 ON T1.friendid = T2.friendid 

Approach 2:

 SELECT T1.friendid FROM friendships T1 JOIN friendships T2 ON T2.userid = 'b' AND T1.friendid = T2.friendid WHERE T1.userid = 'a' 

Answer 2

I would ask: Is it possible for someone to be friends with themselves? What “friendship” means is bidirectional or unidirectional. If bidirectional, then one entry will do, right? If unidirectional, then two entries are needed. If someone cannot be friends with himself, then you implement the business rule on the data entry page (liek on the web page, this will be useful for javascript), where friend1 id <> friend2 id Not sure I agree with " less "- they simply cannot be equal

Answer 3

There is no context, the question is too isolated, and the answers are very limited. The flow is incomplete.

First, for understanding, the table of friends presented does not exist by itself. It is a child of the User table, which has a UserId PK. Both UserId and FriendId are FKs for UserId.

Since friendship is a two-way street or bidirectional (we exclude non-reciprocal lovers and stalkers here!), One row identifies the relationship in both directions.

There are serious consequences in the name of any column, so I would avoid (UserId, FriendId), which implies a hierarchy or one-way relationship, which is false. I suggest (Friend1, Friend2), and of course you need PK ((Friend1, Friend2).

What was not identified, we still did not prevent duplication ([5,12] and [12, 5]), that is, the very redundancy that you are trying to prevent. We need a rule to ensure that Friend1 is always smaller than Friend2.

There are other relationships, such as Bill Materials or the family tree, in which there are Assemblies and Copies that require something more. For example (AssemblyId, ComponentId) or (ParentID, ChildId), where this rule does not apply: a part may be a Component in the assembly, as well as the Network itself. This requires two lines. There are still no duplicates.