Como devo projetar uma tabela de relacionamento para amizade?

Eu armazenaria AB e BA. Uma amizade é realmente uma relação de mão dupla, cada entidade está ligada a outra. Embora intuitivamente pensemos na "amizade" como um elo entre duas pessoas, do ponto de vista relacional é mais como "A tem um amigo B" e "B tem um amigo A". Dois relacionamentos, dois registros.

Se a amizade pretende ser simétrica (ou seja, não é possível A ser amigo, Bmas não vice-versa), então eu apenas armazenaria o relacionamento unidirecional com uma restrição de verificação, garantindo que cada relacionamento só possa ser representado de uma maneira.

Além disso, eu abandonaria o id substituto e teria um PK composto (e possivelmente um índice exclusivo composto também nas colunas invertidas).

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)
  ) 

Você não diz as consultas que isso dificulta, mas sempre pode criar uma View

CREATE VIEW Foo
AS
SELECT UserID1,UserID2 
FROM Friends
UNION ALL
SELECT UserID2,UserID1 
FROM Friends

Assumindo que uma "amizade" é sempre de mão dupla/mútua, eu provavelmente lidaria com algo assim.

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)
)

O resultado é que você muda de uma junção muitos para muitos de "pessoa" para "pessoa", para uma junção muitos para muitos de "pessoa" para "amizade". Isso simplificará junções e restrições, mas tem o efeito colateral de permitir mais de duas pessoas em uma única "amizade" (embora talvez a flexibilidade adicional seja uma vantagem potencial).

Você pode precisar definir índices em torno de amizades em vez de dobrar o número de linhas:

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)
);

Dessa forma, você dobra o armazenamento para índices, mas não para os dados da tabela. Como resultado, isso deve representar uma economia de 25% no espaço em disco. O MySQL Query Optimizer escolherá executar apenas varreduras de intervalo de índice, e é por isso que o conceito de cobertura de índices funciona bem aqui.

Aqui estão alguns links interessantes sobre Índices de Cobertura:

• http://peter-zaitsev.livejournal.com/6949.html
• http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
• http://ronaldbradford.com/blog/tag/covering-index/

EMBARGO

Se a amizade não é mútua, você tem a base para outro tipo de relacionamento : SEGUIDOR

Se friend_to não for amigo de friend_of, você pode simplesmente deixar esse relacionamento fora da mesa.

Se você quiser definir relacionamentos para todos os tipos, sejam eles mútuos ou não, provavelmente poderá usar o seguinte layout de tabela:

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');

Na tabela de relações, você pode organizar as relações para incluir o seguinte:

• Os amigos devem ser mútuos
• Os inimigos podem ser mútuos ou não
• Os seguidores podem ser mútuos ou não
• As outras relações estariam sujeitas a interpretação (pelo esquecido ou abandonado ou o destinatário da vingança (fixo))
• Possíveis relacionamentos podem ser estendidos ainda mais

Isso deve ser mais robusto para todos os relacionamentos, sejam eles mútuos ou não.

Se você pode controlar dentro do aplicativo que o id de A é sempre menor que o id de B (pré-encomendar os ids dos elementos A,B) você pode aproveitar de pedir sem um OR (selecione onde id_A = a AND id_B = b, em vez de perguntar (id_A = a AND id_B = b) OR (id_A = b AND id_B = a) ), e também manter metade dos registros necessários com as outras aproximações. Em seguida, você deve usar outro campo para manter o estado do relacionamento ( are-friends, a-solicited-to-b, b-solicited-to-a, exfriends-a, exfriends-b ), e pronto.

Esta é a maneira que eu gerenciei meu sistema de amizade, e isso simplifica o sistema e usa metade das linhas que você precisa com outros sistemas, apenas dizendo que A é igual ao valor de id mais baixo no código.