Ankündigung

Einklappen
Keine Ankündigung bisher.

Datenbank für einfachen Chat / best practice

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Datenbank für einfachen Chat / best practice

    Moin zusammen.
    Heute mal ein SQL-Problem...

    Ich versuche in einem "Probier-Projekt" ein kleines Chat System zu basteln und bin mir nicht sicher, ob ich zu umständlich bin oder ich mit SQL nicht ganz klar komme.

    Versuch Nr.1:
    den ganzen Chat in einer Tabelle:

    Tabellenname: messages:
    messageID | senderID | recieverID | status | timestamp | content

    meine Abfrage dazu sah so aus:
    Code:
    SELECT * FROM messages WHERE senderID = '$userID' OR recieverID = '$userID' ORDER BY timestamp DESC
    funktioniert wie erwartet, zeigt aber aber natürlich alle Nachrichten an und nicht nur die letze pro Gespräch.
    Ein hinzufügen von:
    Code:
    GROUP BY ...
    scheitert schon mal daran, das ich ja einmal nach recieverID und einmal nach senderID gruppieren müsste...

    meine nächste Idee sah dann so aus, das ich die Abfragen für gesendete und empfangene Nachrichten separat mache... was klappt, aber die Übersichtlichkeit nicht unbedingt verbessert.
    Es sollen prinzipiell die Nachrichten nach Zeit sortiert sein und nicht nach empfangen/gesendet...

    Da ich das Problem auch durch googlen nicht lösen konnte, hab ich einen neuen Versuch mit 2 Tabellen gestartet:

    Versuch Nr.2:
    Zwei Tabellen für den Chat:

    Tabellenname: conversations:
    conversationID | starterID | partnerID

    Tabellenname: messages:
    messageID | conversationID | senderID | status | timestamp | content

    ich benutze jetzt 2 Abfragen:
    mit der ersten hole ich die Konversationen an denen der User beteiligt ist:
    Code:
    SELECT conversationID FROM conversations WHERE starterID = '$userID' OR partnerID = '$userID'
    mit der zweiten hole ich dann aus der anderen Tabelle die jeweils letzte Nachricht:
    Code:
    SELECT * FROM messages WHERE conversationID = '$convID ORDER BY timestamp DESC LIMIT 1
    das funktioniert, da die erste Abfrage aber nach der ID sortiert ist, sind die anschließende Gespräche auch entsprechend sortiert und nicht nach der Zeit.

    Meine Lösung:
    quasi Variante 2b:

    die Tabelle conversations um die Spalte timestamp zu erweitern und bei jeder Nachricht die in die messages-Tabelle eingetragen wird zusätzlich in der conversations-Tabelle den timestamp zu erneuern
    Code:
    UPDATE conversation SET timestamp = CURRENT_TIMESTAMP WHERE conversationID = '$convID'
    und entsprechend sieht die erste Abfrage dann so aus:
    Code:
    SELECT conversationID FROM conversations WHERE starterID = '$userID' OR partnerID = '$userID' ORDER BY timestamp DESC
    damit klappt mein Chat genauso wie ich es will...

    Aber es erscheint mir recht umständlich.
    Ich bin immernoch davon überzeugt, das es eine Möglichkeit mit nur einer Tabelle gibt, wahrscheinlich fehlen mir da noch tiefere SQL-Kenntnisse...

    Ich hätte gern mal eure Meinung dazu:
    Wie würdet ihr das anstellen, was haltet ihr von meinen Versuchen?

  • #2
    Ich würde das so in etwa aufbauen:
    Code:
    // tables
    Table Users {
      Id int [pk, increment]
      NickName varchar
      StatusLevel tinyint
      RegisteredAt datetime
      IsActive bool
    }
    
    Table Conversations {
      Id int [pk, increment] // auto-increment
      CreatedAt datetime
      InitiatorId int
      IsActive bool
    }
    
    Table Messages {
      Id int [pk, increment]
      ConversationId int
      UserId int
      Message varchar
      CreatedAt datetime
      IsActive bool
     }
    
    
    // references
    Ref: Users.Id > Conversations.InitiatorId
    Ref: Conversations.Id > Messages.ConversationId
    Achtung: Pseudocode (dbdiagram.io)


    EDIT:
    Als MySQL Export nochmal ( geht ja auch, ganz vergessen )
    Code:
    CREATE TABLE `Users` (
      `Id` int PRIMARY KEY AUTO_INCREMENT,
      `NickName` varchar(255),
      `StatusLevel` tinyint,
      `RegisteredAt` datetime,
      `IsActive` bool
    );
    
    CREATE TABLE `Conversations` (
      `Id` int PRIMARY KEY AUTO_INCREMENT,
      `CreatedAt` datetime,
      `InitiatorId` int,
      `IsActive` bool
    );
    
    CREATE TABLE `Messages` (
      `Id` int PRIMARY KEY AUTO_INCREMENT,
      `ConversationId` int,
      `UserId` int,
      `Message` varchar(255),
      `CreatedAt` datetime,
      `IsActive` bool
    );
    
    ALTER TABLE `Users` ADD FOREIGN KEY (`Id`) REFERENCES `Conversations` (`InitiatorId`);
    
    ALTER TABLE `Conversations` ADD FOREIGN KEY (`Id`) REFERENCES `Messages` (`ConversationId`);
    Competence-Center -> Enjoy the Informatrix
    PHProcks!Einsteiger freundliche TutorialsPreComposed Packages

    Kommentar


    • #3
      Vielen Dank.
      Also ist die Variante mit 2 Tabellen schon sinnvoll / nötig?
      Bei deiner Variante der conversations Tabelle ist nur die Start-User ID drin, wie filtere ich denn dann die Gespräche heraus, an denen der User beteiligt ist, aber die er nicht begonnen hat?

      Die messages Tabelle nach der User ID durchsuchen mit dem INNER JOIN auf conversations und gleichzeitig in conversations auf starterID ?

      So in der Art
      Code:
      SELECT * FROM messages, conversations WHERE messages.UserID = $userID OR conversationID = ...
      Und da bin ich grade am Ende meiner Weisheit... Bin grade nur per Handy online und kann es grade nicht testen. Ich hoffe meine Richtung stimmt...

      Kommentar


      • #4
        Messages hat ebenfalls eine UserId, die ist verknüpft mit der Id aus der Tabelle Users.
        Damit bekommst Du alle Nachrichten von allen Usern, die an einer Konversation teilnehmen.

        Joins sind schon mal richtig, probiere es einfach aus, wenn Du wieder am rechner bist.
        Aber nicht verwechseln mit Joints!
        Competence-Center -> Enjoy the Informatrix
        PHProcks!Einsteiger freundliche TutorialsPreComposed Packages

        Kommentar


        • #5
          Mach ich... Ich melde mich.

          Kommentar


          • #6
            So, jetzt nochmal in Ruhe angeguckt....

            Bei deinem DB-Design hab ich ein Problem mit der Abfrage.... wie oben schon geschrieben... so tief stecke ich nicht in SQL drin...

            Mein Problem... die messages Tabelle enthält die UserID (des Senders) wenn ich darauf filtere, kriege ich alle Nachrichten die der User gesendet hat...
            Die conversations Tabelle enthält die InitiatorID... damit kriege ich alle Gespräche die der User angefangen hat...

            Ich müsste jetzt also aus der messages Tabelle alle ConverdationID's rausholen, die vom User kommen
            UND in der conversations Tabelle alle ID's rausholen, von Gespräche die der User initiiert hat.
            Dann müsste ich aus beiden Ergebnissen zu der ConversationID jeweils die neuste Nachricht raussuchen.

            Ich habs noch nicht versucht, aber ich wüsste gar nicht, wie ich das in eine Abfrage stecken soll...


            Ist meine Ursprungsvariante mit den ID's der beiden Gesprächspartner in der conversations Tabelle so viel schlechter?
            Ich weiß, wenn ich beide ID's in der conversations-Tabelle ablege und zusätzlich die ID des Senders in der messages Tabelle, dann hab ich da redundate Daten drin.
            Um das zu vermeiden, wäre vielleicht eine Möglichkeit in der messages Tabelle anstatt der SenderID einfach eine Spalte 'direction' einfügen, um die Richtung der Nachricht zu definieren... also zum Gesprächsstarter oder der andersrum...

            Ich hätte zwar immernoch das Problem, das ich den timestamp in der conversations Tabelle bei jedem Eintrag in die messages Tabelle aktualisieren muss, aber so wäre die Abfrage für mich wesentlich einfacher...
            btw. welchen Zweck erfüllt der timestamp in deiner Version der conversation-Tabelle?

            Macht das Sinn oder ist das jetz doof?

            Kommentar


            • #7
              Neueste Nachrichten erhältst du durch die Sortierung(DESC) nach dem Erstellungsdatum. Das Neueste steht dann an erster Stelle und mit LIMIT kannst du das Ergebnis auf die notwendige Anzahl beschränken. Teste es mal und lasse wissen ob es geklappt hat.

              Kommentar


              • #8
                Mein Problem... die messages Tabelle enthält die UserID (des Senders) wenn ich darauf filtere, kriege ich alle Nachrichten die der User gesendet hat...
                Die conversations Tabelle enthält die InitiatorID... damit kriege ich alle Gespräche die der User angefangen hat...
                Du musst erstmal von der Hauptmenge ausgehen, das sind aus meiner Sicht die Messages. Dann ziehst Du Dir alle Daten aus den Konversationen und Benutzern, die Du noch benötigst.

                Beispiel:
                https://www.db-fiddle.com/f/h33w8azCuv8xZdfhPqHFtE/1
                Competence-Center -> Enjoy the Informatrix
                PHProcks!Einsteiger freundliche TutorialsPreComposed Packages

                Kommentar


                • #9
                  Cooler Link, Danke, ich probiere damit mal rum...

                  Kommentar

                  Lädt...
                  X