diseño de esquema de base de datos del sistema de mensajería de subproceso

Estoy tratando de lograr exactamente lo que se explica aquí: crear un sistema de mensajes privado con hebras como Facebook y Gmail , sin embargo, no entiendo completamente la respuesta de Joel Brown. ¿alguien puede explicarme?

Así es como se ven mis tablas db con datos de muestra (supongo que la rellené correctamente para fines de demostración): enter image description here

  1. Necesito mostrar una lista de hilos basados ​​en LoginId (el más nuevo en la parte superior) ¿cómo se vería la consulta en LINQ? (Lo que estoy preguntando es en un grupo de hilos de mensajes, dame el 1 mensaje más nuevo en cada hilo) – al igual que esto se hace en Facebook.

  2. Necesito mostrar TODOS los mensajes en una secuencia de mensajes (LINQ) -> al igual que en Facebook, donde hace clic en el mensaje y verá toda la “conversación” en una banda de rodadura.

¡Por favor ayuda! Gracias

EDITAR -> continuación Joel, ¿es esto correcto?

enter image description here

Joel, estoy un poco confundido, ¿puedes explicarlo? (Comentarios / preguntas en negrita):

La idea aquí es que cada vez que un usuario inicia un nuevo hilo / mensaje, comienza con un nuevo registro en la tabla THREAD. El usuario se agrega como THREAD_PARTICIPANT y el contenido del mensaje se agrega a MESSAGE, que apunta al THREAD que lo contiene. El FK de MENSAJE a USUARIO indica el autor del mensaje.

LoginId 1 envía un mensaje a LoginId2 => nuevo registro se inserta en la tabla MessageThread. También se inserta un registro en el registro MessageThreadParticipant con MessageThreadId = 1, LoginId = 1 (el remitente). Y se inserta un nuevo registro en la tabla de mensajes con MessageId = 1, MessageThreadid = 1, SenderLoginId = 1 (¿correcto?)

esto es lo que tengo después de esa iteración: enter image description here

Creo que estoy confundido porque no hay forma de que Loginid 2 sepa que hay un mensaje para él. ?? O tal vez necesito insertar 2 registros en MessageThreadParticipant ?? (el emisor y el receptor) -> de esta manera ambos pueden ver toda la “conversación” ??

EDIT2: Joe, creo que podría hacer esto:

SELECT Message.MessageId, Message.CreateDate, Message.Body, Login.Username, Message.SenderLoginId , (SELECT MessageReadState.ReadDate FROM MessageReadState WHERE MessageReadState.MessageId = Message.MessageId ) as ReadDate FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId INNER JOIN MessageThreadParticipant mtp on mtp.MessageThreadId = Message.MessageThreadId AND ( Message.MessageId in ( SELECT Max(Message.MessageId) FROM MessageThreadParticipant INNER JOIN Message ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId GROUP BY MessageThreadParticipant.MessageThreadId ) ) Where mtp.LoginId = 2 ORDER BY Message.CreateDate DESC; 

Por favor corrígeme si estoy equivocado 🙂

Bueno, ¿por qué no lo preguntas? 🙂

Permítanme tratar de precisar mi comprensión de su requerimiento. Me parece que estás viendo un hilo siendo una lista lineal (no un árbol) de mensajes entre dos personas. Pensaría que es posible que desee permitir más personas en más de dos. Eso sería como Facebook en la medida en que alguien publica un mensaje y luego cualquier cantidad de personas puede leerlo y luego comenzar a agregar comentarios. Cuando agrega un comentario, lo inserta en el hilo y comienza a recibir actualizaciones de estado y correos electrónicos que le informan sobre la actividad en el hilo y demás. Suponiendo que eso es lo que buscas, entonces el esquema que sugerí a Big Mike no es exactamente lo que estás buscando.

Considere en su lugar lo siguiente:

Esquema

La idea aquí es que cada vez que un usuario inicia un nuevo hilo / mensaje, comienza con un nuevo registro en la tabla THREAD. El usuario se agrega como THREAD_PARTICIPANT y el contenido del mensaje se agrega a MESSAGE, que apunta al THREAD que lo contiene. El FK de MENSAJE a USUARIO indica el autor del mensaje.

Cuando un usuario lee un mensaje, obtiene una entrada en la tabla MESSAGE_READ_STATE para indicar que marcó el mensaje leído, ya sea explícita o implícitamente, según cómo vayan sus necesidades.

Cuando alguien comenta el mensaje inicial en el hilo, se agrega un segundo MENSAJE con un FK de vuelta al HILO original y el autor de la respuesta (usuario) se agrega a la tabla THREAD_PARTICIPANT. Y así sucede cuando los mensajes son agregados al hilo por uno, dos o incluso más participantes.

Para obtener el mensaje más reciente en cualquier hilo, simplemente tome el top 1 de MESSAGE ordenado descendiendo en create date (o una clave de identidad) donde el mensaje FK es para el hilo de interés.

Para obtener el subproceso actualizado más recientemente para un usuario, obtenga el subproceso relacionado con el top 1 del mensaje ordenado descendente en la fecha de creación donde el mensaje está en un subproceso en el que el usuario es un THREAD_PARTICIPANT.

Me temo que nunca puedo decir estas cosas en LINQ sin romper LinqPad. Si tiene problemas para captar mi deriva de lo anterior, podría completar la respuesta con definiciones de tablas y algunos SQL. Solo pregunta en los comentarios.

EDIT: aclaración de los requisitos y la implementación

Aclaración de los requisitos: Inicialmente estaba pensando en los mensajes publicados públicamente con la oportunidad de hacer comentarios, mientras que Shane busca más la función de mensaje directo. En cuyo caso, el destinatario inicial debe incluirse en la tabla THREAD_PARTICIPANT desde el principio.

Para mayor claridad, pongamos algunas filas en tablas. Aquí está el escenario, (en honor al Día de Canadá): Usuario 1 DMs Usuario 2 para preguntar sobre la reunión para una cerveza. El usuario 2 responde con una pregunta sobre dónde reunirse y respuestas del usuario 1. Las tablas se verían así: (probablemente simplificado)

Muestra de datos Parte 1Muestra de datos Parte 2

EDIT # 2: Acceda a SQL para obtener una lista de todos los mensajes en un hilo, con estado de lectura …

Usando el esquema de @ OP, este SQL obtendrá una lista de mensajes en un hilo dado con una indicación de si un usuario dado ha leído cada mensaje o no. Los mensajes están en el primer pedido más reciente.

 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; 

Tenga en cuenta que el truco, si es justo llamarlo así, es que el estado de lectura se recoge con una sub selección. Esto es necesario porque parte de los criterios para obtener el estado de lectura requiere una cláusula where que no se puede satisfacer con una combinación externa. Por lo tanto, utiliza la subselección para determinar qué valor (posiblemente faltante) desea de la tabla secundaria MessageReadState.

EDIT 3: SQL para obtener todos los hilos con el último mensaje en cada uno para un usuario dado …

Para obtener una lista de todos los hilos en los que ha participado un usuario determinado, ordenados por mensaje más reciente primero, con solo el mensaje más reciente que se muestra (1 mensaje por hilo), entonces utilizaría una consulta similar a la anterior, excepto que en lugar de filtrar los mensajes de su FK al hilo de interés, filtra los mensajes mediante una sub consulta que encuentra el último mensaje en cada tema en el que participó el usuario de interés. Se vería así:

 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; 

De acuerdo con la respuesta de Joel Brown, puede agregar la columna LAST_MESSAGE_ID a la tabla LAST_MESSAGE_ID luego obtener todos los hilos con los últimos mensajes SQL se vuelve muy simple. Debe actualizar esta columna cuando se envía cada mensaje.

Obtener todos los hilos con el último mensaje en cada uno para un usuario dado

 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