Jak zaprojektować tabelę bazy danych relacji do przechowywania relacji przyjaźni?

Chcę zaprojektować stół do przechowywania relacji przyjaźni w moim projekcie internetowym

Powinien spełniać przynajmniej 4 następujące warunki:

kto wysyła prośbę o dodanie znajomego, np. (jeśli A TO B to ta kolumna będzie A)

którzy otrzymają prośbę o dodanie znajomego, np. (jeśli A TO B to ta kolumna będzie B)

aktualny status np. (0 oznacza odrzucone, a 1 oznacza zaakceptowane lub 2 oznacza nieprzetworzone

nasz związek partnerski jest dwustronny

Jeśli ktoś z was ma takie doświadczenie, wszelkie sugestie są mile widziane

mój obecny projekt (obecnie wydaje mi się zły) jest taki, to są kolumny

frienshipId  
fromUserId  
toUserId  
status  
requestTime

Stworzyłbym stół podobny do tego, który masz. Używam typów danych i składni programu SQL Server, może być konieczne dostosowanie w zależności od platformy.

CREATE TABLE FriendStatus
(FriendStatusId BIGINT PRIMARY KEY IDENTITY(1,1),
FromUserId BIGINT,
ToUserId BIGINT,
StatusId TINYINT,
SentTime DATETIME2,
ResponseTime DATETIME2);

Indeksowanie stołu będzie miało kluczowe znaczenie, gdy stół wzrośnie do dziesiątek i setek milionów.

Na PostgreSQL:

CREATE TABLE users (
    users_id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);

CREATE TABLE friends (
    friends_id serial PRIMARY KEY,
    timestamp TIMESTAMPTZ default now(),
    user_a integer NOT NULL REFERENCES users,
    user_b integer NOT NULL REFERENCES users,
    status integer NOT NULL default 2
)

Aby wyświetlić listę przyjaźni, zobacz:

CREATE VIEW friendships AS
    SELECT DISTINCT user_a, user_b FROM friends WHERE status = 1
    UNION
    SELECT DISTINCT user_b, user_a FROM friends WHERE status = 1;

Możesz go używać w następujący sposób:

INSERT INTO users ( name ) VALUES ( 'foo' );
INSERT INTO users ( name ) VALUES ( 'bar' );
INSERT INTO users ( name ) VALUES ( 'baz' );

SELECT * FROM users;
 users_id | name 
----------+------
        1 | foo
        2 | bar
        3 | baz

INSERT INTO FRIENDS ( user_a, user_b, status ) VALUES ( 1, 2, 1 );
INSERT INTO FRIENDS ( user_a, user_b, status ) VALUES ( 2, 1, 1 );
INSERT INTO FRIENDS ( user_a, user_b, status ) VALUES ( 1, 3, 1 );

SELECT * FROM friendships ORDER BY user_a, user_b;
 user_a | user_b 
--------+--------
      1 |      2
      1 |      3
      2 |      1
      3 |      1

SELECT a.name, b.name
    FROM friendships
    JOIN users a ON a.users_id = user_a
    JOIN users b ON b.users_id = user_b
    ORDER BY a.name, b.name;
 name | name 
------+------
 bar  | foo
 baz  | foo
 foo  | bar
 foo  | baz

Co sprawia, że ​​uważasz, że twój obecny projekt jest zły? Oto tabela tworzenia dla Oracle:

CREATE TABLE IVR.FRIEND (
     FRIENDID   NUMBER(7) NOT NULL 
   , FROMUSERID NUMBER(7) NOT NULL 
   , TOUSERID   NUMBER(7) NOT NULL 
   , STATUSID   NUMBER(2) NOT NULL
   , REQUESTED  DATE      NOT NULL 
   , CONSTRAINT FRIEND_PK PRIMARY KEY (FRIENDID) ENABLE 
);
CREATE SEQUENCE FRIENDIDSEQ;

Jeśli bazą danych jest Oracle, możesz rozważyć zaindeksowanie wirtualnej kolumny, która ograniczy dane do wpisów potrzebnych dla poszczególnych zapytań. Na przykład możesz mieć wirtualną kolumnę o nazwie AcceptedFromUserId, która korzysta z funkcji DECODE (StatusId, 1, FromUserId, NULL). Indeks zawierałby tylko AcceptedUserIds, a zatem byłby mniejszy niż indeks dla wszystkich UserID. Jeśli regularnie czyścisz odrzucone żądania, indeksowana wirtualna kolumna na PendingToUserId może być bardziej przydatna.

Alternatywą dla partycjonowania byłoby podzielenie tabeli na StatusId.

Jeśli nie potrzebujesz wielu zaproszeń do znajomych między tymi samymi użytkownikami w tym samym czasie, możesz porzucić FriendId, używając FromUserId, ToUserId i StatusId jako klucza podstawowego. W takim przypadku należy również rozważyć uczynienie tabeli tabelą zorganizowaną według indeksu.

Schemat:

CREATE TABLE users (
    users_id serial PRIMARY KEY,
    name text UNIQUE NOT NULL
);

CREATE TABLE friends (
    friends_id serial PRIMARY KEY,
    timestamp TIMESTAMPTZ default now(),
    user_a integer NOT NULL REFERENCES users,
    user_b integer NOT NULL REFERENCES users,
    status integer NOT NULL default 2
)

Z PHP:

select * 
from friends 
where user_a=$myid or user_b=$myid