Создание потоковой частной системы обмена сообщениями, такой как 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, а затем создает новое сообщение на основе этого.

Кроме того, для представления inbox я могу просто запросить все потоки на основе 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 обновляется до true UPDATE thread SET to_read = TRUE WHERE thread_id = 4.

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

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

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

Ответ 1

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

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

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

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

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

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

EDIT: Эскиз ERD:

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

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

EDIT 2: альтернативный ERD и запрос для самого последнего сообщения

@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 ниже:

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

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

Ответ 2

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