Datenbank Design für einen Userserver

[gsh]

Hallo Leute,

ich bin gerade dabei eine Datenbank zu designen. Nun bin ich auf ein paar Fragen/Probleme gestoßen mit denen ich euch jetzt nerven werde :D

Anwendung:
Es gibt einen Service der eine ständige Verbindung zur Datenbank (zurzeit MySQL geplant) besitzt. Dieser Service hat dann so eine Art Chat Server Funktion. Es sollen also mehrere User in der Datenbank gespeichert werden die dann auch eine KontaktListe und so weiter bekommen sollen. Ich lege das ganze mal für 100.000 User aus.

Was ich bis jetzt mir überlegt habe:
Usertabelle:
(PrimärKey) UserID INT(10) Not Null Auto Inc
(PrimärKey) Username VARCHAR(100) Not Null
password VARCHAR(32) Not Null
email VARCHAR(100) Not Null
displayname VARCHAR(100)

In dieser Tabelle sollen die User gespeichert werden. UserID ist eine eindeutige ID für jeden User. Username ist der Anmeldenamen. Das Password wird als MD5 Hash gespeichert. Displayname sollte klar sein.

Avatartabelle:
(PrimärKey) UserID INT(10) Not Null
avatar BLOB

Die Avatare kommen in eine eigene Tabelle und werden als Bilder im BLOB Feld gespeichert.


So nun fangen meine Fragen an:
1) Welches Datenbanksystem verwende ich in diesem Fall am besten? MyISAM oder InnoDB?

2) Ich möchte noch zusätzliche Informationen zu jedem User speichern (Adresse, EMail, Geschlecht, ...) ist es besser diese in die Usertabelle hinzuzufügen oder sollte ich eine eigene Tabelle mit alle zusätzlichen Informationen machen die dann über die UserID verknüpft ist?

3) Wie kann ich die Kontaktliste am besten speichern? Ich habe mir überlegt eine Tabelle mit UserID und PartnerID. Aber angenommen ich habe 100.000 User jeder davon hat 10 Kontakte. Dann hätte diese Liste 1.000.000 Einträge. Ist das so denkbar oder gibt es eine viel bessere Lösung dafür?

ahm ja mehr fällt mir im Moment nicht ein ;)

[Der_Unwissende]

Hi,

erstmal eine Frage:

Zitat:

Zitat von gsh

Was ich bis jetzt mir überlegt habe:
Usertabelle:
(PrimärKey) UserID INT(10) Not Null Auto Inc
(PrimärKey) Username VARCHAR(100) Not Null
password VARCHAR(32) Not Null
email VARCHAR(100) Not Null
displayname VARCHAR(100)

Warum deklarierst Du hier einen zusammengesetzten Primary Key aus UserID und Username? Schon die ID identifiziert einen Benutzereindeutig, somit auch den Nutzernamen. Umgekehrt gilt das allerdings auch. Der Username muss schließlich im System eindeutig vergeben werden, entsprechend reicht dieser Name völlig aus um einen Nutzer eindeutig zu identifizieren. Wenn Du nicht auf ein O/R-Mapper setzt (oder ein anderes Tool/Framework/etc. einsetzt), welches eine künstliche ID bedingt, dann solltest Du die UserID einfach wegfallen lassen.

Zitat:

Zitat von gsh

So nun fangen meine Fragen an:
1) Welches Datenbanksystem verwende ich in diesem Fall am besten? MyISAM oder InnoDB?

Ich würde Dir zu InnoDB raten. Hier hast Du Transaktionen (quasi atomare Aktionen, werden ganz oder gar nicht ausgeführt) und referentielle Integrität. Damit hast Du zwei wichtige Vorteile eines DBMS, welche Dir bei MyISAM einfach fehlen. Da die dafür sorgen können, dass Du immer ein konsistentes System vorfindest, sollte man nicht unnötig darauf verzichten.

Zitat:

Zitat von gsh

2) Ich möchte noch zusätzliche Informationen zu jedem User speichern (Adresse, EMail, Geschlecht, ...) ist es besser diese in die Usertabelle hinzuzufügen oder sollte ich eine eigene Tabelle mit alle zusätzlichen Informationen machen die dann über die UserID verknüpft ist?

Schau Dir mal Datenbanken - Normalisierungen an. Du solltest die dritte Normalisierung anstreben und dann einfach mal schauen, da sollte dann klar sein was besser ist (und wenn Du Dir die Erklärungen dazu anschaust sicher auch das Warum!)

Zitat:

Zitat von gsh

3) Wie kann ich die Kontaktliste am besten speichern? Ich habe mir überlegt eine Tabelle mit UserID und PartnerID. Aber angenommen ich habe 100.000 User jeder davon hat 10 Kontakte. Dann hätte diese Liste 1.000.000 Einträge. Ist das so denkbar oder gibt es eine viel bessere Lösung dafür?

Wo siehst Du denn dort das Problem? Die 1.000.000 Einträge? Dass ist doch gar nicht so viel. Geh einfach mal davon aus, dass das alles nur als zwei Referenzen gespeichert wird. Gehen wir mal von 64 bittigen Referenzen und 1.048.576 Einträgen aus, dann sind das gerade mal 8 MByte (8 Byte * 1.048.576). Die kannst Du entsprechend schnell verarbeiten. Natürlich kommt hier noch etwas Overhead für die Struktur hinzu, je nachdem wie Du das ganze abspeicherst um eben schneller über die Datensätze iterieren zu können oder eine schnelle Suche zu ermöglichen. Trotzdem sollte dies schon einen groben Eindruck geben, wie wenig das wäre.
An sich ist das übrigens durchaus eine Lösung, die völlig ok ist.
Du hast ggf. allerdings etwas Redundanz, die sich vermeiden lässt. Hier musst Du schauen, ob die Relation immer bidirektional ist, also gilt immer, dass wenn User A Partner B hat, dann auch User B Partner A hat? Wenn dem so ist, dann solltest Du nicht beide Richtungen abspeichern (macht die Pflege schwerer wenn die Partnerschaft aufgehoben wird, dann musst Du halt unnötig viele Stellen korrigieren). Da solltest Du dann lieber sicherstellen, dass es immer nur in einer Richtung gespeichert wird und dann eben beim Suchen entsprechend berücksichtigen (natürlich kann man aus Perfomance - Gründen auch eine unnötige Redundanz hinnehmen).

Wie Du hier vielleicht schon ahnst, es gibt keine Patentlösung. Vieles hängt vom konkreten Anwendungsszenario ab. Da können dann völlig unterschiedliche Ansätze für die Optimierung zum Einsatz kommen.

Gruß,
Der Unwissende

[gsh]

Zitat:

Zitat von Der_Unwissende

Hi,

Hi und erstmal vielen Dank für deine ausführliche Antwort

Zitat:

Zitat von Der_Unwissende

erstmal eine Frage:

Zitat:

Zitat von gsh

Was ich bis jetzt mir überlegt habe:
Usertabelle:
(PrimärKey) UserID INT(10) Not Null Auto Inc
(PrimärKey) Username VARCHAR(100) Not Null
password VARCHAR(32) Not Null
email VARCHAR(100) Not Null
displayname VARCHAR(100)

Warum deklarierst Du hier einen zusammengesetzten Primary Key aus UserID und Username? Schon die ID identifiziert einen Benutzereindeutig, somit auch den Nutzernamen. Umgekehrt gilt das allerdings auch. Der Username muss schließlich im System eindeutig vergeben werden, entsprechend reicht dieser Name völlig aus um einen Nutzer eindeutig zu identifizieren. Wenn Du nicht auf ein O/R-Mapper setzt (oder ein anderes Tool/Framework/etc. einsetzt), welches eine künstliche ID bedingt, dann solltest Du die UserID einfach wegfallen lassen.

Die UserID und den Usernamen hab ich aus folgendem Grund: Die UserID ist die eindeutige ID für diesen User. Bei einer anderen Tabelle (z.b. Avatar) wird diese ID dann verwendet, um den jeweiligen Eintrag einem User zuordnen zu können. Den Usernamen will ich dafür nicht verwenden, da es 1. ein Name ist (Feld muss größer sein) und 2. vielleicht einmal von dem User oder von mir geändert werden kann.

Noch kurz warum ich zwei Primär Keys hab:
Ich denke, ein select sollte man immer nur auf einen identizierten Eintrag durchführen. Da sich der User über den Usernamen anmeldet, brauch ich den am Anfang und dann verwendet der Server nur noch die UserID zur Identifikation.

Zitat:

Zitat von Der_Unwissende

Zitat:

Zitat von gsh

So nun fangen meine Fragen an:
1) Welches Datenbanksystem verwende ich in diesem Fall am besten? MyISAM oder InnoDB?

Ich würde Dir zu InnoDB raten. Hier hast Du Transaktionen (quasi atomare Aktionen, werden ganz oder gar nicht ausgeführt) und referentielle Integrität. Damit hast Du zwei wichtige Vorteile eines DBMS, welche Dir bei MyISAM einfach fehlen. Da die dafür sorgen können, dass Du immer ein konsistentes System vorfindest, sollte man nicht unnötig darauf verzichten.

hmm ok hat InnoDB sonst irgendwelche Nachteile?
Funktionieren die Zeos überhaupt richtig mit den Transaktionen?

Zitat:

Zitat von Der_Unwissende

Zitat:

Zitat von gsh

2) Ich möchte noch zusätzliche Informationen zu jedem User speichern (Adresse, EMail, Geschlecht, ...) ist es besser diese in die Usertabelle hinzuzufügen oder sollte ich eine eigene Tabelle mit alle zusätzlichen Informationen machen die dann über die UserID verknüpft ist?

Schau Dir mal Datenbanken - Normalisierungen an. Du solltest die dritte Normalisierung anstreben und dann einfach mal schauen, da sollte dann klar sein was besser ist (und wenn Du Dir die Erklärungen dazu anschaust sicher auch das Warum!)

ok danke schau ich mir an

Zitat:

Zitat von Der_Unwissende

Zitat:

Zitat von gsh

3) Wie kann ich die Kontaktliste am besten speichern? Ich habe mir überlegt eine Tabelle mit UserID und PartnerID. Aber angenommen ich habe 100.000 User jeder davon hat 10 Kontakte. Dann hätte diese Liste 1.000.000 Einträge. Ist das so denkbar oder gibt es eine viel bessere Lösung dafür?

Wo siehst Du denn dort das Problem? Die 1.000.000 Einträge? Dass ist doch gar nicht so viel. Geh einfach mal davon aus, dass das alles nur als zwei Referenzen gespeichert wird. Gehen wir mal von 64 bittigen Referenzen und 1.048.576 Einträgen aus, dann sind das gerade mal 8 MByte (8 Byte * 1.048.576). Die kannst Du entsprechend schnell verarbeiten. Natürlich kommt hier noch etwas Overhead für die Struktur hinzu, je nachdem wie Du das ganze abspeicherst um eben schneller über die Datensätze iterieren zu können oder eine schnelle Suche zu ermöglichen. Trotzdem sollte dies schon einen groben Eindruck geben, wie wenig das wäre.
An sich ist das übrigens durchaus eine Lösung, die völlig ok ist.
Du hast ggf. allerdings etwas Redundanz, die sich vermeiden lässt. Hier musst Du schauen, ob die Relation immer bidirektional ist, also gilt immer, dass wenn User A Partner B hat, dann auch User B Partner A hat? Wenn dem so ist, dann solltest Du nicht beide Richtungen abspeichern (macht die Pflege schwerer wenn die Partnerschaft aufgehoben wird, dann musst Du halt unnötig viele Stellen korrigieren). Da solltest Du dann lieber sicherstellen, dass es immer nur in einer Richtung gespeichert wird und dann eben beim Suchen entsprechend berücksichtigen (natürlich kann man aus Perfomance - Gründen auch eine unnötige Redundanz hinnehmen

Ich war mir nicht ganz sicher, ob das überhaupt ein guter Lösungsansatz ist.
Ich glaub schon, dass ich für jeden Kontakt einen Eintrag benötige, da, nur weil Person A Kontakt B hat, noch lang nicht Person B die Person A als Kontakt haben muss.

Zitat:

Zitat von Der_Unwissende

Wie Du hier vielleicht schon ahnst, es gibt keine Patentlösung. Vieles hängt vom konkreten Anwendungsszenario ab. Da können dann völlig unterschiedliche Ansätze für die Optimierung zum Einsatz kommen.

Gruß,
Der Unwissende

jaja das ist doch immer so ;)

[Basilikum]

Zitat:

Zitat von gsh

Zitat:

Zitat von Der_Unwissende

erstmal eine Frage:

Zitat:

Zitat von gsh

Was ich bis jetzt mir überlegt habe:
Usertabelle:
(PrimärKey) UserID INT(10) Not Null Auto Inc
(PrimärKey) Username VARCHAR(100) Not Null
password VARCHAR(32) Not Null
email VARCHAR(100) Not Null
displayname VARCHAR(100)

Warum deklarierst Du hier einen zusammengesetzten Primary Key aus UserID und Username? Schon die ID identifiziert einen Benutzereindeutig, somit auch den Nutzernamen. Umgekehrt gilt das allerdings auch. Der Username muss schließlich im System eindeutig vergeben werden, entsprechend reicht dieser Name völlig aus um einen Nutzer eindeutig zu identifizieren. Wenn Du nicht auf ein O/R-Mapper setzt (oder ein anderes Tool/Framework/etc. einsetzt), welches eine künstliche ID bedingt, dann solltest Du die UserID einfach wegfallen lassen.

Die UserID und den Usernamen hab ich aus folgendem Grund: Die UserID ist die eindeutige ID für diesen User. Bei einer anderen Tabelle (z.b. Avatar) wird diese ID dann verwendet, um den jeweiligen Eintrag einem User zuordnen zu können. Den Usernamen will ich dafür nicht verwenden, da es 1. ein Name ist (Feld muss größer sein) und 2. vielleicht einmal von dem User oder von mir geändert werden kann.

Noch kurz warum ich zwei Primär Keys hab:
Ich denke, ein select sollte man immer nur auf einen identizierten Eintrag durchführen. Da sich der User über den Usernamen anmeldet, brauch ich den am Anfang und dann verwendet der Server nur noch die UserID zur Identifikation.

Als Primary Key würde ich immer einen "synthetischen" Key verwenden (synthetisch im Sinne von, hat keinen Zusammenhang mit der realen Welt), insbesondere dann, wenn aus anderen Tabellen per Foreign Key darauf referenziert werden soll (als Referenz ist ein Varchar natürlich sehr ungeeignet)... um die Eindeutigkeit des Username sicherzustellen, würde sich ein Unique Key gut eignen:

SQL-Code:

(Primary Key) UserID   INT(10)   Not Null Auto Inc
(Unique Key) Username VARCHAR(100) Not Null
password VARCHAR(32) Not Null
email VARCHAR(100) Not Null
displayname VARCHAR(100)

[sx2008]

Zitat:

Zitat von gsh

Noch kurz warum ich zwei Primär Keys hab:
Ich denke, ein select sollte man immer nur auf einen identizierten Eintrag durchführen. Da sich der User über den Usernamen anmeldet, brauch ich den am Anfang und dann verwendet der Server nur noch die UserID zur Identifikation.

Das ist so nicht richtig.
Dein Primärschlüsselfeld ist UserID; ein künstlicher Schlüssel.
Das ist so auch ganz in Ordnung.
Das Feld Username ist nun ein Ersatz- oder Surrogatschlüssel für den Primärschlüssel.
Username ist aber nicht Bestandteil des Primärschlüssels!!
Auf dem Feld Username sollte unbedingt ein Index liegen.
Dieser Index sollte UNIQUE sein; damit wird verhindert, dass es zwei oder mehr Datensätze mit dem gleichen Username geben kann.
Dennoch ist das Umbenennen des Usernamen problemlos möglich, weil abhängige Tabellen nicht betroffen sind.

Deine Usertabelle sollte auch noch ein Disabled-Feld enthalten.
Damit kann man User sperren, ohne den Datensatz löschen zu müssen.

[gsh]

Zitat:

Zitat von Basilikum

Als Primary Key würde ich IMMER einen "synthetischen" Key verwenden (synthetisch im Sinne von, hat keinen Zusammenhang mit der realen Welt), insbesondere dann, wenn aus anderen Tabellen per Foreign Key darauf referenziert werden soll (als Referenz ist ein Varchar natürlich sehr ungeeignet)... um die Eindeutigkeit des Username sicherzustellen, würde sich ein Unique Key gut eignen

ahm ja Datenbanken ist nicht mein spezial gebiet, also bitte verzeih mir das ich nicht alles verstanden habe.
Also versuch ich mal das zu wiederholen, dass ich es verstehe:
Also den Usernamen zu verwenden um in anderen Tabellen auf diesen Usereintrag zu referenzieren wäre ziemlich schlecht da es ein Varchar ist (Bin ich der gleichen Meinung). Bis jetzt hab ich immer nur Primary Keys verwendet aber jetzt hab ich auch herausgefunden, dass ich im MySQL Administrator auch andere Indexe angeben kann. Somit hab ich jetzt einen neuen Index mit der Indexart UNIQUE und dem IndexTyp BTREE (war default) für den username erstellt. Aber was ist jetzt der genaue Unterschied schließlich kann bei einem Primär Key auch nichts doppelt sein? Wird der username dann auch richtig indiziert damit das ganze Performant ist wenn ich darauf eine Abfrage dafür erstelle?

Als Primären Key würdest du also einen "künstlichen" Key verwenden der nirgendwo anderes verwendet wird oder? Aber was bringt der mir dann? Das mit den Forgeign Keys habe ich mit Google so verstanden, dass ich damit Spalten von zwei Tabellen verknüpfen kann. Aber wie kann ich das dann genau verwenden und auf was muss ich dabei beachten?

Fragen über Fragen.

[gsh]

Zitat:

Zitat von sx2008

Das ist so nicht richtig.
Dein Primärschlüsselfeld ist UserID; ein künstlicher Schlüssel.
Das ist so auch ganz in Ordnung.
Das Feld Username ist nun ein Ersatz- oder Surrogatschlüssel für den Primärschlüssel.
Username ist aber nicht Bestandteil des Primärschlüssels!!
Auf dem Feld Username sollte unbedingt ein Index liegen.
Dieser Index sollte UNIQUE sein; damit wird verhindert, dass es zwei oder mehr Datensätze mit dem gleichen Username geben kann.
Dennoch ist das Umbenennen des Usernamen problemlos möglich, weil abhängige Tabellen nicht betroffen sind.

Deine Usertabelle sollte auch noch ein Disabled-Feld enthalten.
Damit kann man User sperren, ohne den Datensatz löschen zu müssen.

Ah ok danke hab dies bereits auf Unique umgestellt (siehe oben)

Das Disabled Feld klingt gut ... Dafür sollte ich am besten ein Feld mit dem Typ BOOLEAN verwenden oder?

[Billa]

Besser einen Integer: Wer weiß, welchen "Zustand" deine Datensätze später noch so haben sollen...

also z.B.

0 = normal
1 = gesperrt
2 = abgelaufen usw. usw.

[mirage228]

Zitat:

Zitat von Billa

Besser einen Integer: Wer weiß, welchen "Zustand" deine Datensätze später noch so haben sollen...

also z.B.

0 = normal
1 = gesperrt
2 = abgelaufen usw. usw.

Wenn Du MySQL benutzt, wäre ggf. ja ENUM was für dich - das kannst Du dann auch als Integer zusätzlich noch ansteuern :)

[gsh]

Zitat:

Zitat von mirage228

Wenn Du MySQL benutzt, wäre ggf. ja ENUM was für dich - das kannst Du dann auch als Integer zusätzlich noch ansteuern :)

Zu Datenbank spezifisch will ich eigentlich nicht werden. Möglichweiße will ich ja irgendwann auf ein anderes DBMS umstellen

Zitat:

Zitat von Billa

Besser einen Integer: Wer weiß, welchen "Zustand" deine Datensätze später noch so haben sollen...

also z.B.

0 = normal
1 = gesperrt
2 = abgelaufen usw. usw.

ok dann werde es mit einem Integer machen