Erstellen eines privaten Nachrichtensystems mit Threads wie Facebook und Gmail

Ich erstelle ein Thread-Nachrichtensystem ähnlich wie Google Mail und Facebook, wo der Posteingang die neuesten Threads auflistet, die den Betreff, den Namen des Absenders und den Zeitstempel der neuesten Nachricht anzeigen.

So sind meine Tische aufgebaut:

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

Was ich gerade mache, ist, wenn ein Benutzer eine neue Nachricht erstellt, erstellt er einen neuen Thread in der Thread-Tabelle und dann eine neue Nachricht in der Nachrichtentabelle, und wenn ein Benutzer auf einen Thread antwortet, dupliziert er den aktuellen Thread in der Thread-Tabelle außer es tauscht die to_id und from_id und erstellt dann basierend darauf eine neue Nachricht.

Außerdem kann ich für die Posteingangsansicht alle Threads basierend auf a abfragen user_id. also sowas wie SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC oder wenn ich Nachrichten im Postausgang anzeigen möchte, wäre es so etwas wie SELECT * FROM thread WHERE from_id = 2 and from_keep = TRUE ORDER BY date DESC.

Wenn ein Benutzer einen Thread öffnet, wenn es eine neue Nachricht gibt, wird to_read auf true aktualisiert UPDATE thread SET to_read = TRUE WHERE thread_id = 4.

Ich habe das Gefühl, dass ich diesen Prozess zu kompliziert mache und dass es einen besseren Weg geben sollte, dies zu tun.

Jede mögliche Hilfe oder Ideen würden geschätzt.

Auf diese Weise wähle ich einfach alles aus der Thread-Tabelle aus und mache dann eine Verknüpfung mit der Benutzertabelle, um alles anzuzeigen, was ich brauche. Ich habe jedoch das Gefühl, dass es einen besseren Weg geben sollte, dies zu tun.

Warum trennen Sie Nachrichtenbeziehungen nicht von der Benutzeransicht jeder Nachricht?

Ich würde das Threading durch eine selbstreferenzierende Beziehung in der Nachricht durchführen. Mit anderen Worten, die Nachricht hat eine Spalte “responding_to_message_id”.

Ich bin mir nicht sicher, ob ich verstehe, warum Sie eine “to_id” haben. Werden Nachrichten an einzelne Benutzer gerichtet? Dies scheint sehr begrenzt. Ich würde denken, dass Sie entweder keinen Empfänger haben (dh der Empfänger ist ein Message Board, das jeder lesen kann) oder Sie hätten die Möglichkeit, mehrere Empfänger anzugeben, genau wie bei einer E-Mail. Vielleicht können Sie näher erläutern, wie das System verwendet werden soll.

Angenommen (der Einfachheit halber), dass Sie an ein Board posten, also ist nur das “von” wichtig, dann haben Sie Ihre Nachrichtentabelle mit selbstreferenzierender Beziehung für das Threading, eine Benutzertabelle und dann eine Schnittpunkttabelle zwischen Benutzer und Nachricht die speichert, welche Nachrichten von jedem Benutzer gelesen wurden.

Wenn Sie also wissen möchten, ob ein Benutzer eine Nachricht gelesen hat oder nicht, versuchen Sie einfach, die Benutzer-ID in der Schnittpunkttabelle für die jeweilige Nachricht zu lesen. Wenn es ist nicht dort, dann wird diese Nachricht von diesem Benutzer ungelesen.

Beachten Sie, dass dieses Design gilt, wenn Sie einzelne Empfänger haben möchten, und wenn Sie mehrere Empfänger haben möchten, können Sie eine Schnittpunkttabelle verwenden, um die Liste der Empfänger für jede Nachricht zu speichern. Wenn Sie eine Empfängerschnitttabelle haben, kann sie als Ihre Lesestatustabelle fungieren.

BEARBEITEN: ERD-Skizze:

Hier ist eine kurze Skizze dessen, wovon ich spreche …

Ob der Absender sich dafür entschieden hat, die Nachricht aufzubewahren oder nicht, wird in der Nachricht selbst gekennzeichnet. Wenn die Nachricht der Beginn eines neuen Threads ist, ist die Spalte „reply_to_message_id“ NULL, andernfalls ist sie die „message_id“ der übergeordneten Nachricht. Es kann mehrere Empfänger geben, von denen jeder seine eigene Fähigkeit hat, die Nachricht aufzubewahren oder nicht, sowie die Fähigkeit, das Datum und die Uhrzeit zu verfolgen, zu der der Empfänger die Nachricht liest.

BEARBEITEN 2: Alternate ERD und Abfragen nach der neuesten Nachricht

@OP hat gefragt, wie die neueste Nachricht in einem Thread abgefragt werden kann. Die Antwort hängt von der Form des Fadens ab. Sie können entweder einen flachen Thread haben, bei dem jede Nachricht an das Ende des linearen Nachrichtenstroms geht, oder Sie können einen baumförmigen Thread haben, bei dem jede Nachricht ein bestimmtes übergeordnetes Element hat, es sei denn, es ist der Stamm des Threads. In der obigen ERD könnte das Feld „reply_to_message_id“ auf beide Arten verwendet werden. Wenn der Thread flach ist, dann ist der FK immer die Wurzel MESSAGE. Wenn der Thread baumförmig ist, dann ist der FK der unmittelbare Elternteil der Antwort NACHRICHT.

Wenn eine typische Abfrage, die Sie ausführen möchten, lautet: “Was ist die neueste Nachricht in einem Thread?” und Ihre Threads sind flach, dann können Sie SQL wie folgt verwenden:

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

Wenn Ihre Threads andererseits baumförmig sind und Sie diese Abfrage schnell und einfach ausführen möchten, ist das Schema in der obigen ERD nicht sehr einfach zu handhaben. SQL ist nicht gut in Bäumen. Sie können das Problem mit ein wenig Denormalisierung lösen. Siehe ERD unten:

Beachten Sie, dass es jetzt einen FK gibt, um den unmittelbaren Elternteil anzuzeigen, und einen FK, um die Wurzel anzuzeigen. Da Threads keiner Bearbeitung unterliegen – zumindest bei Bearbeitungen, bei denen der Stamm einer Nachricht geändert wird, um auf einen anderen Thread zu verweisen, impliziert die damit verbundene Denormalisierung kein Risiko von Aktualisierungsanomalien, sodass die Redundanz nicht zu problematisch ist.

Wenn Sie diese ERD verwenden, ist die Abfrage für „neueste Nachricht in Thread X“ die gleiche wie oben, aber mit M.thread_root_message_id in der where-Klausel anstelle von M.reply_to_message_id.