問題描述

我正在創(chuàng)建一個(gè)類似于 gmail 和 facebook 的線程消息系統(tǒng)，其中收件箱列出了顯示主題、發(fā)件人姓名和最新消息時(shí)間戳的最新線程.

這是我的表的設(shè)置方式:

用戶:用戶身份用戶名線:線程ID標(biāo)題to_id保持讀書from_idfrom_keep日期信息:message_id線程IDto_idfrom_id消息文本日期

我現(xiàn)在正在做的是當(dāng)用戶創(chuàng)建一條新消息時(shí)，它在線程表中創(chuàng)建一個(gè)新線程，然后在消息表中創(chuàng)建一個(gè)新消息，如果用戶響應(yīng)一個(gè)線程，它會(huì)復(fù)制當(dāng)前線程表中的線程，除了它交換 to_id 和 from_id 然后基于此創(chuàng)建新消息.

此外，對于收件箱視圖，我可以根據(jù) user_id 查詢所有線程.所以類似于 SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC 或者如果我想查看發(fā)件箱中的消息，它會(huì)類似于 SELECT * FROM thread WHERE from_id= 2 and from_keep = TRUE ORDER BY date DESC.

如果用戶在有新消息時(shí)打開一個(gè)線程，則 to_read 更新為 true UPDATE thread SET to_read = TRUE WHERE thread_id = 4.

我覺得我把這個(gè)過程復(fù)雜化了，應(yīng)該有更好的方法來做到這一點(diǎn).

任何幫助或想法將不勝感激.

這種方式讓我只需從線程表中選擇所有內(nèi)容，然后與用戶表進(jìn)行連接以顯示我需要的所有內(nèi)容.但是我覺得應(yīng)該有更好的方法來做到這一點(diǎn).

為什么不從用戶對每條消息的視圖中分離出消息關(guān)系?

我會(huì)通過消息的自引用關(guān)系進(jìn)行線程處理.換句話說，消息有一個(gè)responding_to_message_id"列.

我不確定我是否理解您為什么有to_id".消息是針對個(gè)人用戶的嗎?這似乎非常有限.我認(rèn)為您要么沒有收件人(即收件人是任何人都可以閱讀的留言板)，要么您可以指定多個(gè)收件人，就像使用電子郵件一樣.也許你可以解釋更多關(guān)于如何使用系統(tǒng)的信息.

假設(shè)(為簡單起見)您要發(fā)布到板上，因此只有發(fā)件人"很重要，那么您就有了消息表，具有線程的自引用關(guān)系，用戶表，然后是兩者之間的交集表user 和 message 存儲(chǔ)每個(gè)用戶已閱讀的消息.

這樣，如果您想知道用戶是否已閱讀消息，只需嘗試讀取給定消息的交集表中的用戶 ID.如果不存在，則該用戶未閱讀該消息.

請注意，如果您希望此設(shè)計(jì)擁有單個(gè)收件人，并且您希望擁有多個(gè)收件人，則可以使用交集表來保存每條消息的收件人列表.如果您確實(shí)有收件人交集表，它可以作為您的讀取狀態(tài)表執(zhí)行雙重任務(wù).

ERD 草圖:

這是我正在談?wù)摰膬?nèi)容的簡要概述...

發(fā)送者是否選擇保留消息在消息本身上有標(biāo)記.如果消息是新線程的開始，則reply_to_message_id 列為NULL，否則為父消息的message_id.可以有多個(gè)收件人，每個(gè)收件人都有自己保留或不保留郵件的能力，以及跟蹤收件人閱讀郵件的日期和時(shí)間的能力.

編輯 2:替代 ERD 和查詢最新消息

@OP 詢問如何查詢線程中的最新消息.答案取決于螺紋的形式.您可以擁有一個(gè)扁平線程，其中每條消息都到達(dá)線性消息流的末尾，或者您可以擁有一個(gè)樹形線程，其中每條消息都有一個(gè)特定的父級(jí)，除非它是線程的根.在上面的 ERD 中，可以使用任何一種方式使用 reply_to_message_id 字段.如果線程是扁平的，那么 FK 總是到根 MESSAGE.如果線程是樹形的，則 FK 是回復(fù) MESSAGE 的直接父級(jí).

如果您要運(yùn)行的典型查詢是線程中的最新消息是什么?"并且您的線程是扁平的，那么您可以像這樣使用 SQL:

選擇前1M.message_id, M.sent_datetime, M.title, M.message_text, S.user_id, S.user_name——以及任何你想要的……來自 MESSAGE M 內(nèi)連接 USER SM.sender_user_id = U.user_id其中 M.reply_to_message_id = @ThreadRootMessageID訂購方式M.sent_datetime desc

另一方面，如果您的線程是樹形的并且這是一個(gè)您希望能夠快速輕松地運(yùn)行的查詢，那么上面的 ERD 中的架構(gòu)就不太容易使用.SQL 不擅長樹.你可以通過一些非規(guī)范化來解決這個(gè)問題.請參閱下面的 ERD:

請注意，現(xiàn)在有一個(gè) FK 顯示直接父項(xiàng)，一個(gè) FK 顯示根.由于線程不受編輯 - 至少在消息的根被更改為指向不同線程的編輯中，這帶來的非規(guī)范化并不意味著更新異常的風(fēng)險(xiǎn)，因此冗余不會(huì)太成問題.

如果您使用此 ERD，則線程 X 中的最新消息"的查詢與上述相同，但在 where 子句中使用 M.thread_root_message_id 而不是 M.reply_to_message_id.

I am creating a threaded message system much like gmail and facebook where the inbox lists the most recent threads displaying the subject, the sender's name, and time-stamp of the most recent message.

Here's How my tables are set up:

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

What I'm doing right now is when a user creates a new message, it creates a new thread in the thread table and then a new message in the message table and if a user responds to a thread, it duplicates the current thread in the thread table except it swaps the to_id and from_id and then creates a new message based on that.

Also, for the inbox view, I'm able to just query all threads based on a user_id. so something like SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC or if I want to view messages in the outbox it would be something like SELECT * FROM thread WHERE from_id = 2 and from_keep = TRUE ORDER BY date DESC.

If a user opens a thread when there's a new message, then to_read is updated to true UPDATE thread SET to_read = TRUE WHERE thread_id = 4.

I feel like I'm over complicating this process and that there should be a better way to do this.

Any help or ideas would be appreciated.

This way let's me just select everything from the thread table and then do a join with the user table to display everything I need. However I feel like there should be a better way to do this.

Why don't you separate out message relationships from user's view of each message?

I would do the threading by a self-referencing relationship on message. In other words, the message has a "responding_to_message_id" column.

I'm not sure I understand why you have a "to_id". Are messages directed to individual users? This seems very limited. I would think that you would either have no recipient (i.e. the recipient is a message board that anyone can read) or you would have the ability to specify multiple recipients, just like with an e-mail. Perhaps you can explain more about how the system is to be used.

Assuming (for simplicity) that you are posting to a board, so only the "from" is important, then you have your message table, with self-referencing relationship for threading, a user table, and then an intersection table between user and message that stores which messages have been read by each user.

That way, if you want to know if a user has read a message or not, just attempt to read the user ID in the intersection table for the given message. If it isn't there, then that message is unread by that user.

Note that if you want to have single recipients this design holds and if you want to have multiple recipients you can use an intersection table to hold the list of recipients for each message. If you do have a recipient intersection table, it can do double-duty as your read status table.

EDIT: ERD Sketch:

Here is a quick sketch of what I'm talking about...

Whether or not the sender has chosen to keep the message is flagged on the message itself. If the message is the start of a new thread, the reply_to_message_id column is NULL, otherwise it is the message_id of the parent message. There can be mulitple recipients, each of which have their own ability to keep the message or not, as well as the ability to track the date and time when the recipient reads the message.

EDIT 2: Alternate ERD and Querying for Most Recent Message

@OP asked how to query for the most recent message in a thread. The answer depends on the form of the thread. You can either have a flat thread where every message goes to the end of the linear stream of messages or you can have a tree-shaped thread where each message has a specific parent, unless it's the root of the thread. In the ERD above, the reply_to_message_id field could be used either way. If the thread is flat, then the FK is always to the root MESSAGE. If the thread is tree-shaped, then the FK is to the immediate parent of the reply MESSAGE.

If a typical query you want to run is "what is the most recent message in a thread?" and your threads are flat, then you can use SQL like this:

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

If, on the other hand, your threads are tree-shaped and this is a query you want to be able to run quickly and easily, then the schema in the ERD above is not very easy to work with. SQL is not good at trees. You can solve the problem with a little bit of denormalization. See the ERD below:

Note that there is now one FK to show the immediate parent and one FK to show the root. Since threads aren't subject to editing - at least to edits where the root of a message is changed to point at a different thread, the denormalization that this entails does not imply risk of update anomallies so the redundancy is not too problematic.

If you use this ERD then the query for "most recent message in thread X" is the same as above, but with M.thread_root_message_id in the where clause instead of M.reply_to_message_id.

這篇關(guān)于創(chuàng)建一個(gè)線程私人消息系統(tǒng)，如 facebook 和 gmail的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！