Таблица отношений пользователей. Как правильно?

Question

[redkin]

Есть таблица пользователей, у всех пользователей есть user_id(например: 111, 222, 333, 444). Требуется добавить таблицу с друзей:
user_id(111) дружит с user_id(333) и user_id(444),
user_id(222) дружит с user_id(444)

Из нагугленного пришел к такой таблице:
user_id | friend_id
222 | 444
444 | 222

Сомневаюсь что так правильно, т.к. для каждой пары друзей создается две записи.
Как сделать лучше чем привел в примере?

Comments

[Армянское Радио]

А у вас отношение дружбы симметрично или не симметрично?

[redkin]

Да, на этом этапе симметрично. Пользователи дружат только если оба согласны.

[Иван Филатов]

redkin: а где вы храните запросы дружбы?

Answer 1

[Андрей Галкин]

Правильная таблица отношений:
Отмечу на всякий случай никаких id(самих записей) не должно быть! все выборки по ключам приведенным ниже
user_id|friend_id|relation_id|date(timestamp)
222|444|1|ts
444|222|2(не просто дружит а например встречается, женат, и т.п. для каждого случая свой уникальный id)|ts

первичный ключ составной (user_id,relation_id)
дополнительный ключ (friend_id, relation_id)

если кидают заявку создается 1 запись user_id>friend_id>0

проверка на поступившие заявки friend_id(current_user_id) | relation_id = 0

подтверждение: (должна быть транзакция!) то есть если отвалится один из запросов нужно вернуть все как было.
на MyISAM ручками(средствами PHP проверяем). На innodb делаем транзакцию средствами БД.
вписываем строчку с подтверждением курент_юзер > фриенд_ид > relid - 1 и обновляем основную заявку до единицы. Повторюсь если одна из команд не пройдет надо все откатить.

если отказ то удаляем основную заявку из базы.

по поводу дополнительных отношений:
кинуть заявку можно только пользователю у которого взаимно стоит 1 и проверка собственно на заявки
friend_id(current_user_id) | relation_id >(больше) 1.
Остальной алгоритм тот же. одновременно у 1 пользователя может быть только одна запись с отношением больше 1 (желательно, это как индикатор именно личного отношения, для группировки друзей лучше все же завести отдельное поле)
Описание с реальной базы данных на 30 миллионов пользователей.

Answer 2

[Yago]

Не совсем для каждой пары друзей создается 2 записи.

Создается одна запись на одно предложение дружбы. Она же может быть не взаимной (неподтвержденные предложения не означают дружбу).

Считаю схему таблицы правильной.

Comments

[redkin]

А если представить что предложения дружбы хранятся в другой таблице, а в этой таблице только лишь пары друзей. Которые обоюдно подтвердили согласие на дружбу. Тогда что скажете о таблице с такой схемой:
user_id_1 | user_id_2
111 | 222

[Игорь Воротнёв]

redkin: а зачем еще одну таблицу промежуточную и гонять данные туда-сюда, джоины лепить? Предложенная таблица - это отношение "многие к многим", полностью решает эту задачу. Есть, конечно, некоторые нюансы, но тут скорее надо исходить из конкретных требований.

[redkin]

Игорь Воротнёв: >но тут скорее надо исходить из конкретных требований.
Изначально вообще не было такого требования, меня вопрос выше натолкнул на эту мысль, что раз есть факт дружбы, значит было действие о предложении дружбы. У меня в голове четкое разделение есть, действия пользователей отдельно от их данных. Предложение дружбы действие, факт дружбы это данные о пользователе.

Это скорее учебный проект, поэтому и требования возникают по мере продвижения. Ну и естественно хочется понять как правильно.

[Игорь Воротнёв]

redkin: запрос на дружбу - это есть и то и другое одновременно. Это и факт дружбы (в одностороннем порядке), и действие - предложение другому пользователю подтвердить. Нюансы, о которых я упомянул - в какие стороны нужен поиск и фильтрация, группировка и т.д. Это как минимум. Где и как эти данные будут использоваться. Исходя из таких нюансов формируется архитектура. Возможно, стоит добавить в таблицу колонку-флаг "mutual" (взаимно или нет). Возможно хранить отдельными записями. Возможно, создавать объединенный индекс. Варианты есть, нужно смотреть по конкретной задаче.

Прелесть SQL-структур и абстракций данных в архитектуре как раз в том, что при правилььном планировании на начальном этапе в дальнейшем можно добавлять-удалять колонки, индексы, строить связи с другими таблицами, но при этом не ломать и не переколбашивать уже существующее. Простые relational таблицы - отличный пример подобных решений.

Answer 3

[AxisPod]

А по другому вменяемо никак и не сделать.

Comments

[redkin]

Есть вариант, из одной записи на пару друзей, но из за недостатка опыта у меня и в этом варианте сомнения:
user_id_1 | user_id_2
111 | 222

[AxisPod]

Можно, сделать одиночные индексы на каждое поле и вытаскивать через OR, но увы, не всегда вменяемо это может работать (в плане использования индексов).

[redkin]

>в плане использования индексов
Я не знаю что такое индексы.
Если я просто буду обращаться к такой таблице через OR, разве будет не достаточно? Например чтобы выбрать всех друзей пользователя, делю такой запрос "Достать все записи где юзер(user_id) найден в первой или второй колонке"

[Игорь Воротнёв]

redkin: индексы - это некие ключи для более быстрых выборок и сортировок. Учите, что это, без них никак. Если нет индексов - на каждый запрос будет делаться полное сканирование таблицы бд, всех строк. С индексами сложные выборки по одному или нескольким полям проводятся намного быстрее и эффективнее.

Answer 4

[Иван Филатов]

Лучше реализовать хранение друзей не через связи-ключи в другой таблице - а через хранение LIST (любой другой набор) в XML | JSON формате в самой записи.

пример
userid name friendlist requestfriendlist subscrybefriendlist
1111 Ivan {2222, 3333} {4444} {}
2222 Sergey {1111, 3333} {} {4444}
3333 Olga {1111, 2222} {4444} {}
4444 Max {} {2222} {3333, 1111}

в friendlist - хранить подтвержденных друзей.
в requestfriendlist - хранить запросы друзей.
в subscrybefriendlist - подписчиков и т.д.

в коде делать связи на основе списков.

Удобно сериализовать данные и удобно ими оперировать. Перебрасывать из одного списка в другой.

Comments

[redkin]

Я ошибаюсь? что в случае вами предложенного варианта все выборки из базы которые я буду делать в дальнейшем мне придется делать в php коде. Т.е. сначала выбрать все из таблицы, а после уже парсить в коде приложения полученные xml/json.
Если так то это менее удобно, чем отдельная таблица для пользователей и отдельная таблица для их отношений.

[Иван Филатов]

redkin: делать связи и JOIN запросы на БД еще хуже.

не знаю как там у PHP с парсингом-сериализацией, неужели все так плохо?

вам достаточно будет сделать запрос конкретного USER - профиль пользователя.
и затем сделать запрос множества USERs - друзья - c условием что userid = requestfriendlist конкретные значения.

1. запрос к БД очень легкий. вытащить кокретные записи по ID - индексы и всетакое.
2. легко управлять списком друзей - обращение к одной записи.

[Иван Филатов]

redkin: "Я не знаю что такое индексы. " - идите учите СУБД, прежде чем создавать свой Facebok или Vkonakte с блекджеком и куртизанками.

[FanatPHP]

"делать связи и JOIN запросы на БД еще хуже" - идите учите СУБД, прежде чем давать советы другим.

[Иван Филатов]

FanatPHP: habrahabr.ru/post/122210 - читай

[redkin]

Иван Филатов: >1. запрос к БД очень легкий. вытащить кокретные записи по ID - индексы и всетакое.
Зачем вообще запросы в БД, если вы предлагаете хранить в ней файлы(xml/json). Можно вообще отказаться от базы и все хранить в файлах и парсить их по мере надобности. Правильно развил ваш ответ?

>не знаю как там у PHP с парсингом-сериализацией, неужели все так плохо?
Зачем работать если можно не работать. Зачем перекладывать задачи для БД на код PHP.
Типа вы лучше это сделаете чем разрабы MySql?

[Иван Филатов]

redkin: что за х*** ты несешь?

1. я предлагаю не создавать дополнительную таблицу под хранилище пар ключей. и не делать дорогущие запросы через JOINы. Вы просто храните список внутри существующей таблицы. Вам останется только реализовать добавление в список, удаление из списка.

2. Умей отличать реальный проект или лабораторной работы в университете! В вашем проекте с 1 лямом юзеров вы будете хранить еще N лямов записей ключей. И это только функционал Друзья. А если захочется сделать функционал Подпизчики - еще куча ключей-записей. и т.д. За***тесь оптимизировать. + у вас не будет возможности сделать масштабирование своей БД.

В реальном проекте проще в логике приложения (код на ПэХаПе в вашем случае) сделать подгрузку данных и вывести в браузере. Чем ждать 100500 секунд когда база MySql вам отдаст JOIN JOIN JOIN.... запрос и при этом ваш сервер ну умрет.

[Иван Филатов]

А еще посмотрите, что такое NoSQL, in memory DB и key-value хранилище.

[redkin]

Иван Филатов:
>2. Умей отличать реальный проект или лабораторной работы в университете!
Вы то от куда знаете какие в универах лабораторные?)

[FanatPHP]

redkin: ты прав, а он нет. Видно что человек нахватался по верхам умных слов, но не понимает их смысла. Статья про джойны особенно доставляет - видно, что все свои представления о реляционных БД он почерпнул из этой статьи. В итоге, не имея реального опыта, он предполагает, что связь только однонаправленная - от юзера к френдам. Но если представить, что нам понадобится обратный запрос - по айди френда найти юзера, то получается тот самый фулл скан руками, о котором ты говорил. Делай нормальной таблицей многие-ко-многим.

[redkin]

Иван Филатов: Пример с друзьями из linkedin, когда можно отслеживать связь знакомы через кого-то как будет решаться в вашей схеме? И как в моей?
Если че это я не ради спора спрашиваю, просто не понимаю.

[Иван Филатов]

redkin: сделать связь знакомых так:

юзер А зашел на страницу юзера В и ему показали что у них есть общие друзьяшки.

1. вытаскиваете конкретного юзера B по ID - обычный select. Дешевая операция. Очень быстрая.
2. получаете список друзей юзера B, из поля. скидываете его в LIST или МАССИВ, программно.
3. делаете пересечение ПОЛЯ ДРУЗЕЙ текущего пользователя А со списком B - все что в результате - это конкретные ID их общих друзей.
4. делаете запрос к users таблице с условием этих ID = общим друзьям.
5. выводите общих друзей.

Все операции очень быстрые. И не надо строить супер запросы на БД, которые тяжелые и дорогие.

БД умеет много чего - но это не значит что нужно всю логику переложить в БД и ждать когда она выплюнет правильный ответ на ваш запрос. Она его выплюнет, но насколько быстро и затратно?

[FanatPHP]

redkin: для начала пусть напишет, как найти всех юзеров, у кторых во френдах находится данный юзер. Этот наш теоретик вместо ответа написал, как найти юзера по айди. Этим, видимо, и исчерпывается его опыт работы с БД - других запросов он не знает.

[Иван Филатов]

FanatPHP:

1. SELECT поля
FROM users
WHERE userid IN ({friendlist});

получаешь список всех друзей пользователя.

списки ведуться в обоих (у всех) юзеров.

[FanatPHP]

Непонятно, а что в {friendlist}

[Иван Филатов]

FanatPHP: usetid всех френдов юзера через запятую.

автору нужно будет только написать код для добавления/удаления userid в списках обоих пользователей между которыми идет связь.

[Иван Филатов]

FanatPHP: еще раз прокрути наверх до конца - там пример.

[FanatPHP]

И что найдет этот запрос? Всех пользователей, котоыре сами у себя в друзьях?

[Иван Филатов]

FanatPHP: ну почему ты такой тупой, скажи? friendlist это список других userid, которые в друзьях.

я уже на пальцах все объяснил.

[FanatPHP]

Я понимаю, что значит friendlist. Я не понимаю, что делает запрос, который ты написал. Даже если предположить, что он работает, то он ищет каждого юзера среди его же друзей. Я просил немного не это.

[Иван Филатов]

FanatPHP: данный запрос сделает "найти всех юзеров, у кторых во френдах находится данный юзер". что ты просил - то я и сделал.

формулируй тогда правильно.

[FanatPHP]

Ты нашел всех юзеров, которые во френдах сами у себя. но допустим, это опечатка, и ты подставляешь в условие не поле юзер айди, а искомый айди. В этом случае мы имеем фуллскан по таблице, поскольку каждое упаковнное значение надо сначала распаковать. Так что, дружочек, " идите учите СУБД, прежде чем рассказывать другим про индексы"

[Константин]

Считаю, что вечер пятницы удался

[Иван Филатов]

TheSpbra1n: ну просто человек не знает что такое "select from where in" а учит всех тут.

[FanatPHP]

Не удался. explain с поиском по индексу мы так и не увидели. what a pity