✔ JOINs oder NULLable?

[VanHellsehn]

Ihr meint folgendes:

SELECT u.Name, p.Name, c.Title, l.timestmp
FROM Log l
INNER JOIN USER u ON l.Info = u.Id
INNER JOIN Projekt p ON l.resource_id = p.Id AND l.ActivityId = 2
INNER JOIN Comment c ON l.resource_id = c.Id AND l.ActivityId = 1

Ihr meint folgendes?

Ein Problem habe ich bei dieser Lösung aber. Ich habe oben auch etwas von einem Statusupgrade mache möchte ich auch wissen auf was er geändert wird. Also auf jedenfall ein Nullable bei dieser Herangehensweise. Genau so will ich wissen zu welcher Teilaufgabe eines Projektes das Kommentar ist, noch ein Nullable Feld.

 

[Improof]

Ich glaube langsam wir reden hier mehr als aneinander vorbei.
Soll das heißen, eine Resource kann auch ein Kommentar sein, weil du ja die resource_id mit der comment id joinst? Ich dachte eher, die Resource ist immer das Projekt. Und wenn jemand einen Kommentar hinzufügt, dann steht die Referenz Id im Info Feld deiner Log Tabelle und in die resource id verweist auf das Projekt, in dem der jeweilige Kommentar verfasst wurde. Der SQL, den du da geschrieben hast, ist nicht das was ich meinte. Zumal ich wie gerade erklärt den Sinn dahinter nicht ganz verstehe. Erklär mir mal verschiedene Fälle, was du aus deiner Datenbank herauslesen willst und ich versuche dir dann SQLs zu genau diesen Fällen zu basteln. Vielleicht wird es dann nochmals klarer.

Und um auf deine Statusupdates einzugehen: Ein Kommentar wird in einer eigenen Tabelle gespeichert und über das Info Feld in der Log Tabelle referenziert. Warum nicht genauso vorgehen? Eine Tabelle für die Statusupdates, in der du alles speicherst, was du über den Update wissen willst. Die Referenz dazu kommt dann wieder ins Info Feld.


Gruß
Daniel

 

[VanHellsehn]

Tut mir Leid. Ich meinte natürlich folgende Abfrage:

SELECT u.Name, p.Name, c.Title, l.timestmp
FROM Log l
INNER JOIN Projekt p ON l.resource_id = p.Id 
INNER JOIN USER u ON l.Info = u.Id AND l.ActivityId = 2
INNER JOIN Comment c ON l.Info = c.Id AND l.ActivityId = 1

Und tut mir Leid. Evtl. habe ich mich schlecht ausgedrückt. Hab mal ein Bild von den Tabellen erstellt:

Ignoriere hier bei die Striche. Sie stellen keine SQL Relationen dar.

Was soll geloggt werden:

• Task wird Kommentieren (Von wem? Welcher Task?)
  
• User wird zu Projekt hinzugefügt? (Von wem? Und wen?)
• Status eines Projekts ändert sich (Auf was? Von wem? Welches Project?)
• Status eines Tasks ändert sich (Auf was? Von wem? Welcher Task?)

Danke fürs dran bleiben

 

[Improof]

Hi,

jetzt wird dein Query schon klarer, aber ich verstehe immer noch nicht, warum den User und den Comment zusammen in einem SELECT joinst. Aber vermutlich ist auch hier wieder ein Verständnisproblem:
Möchtest du beim Hinzufügen eines Users auch einen Kommentar zu dieser Aktion speichern? Dann verstehe in nämlich warum du das in einem SELECT versuchst, das müsste man dann aber auch anders aufbauen.
Ich meinte bisher immer nur den Kommentar, den du zu einem Task schreibst, unabhängig einer Aktivität (also User hinzufügen / entfernen, Statusänderungen, ...). Wenn du zu den Aktivitäten auch Kommentare loggen willst, dann klar, brauchst du ein Nullable Feld Comment. Das darfst du dann aber nicht mit deinen Task-Comments verwechseln!

Und noch ein Nachtrag zu deinem Post gestern Abend, wenn du zusätzlich auf Teilprojekte (Tasks?) loggen willst, dann machst du in die Log-Tabelle außer resource_id zusätzlich task_id rein, als Nullable. Hier sind NULL-Werte wieder von Vorteil: Ist der Task Null gilt die Aktivity für das gesamte Projekt, ansonsten nur für den Task innerhalb des Projekts.
Außerdem: Ein Log sollte auch nur loggen und für mehr sollte die Tabelle nicht verwendet werden. Also bei Statusänderungen und einer Historie bitte nicht alten und neuen Status in die Log Tabelle schreiben. Mach eine Tabelle Statusänderung mit den Feldern Id, Projekt Id, Task Id (Nullable), State. Den alten Status brauchst du nicht zusätzlich speichern, da er ja als vorheriger Eintrag in der Historie verfügbar ist (Normalformen beachten). Falls es aber dann mit Joins und Subselects zu aufwendig wird, kannst du natürlich auch den old_state immer mit speichern. In die Log Tabelle kommt dann nur noch die Id als Referenz ins Info Feld. Und da ist dann auch wieder der Timestamp der Änderung. So hat alles seinen Platz.


Im Endeffekt stelle ich es mir (vereinfacht) so vor:

Tabelle Users:

• id
• name

Tabelle Projects:

• id
• name
• description

Tabelle Projects_Users (Verknüpfungstabelle der n:m Beziehung):

• user_id
• project_id

Tabelle States:

• id
• name
• description

Tabelle Tasks:

• id
• project_id
• name
• description

Tabelle StateHistory:

• id
• state_id
• project_id
• task_id

Tabelle TaskComments:

• id
• content

Tabelle Activities:

• id
• name
• description

Tabelle ActivityLog:

• id
• project_id
• task_id (NULLABLE)
• user_id
• info
• comment (NULLABLE)
• timestamp

Für deine Abfragen musst du dann eben immer die Log-Tabelle mit einbeziehen. Da bekommst du die Timestamps der Aktivitäten her. Wenn du z.B. die letzte Statusänderung von Projekt 1, Task 3 sehen willst, dann musst du in der Log nach project_id = 1, zusätzlich task_id = 3 und actvity_id = {ID FÜR STATUSÄNDERUNG} filtern und dann die beiden neuesten Einträge holen. Der neuere der beiden ist dann der neue Status und der andere ist der vorherige (demnach alte) Status, die status_id ist dann jeweils im Info-Feld.
Außerdem kannst du, falls gewünscht, zu jedem Log einen Kommentar dazu schreiben. Wenn deine TaskComments Tabelle nicht noch für etwas anderes benötigt wird, bzw. wenn du nur Kommentar-Texte bei Activities speicherst, kannst du die TaskComments Tabelle natürlich auch weg lassen, weil der Kommentartext ja dann im Log verfügbar ist.


Ich hoffe, das zeigt nun etwas klarer, wie ich mir den Anwendungsfall von dir vorstelle und wie ich denke, dass man ihn designtechnisch am elegantesten lösen kann.

Gruß
Daniel

 

[Improof]

Noch ein Nachtrag zu meinem vorherigen Beitrag:
Die Tabelle StateHistory kann weg gelassen werden, da ja in der ActivityLog automatisch eine Historie erzeugt wird!

 

[Improof]

So, ich hab jetzt selber mal ein bisschen rum probiert.
Ich stelle dir 4 Dateien zur Verfügung, einmal eine, die die Testtabellen erstellt, eine zweite, mit der du sie wieder löschen kannst, eine dritte mit Inserts (Testdaten, frei gewählt) und eine vierte mit ein paar Beispiel-Abfragen, wie du sie benötigen wirst.

Alles geschrieben und getestet mit Oracle 11 (lässt sich aber sicherlich leicht auf jedes andere DBMS portieren).
Ich hoffe, dass du dir das mal anschauen kannst und dann siehst, wie man mit diesem Design arbeitet.

Und nochmal ein Nachtrag: Die Comments-Tabelle habe ich für diesen Test mal raus gelassen und eine Kommentar-Spalte (VARCHAR(255)) in die Log Tabelle eingefügt.

Außerdem hab ich ein paar Posts früher erwähnt, dass du bitte in der Log nur loggen sollst und keine Status Historie erstellen und dann aber gestern gesagt, du brauchst keine StateHistory Tabelle weil in der Log automatisch eine Historie erzeugt wird. Das ist natürlich sehr widersprüchlich, darum erkläre ich es hier jetzt mal ganz ausführlich:
Was du nicht tun solltest ist NULLABLE Referenz Spalten in der Log anlegen, z.B. für old_state und new_state. Aber eine eigene Tabelle dafür benötigst du ja auch nicht, weil du in der Log Tabelle einfach für ein bestimmtes Projekt (und ggf. bestimmten Task) die beiden neuesten Einträge mit der ActivityID = Statusänderung nimmst und dann hast du den alten und den neuen Status.

Gruß
Daniel

 

[VanHellsehn]

SELECT DISTINCT
   ac.NAME AS Aktivitaet,
   proj.NAME AS Projektname,
   tasks.NAME AS Taskname,
   usr.NAME AS Ausfuerender_User,
   aclog.TIMESTP AS Zeit,
   sta.name AS Status,
   usr2.name AS Hinzugefuegter_User
FROM TEST_ACTIVITY_LOG aclog
INNER JOIN TEST_ACTIVITIES ac ON ac.ID = aclog.ACTIVITY_ID
INNER JOIN TEST_PROJECTS proj ON proj.ID = aclog.PROJECT_ID
LEFT OUTER JOIN TEST_TASKS tasks ON tasks.ID = aclog.TASK_ID
INNER JOIN TEST_USERS usr ON usr.ID = aclog.USER_ID
LEFT JOIN TEST_STATES sta ON sta.ID = aclog.ref_id AND aclog.activity_id = 4
LEFT JOIN TEST_USERS usr2 ON usr2.ID = aclog.ref_id AND (aclog.activity_id = 2 OR aclog.activity_id = 3)
WHERE proj.ID = 1
ORDER BY aclog.TIMESTP;

So habe ich alle Informationen die ich brauche. Ein Activity log für ein Projekt und dann filter ich für die Tasks noch nach der Task.ID .

Aber wäre es nicht evtl besser die TEST_ACTIVITIES weg zu lassen und stattdessen mit einem Enum in TEST_ACTIVITY_LOG ein zu setzten. Also das die activity_id dann durch das Feld activity_type enum('user_added'...) ersetzt wird? Ich möchte niemals neue


Grüße Marv

 

[Improof]

Klar kannst du auch mit Enums arbeiteten, solange du nicht zusätzlich irgendwelche Informationen zu den Activities brauchst, die du joinen willst.
Mit ging es nur darum, dir das Grunddesign etwas näher zu bringen, da es ja doch sehr komplex ist und man es so wohl eher beschreiben kann, als nur mit Worten. (HAt man ja auch gesehn, wie oft wir irgendwie aneinander vorbei geredet haben )

Gruß
Daniel

 

[VanHellsehn]

Stimmt. Nächste mal poste ich direkt die Creates

Aber danke das du dran geblieben bist =) Ich denke mit dem Layout bin ich ganz zufrieden.

Grüße Marv

 

[Improof]

Haha joa ich werd mir des auch merken, ITler verstehen sich nunmal am besten über Code XD
Und bitte, ich helf doch immer gern

Gruß
Daniel