rid field as a PRIMARY KEY in the user_relationships table? [#2106611]

Hi there!

I wanted to know if there was a specific reason about the fact that the rid field is not set as a PRIMARY KEY in the user_relationships table.

What I want to do is to archive multiple relationships between a requester and a requestee.
In order to accomplish this, I am using a dedicated rtid.
However, the existing PRIMARY KEY (requester_id, requestee_id, rtid) prevents me to do so.
This is why I want to add the rtid field as a PRIMARY KEY: to bypass this restriction and to have duplicates of (requester_id, requestee_id, rtid).

Thanks!

Comments

Comment #1

Berdir

German

Switzerland

CreditAttribution: Berdir commented 7 October 2013 at 15:16

Relationship types that go in both direction (reciprocal is it called IIRC) create two relationships with the same rid.

Comment #2

StephaneSeng CreditAttribution: StephaneSeng commented 8 October 2013 at 14:02

Thanks for your very quick reply!

Just to clarify things up:
From what I have understand and using your reply, there are 3 relation types.
Let R(A, B) be an oriented relation between A and B.

The relations are either :

two-way or mutual (The default relationship type):
- Bidirectional (create two rows in the user_relationships table for easier database queries using the same rid)
- Eg: When R(A, B) is created, R(B, A) is also created at the same time, with the same rid
one-way (The checkbox "This is a one-way relationship" is checked):
- Unidirectional and cannot be reciprocal between two requester/requestees
- Eg: After R(A, B) has been created, R(B, A) cannot be realized
one-way and reciprocal (The checkbox "This one-way relationship can be reciprocated" is also checked):
- Unidirectional and can be reciprocal between two requester/requestees
- Eg: After R(A, B) has been created, R(B, A) can be created, with a different rid

So the rid is shared for the mutual relations and not for the reciprocal ones.

PS:
For my very specific need of archiving relations, adding the rid field in the PRIMARY KEY set is still feasible.
It reduces the set of non working cases but the existing cases of today will still work, even if the rid field is duplicated.