Ответ 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.