Datenbankdesign minimalistisches Forensystem

[Nuebel]

Tag,

ich habe bisher immer einen großen Bogen um Webentwicklung gemacht. Das möchte ich jetzt ändern, indem ich für mich ein kleines Projekt starte, das sich beliebig erweitern lässt. Die erste Idee, die mir in den Sinn kam, war ein kleines, simples Forensystem. In diesem Thread geht es mir vorrangig um das Datenbankdesign. Die Idee ist keine halbe Stunde alt; dementsprechend sieht mein bisheriger unvollständiger Ansatz auch aus. Alles, was ich bisher getan habe, sind ein paar in Notepad geschriebene SQL-Befehle. Für jegliche Vorschläge, Ergänzungen, Kritiken bin ich offen und dankbar.
(Bitte ignoriert vorerst die Datentypen für Zeitangaben. Ich möchte es anfangs völlig stressfrei zwischen SQLite und MySQL wechseln können.)

Tabellen für (Sub-)Foren und Beiträge:

CREATE TABLE forum (
    id			INTEGER AUTO_INCREMENT,
    name		TEXT,
    description		TEXT,
    parent_id		INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES forum(id) ON DELETE CASCADE
);

Ich habe mich dazu entschlossen, keine extra Tabelle für Kategorien zu erstellen. Dem aufmerksamen Betrachter mag aufgefallen sein, dass parent_id ein Fremdschlüssel ist, der auf die eigene Tabelle zeigt. Der Gedanke dahinter war, dass Einträge mit parent_id = NULL als Kategorien fungieren, und alle anderen als Foren. Theoretisch lassen sich also beliebig tiefe Foren-Hierarchien aufbauen. Ansonsten wird noch der Name und eine Beschreibung des Forums gespeichert.

CREATE TABLE post (
	id 			INTEGER AUTO_INCREMENT,
	forum_id		INTEGER,
	user_id			INTEGER,
	title			TEXT,
	content			TEXT,
	created			TEXT,
	parent_id		INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY (forumd_id) REFERENCES forum(id) ON DELETE CASCADE,
	FOREIGN KEY (user_id) REFERENCES user(id),
	FOREIGN KEY (parent_id) REFERENCES post(id) ON DELETE CASCADE
);

Auch hier gibt es wieder das Spielchen mit parent_id. Diese Tabelle soll nicht nur Threads speichern können, sondern auch die Antworten darauf. parent_id = NULL bedeutet Thread, alles andere Antwort. Wird das Forum gelöscht, werden sämtliche Threads gelöscht. Wird ein Thread gelöscht, werden sämtliche dazugehörigen Antworten gelöscht. Ich brauche hier nicht wirklich eine Art Wiederherstellungsmöglichkeit, wobei ich das Löschen nur vortäusche, indem ich zum Beispiel eine Spalte deleted einführe und diese dann auf true setze.

Tabellen für User, Gruppen und Berechtigungen:

CREATE TABLE user (
	id 			INTEGER AUTO_INCREMENT,
	name			TEXT,
	password		TEXT,
	email			TEXT,
	avatarurl		TEXT,
	signature		TEXT,
	registered		TEXT,
	PRIMARY KEY (id)
);

Ich bin mir noch nicht sicher, ob ich dicke Profile über Benutzer speichern möchte mit hunderten Kontaktmöglichkeiten und Angaben zur Person. Habe es in diesem ersten Ansatz bei avatarurl und signature belassen.

CREATE TABLE groups (
	id 			INTEGER AUTO_INCREMENT,
	name			TEXT,
	PRIMARY KEY (id)
);

CREATE TABLE user_groups (
	user_id 		INTEGER,
	group_id		INTEGER,
	PRIMARY KEY (user_id, group_id),
	FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
	FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE
);

Es soll möglich sein, Gruppen anzulegen. Ein Benutzer kann in mehreren Gruppen sein. Für den Anfang dachte ich an drei Gruppen: Registrierte, Moderatoren, Administratoren. Den jeweiligen Gruppen können Berechtigungen zugesprochen werden. Dazu sind die folgenden zwei Tabellen gedacht:

CREATE TABLE permission (
	id 		INTEGER AUTO_INCREMENT,
	description 	TEXT,
	PRIMARY KEY (id)
);

CREATE TABLE group_permission (
	group_id 	INTEGER,
	permission_id 	INTEGER,
	PRIMARY KEY (group_id, permission_id),
	FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE,
	FOREIGN KEY (permission_id) REFERENCES permission(id) ON DELETE CASCADE
);

Eine Gruppe kann mehrere Berechtigungen haben. Unter Berechtigungen verstehe ich etwas wie "Darf Beitrag editieren". Sinn der ganzen Gruppen-Berechtigungssache soll sein, dass ich später in der Implementierung bei Aktionen nicht auf einzelne Berechtigung prüfen muss, sondern einfach auf Gruppenzugehörigkeit. Da die Anzahl der Gruppen deutlich niedriger sein wird als die Anzahl an Berechtigungen, erhoffe ich mir schlicht weniger Tipparbeit für die verschiedenen Abfragen.


Wie gesagt, die ganze Idee ist keine halbe Stunde alt. Ich habe mir noch keine Gedanken zu einem Nachrichten-System gemacht. Ob man das einfach in der Form "Sender-ID - Empfänger-ID - Nachricht - Datum - (gelesen)" speichert? Und auch werden keine Informationen darüber gespeichert, in welcher Reihenfolge zum Beispiel die Foren gelistet werden. Ich bin mir noch nicht sicher, ob ich für die ganzen "Aussehens-Angelegenheiten" extra Tabellen anlegen soll, oder es in den "Logiktabellen" unterbringen soll. Dieselbe Unsicherheit habe ich bei Spielchen wie "Wer ist online?". Ob ich da bei jeder Benutzeraktivität die user-Tabelle aktualisiere und damit den Online-Status speichere oder auch wieder eine kleine Tabelle eigens dazu.

Habt ihr Vorschläge dazu?

[DMW007]

Bitte ignoriert vorerst die Datentypen für Zeitangaben. Ich möchte es anfangs völlig stressfrei zwischen SQLite und MySQL wechseln können.

SQLite unterstützt zwar keinen Typ wie DateTime, aber du kannst alternativ auch einfach ein Unix-Timestamp verwenden, das wird einfach als unsigned Integer gespeichert. Formatieren musst du ohnehin beide Typen, da auch DateTime unter MySQL im englischen Format gespeichert wird. Das will man in der Regel auf Deutschen Seiten nicht haben, weil Montag und Tag vertauscht sind, das verwirrt viele. Letztendlich ist es eher Geschmackssache, ob was von beidem man verwendet.

Aber warum muss es denn überhaupt SQLite sein? Willst du extrem flexibel sein und die Datenbank in einer Versionsverwaltung oder ähnlichem auf verschiedenen Systemen nutzen? Ansonsten würde ich einfach Xampp installieren. Da hast du alles was du brauchst im Bundle ohne Konfigurationsarbeit. Ist mit ein paar Klicks installiert, optimal als Entwicklungsumgebungen.

Diese Tabelle soll nicht nur Threads speichern können, sondern auch die Antworten darauf. parent_id = NULL bedeutet Thread, alles andere Antwort.

Nette Idee, würde ich aber hier nicht machen. Bei den Foren macht das Sinn, weil die Unterforen ja die gleiche Datenstruktur haben, nur eben mit Id des Eltern-Forums. In der Thread-Tabelle wirst du aber noch weitere Meta-Informationen unterbringen wie etwa ob der Thread geschlossen ist oder wie wie oft er aufgerufen wird. Je nachdem welchen Funktionsumfang du unter "Minimalistisch" verstehst kommen da auch noch Dinge wie z.B. das Themenpräfix hinein.

Aus Performance-Gründen sollten außerdem einige Zählerwerte dort gecached werden. Auf der Startseite wirst du vermutlich die Foren auflisten und ähnlich wie auf U-Labs weitere Informationen, etwa die Anzahl der Themen und Antworten. Wenn der Datenbankserver das bei jedem Aufruf erst berechnen muss entsteht eine Mehrbelastung, die zusammen mit den Nutzerzahlen exponentiell steigt. Aus dem Grund macht man in der Thread-Tabelle z.B. eine Spalte replys_count und wenn eine Antwort geschrieben wird inkrementiert man diesen Wert.

Auf der Startseite nutzt du dann einfach diesen Wert, anstatt den Datenbankserver mit Joins zu ärgern in denen er die Anzahl der Antworten zählen muss. Oder auch in der Themen-Liste innerhalb der Foren, dort wird das ja in der Regel auch gebraucht. Wenn du mit diesen Daten eine Tabelle für beides verwendest, verschwendest du Ressourcen.

Aus dem Stehgreif gesagt würde ich das so machen:

CREATE TABLE thread(

    thread_id,

    forum_id,

    user_id,

    title, 

    content,

    created,

    is_open,

    replys_count,

    views_count

);



CREATE TABLE post(

    post_id,

    thread_id,

    user_id,

    content,

    created

);

Die Tipparbeit der Datentypen und Relationen spare ich mir an dieser Stelle mal. Darum gehts hier ja nicht und das sollte sich anhand der Spaltennamen ohnehin ergeben. Die Titel-Spalte habe ich bei den Beiträgen bewusst weggelassen, da sie meiner Ansicht nach keinen Sinn macht. vBulletin macht das zwar tatsächlich so wegen der hybriden Darstellung. Mit anderen Worten: Foren auf dem Niveau von vor 15+ Jahren. Da werden die Beiträge nicht Linear dargestellt wie es auf U-Labs standardmäßig der Fall ist sondern du hast eine hierarchische Struktur:

Was haltet ihr von Windows 10?
-- Mangelhafter Datenschutz
-- Lauter Probleme beim Upgraden
-- Sehr innovativ
---- Sieht doch aus wie Windows 8 nur mit automatischem Startmenü

Das bildet sich dann z.B. mit dem Antwortbutton. Sprich jemand schreibt den Beitrag mit dem Titel 'Sehr innovativ' und 'Sieht doch aus wie Windows 8 nur mit automatischem Startmenü' wäre dann eine Antwort darauf. Halte ich für überholt. Sinnvoller finde ich es, Antworten auf Beiträge optisch als solche hervorzuheben. Sprich z.B. direkt unter dem Beitrag darstellen und etwas einrücken. Würde ich aber auch nur über eine Ebene machen, sonst sieht das nachher grauenhaft aus und wird insbesonders mit einer hohen Anzahl an Antworten sehr übersichtlich.

Hat auch den Nebeneffekt, dass es keine Probleme mit Fullquotes gibt. Ich finde es immer extrem nervig, wenn ich z.B. beim recherchieren auf Foren stoße und da dann erst mal mein Mausrad auslasten kann, weil die eigentliche antwort erst nach 10 ineinander verschachtelten Quotes sichtbar ist... Ich möchte in U-Labs langfristig auch eine Möglichkeit zum vernünftigen Antworten auf einzelne Beiträge einbauen. Nur ist mit der vB-Struktur etwas schwierig. Zumal ja auch bereits viel Content existiert, der nach Möglichkeit in das neue System eingepflegt werden soll.

Btw. hast du noch eine Spalte vergessen die angibt, ob der Thread geöffnet oder geschlossen ist, die habe ich auch mit eingebaut.

Ich brauche hier nicht wirklich eine Art Wiederherstellungsmöglichkeit, wobei ich das Löschen nur vortäusche, indem ich zum Beispiel eine Spalte deleted einführe und diese dann auf true setze.

Also aus der Praxis kann ich dir sagen, dass das schon Sinn macht. Etwa für den Fall, dass versehentlich etwas gelöscht wurde. Oder ein Moderator löscht aus welchem Grund auch immer einen Beitrag der nicht gelöscht werden soll. Beispielsweise weil er neu ist, sich verklickt hat oder aus welchem Grund auch immer. Dafür bietet es sich an, standardmäßig nicht hart zu löschen. Dafür dann automatisiert regelmäßig alle gelöschten Beiträge aus der Datenbank entfernen, die z.B. länger als 3 Monate alt sind. So wie du sagst geht es dir aber eher um den Lerneffekt, daher dürfte das für dich keine Rolle spielen. Sei nur mal als informelle Randanmerkung erwähnt.

Habe es in diesem ersten Ansatz bei avatarurl und signature belassen.

Ich würde es vermeiden, irgendwo vollständige URLs zu speichern. Man möchte ja so wenig redundante Informationen wie möglich haben und möglichst flexibel sein. Ich würde viel eher eine Avatar-Revision vergeben. Also eine fortlaufende Nummer. 0 steht für kein Avatar, alle Werte darüber sind die Version des Avatars. Heißt wenn ich mein erstes Avatar hochlade lautet die Revision = 1, ändere ich das Bild steht sie auf 2 usw. Dieser sollte im Pfad oder der Namenskonvention für Avatare untergebracht werden. Beispielsweise {userId}_{revisid.

Hat sich als beste Lösung erwiesen, da du so die Caching-Lebensdauer für Grafiken auf nahezu unendlich stellen kannst. Trotzdem wird ein geändertes Avatar sofort angezeigt, weil es ja einen anderen Dateinamen hat: Das alte heißt z.B. 1_1.png, das neue 1_2.png. Ein alternativer Workaround wäre ein Versionsparameter, etwa das Timestamp an dem das Avatar hochgeladen wurde: 1.png?d=1441292006 Ist aber nur die zweitbeste Lösung, weil manche Browser dann gar nicht cachen. Somit wird etwas Bandbreite verschwendet weil das Bild jedes mal übertragen wird.

Aber wir schweifen ab: Im Idealfall hast du EINE zentral definierte URL, die systemweit als Basis-URL genutzt wird (statische Daten z.B. auf cookiefreie Comains oder CDNs auszulagern lassen wir hier bewusst mal außen vor, das wäre hier wohl definitiv zu viel des Guten). Mit der Avatar-Revision hast du dann irgendwo eine Funktion die dir daraus eine absolute URL wie http://u-labs.de/avatars/1337_1.png erzeugt. Relative gehen natürlich grundsätzlich auch. Mit absoluten hast du aber generell weniger Probleme, vor allem wenn später z.B. noch SEO-URLs dazu kommen, wodurch der Webserver dann in einem Verzeichnis suchen könnte das es gar nicht gibt.

Für den Anfang dachte ich an drei Gruppen: Registrierte, Moderatoren, Administratoren.

Eine Gruppe für gesperrte Nutzer sollte noch dazu. Darin lässt sich dann festlegen, dass diese bestimmte Funktionen wie z.B. Posten oder Nachrichten versenden nicht verwenden dürfen. Sinnvoll finde ich weitere Gruppe für nicht angemeldete User. Beispielsweise könnte man die in bestimmten Foren wie Support posten. Aber auch wenn man das nicht möchte, kommt dadurch Konsistenz in dein Rechtesystem und du bist flexibel.

Sinn der ganzen Gruppen-Berechtigungssache soll sein, dass ich später in der Implementierung bei Aktionen nicht auf einzelne Berechtigung prüfen muss, sondern einfach auf Gruppenzugehörigkeit.

Naja das hat mit deinem genannten Problem wenig zutun. Gruppen setzt man dafür ein, um Rechte gebündelt möglichst einfach auf mehrere Benutzer anwenden zu können. Die Abfragen ob der User dies und jenes darf brauchst du so oder so, egal ob mit User- oder Gruppenspezifischen Rechten. Die Logik ist immer die gleiche, nur die Quelle der Berechtigungen eine andere. Ich würde mir für die Berechtigungen einfach einen Wrapper schreiben, mit dem du dann auf die Rechte nach dem Schema user.CanPost() zugreifen kannst.

Ich habe mir noch keine Gedanken zu einem Nachrichten-System gemacht. Ob man das einfach in der Form "Sender-ID - Empfänger-ID - Nachricht - Datum - (gelesen)" speichert?

Das kommt drauf an. Für ein simples Nachrichtensystem reicht das im wesentlichen schon aus. Ich persönlich finde solche Systeme aber heutzutage überholt. Besser ist etwas im Konversations-Stil wie man es von Telegram, TextSecure und anderen Messengern her kennt. Sprich pro Thema wird eine Konversation erstellt, die beliebig viele Nachrichten enthalten kann. So ist der Gesprächsverlauf wesentlich besser ersichtlich. In diesem Fall würde man das ganze dann ähnlich wie Themen und Beiträge in zwei Tabellen aufsplitten: Eine Tabelle für die Konversationen und eine für deren Nachrichten.

Hierbei gehts aber wirklich nur um Usability. Dir geht es ja offensichtlich primär ums Lernen und weniger ums Ergebnis. Unter dem Hintergrund kannst du das Nachrichtensystem im Prinzip auch ganz weglassen, weil du da nichts neues entwickelst. Wie schon gesagt hast du die gleiche Struktur wie bei Themen und Antworten. Machst du es auf die klassische Variante wie du beschrieben hast ist es sogar noch simpler. Dort hast du ja nicht mal CRUD sondern erzeugst mit einer Nachricht einfach einen Datensatz und Benachrichtigung, der kann beantwortet werden was wiederum einen Datensatz erzeugt, das wars.

Und auch werden keine Informationen darüber gespeichert, in welcher Reihenfolge zum Beispiel die Foren gelistet werden. Ich bin mir noch nicht sicher, ob ich für die ganzen "Aussehens-Angelegenheiten" extra Tabellen anlegen soll, oder es in den "Logiktabellen" unterbringen soll.

Für einfache benutzerdefinierte Sortierungen legt man normalerweise keine extra Tabelle an. Man muss die Dinge nicht komplizierter machen als nötig Einfach die jeweilige Tabelle wie z.B. von den Foren um eine Spalte display_position erweitern. Im Adminbereich fügst du dann zu jedem Forum ein Feld ein, in dem man eine Zahl eingeben kann. Das Forum mit der Nummer 1 steht dann an erster Stelle, danach kommt das mit der Nummer 2 usw.


Sonstige Anregungen

Sessions
Scheinst du noch nicht bedacht zu haben. Klar kann man das grundsätzlich auch ohne Datenbank machen, z.B. mit den PHP-Eigenen Sessions. Aber du willst ja z.B. eine Wer ist online Liste anfertigen. Da macht es Sinn, die Sessions auch gleich in die Datenbank zu verlagern. Deine Online-Liste wird dann auf Basis der Sessions erzeugt. Für eine vollwertige Liste wie in U-Labs musst du zudem für JEDEN Seitenaufruf eine Session erzeugen, um alle zu tracken. Wer nicht angemeldet ist hat dann halt eine Gast-Session. Was der Nutzer gerade macht gehört nicht in die User-Tabelle, sondern zur Session. Du kannst ja auch einen nicht angemeldeten User haben, der z.B. ein Thema ließt.

Passwörter
Da du in der User-Tabelle nur ein Passwort-Feld hast, planst du die wohl nur gehasht zu speichern. Bitte nicht machen, das ist unsicher. Rainbow Tables lassen grüßen... Passwörter sollten gesalzen gehasht werden, alles andere ist fahrlässig. Noch schlimmer ist nur die Zugangsdaten gleich im Klartext zu speichern wie z.B. Sony das gemacht hat, dann braucht man nämlich nicht mal Rainbow Tables

Namenskonventionen
Achte auf einheitliche Namenskonventionen. Du verwendest z.B. bei der User-Tabelle die Einzahl, aber die Gruppen sind im Plural benannt. Aus Erfahrung kann ich dir sagen: Du wirst dich später ärgern, weil du es ständig durcheinander bringst. Statt user schreibst du dann users oder group statt group. Gerade in komplexeren SQL-Abfragen mit mehreren Joins erkennt man so einen Fehler auch nicht unbedingt auf den ersten Blick. Ob du nun Singular oder Plural wählst ist eigentlich egal und eher Geschmackssache. Wichtig ist nur, dass es einheitlich ist. Dann weißt du beim schreiben automatisch, ob da ein s ans Ende muss oder eben nicht.

[Nuebel]

Vielen Dank für die ausführliche Antwort, DMW007.

Aber warum muss es denn überhaupt SQLite sein? Willst du extrem flexibel sein und die Datenbank in einer Versionsverwaltung oder ähnlichem auf verschiedenen Systemen nutzen? Ansonsten würde ich einfach Xampp installieren.

Ich habe vor die Sache in Python + Bottle zu realisieren und Python kommt von Haus aus mit SQLite-Unterstützung. Für MySQL müsste ich extra ein Package installieren, aber weil ich meine Python-Installation gerne "unbefleckt" lassen wollen würde, müsste ich mit virtualenv arbeiten. Ansich alles kein Problem, aber ich dachte, dass es SQLite auch tun würde, weil es ja nur ein Lernprojekt ist.
Xampp kenne ich und reizt mich auch ziemlich (deshalb auch im Eingangspost erwähnt, dass ich gerne unproblematisch zwischen SQLite und MySQL wechseln können möchte), so würde ich direkt meine PHP-Kenntnisse auffrischen und hoffentlich erweitern.

In der Thread-Tabelle wirst du aber noch weitere Meta-Informationen unterbringen wie etwa ob der Thread geschlossen ist oder wie wie oft er aufgerufen wird.

Ah, an diesen Informationen habe ich überhaupt nicht gedacht. Dann ist es tatsächlich nicht mehr sinnvoll, Themen und Beiträge in derselben Tabelle zu speichern.

Aus Performance-Gründen sollten außerdem einige Zählerwerte dort gecached werden.

Diesen Gedanken hatte ich auch, habe es aber mit einem Schulterzucken und ein "Ach, das wird schon nicht so ins Gewicht fallen" abgetan. "Nur ein Lernprojekt" kann ich aber schlecht jetzt als Ausrede für Alles nehmen. Werde es übernehmen.

Würde ich aber auch nur über eine Ebene machen, sonst sieht das nachher grauenhaft aus und wird insbesonders mit einer hohen Anzahl an Antworten sehr übersichtlich.

Ich habe mich da ein wenig von diesen QA-Seiten wie StackOverflow inspirieren lassen. Dort gibt es quasi zwei Arten von Antworten. Kleine Einzeiler oder kurze Fragen werden direkt unter dem jeweiligen Beitrag angezeigt. Anders als die "richtigen" Antworten. Gilt das als 2 Ebenen? Ansonsten stimme ich dir voll zu. Es ist halt nur etwas reizvoll, theoretisch in der Lage zu sein, beliebig viele Ebenen von Antworten zuzulassen.

Ich würde es vermeiden, irgendwo vollständige URLs zu speichern.

Mit avatarurl dachte ich an externe URLs. Dass ich die Möglichkeit biete, Avatars auf meinem Server hochzuladen, habe ich noch nicht bedacht. Mir gefällt dass systematische Benennen von Dingen aber , also werde ich deinen Vorschlag dankend übernehmen.

Eine Gruppe für gesperrte Nutzer sollte noch dazu. Darin lässt sich dann festlegen, dass diese bestimmte Funktionen wie z.B. Posten oder Nachrichten versenden nicht verwenden dürfen. Sinnvoll finde ich weitere Gruppe für nicht angemeldete User. Beispielsweise könnte man die in bestimmten Foren wie Support posten. Aber auch wenn man das nicht möchte, kommt dadurch Konsistenz in dein Rechtesystem und du bist flexibel.

Gesperrte Benutzer würde ich so behandeln wie nicht registrierte Nutzer. Sie sind in gar keiner Gruppe enthalten und haben dadurch auch die wenigsten Berechtigungen.
Wie erkenne ich nicht angemeldete Benutzer, die aber registriert sind? Wie gesagt, ich habe immer einen Bogen um Webentwicklung gemacht und kenne viele Möglichkeiten nicht. So ganz naiv würde ich das wohl mit Cookies machen. Was ist das in diesem Fall "best practice"?

Die Abfragen ob der User dies und jenes darf brauchst du so oder so, egal ob mit User- oder Gruppenspezifischen Rechten.

Ja, stimmt, du hast Recht. Ich hatte den Denkfehler, dass ich außer Acht gelassen habe, dass es nicht ausreicht, die Gruppenzugehörigkeit eines Benutzers abzufragen, sondern ich auch tatsächlich abfragen muss, ob die Gruppe diese oder jene Berechtigung überhaupt hat. Einen Wrapper wollte ich sowieso schreiben, ORM-mäßig mit bisschen Extra.

So ist der Gesprächsverlauf wesentlich besser ersichtlich. In diesem Fall würde man das ganze dann ähnlich wie Themen und Beiträge in zwei Tabellen aufsplitten: Eine Tabelle für die Konversationen und eine für deren Nachrichten.

Die genannte Ähnlichkeit mit der Themen- und Beiträge-Tabelle würde mich stören. Das wäre mir ein Dorn im Auge, weil ich da Redundanzen (in der Struktur) sehen würde, wo vielleicht gar keine sind, bzw. ruhig "übersehen werden dürfen".
Würde ich ein Nachrichtensystem in diesem Konversationsstil anstreben (und das möchte ich jetzt tatsächlich; danke für diese Idee), würde ich die Themen- und Beiträge-Tabellen für beides, also auch für das Nachrichtensystem, nutzen und die von dir genannten Metainformationen, die zu Themen gehören, dann in eine eigene Tabelle auslagern. Wäre dann in den Abfragen natürlich aber ein JOIN mehr.

Für einfache benutzerdefinierte Sortierungen legt man normalerweise keine extra Tabelle an. Man muss die Dinge nicht komplizierter machen als nötig Einfach die jeweilige Tabelle wie z.B. von den Foren um eine Spalte display_position erweitern.

Wie auch im Absatz davor, würde mich das stören, ein Spalte display_position zu haben, weil sie für mich logisch nicht dazu passt. Aber der Perfomance zur Liebe muss ich hier wohl nachgeben. :/ Hunderte Tabellen will ich ja auch nicht unbedingt haben.

Sonstige Anregungen

Ja, ich hätte die Passwörter wirklich nur gehasht gespeichert. Hatte wieder den Monolog, in dem die Ausrede "ist ja nur ein Lernprojekt" gefallen ist.

Bezüglich der Namenskonventionen gebe ich dir vollkommen Recht. Groups ist die einzige Tabelle, die aus dem Rahmen fällt, weil "group" zumindest bei SQLite ein unzulässiger Name ist und für einen Syntax-Error sorgt (wenn man alles ohne Anführungsstriche schreibt, wie ich während des kleinen Entwurfs). Habe ich dann aber wohl zu schnell vergessen, sodass ich die anderen Tabellen im Singular ließ.

Ich habe da noch eine ganze Menge an Ideen, die zwar nicht innovativ und ziemlich verbreitet sind, aber über dessen Realisierung ich mir aber noch nie Gedanken gemacht habe.
Ich werde das bereits Geplante sobald wie möglich umsetzen und dann immer mal wieder erweitern. Um eine Historie bearbeiter Beiträge zum Beispiel, ein Danke- und Reportsystem. Die Möglichkeiten sind ja fast grenzenlos.

[DMW007]

Ich habe mich da ein wenig von diesen QA-Seiten wie StackOverflow inspirieren lassen. Dort gibt es quasi zwei Arten von Antworten. Kleine Einzeiler oder kurze Fragen werden direkt unter dem jeweiligen Beitrag angezeigt. Anders als die "richtigen" Antworten. Gilt das als 2 Ebenen? Ansonsten stimme ich dir voll zu. Es ist halt nur etwas reizvoll, theoretisch in der Lage zu sein, beliebig viele Ebenen von Antworten zuzulassen.

Was du meinst sind Kommentare. Dort dienen sie dazu, Nachfragen und Anmerkungen von den Antworten abzugrenzen. Sprich wenn z.B. jemand fragt wie er eine Variable in der Datenbank speicherst, und du antwortest mit so was wie dem hier

mysqli_query('INSERT INTO blubb SET content = "' . $_POST['content'] . '" WHERE id = ' . $_GET['id']); 

würde ich als Kommentar schreiben, dass das keine so gute Idee ist und man da unbedingt ein mysqli_escape_string() um die Usereingaben von außen machen sollte. Dann solltest du das zur Kenntnis nehmen und die Antwort dementsprechend überarbeiten. Das ist der Stil und Sinn in SO: Man Diskutiert gemeinsam über die jeweilige Antwort, und begründete Kritik in den Kommentaren sollte der Autor der Antwort nutzen, um dessen Qualität zu verbessern.

Etwas nach dem Prinzip meinte ich. Allerdings würde ich im Sinne einer Community-Software die Kommentare als vollwertige Beiträge betrachten und auch so umsetzen. Denn gerade in Diskussionen wird ja nicht das QA-Prinzip verfolgt. Da soll die Funktion dann eher dazu dienen, um auf den Beitrag eines anderen Nutzers einzugehen und dies klar zu verdeutlichen. Beispiel: Wir haben ein Diskussionsthema über Atomkraft. Einer antwortet, dass Atomkraft voll umweltfreundlich und ungefährlich ist. Wenn ich auf diese Antwort nun Bezug nehmen möchte um etwa zu sagen, dass er vergisst, wie viel hochgiftiger Müll entsteht für den es keine Lösung gibt, würde ich das nicht als generelle Antwort auf das Diskussionsthema schreiben, sondern als Antwort auf seinen Beitrag.

Sprich von der Struktur her sieht das dann so aus:

- Diskussion: Atomkraft
-- Antwort: Atomkraft voll umweltfreundlich
---- Meine Reaktion: Nicht wirklich, was ist mit dem Müll?

Die Beschränkung auf 2 Ebenen aus dem Grund, damit nachher nicht so etwas herauskommt:

- Diskussion: Atomkraft
-- Antwort: Atomkraft ist voll umweltfreundlich
---- A: Nicht wirklich, was ist mit dem Müll?
----- B: Das bunkern wir einfach ein
------ A: In Salzbunker? Toll, die werden irgendwann undicht und wir haben die Sauerei!
------- B: Äääh ok, dann recyceln wir halt einfach
-------- A: Zu teuer, dann kostet Atomstrom mehr als Ökostrom!

Wenn jetzt dazwischen noch jemand auf die ursprüngliche Antwort postet, haben wir das perfekte optische durcheinander

Daher insgesamt maximal zwei Eben:

- Diskussion: Umweltfreundlichkeit von Atomkraft
-- Antwort: Atomkraft ist voll umweltfreundlich weil kein CO2 entsteht
---- A: Nicht wirklich, was ist mit dem Müll?
---- B: Das bunkern wir einfach ein
---- A: In Salzbunker? Toll, die werden irgendwann undicht und wir haben die Sauerei!
---- B: Äääh ok, dann recyceln wir halt einfach
---- A: Zu teuer, dann kostet Atomstrom mehr als Ökostrom!

Innerhalb einer Antwort kann man dann ggf. auch mit @username auf einzelne verweisen, falls dort später mehr als zwei Leute miteinander diskutieren. Bleibt trotzdem übersichtlich, weil sich die Diskussion unter einer Antwort ja immer nur auf die eine Antwort bezieht. In meinem Beispiel auf die Aussage, dass Atomkraft umweltfreundlich ist weil kein CO2 entsteht. Wenn das nun eine Frage ist, dann kann man natürlich wie bei klassischen AQ-Seiten die Antworten auf eine Antwort als Kommentare betrachten wie im Beispiel oben.

Mit avatarurl dachte ich an externe URL

Ich HASSE externe Inhalte, weil man zu 100% vom Bereitsteller abhängig ist und keine vernünftige Kontrolle darüber hat. Gerade bei Bildern ist das Furchtbar:

• Ein Scherzkeks fügt ein Link zu seinem Server ein, den er so konfiguriert, dass eine gefakte Htaccess-Abfrage erscheint, die aussieht als würde sie von deiner Seite kommen und zur Eingabe der Logindaten auffordert die er abfängt - Fallen erfahrungsgemäß nicht wenige drauf rein. Stell dir vor das passiert mit einem Account wie meinem mit über 3000 Beiträge, da erscheint das Ding bald in allen Themen
• Da knallt dir einer sein 5MB Gif-Avatar rein, und die User wundern sich darum ihr Traffickontingent so schnell weg ist und deine Seite ewig lädt
• Der Hoster auf dem das Bild liegt ist überlastet und lädt ewig
• Das Bild wird - aus welchem Grund auch immer - gelöscht
• Der Hoster macht dicht (Erst im Januar mit einem großen passiert: Imagebanana)

Aus diesen Gründen habe ich damals auch U-IMG ins leben gerufen. Derzeit sind neben U-IMG nur ein paar große und zuverlässige Hoster wie abload erlaubt. Das ist aber auch nur Semi-Optimal. Imagebanana galt bis zu seinem Ende auch als zuverlässiger Hoster. Und wenn du dir alte U-Labs Themen anschaust wirst du im einen oder anderen auch tote Links auf bekannten Hostern finden. Beispielsweise weil sich das Bild in einem Hide-Tag befindet und der Thread nicht all zu häufig von Usern aufgerufen wird die den Hide-Tag sehen können. Daher möglichst alles auf die eigenen Server und es gibt am wenigsten Probleme.

Gesperrte Benutzer würde ich so behandeln wie nicht registrierte Nutzer.

Das würde ich aus verschiedenen Gründen nicht so machen. Fängt dabei an, dass du gesperrte User sicher irgendwie als solche darstellen willst (z.B. Rot durchstreichen wie bei uns). Diese Formatierungen wickelt man normalerweise über die Gruppen ab. Dann hast du wieder inkonsistenzen. Hört bei zusätzlichen Datenbankabfragen bzw. Joins auf, da du für die Sperren später eine eigene Tabelle haben wirst, wo auch die ganzen Meta-Daten wie z.B. Sperrgrund und Dauer/Zeit drin sind. Um festzustellen ob der User gesperrt ist musst du dann jedes mal die Tabelle abfragen, ob da ein entsprechender Eintrag vorhanden ist. Oder denk nur mal daran, du willst Gastbeiträge erlauben. Für Gesperrte User will man das ja dann auf keinen Fall auch, sonst wäre die Sperre sinnfrei.

Ohne Romane zu schreiben: Es ist suboptimal. Je nachdem was du noch so alles einbaust kann das an genug anderen Stellen für potenzielle Probleme sorgen. Außerdem finde ich das logisch schon wesentlich schlüssiger wenn ich da eine Abfrage habe nach dem Schema if(user.IsBanned) als wie wenn du ständig prüfst ob der User Gast ist und dann ob er gebannt ist oder nicht. Letzteres wäre ja z.B. nötg um zu entscheiden, ob eine Sperrbegründung angezeigt wird.

Wie erkenne ich nicht angemeldete Benutzer, die aber registriert sind?

Zuverlässig überhaupt nicht. Du kannst hartnäckige Cookies setzen (z.B. über Flash), die sich schwieriger löschen lassen. Ich würde mich darauf aber gar nicht zu sehr versteifen, höchstens zur Erkennung von Doppelaccounts, und auch nur da wenn es Sinn macht (z.B. du bietest etwas wie die Account-Boerse, wo die User gerne mal mehr als 1 Account registrieren um mehr laden zu können). Aber ansonsten: Wenn der User bestimmte Funktionen wie das Posten nutzen möchte, muss er sich einloggen. Das ist auf jeder Seite mit Authentifizierung so.

Was natürlich Sinn macht ist dem User anzubieten eine persistente Session zu erstellen. Sprich er bleibt eingeloggt bis er sich manuell ausloggt und wird nicht automatisch nach X Tagen ausgeloggt. Sicherheitstechnisch nicht ganz so toll aber so lange es nicht gerade um Onlinebanking oder ähnliches geht ein akzeptabler Kompromiss aus Sicherheit und Usability. Die Wahrscheinlichkeit, dass deine User schwache Passwörter verwenden ist in der Praxis wesentlich höher, wie dass deren Sitzungen durch Session Hijacking gekapert werden

So ganz naiv würde ich das wohl mit Cookies machen. Was ist das in diesem Fall "best practice"?

Jop, die Sessions selbst werden über Cookies abgewickelt: User loggt sich ein, Server generiert einen Sessionhash, der wird beim Client als Cookie gesetzt. Bei jeder Anfrage prüft der Server zu Beginn, ob ein Sessioncookie vorhanden ist, und wenn ja ob er valide ist. Wenn das der Fall ist, wird der zur Sitzung gehörtende Nutzer als Eingeloggt betrachtet. Nach dem Prinzip funktioniert jede Webanwendung mit Login: U-Labs, Google, Facebook usw.

[QUOTE=Nuebel;414876]Würde ich ein Nachrichtensystem in diesem Konversationsstil anstreben (und das möchte ich jetzt tatsächlich; danke für diese Idee), würde ich die Themen- und Beiträge-Tabellen für beides, also auch für das Nachrichtensystem, nutzen und die von dir genannten Metainformationen, die zu Themen gehören, dann in eine eigene Tabelle auslagern.

Wordpress verfährt nach einem ähnlichen Schema, dort ist alles ein Post. Alles was dazu gehört wird als Meta-Daten gespeichert. Aus dem Grund hat WP für seinen Funktionsumfang extrem wenige Tabellen (Glaube ~10). Bringt aber auch seine Nachteile mit sich, vor allem hinsichtlich ordentlicher Abfragen und Performance. Du hast wenige Tabellen, die langfristig riesig werden können. Indizes zu setzen kann aufgrund der hohen Anzahl verschiedenster Anfragen schwer werden. Ich bin daher kein Fan davon, aber prinzipiell kann man das machen ja.

WP betreibt das ja zugunsten von bestmöglichster Flexibilität in Extremform. Du kannst z.B. Einen Array als Meta-Daten speichern. Der wird dann intern in JSON umgewandelt. Schön einfach und abstrakt für den Plugin-Entwickler, aber wenn ich diesen Array nun filtern möchte wirds ekelig. Aber das brauchst du ja nicht, so schlimm wird es in deinem Fall daher nicht. Du hast dann eine Content-Tabelle in der nur die globalen Gemeinsamkeiten wie Autor, Inhalt, Erstellungsdatum präsent sind. Und dann halt fallspezifisch eine Tabelle mit der ContentId und den zusätzlichen Attributen, bei PNs etwa Lesestatus.

Wie auch im Absatz davor, würde mich das stören, ein Spalte display_position zu haben, weil sie für mich logisch nicht dazu passt.

Warum soll sie logisch denn nicht dazu passen? Du hast einen Datensatz der in einer benutzerdefinierten Position angezeigt werden soll. Da diese keinem logischen Schemata folgt wie z.B. Alphabetisch, muss die Reihenfolge gespeichert werden. Das gehört zum Datensatz selber und daher in die gleiche Tabelle, genau wie z.B. die eMail in die User-Tabelle gehört und nicht in eine eigene wie user_mail. Das geht natürlich, macht aber keinen Sinn. Der Zweck hinter verschiedenen Tabellen ist ja, Datensätze darin abzubilden.

Aufsplitten macht nur dann Sinn, wenn du verschiedene Datensätze hast, die sich dadurch wiederholen würden. Ein gutes Beispiel dafür ist die User-Tabelle. Du könntest in die Post-Tabelle alle Userinformationen dazunehmen wie eMail, Passwort, Salt etc. Die würden sich dann aber bei JEDEM Post wiederholen => Nicht gut, ein Fall für die 2. Normalform. Wenn du die nicht kennst und dich mit dem Thema etwas schwer tust, würde ich mir mal die Normalisierung anschauen. Die macht letztendlich nichts anderes als Schrittweise über die Normalformen alle Redundanzen aus der Struktur zu entfernen.

Ich werde das bereits Geplante sobald wie möglich umsetzen und dann immer mal wieder erweitern. Um eine Historie bearbeiter Beiträge zum Beispiel, ein Danke- und Reportsystem. Die Möglichkeiten sind ja fast grenzenlos.

Jop, da kommst du vom Hundertstel ins Tausendstel Hatte das auch schon im Kopf und noch mehr. Eine Funktion zur Auszeichnung der besten Antwort bei Fragen könnte man beispielsweise ebenfalls einbauen. Die Antwort wird dann oben angezeigt. Da gibts noch mehr wenn man gründlich überlegt. Aber da du von "minimalistischem Forensystem" gesprochen hattest habe ich das bewusst weggelassen. Mit den Ideen aus diesem Thread solltest du aber denke ich erst mal ganz gut ausgelastet sein Und falls die WIRKLICH mal ausgehen sollten bau ein Pluginsystem ein. Das ist immer gut, gerade für optionale Funktionen, und kann je nach Umfang durchaus noch mal eine Herausforderung für sich darstellen.

[Nuebel]

Wieder einmal vielen Dank für deinen Beitrag.

Ich habe heute schon ein bisschen quick'n'dirty-Code geschrieben. In PHP statt Python.
Wie macht man das in PHP am besten mit der Datenbankverbindung? Ich habe eine Klasse im Singleton-Muster geschrieben dafür. Brauche ja nur eine Verbindung zum Datenbankserver.

Spoiler:

// dbconnection.php

class DBConnection {
    private static $INSTANCE = null;

    public static function getInstance() {
        if (null === self::$INSTANCE) {
            self::$INSTANCE = new self(
                "*****", "*****", "*****", "*****"
            );
        }
        return self::$INSTANCE;
    }

    /* ################################################## */

    private $conn = null;


    private function __construct($host, $user, $pass, $db) {
        $this->conn = new mysqli($host, $user, $pass, $db);
    }

    private function __clone() {}


    public function __destruct() {
        $this->conn->close();
    }


    public function query($query, $resultmode = MYSQLI_STORE_RESULT) {
        $result = $this->conn->query($query, $resultmode);
        if (FALSE === $result) {
            throw new Exception($this->conn->error);
        }
        return $result;
    }

    public function real_escape_string($escapestr) {
        return $this->conn->real_escape_string($escapestr);
    }

    public function connect_error() {
        return $this->conn->connect_error;
    }
} 

Und zusätzlich habe ich noch einen kleinen Ansatz, wie ich die Tabellen aus der Datenbank gerne im Code repräsentieren würde. Jeweils als Klassen. Hier mal anhand der users-Tabelle (ja, ich kommentiere gerne in deutsch ):

include_once "dbconnection.php";


class UsernameTakenException extends Exception {}
class UserNotFoundException extends Exception {}

/*
 * Tabelle: users
 * name         | Typ       | Anmerkung
 * -------------|-----------|----------------------------
 * id           | INTEGER   | PRIMARY KEY, AUTO_INCREMENT
 * name         | TEXT      | einmalig
 * password     | TEXT      | 
 * email        | TEXT      |
 * avatar       | TEXT      | Name: {uid}_{revid}.png
 * signature    | TEXT      |
 * registered   | INTEGER   | Unix-Timestamp
 * -------------|-----------|----------------------------
 */
class User {

    /*
     * Erstellt einen neuen Benutzer (auch in der Datenbank),
     * und gibt die Referenz auf die User-Instanz zurück.
     * Bei einem Fehler wird FALSE zurückgegeben.
     *
     * Name und E-Mail sollten escaped übergeben werden.
     * Das Passwort wird in Klartext übergeben. Hash und Salt
     * werden in dieser Funktion generiert.
     *
     * Wirft UsernameTakenException wenn der Name bereits 
     * von einem registrierten Benutzer verwendet wird.
     */
    public static function createNew($name,    $pass, $email) {
        $result = DBConnection::getInstance()->query(
            "SELECT name FROM users WHERE name = '$name';"
        );
        if ($result->num_rows > 0) {
            throw new UsernameTakenException(
                "Benutzername bereits vergeben: $name"
            );
        }
        $pass = password_hash($pass, PASSWORD_BCRYPT);
        $result = DBConnection::getInstance()->query(
            "INSERT INTO users (name, password, email, registered)" .
            "VALUES ('$name', '$pass', '$email', ".time().");"
        );
        return TRUE === $result ? self::getBy("name", $name) : FALSE;
    }

    /*
     * Liest einen User aus der Datenbank aus.
     * Es sollte möglichst eine Spalte ausgewählt werden, die
     * bei Abfrage nur einen Datensatz liefert. Hier: id oder name
     * 
     * Bei mehreren Ergebnisdatensätze wird der erste gewählt.
     * Bei Misserfolg wird UserNotFoundException geworfen.
     */
    public static function getBy($column, $value) {
        $result = DBConnection::getInstance()->query(
            "SELECT * FROM users WHERE $column = '$value';"
        );
        if ($result->num_rows == 0) {
            throw new UserNotFoundException(
                "Es konnte kein Benutzer mit $column='$value'" .
                " gefunden werden."
            );
        }
        return new self($result->fetch_assoc());
    }

    /* ############################################################ */

    /* Assoziatives Array, mit allen Spalten aus der Tabelle */
    private $data;
    /* Benutzerfelder, die geändert werden dürfen. */
    private $editable = array("password", "email", "avatar", "signature");


    private function __construct($data) {
        $this->data = $data;
    }

    private function __clone() {}


    public function getValue($column) {
        return $this->data[$column];
    }

    /*
     * Ändert den Wert eines Benutzerfeldes.
     * Bei Misserfolg oder Versuch der unerlaubten Änderung 
     * wird FALSE zurückgegeben. Ansonsten TRUE.
     *
     * Die Änderung wird sofort in die Datenbank übernommen.
     * newValue sollte escaped übergeben werden.
     */
    public function setValue($column, $newValue) {
        if (!in_array($column, $this->editable)) {
            return FALSE;
        }
        $result = DBConnection::getInstance()->query(
            "UPDATE users SET $column='$newValue' " .
            "WHERE id = '".$this->data["id"]."';"
        );
        if (TRUE === $result) {
            $this->data[$column] = $newValue;
            return TRUE;
        }
        return FALSE;
    }

    /* 
     * Überprüft ob das übergebene Klartext-Passwort mit dem
     * in der Datenbank gespeicherten Hash übereinstimmt.
     */
    public function checkPassword($password) {
        return password_verify($password, $this->data["password"]);
    }


    public function getGroups() {
        /* TODO */
    }

    public function isBanned() {
        /* TODO 
         * Etwa so:  
         * return $this->getGroups().contains(Groups.BANNED); 
         */
    }

    public function canDo($action) {
        /* TODO */
    }
} 

Bin mir aber noch nicht sicher, ob das alles so sinnvoll ist was ich da getan habe, wie ich im ersten Moment dachte.