SQL - схема базы данных чата для удаления разговора для одной стороны или для обеих сторон
Я создаю базу данных чата со следующими требованиями:
- Только личные сообщения, от А до Б. Нет групп, ни комнаты.
- Когда пользователь A отправляет сообщение B, если пользователь A удаляет диалог, пользователь B по-прежнему сможет просматривать диалог до тех пор, пока пользователь B не удалит его.
- Сообщения не будут стираться по отдельности. Только в состоянии удалить всю историю разговора.
И теперь у меня есть это:
- Когда пользователь A отправит сообщение пользователю B, будет создан один регистр сообщений с идентификатором. Это будет внешний ключ к таблице разговоров.
- В таблице разговоров для одного и того же идентификатора сообщения будут созданы два регистра. Один для пользователя, который отправляет сообщение, а другой для пользователя, который получает сообщение. Каждый регистр имеет поле с именем in-out, где указывается, было ли сообщение отправлено или получено. Пример:
/*
conversation_table messages_table
+--------------------------------------------+ +----------------------------------------+
| user_id | participant_id | in-out | msg_id | | msg_id | body |
+--------------------------------------------+ +----------------------------------------+
| A | B | 0 | 101 | | 101 | Hello B, what's up |
| B | A | 1 | 101 | | 102 | Hey A, here in stackru |
| B | A | 0 | 102 | | 103 | That's nice B, and what's new |
| A | B | 1 | 102 | +----------------------------------------+
| A | B | 0 | 103 |
| B | A | 1 | 103 |
+--------------------------------------------+
Chat windows
+-----------------------------------------+
| User A |
+-----------------------------------------+
| Sent: Hello B, what's up |
| Received: Hey A, here in stackru |
| Sent: That's nice B, and what's new |
+-----------------------------------------+
+-----------------------------------------+
| User B |
+-----------------------------------------+
| Received: Hello B, what's up |
| Sent: Hey A, here in stackru |
| Received: That's nice B, and what's new |
+-----------------------------------------+
*/В этом случае. Я могу выделить для каждого отдельного пользователя его полную историю чата, а затем выполнить фильтрацию с нужным участником.
И отделить отправку сообщения от полученных сообщений легко с помощью in-out var. например, если сообщение получено (0), поместите его с левой стороны, или если сообщение было отправлено, то поместите его с правой стороны.
SQL для получения сообщений для пользователя A, общающегося с пользователем B:
SELECT * FROM conversation_table C INNER JOIN messages_table M ON (C.msg_id=M.msg_id) WHERE C.user_id=A AND C.participant=B
И чтобы вставить сообщения от пользователя A пользователю B:
INSERT INTO messages_table (msg_id, body) VALUES (101, 'Hello B, what's up')
INSERT INTO conversation_table (user_id, participant_id, in-out, msg_id) VALUES
(A, B, 0, 101) #messages get out from user A to User B
(B, A, 1, 101) #message comes in to user B from user A
Чтобы удалить историю сообщений для пользователя A, в чате с пользователем B:
Сначала проверьте, не удалил ли пользователь Б свой разговор. Если удалить, то сообщения будут удалены из таблицы сообщений. в противном случае нет.
DELETE FROM conversation_table WHERE user_id=A AND participant_id=B
Это удалит весь разговор между пользователем A и B в учетной записи пользователя A. Пользователь Б имеет свою собственную копию сообщений.
Таблица сообщений будет иметь метаданные, такие как:
- Отметка времени (текущие милисекунды UTC) для получения даты и времени визуализации
Ну, все работает здесь, но теперь некоторые вопросы:
- Является ли представленный дизайн хорошей моделью для работы с тысячами пользователей? Я имею в виду, сохраняя для каждого пользователя свои входящие и исходящие сообщения.
- Что насчет идентификатора сообщения? Я думаю в UUID с 32 символами. Желательно? (рекомендуемые). Я имею в виду, что если сообщение содержит только тело со словом "привет", потребуется уникальный идентификатор из 32 символов, и я думаю, что это будет ненужным или нет?
- Можете ли вы помочь мне направить меня в этом дизайне?
Спасибо.
5 ответов
Эта модель данных должна подойти для вашей задачи. Для Сессий следует установить «Каскадное удаление».
УДАЛИТЬ ИЗ СЕССОВ, ГДЕ DeleteFrom = 1 И DeleteTo = 1
Он удалит все сеансы и сообщения, удаленные обоими пользователями. Когда пользователь удаляет сеанс SET DeleteFrom / To = 1.
Идентификатор IDENTITY = Автоинкремент. Order By Id обеспечивает порядок сообщений.
Добавьте «From BIT» = 1, если сообщение от UserIdFrom, = 0, если - от UserIdTo.
Я думаю, что хорошо в реляционной базе данных. Большинство людей следуют подобной архитектуре
Учитывая, что вы используете первичные ключи последовательного автоинкремента mysql для идентификаторов
Создайте столбец, удаленный 1 и удаленный 2 (удаленный одним пользователем 1, который имеет более низкий номер идентификатора пользователя, и удаленный 2 с более высоким номером).
Затем при извлечении сообщений получайте сообщения только после удаленного идентификатора для этого пользователя на основе значения столбца пользователей, являющегося идентификатором сообщения, которое было удалено последним.
Вы также можете использовать метки времени для хранения точки удаления, но это создает проблемы с часовыми поясами и различными фреймворками, такими как mysql, django, laravel или express, имеющими свои собственные проблемы с датой и временем.
Я не буду предлагать использовать UUID или NEWID() (используемые в SQL Server) для хранения каждого сообщения, как вы предложили ранее, для каждого сообщения будет сгенерирован идентификатор из 32 символов. Например, если у вас 1000 пользователей, и каждый пользователь отправляет около 10 сообщений в день, общий сгенерированный UID будет 10000, теперь вы можете выполнять свои вычисления для каждой недели, месяца и года. На самом деле данных будет гораздо больше, поэтому я предлагаю вам использовать комбинацию двух значений в качестве уникального идентификатора. UserID и MessageID. Отметьте их как 15-значное числовое значение или как вам кажется правильным и объедините их, чтобы идентифицировать сообщения каждого пользователя.
Пример случая:
UserID: 1000001MessageId: 1000001 Сообщение: 'Hello'
UserID: 1000002MessageId: 1000001 Сообщение: 'Hi'
Дайте мне знать, что вы думаете!!
Я согласен с вашей логикой по настройке таблицы и последующим запросам. Однако я думаю, что есть несколько вещей, которые следует отметить в отношении UUID, особенно когда они используются в качестве первичных ключей. Я нашел еще один пост, связанный с хранилищем и производительностью, здесь и статью здесь . Похоже, BigInt может быть хорошим вариантом для msg_id.