Дизайн схемы базы данных потоков сообщений

Ответ 1

Хорошо, почему бы вам просто не спросить?:)

Позвольте мне попытаться определить мое понимание вашего требования. Мне кажется, что вы смотрите на поток, являющийся линейным списком (а не деревом) сообщений между двумя людьми. Я бы подумал, что вы можете позволить больше людей, чем просто двум. Это будет похоже на Facebook, поскольку кто-то отправляет сообщение, а затем любое число людей может его прочитать, а затем начать добавлять комментарии. Когда вы добавляете комментарий, он помещает вас в поток, и вы начинаете получать обновления статуса и сообщения электронной почты о активности в потоке и т.д. Предполагая, что это то, что вам нужно, тогда схема, которую я предложил Big Mike, не совсем то, что вы ищете.

Рассмотрим вместо этого следующее:

Идея здесь в том, что каждый раз, когда пользователь начинает новый поток/сообщение, он начинается с новой записи в таблице THREAD. Затем пользователь добавляется как THREAD_PARTICIPANT, а содержимое сообщения добавляется к MESSAGE, которое указывает на содержащую THREAD. FK от MESSAGE к USER указывает автора сообщения.

Когда пользователь читает сообщение, он получает запись в таблице MESSAGE_READ_STATE, чтобы указать, что они отметили сообщение, явно или неявно, в зависимости от того, как ваши требования идут.

Когда кто-то комментирует начальное сообщение в потоке, второе сообщение MESSAGE добавляется с FK обратно к исходной THREAD, и автор ответов (пользователь) добавляется в таблицу THREAD_PARTICIPANT. И так происходит, когда сообщения добавляются в поток одним, двумя или даже более участниками.

Чтобы получить последнее сообщение в любом потоке, просто возьмите верхнюю часть 1 из СООБЩЕНИЯ, отсортированную по убыванию, на дату создания (или ключ идентификации), где сообщение FK относится к интересующей теме.

Чтобы получить последний обновленный поток для пользователя, получите THREAD, относящийся к первому 1 из отсортированного по убыванию сообщения, в дату создания, где сообщение находится в потоке, в котором пользователь является THREAD_PARTICIPANT.

Боюсь, я никогда не смогу описать эти вещи в LINQ, не вырвав LinqPad. Если у вас возникли проблемы с улавливанием моего отклонения от вышеизложенного, я мог бы найти ответ с определениями таблиц и некоторым SQL. Просто спросите в комментариях.

РЕДАКТ: Разъяснение требований и реализации

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

Для некоторой ясности поставьте несколько строк в таблицах. Вот сценарий (в честь Дня Канады): User 1 DMs User 2, чтобы спросить о встрече за пивом. Пользователь 2 отвечает вопросом о том, где встретиться и ответам пользователя 1. Таблицы выглядели бы примерно так: (возможно, упрощенное)

РЕДАКТИРОВАТЬ # 2: получить доступ к SQL для списка всех сообщений в потоке с состоянием чтения...

Используя схему @OP, этот SQL получит список сообщений в данном потоке с указанием того, прочитал ли данный пользователь каждое сообщение или нет. Сообщения находятся в самом последнем первом порядке.

SELECT 
  Message.MessageId
, Message.CreateDate
, Message.Body
, Login.Username
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     and MessageReadState.LoginId = 2) as ReadState
FROM (Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId) 
WHERE (((Message.MessageThreadId)=10))
ORDER BY Message.CreateDate DESC;

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

EDIT 3: SQL для получения всех потоков с последним сообщением в каждом для данного пользователя...

Чтобы получить список всех потоков, в которых участвовал данный пользователь, сначала отсортировано по самому последнему сообщению, причем отображается только самое последнее сообщение (1 сообщение на поток), тогда вы будете использовать аналогичный запрос к один выше, за исключением того, что вместо фильтрации сообщений по их FK в интересующий поток вы фильтруете сообщения подзапросом, который находит последнее сообщение в каждом потоке, в котором участвует пользователь, представляющий интерес. Он будет выглядеть следующим образом:

SELECT
  Message.MessageId
, Message.CreateDate
, Message.Body
, Login.Username
, (SELECT MessageReadState.ReadDate 
   FROM MessageReadState 
   WHERE MessageReadState.MessageId = Message.MessageId 
     and MessageReadState.LoginId = 2) AS ReadState
FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
WHERE ( Message.MessageId in 
        ( SELECT Max(Message.MessageId)
          FROM MessageThreadParticipant INNER JOIN Message 
            ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
          WHERE MessageThreadParticipant.LoginId=2
          GROUP BY MessageThreadParticipant.MessageThreadId
        )
      )
ORDER BY Message.CreateDate DESC;

Ответ 2

В соответствии с Joel Brown'answer вы можете добавить столбец LAST_MESSAGE_ID в таблицу THREAD, а затем получить все потоки с последними сообщениями SQL стало очень простым. Вы должны обновить этот столбец при каждом отправке сообщения.

Получение всех потоков с последним сообщением в каждом для данного пользователя

SELECT *
FROM THREAD T
INNER JOIN MESSAGE M ON T.LAST_MESSAGE_ID=M.MESSAGE_ID
INNER JOIN USER SENDER ON M.USER_ID=SENDER.USER_ID
LEFT JOIN MessageReadState MRS ON M.MESSAGE_ID=MRS.MESSAGE_ID AND MRS.USER_ID=2