5

I have a MySQL table for Users with the primary key _id, and I want to represent friendships (visibility on friends lists of other users) as a table with pairs of userId foreign keys. I'm thinking something like:

CREATE TABLE UserFriendships (
  userIdA INT NOT NULL,
  userIdB INT NOT NULL,
  PRIMARY KEY (userIdA, userIdB),
  FOREIGN KEY (userIdA) REFERENCES Users(_id)
  FOREIGN KEY (userIdB) REFERENCES Users(_id)
)

It's my understanding that this will allow both (userIdA = 2, userIdB = 7) and (userIdA = 7, userIdB = 2) to be inserted as distinct rows. I want one row per friendship, that is to say one row per pair of userIds.

Does anyone know how I could improve this? Even if the above constraint is met, to get a list of all the friends of user foo I have to do a union, something like: SELECT userIdB AS friendUserId WHERE userIdA = foo UNION SELECT userIdA WHERE userIdB = foo. Is this the best way to accomplish that query, or should I think about changing my schema?

Tagir Valeev
  • 97,161
  • 19
  • 222
  • 334
Mike Turley
  • 1,172
  • 1
  • 9
  • 26
  • how do you deal with reciprocation? – Strawberry Mar 31 '13 at 18:16
  • 4
    Would be quite easy with any other DBMS, but with MySQL I can only think of a trigger solution to prevent this. –  Mar 31 '13 at 18:23
  • @Strawberry I hadn't thought of that, but I may include a seperate table of FriendRequests which, once reciprocated, result in an insert into UserFriendships. Although they would contain the same data, so that seems redundant doesn't it... – Mike Turley Mar 31 '13 at 20:13
  • @Strawberry I could also just add two more fields to UserFriendships, confirmedByUserA and confirmedByUserB. Sender of the friend request gets their confirmation flag set, and whichever one isn't set is the recipient. – Mike Turley Mar 31 '13 at 20:16
  • 1
    OK. Good that you're thinking about that. Next question "I want one row per friendship, that is to say one row per pair of userIds." Why? Why not two? That way I can 'unfriend' you (i.e. prohibit you from seeing my profile/updates) without you automatically 'unfriending' me. – Strawberry Apr 01 '13 at 10:57
  • Oh interesting... I hadn't thought of that, I was thinking of friends as an undirected graph rather than a directed one – Mike Turley Apr 02 '13 at 04:17

1 Answers1

5

You can use a TRIGGER BEFORE INSERT to enfore a business rule:

  • userIdA is always the user with the lower ID and userIdB always the user with the higher ID

This way both combinations (A,B) and (B,A) result in the same column order with the same primary key.

DELIMITER |
CREATE TRIGGER enforce_friendship_id_order BEFORE INSERT ON UserFriendships
  FOR EACH ROW BEGIN
    SET @lowerId := IF(NEW.userIdA < NEW.userIdB, NEW.userIdA, NEW.userIdB);
    SET @higherId := IF(NEW.userIdA > NEW.userIdB, NEW.userIdA, NEW.userIdB);
    SET NEW.userIdA = @lowerId;
    SET NEW.userIdB = @higherId;
  END;
|
DELIMITER ;
Kaii
  • 20,122
  • 3
  • 38
  • 60
  • Yikes. Upvote for a working solution, but I think I'd rather leave the rule unenforced and risk duplicates than mess with triggers. I suppose this is the kind of thing they're for, but it just doesn't feel clean, you know? – Mike Turley Mar 31 '13 at 20:18
  • 1
    well, yes this is one of the use cases triggers are for. i don't see how a change in your table schema could help you, that's why i posted this answer. you may enfore the same business rule in your application code, but i think it belongs to the database. MySQL isn't a real help here. – Kaii Apr 01 '13 at 02:52
  • @MikeTurley is "messing" with triggers so bad, really? – Kaii Apr 01 '13 at 02:53
  • Only because I'm not too experienced with advanced MySQL. I should just learn more and use them. :) – Mike Turley Apr 02 '13 at 04:20