Есть таблица пользователей, у всех пользователей есть 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
Сомневаюсь что так правильно, т.к. для каждой пары друзей создается две записи.
Как сделать лучше чем привел в примере?
Правильная таблица отношений:
Отмечу на всякий случай никаких 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 миллионов пользователей.
А если представить что предложения дружбы хранятся в другой таблице, а в этой таблице только лишь пары друзей. Которые обоюдно подтвердили согласие на дружбу. Тогда что скажете о таблице с такой схемой:
user_id_1 | user_id_2
111 | 222
redkin: а зачем еще одну таблицу промежуточную и гонять данные туда-сюда, джоины лепить? Предложенная таблица - это отношение "многие к многим", полностью решает эту задачу. Есть, конечно, некоторые нюансы, но тут скорее надо исходить из конкретных требований.
Игорь Воротнёв: >но тут скорее надо исходить из конкретных требований.
Изначально вообще не было такого требования, меня вопрос выше натолкнул на эту мысль, что раз есть факт дружбы, значит было действие о предложении дружбы. У меня в голове четкое разделение есть, действия пользователей отдельно от их данных. Предложение дружбы действие, факт дружбы это данные о пользователе.
Это скорее учебный проект, поэтому и требования возникают по мере продвижения. Ну и естественно хочется понять как правильно.
redkin: запрос на дружбу - это есть и то и другое одновременно. Это и факт дружбы (в одностороннем порядке), и действие - предложение другому пользователю подтвердить. Нюансы, о которых я упомянул - в какие стороны нужен поиск и фильтрация, группировка и т.д. Это как минимум. Где и как эти данные будут использоваться. Исходя из таких нюансов формируется архитектура. Возможно, стоит добавить в таблицу колонку-флаг "mutual" (взаимно или нет). Возможно хранить отдельными записями. Возможно, создавать объединенный индекс. Варианты есть, нужно смотреть по конкретной задаче.
Прелесть SQL-структур и абстракций данных в архитектуре как раз в том, что при правилььном планировании на начальном этапе в дальнейшем можно добавлять-удалять колонки, индексы, строить связи с другими таблицами, но при этом не ломать и не переколбашивать уже существующее. Простые relational таблицы - отличный пример подобных решений.
Можно, сделать одиночные индексы на каждое поле и вытаскивать через OR, но увы, не всегда вменяемо это может работать (в плане использования индексов).
>в плане использования индексов
Я не знаю что такое индексы.
Если я просто буду обращаться к такой таблице через OR, разве будет не достаточно? Например чтобы выбрать всех друзей пользователя, делю такой запрос "Достать все записи где юзер(user_id) найден в первой или второй колонке"
redkin: индексы - это некие ключи для более быстрых выборок и сортировок. Учите, что это, без них никак. Если нет индексов - на каждый запрос будет делаться полное сканирование таблицы бд, всех строк. С индексами сложные выборки по одному или нескольким полям проводятся намного быстрее и эффективнее.
Лучше реализовать хранение друзей не через связи-ключи в другой таблице - а через хранение 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 - подписчиков и т.д.
в коде делать связи на основе списков.
Удобно сериализовать данные и удобно ими оперировать. Перебрасывать из одного списка в другой.
Я ошибаюсь? что в случае вами предложенного варианта все выборки из базы которые я буду делать в дальнейшем мне придется делать в php коде. Т.е. сначала выбрать все из таблицы, а после уже парсить в коде приложения полученные xml/json.
Если так то это менее удобно, чем отдельная таблица для пользователей и отдельная таблица для их отношений.
redkin: делать связи и JOIN запросы на БД еще хуже.
не знаю как там у PHP с парсингом-сериализацией, неужели все так плохо?
вам достаточно будет сделать запрос конкретного USER - профиль пользователя.
и затем сделать запрос множества USERs - друзья - c условием что userid = requestfriendlist конкретные значения.
1. запрос к БД очень легкий. вытащить кокретные записи по ID - индексы и всетакое.
2. легко управлять списком друзей - обращение к одной записи.
Иван Филатов: >1. запрос к БД очень легкий. вытащить кокретные записи по ID - индексы и всетакое.
Зачем вообще запросы в БД, если вы предлагаете хранить в ней файлы(xml/json). Можно вообще отказаться от базы и все хранить в файлах и парсить их по мере надобности. Правильно развил ваш ответ?
>не знаю как там у PHP с парсингом-сериализацией, неужели все так плохо?
Зачем работать если можно не работать. Зачем перекладывать задачи для БД на код PHP.
Типа вы лучше это сделаете чем разрабы MySql?
1. я предлагаю не создавать дополнительную таблицу под хранилище пар ключей. и не делать дорогущие запросы через JOINы. Вы просто храните список внутри существующей таблицы. Вам останется только реализовать добавление в список, удаление из списка.
2. Умей отличать реальный проект или лабораторной работы в университете! В вашем проекте с 1 лямом юзеров вы будете хранить еще N лямов записей ключей. И это только функционал Друзья. А если захочется сделать функционал Подпизчики - еще куча ключей-записей. и т.д. За***тесь оптимизировать. + у вас не будет возможности сделать масштабирование своей БД.
В реальном проекте проще в логике приложения (код на ПэХаПе в вашем случае) сделать подгрузку данных и вывести в браузере. Чем ждать 100500 секунд когда база MySql вам отдаст JOIN JOIN JOIN.... запрос и при этом ваш сервер ну умрет.
redkin: ты прав, а он нет. Видно что человек нахватался по верхам умных слов, но не понимает их смысла. Статья про джойны особенно доставляет - видно, что все свои представления о реляционных БД он почерпнул из этой статьи. В итоге, не имея реального опыта, он предполагает, что связь только однонаправленная - от юзера к френдам. Но если представить, что нам понадобится обратный запрос - по айди френда найти юзера, то получается тот самый фулл скан руками, о котором ты говорил. Делай нормальной таблицей многие-ко-многим.
Иван Филатов: Пример с друзьями из linkedin, когда можно отслеживать связь знакомы через кого-то как будет решаться в вашей схеме? И как в моей?
Если че это я не ради спора спрашиваю, просто не понимаю.
юзер А зашел на страницу юзера В и ему показали что у них есть общие друзьяшки.
1. вытаскиваете конкретного юзера B по ID - обычный select. Дешевая операция. Очень быстрая.
2. получаете список друзей юзера B, из поля. скидываете его в LIST или МАССИВ, программно.
3. делаете пересечение ПОЛЯ ДРУЗЕЙ текущего пользователя А со списком B - все что в результате - это конкретные ID их общих друзей.
4. делаете запрос к users таблице с условием этих ID = общим друзьям.
5. выводите общих друзей.
Все операции очень быстрые. И не надо строить супер запросы на БД, которые тяжелые и дорогие.
БД умеет много чего - но это не значит что нужно всю логику переложить в БД и ждать когда она выплюнет правильный ответ на ваш запрос. Она его выплюнет, но насколько быстро и затратно?
redkin: для начала пусть напишет, как найти всех юзеров, у кторых во френдах находится данный юзер. Этот наш теоретик вместо ответа написал, как найти юзера по айди. Этим, видимо, и исчерпывается его опыт работы с БД - других запросов он не знает.
Я понимаю, что значит friendlist. Я не понимаю, что делает запрос, который ты написал. Даже если предположить, что он работает, то он ищет каждого юзера среди его же друзей. Я просил немного не это.
Ты нашел всех юзеров, которые во френдах сами у себя. но допустим, это опечатка, и ты подставляешь в условие не поле юзер айди, а искомый айди. В этом случае мы имеем фуллскан по таблице, поскольку каждое упаковнное значение надо сначала распаковать. Так что, дружочек, " идите учите СУБД, прежде чем рассказывать другим про индексы"
