Создание потоковой частной системы обмена сообщениями, такой как facebook и gmail

Я создаю поточную систему сообщений, подобную gmail и facebook, где в папке «Входящие» перечислены самые последние потоки, отображающие тему, имя отправителя и отметку времени последнего сообщения.

Вот как мои таблицы настроены:

users: user_id user_name thread: thread_id title to_id to_keep to_read from_id from_keep date message: message_id thread_id to_id from_id message_text date

То, что я делаю прямо сейчас, – это когда пользователь создает новое сообщение, создает новый поток в таблице потоков, а затем новое сообщение в таблице сообщений, и если пользователь отвечает на поток, он дублирует текущий поток в таблица потока, за исключением того, что она меняет местами to_id и from_id а затем создает на ней новое сообщение.

Кроме того, для представления «Входящие» я могу просто запросить все потоки на основе user_id . поэтому что-то вроде SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC или если я хочу просматривать сообщения в outbox, это будет что-то вроде SELECT * FROM thread WHERE from_id = 2 and from_keep = TRUE ORDER BY date DESC ,

Если пользователь открывает поток при появлении нового сообщения, то to_read обновляется до истинного UPDATE thread SET to_read = TRUE WHERE thread_id = 4 .

Я чувствую, что я слишком усложняю этот процесс и что должен быть лучший способ сделать это.

Любая помощь или идеи будут оценены.

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

Почему вы не отделяете отношения сообщений от представления пользователя каждого сообщения?

Я бы сделал поток с помощью ссылки для ссылки на сообщение. Другими словами, сообщение имеет столбец «replying_to_message_id».

Я не уверен, что понимаю, почему у вас есть «to_id». Являются ли сообщения адресованы отдельным пользователям? Это кажется очень ограниченным. Я бы подумал, что у вас либо не будет получателя (т. Е. Получатель – это доска сообщений, которую может прочитать любой пользователь), либо у вас будет возможность указывать несколько получателей, как с помощью электронной почты. Возможно, вы можете больше объяснить, как использовать систему.

Предполагая (для простоты), что вы отправляете на доску, поэтому важна только «от», тогда у вас есть таблица сообщений с привязкой к привязке для потоковой передачи, таблица пользователя, а затем таблица пересечений между пользователем и сообщением который хранит сообщения, которые были прочитаны каждым пользователем.

Таким образом, если вы хотите узнать, прочитал ли пользователь сообщение или нет, просто попробуйте прочитать идентификатор пользователя в таблице пересечений для данного сообщения. Если его нет, то это сообщение непрочитано этим пользователем.

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

EDIT: ERD Sketch:

Вот краткий обзор того, о чем я говорю …

ERD Sketch

Независимо от того, выбрал ли отправитель для сохранения сообщения, помечено в самом сообщении. Если сообщение является началом нового потока, столбец reply_to_message_id равен NULL, иначе это message_id родительского сообщения. Могут быть многолюдные получатели, у каждого из которых есть своя способность сохранять сообщение или нет, а также возможность отслеживать дату и время, когда получатель читает сообщение.

EDIT 2: Alternate ERD и Querying для самого последнего сообщения

@OP спросила, как запросить последнее сообщение в потоке. Ответ зависит от формы потока. Вы можете либо иметь плоскую нить, где каждое сообщение доходит до конца линейного потока сообщений, либо вы можете иметь древовидный поток, где каждое сообщение имеет определенный родительский элемент, если только он не является корнем потока. В приведенном выше ERD поле reply_to_message_id может использоваться в любом случае. Если нить плоская, то FK всегда находится в корневом СООБЩЕНИИ. Если поток имеет древовидную структуру, то FK является непосредственным родителем ответа MESSAGE.

Если типичный запрос, который вы хотите запустить, «какое последнее сообщение в потоке?» и ваши потоки плоские, тогда вы можете использовать SQL следующим образом:

 select top 1 M.message_id , M.sent_datetime , M.title , M.message_text , S.user_id , S.user_name -- and anything else you want... from MESSAGE M inner join USER S on M.sender_user_id = U.user_id where M.reply_to_message_id = @ThreadRootMessageID order by M.sent_datetime desc

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

Дерево Thread ERD

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

Если вы используете этот ERD, запрос для «самого последнего сообщения в потоке X» будет таким же, как и выше, но с M.thread_root_message_id в предложении where вместо M.reply_to_message_id.

Ваш подход может быть самым интуитивным и простым, но если у вас много потоков, вы получите проблемы с производительностью. Я предлагаю вам взглянуть на « модель вложенного набора », которая намного быстрее (особенно, если вы устанавливаете путь к узлу или уровень / глубина каждого узла).