Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods.

Here is my observation:

  • If I keep both then I have to update both when receive a request from a friend.
  • If I don't keep both, then I found it difficult when having to do multiple JOIN with this table.

Currently, I keep the relationship one way.

enter image description here

So what should I do in this case? Any advice?

share|improve this question
    
Are you committed to a platform, or is this a theoretical question? –  Nick Chammas Jan 5 '12 at 16:15
    
What about a hybrid approach: model requited and unrequited friendships respectively in separate tables, ensure a friendship is inserted into exactly one of those tables, not nice to achieve using today's SQL products :( –  onedaywhen Jan 5 '12 at 16:33
    
@onedaywhen - Yeah, sounds more appropriate for a graph database. –  Nick Chammas Jan 5 '12 at 18:13
    
@NickChammas: It's not theoretical question. I'm working on mysql which is stored in Amazon cloud. –  Chan Jan 5 '12 at 18:24
1  
@Chan - Ah that means you can't use check constraints to enforce the relationship is only stored one way then (MySQL does not enforce these) –  Martin Smith Jan 5 '12 at 18:28
show 1 more comment

4 Answers 4

up vote 9 down vote accepted

I would store AB and BA. A friendship is really a two-way relationship, each entity is linked to another. Even though intuitively we think of the "friendship" as one link between two people, from a relational point of view it is more like "A has a friend B" and "B has a friend A". Two relationships, two records.

share|improve this answer
1  
Many thanks. I really need to think about your idea carefully! The reason that I avoid storing AB and BA is because of the storage, since each time I have a friendship, my table would store twice as much. –  Chan Jan 5 '12 at 18:31
    
You are right about the storage, but remember that if stored as integers, each friend-friend relationship would take around 30 byes (2 records x 3 columns x 4 bytes per integer = 24 bytes plus some padding). 1 million people with 10 friends each would still be only around 300MB of data. –  datagod Jan 5 '12 at 19:48
1  
datagod: that's right! –  Chan Jan 6 '12 at 2:32
    
This is how I designed my tables as well, AB & BA. –  kabuto178 Feb 6 '13 at 13:14
2  
Plus, in situations where there is only AB and not BA, this can represent a 'pending friend request'. –  Greg May 13 at 13:31
add comment

If friendship is intended to be symmetrical (i.e. it is not possible for A to be friends with B but not vice-versa) then I would just store the one way relationship with a check constraint ensuring that each relationship can only be represented one way.

Also I would ditch the surrogate id and have a composite PK instead (and possibly a composite unique index also on the reversed columns).

CREATE TABLE Friends
  (
     UserID1 INT NOT NULL REFERENCES Users(UserID),
     UserID2 INT NOT NULL REFERENCES Users(UserID),
     CONSTRAINT CheckOneWay CHECK (UserID1 < UserID2),
     CONSTRAINT PK_Friends_UserID1_UserID2 PRIMARY KEY (UserID1, UserID2),
     CONSTRAINT UQ_Friends_UserID2_UserID1 UNIQUE (UserID2, UserID1)
  ) 

You don't say the queries that this makes difficult but you can always create a View

CREATE VIEW Foo
AS
SELECT UserID1,UserID2 
FROM Friends
UNION ALL
SELECT UserID2,UserID1 
FROM Friends
share|improve this answer
add comment

You may need to define indexes around friendships instead of doubling the number of rows:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE friendship
(
    friend_of INT NOT NULL,
    friend_to INT NOT NULL,
    PRIMARY KEY (friend_of,friend_to),
    UNIQUE KEY friend_to (friend_to,friend_of)
);

This way, you double the storage for indexes but not for the table data. As a result, this should be a 25% savings on diskspace. The MySQL Query Optimizer will choose perform index range scans only, which is why the concept of covering indexes works well here.

Here are some nice links on Covering Indexes:

CAVEAT

If friendship is not mutual, you have the basis for another type of relationship : FOLLOWER

If friend_to is not a friend of friend_of, you can simply leave that relationship out of the table.

If you want to define relationships for all types, whether they are mutual or not, you could probably use the following table layout:

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE relationship
(
    rel_id INT NOT NULL AUTO_INCREMENT,
    person_id1 INT NOT NULL,
    person_id2 INT NOT NULL,
    reltype_id TINYINT,
    PRIMARY KEY (rel_id),
    UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2),
    UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
    KEY has_relationship_to (person1_id,reltype_id),
    KEY has_relationship_by (person2_id,reltype_id)
);
CREATE TABLE relation
(
    reltype_id TINYINT NOT NULL AUTO_INCREMENT,
    rel_name VARCHAR(20),
    PRIMARY KEY (reltype_id),
    UNIQUE KEY (rel_name)
);
INSERT INTO relation (relation_name) VALUES
('friend'),('follower'),('foe'),
('forgotabout'),('forsaken'),('fixed');

From the relation table, you could arrange the relationships to include the following:

  • Friends should be mutual
  • Foes could be mutual or not
  • Followers could be mutual or not
  • The other relationships would be subject to interpretation (by the forgotten or forsaken or the recipient of revenge (fixed))
  • Possibie relationships can be further extended

This should be more robust for all relationships, whether the relationship is mutual or not.

share|improve this answer
add comment

Assuming a "friendship" is always two-way/mutual, I'd probably handle it something like this.

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

CREATE TABLE friendship (
    friendship_id int IDENTITY(1,1) PRIMARY KEY,
    ...other columns, if any...
)

CREATE TABLE person_friendship (
    person_id int NOT NULL,
    friendship_id int NOT NULL
    PRIMARY KEY (person_id, friendship_id)
)

The result is that you change it from a many-to-many join from "person" to "person", to a many-to-many join from "person" to "friendship". This will simplify joins and constraints, but has the side effect of allowing more than two people in a single "friendship" (though maybe the additional flexibility would be a potential advantage).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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