Список друзей: дизайн таблиц реляционной базы данных

Это отношение «многие ко многим», для этого требуется таблица отношений.

create table Person (
   person_id int not null primary key,
   username varchar(100) not null,
   ... other_cols ...
)


create table Buddy (
   person_id1 int not null,
   person_id2 int not null,
   primary key (person_id1, person_id2),
   foreign key (person_id1) reference Person (person_id),
   foreign key (person_id2) reference Person (person_id)
)

Таким образом, таблица Person, очевидно, будет содержать 1 строку для каждого человека. Он будет содержать любые данные о приятелях, поскольку это сделает его денормализованным. Вместо этого таблица друзей будет содержать отношения между людьми.

Допустим, у вас есть что-то вроде этого в таблице Person:

person_id    username
1            George
2            Henry
3            Jody
4            Cara

Генри и Кара — приятели, как и Джордж и Кара, так что:

person_id1   person_id2
2            4
1            4

Если вам нужно, чтобы отношения не были неявно взаимными, вам нужно будет добавить дополнительные строки, чтобы сделать это явным. Итак, теперь давайте предположим, что Генри считает Кару другом, и Кара также думает о Генри как о друге, в то время как Джордж считает Кару другом, но Кара не отвечает Джорджу взаимностью:

person_id1   person_id2
2            4
4            2
1            4

Отсутствие 4 1 указывает на то, что Кара не считает Джорджа другом. Это сохраняет чистоту и позволяет избежать аномалий данных. Вы можете настроить отношения, не искажая данные о человеке. Также вы можете определить правило удаления каскадов для внешних ключей, чтобы удаление Person автоматически удаляло все связанные отношения для вас. И наоборот, вы можете захотеть предотвратить это, вместо этого вы можете указать ограничение (по умолчанию) для внешних ключей, которое предотвратит удаление человека с определенными отношениями.

Запросы тоже просты:

Сколько приятелей у Кары (допустим, отношения для списков приятелей неявны):

select count(*) from Person 
                join Buddy on person_id = person_id1 or person_id = person_id2
 where name = 'Cara'

В случае, когда отношения не подразумеваются, может быть лучше вместо этого переименовать столбцы следующим образом:

person_id   considers_as_buddy_id
2           4
4           2
1           4
4           3

select count(*) from Person P
                join Buddy B on P.person_id = B.person_id
 where name = 'Cara'

Это возвращает количество людей, которых Кара считает друзьями. В данном случае 2. Пока Джоди не думает о Каре как о приятеле, поэтому, чтобы выяснить взаимоотношения, вы бы сделали следующее:

select count(*) from Person P
                join Buddy B on P.person_id = B.person_id and 
                                B.considers_as_buddy_id = P.person_id
 where name = 'Cara'

Здесь есть несколько возможных сценариев.

Во-первых, вам нужно определить, могут ли дружеские отношения быть односторонними. Может ли человек А быть приятелем человека Б, если Б не является приятелем А? Я бы предположил, что нет, но стоит быть явным.

Здравый смысл подсказывает, что вы запрашиваете больше, чем обновляете отношения. Таким образом, оптимизируйте свои данные для этого. При создании дружеских отношений создайте две записи: от A до B и от B до A. Это сделает запрос тривиальным. Так что да, используйте две записи, как вы уже подумали.