스레드 메시징 시스템 데이터베이스 스키마 설계

스레드 메시징 시스템 데이터베이스 스키마 설계

저는 여기에 설명된 것을 정확히 달성하기 위해 노력하고 있습니다: 페이스북과 gmail과 같은 스레드화된 개인 메시지 시스템을 만드는 것입니다. 하지만 저는 조엘 브라운의 대답을 완전히 이해하지 못합니다. 누가 설명해 주시겠습니까?

샘플 데이터를 사용한 데이터베이스 테이블은 다음과 같습니다(데모 목적으로 올바르게 입력한 것으로 가정함).

1. LoginId를 기반으로 스레드 목록을 표시해야 합니다(맨 위에 있는 최신 정보). LINQ에서 쿼리는 어떤 모양입니까? (내가 요청하는 것은 메시지 스레드 그룹에 있습니다. 각 스레드에서 최신 메시지 1개를 제공하십시오.) - 페이스북에서 수행하는 것처럼.

2. 모든 메시지를 메시지 스레드(LINQ)에 표시해야 합니다. 페이스북에서 메시지를 클릭하면 전체 "대화"가 트레드에 표시됩니다.

도와주세요! 감사합니다.

EDIT -> 계속 조엘, 이것이 맞습니까?

Joel, 제가 좀 헷갈리는데 설명 좀 해주시겠어요? (굵은 글씨로 쓴 코멘트/질문)

여기서는 사용자가 새 스레드/메시지를 시작할 때마다 스레드 테이블의 새 레코드로 시작합니다.그런 다음 사용자가 TRADE_Participant로 추가되고 메시지 내용이 MESSAGE에 추가되며 이 메시지는 TRADE를 포함하는 내용을 다시 가리킵니다.MESSAGE에서 USER로 FK는 메시지 작성자를 나타냅니다.

LoginId 1이 LoginId2에 메시지를 보냅니다. => 새 레코드가 메시지에 삽입되었습니다.스레드 테이블.또한 레코드가 메시지에 삽입됩니다.메시지가 포함된 스레드 참가자 레코드ThreadId = 1, LoginId = 1(발신자)입니다.새 레코드가 MessageId = 1인 Message 테이블에 삽입됩니다.스레드 ID = 1, SenderLoginId = 1(맞습니까??)

그 반복 입니다: 이이그반이복내가것가입다니진에.

Loginid 2가 그에게 온 메시지가 있다는 것을 알 수 있는 방법이 없기 때문에 제가 혼란스럽다고 생각합니다. ?아니면 2개의 레코드를 메시지에 삽입해야 할 수도 있습니다.스레드 참가자?(발신자와 수신자)-> 이런 식으로 둘 다 전체 "해석"을 볼 수 있습니다.

편집2: 조, 나는 이것을 할 수 있다고 생각합니다:

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;

제가 틀렸다면 고쳐주세요 :)

그럼 그냥 물어보는 게 어때요? :)

제가 당신의 요구 사항에 대한 저의 이해를 확실히 하도록 하겠습니다.내가 보기에 당신은 두 사람 사이의 메시지의 선형 목록(트리가 아닌)인 스레드를 보고 있는 것 같습니다.저는 당신이 두 명만 허용하는 것보다 더 많은 사람들을 허용하는 것을 원할 것이라고 생각합니다.그것은 누군가가 메시지를 게시하고 얼마든지 많은 사람들이 그것을 읽고 댓글을 추가하기 시작하는 한 페이스북과 같을 것입니다.설명을 추가하면 스레드에 추가되고 상태 업데이트 및 스레드의 활동 등에 대해 알려주는 전자 메일을 받기 시작합니다.그게 당신이 추구하는 것이라고 가정한다면, 제가 빅 마이크에게 제안한 스키마는 당신이 원하는 것이 아닙니다.

대신 다음을 고려합니다.

여기서는 사용자가 새 스레드/메시지를 시작할 때마다 스레드 테이블의 새 레코드로 시작합니다.그런 다음 사용자가 TRADE_Participant로 추가되고 메시지 내용이 MESSAGE에 추가되며 이 메시지는 TRADE를 포함하는 내용을 다시 가리킵니다.MESSAGE에서 USER로 FK는 메시지 작성자를 나타냅니다.

사용자가 메시지를 읽을 때 MESSAGE_READ_STATE 테이블에 요구 사항에 따라 메시지를 명시적으로 또는 암시적으로 읽음으로 표시했음을 나타내는 항목이 표시됩니다.

누군가 스레드의 초기 메시지에 주석을 달면 원래 스레드에 FK가 포함된 두 번째 메시지가 추가되고 응답 작성자(사용자)가 스레드_PARTINT 테이블에 추가됩니다.그래서 한 명, 두 명 또는 그 이상의 참가자들이 스레드에 메시지를 추가하는 방식으로 진행됩니다.

스레드에서 가장 최근의 메시지를 얻으려면 메시지 FK가 관심 스레드에 있는 생성 날짜(또는 ID 키)에서 내림차순으로 정렬된 MESSAGE에서 상위 1을 가져옵니다.

사용자에 대해 가장 최근에 업데이트된 스레드를 가져오려면 메시지가 스레드에 있는 생성 날짜를 기준으로 내림차순으로 정렬된 메시지에서 상위 1과 관련된 스레드를 가져옵니다.

유감스럽게도 저는 이러한 것들을 LINQ로 표현할 수 없습니다. LinqPad를 없애야 합니다.위에서 제 드리프트를 파악하는 데 어려움이 있다면 테이블 정의와 SQL로 답변을 구체화할 수 있습니다.그냥 댓글로 물어보세요.

편집: 요구사항 및 구현 설명

요구 사항 명확화:처음에 저는 댓글을 달 수 있는 기회로 공개적으로 게시된 메시지에 대해 생각하고 있었지만, 셰인은 직접 메시지 기능을 더 추구하고 있습니다.이 경우 초기 수신자를 처음부터 TRADE_PARTICATANT 테이블에 포함시켜야 합니다.

명확한 설명을 위해 테이블에 몇 개의 행을 배치합니다.(캐나다의 날을 기념하여) 다음과 같은 시나리오가 있습니다.사용자 1 DM 사용자 2 맥주 한 잔을 위한 회의에 대해 묻습니다.사용자 2는 만날 장소에 대한 질문으로 대답하고 사용자 1은 대답합니다.표는 다음과 같습니다. (아마도 너무 단순화되었을 것입니다.)

EDIT #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개의 메시지)만 표시된 상태에서 가장 최근의 메시지(스레드당 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;

Joel Brown의 답변에 따르면 다음과 같이 추가할 수 있습니다.LAST_MESSAGE_ID스레드 테이블에 열을 올린 다음 마지막 메시지가 포함된 모든 스레드 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

언급URL : https://stackoverflow.com/questions/6541302/thread-messaging-system-database-schema-design