varchar oder text?

[dimitri]

Ich muss sagen, dass ich gerade etwas wütend bin, denn - das kannst du dir sicher vorstellen - das, was ich hier gerade anwende, ist das, was ich in meinem Studium gelernt habe. Ich frage mich gerade, wozu.

Gemeinhin zu wenig Praxisbezug/Erfahrung der Dozenten. Darum werden z.B. auch immer wieder Trigger als Allheilmittel für diverse fachliche Anforderungen verkauft.

die user-id als PK: nein

Richtig. Die Userid ist ein fachlicher wert. Irgend jemand könnte mal auf die Idee kommen und alle userids ändern. Bei einer Zusammenlegung von Userdaten aus verschiedenen System kann das z.B. vorkommen.
Da man hier mit einer laufenden Nummer auch nicht auf der sicheren Seite ist, ist in unserer Datenbank der PK generell ein 32 stelliges VARCHAR2 Feld, welches mit der SYS_GUID() Funktion erzeugt wird:

select sys_guid() from dual

in jeder Tabelle eine EXTRA ID einführen, user-id kann in der tabelle dann unique sein..

Korrekt.

Dim

[dunas]

Herzlichen Dank für deine Antwort.

Richtig. Die Userid ist ein fachlicher wert. Irgend jemand könnte mal auf die Idee kommen und alle userids ändern. Bei einer Zusammenlegung von Userdaten aus verschiedenen System kann das z.B. vorkommen.
Da man hier mit einer laufenden Nummer auch nicht auf der sicheren Seite ist, ist in unserer Datenbank der PK generell ein 32 stelliges VARCHAR2 Feld, welches mit der SYS_GUID() Funktion erzeugt wird:

Das leuchtet mir ein. SYS_GUID() kann ich wohl leider nicht verwenden, da ich mit dem MySQL Server 5.1 arbeite - zumindestens habe ich bei meiner Recherche nichts gefunden.

Wenn ich das richtige verstehe, müsste ich doch dann in der User-Tabelle zwei IDs haben, oder? Eine ID für die User (User-ID) und eine ID, die als PK funktioniert (PK-ID). In den anderen Tabellen würde ich dann aber die User-ID als Foreign Key nehmen und gleichzeitig als unique setzen.

Warum wäre ich denn mit einem normalen int auto_increment-Wert nicht auf der sicheren Seite für meine PK-ID? Diese wird ja nur in der User-Tabelle als PK verwendet und taucht nirgendwo anders auf, hat also auch keine Abhängigkeiten. Sollte man Datenbanken zweier Firmen zusammenlegen, wäre es doch kein Problem, diese Werte zu ändern, oder? Oder macht ihr das deshalb, damit ihr eure Werte möglichst gar nicht ändern müsst, weil die andere Firma wohl kaum die gleichen erzeugte sys_guid() Werte haben wird?

Ich muss sagen, dass es sehr interessant ist, dies alles zu erfahren. Ich danke dir, dass du dein Wissen mit mir geteilt hast. Ohne praktische Erfahrung (wie du wohl merken wirst), hat man ja keine sonderlich große Möglichkeit, sich dieses Wissen anzueignen. Beim "googeln" muss ich mich richtig anstrengen, bis ich etwas über die Vorhergehensweise finde, die du mir hier erklärt hast.

:-)




[EDIT: gerade gemerkt: zwei auto_increment Spalten gehen ja gar nicht - nun brauche ich tatsächliche eine MySQL Alternative dafür :-( Außerdem EDIT: sys_guid() ist immer global eindeutig, es kommt also nie zu Konflikten, wenn zwei DBs zusammengelegt werden? (Wie geht sowas? Das sind doch auch nur Zeichenketten, oder?) Warum dann nicht eigentlich die User-ID als Sys_guid nehmen, dann bräuchte man doch theoretisch keine zwei IDs, weil es nie zu Überschneidungen käme.... hm. Sollte man zwei Datenbanken zusammenlegen und die PK-ID mit sys_guid() definiert haben, gäbe es dort vielleicht keine Probleme, aber bei einem auto_increment für die USER-ID ja sicherlich trotzdem (mit unique für die USER-ID) - leuchtet mir jetzt noch nicht so ganz ein.]

[EDIT: Vielleicht mache ich ja jetzt auch totalen Blödsinn. Vielleicht sollte ich, wie jetzt erlernt, jeder Tabelle eine ID geben (auto_increment), und als FK in anderen Tabellen immer den Usernamen nehmen, der ja auch eindeutig sein muss? Dann hätte ich auch nur eine ID-Spalte in der Users-Tabelle, wäre das nicht besser? In meinem Programm benötige ich keine User-Id (also, in manchen Anwendungen bekommt man ja eine User-ID um sich anzumelden, etc - bei mir war die User-Id nur zur internen Verwendung da) Um das zusammenzufassen, die unterschiedlichen Möglichkeiten im nächsten Post]

[dunas]

Möglichkeit 1, aber: zwei auto_increment geht nicht, man müsste also für die pk_id oder die user_id eine andere möglichkeit finden (sys_guid() geht bei mySql nicht - soweit ich weiss)

create table USERS (
	pk_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	users_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	username varchar(30) NOT NULL, 
	password varchar(50) NOT NULL, 
	is_enabled boolean NOT NULL, 
	primary key(pk_id), 
	unique(username),
	unique(users_id)) 
	ENGINE=InnoDB;


create table USERS_AUTHORITIES (
	pk_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	fk_users_id int UNSIGNED NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(pk_id),
	foreign key(fk_users_id) references users(users_id) ON DELETE CASCADE ON UPDATE CASCADE,
	unique(fk_users_id)) 
	ENGINE=InnoDB;

Möglichkeit 2: Warum überhaupt zwei IDs. Klar, jede Tabelle soll einen unabhängigen technischen Primary Key haben, also kann ich in Authorities (oder anderen Tabellen) nicht die users_id oder pk_id aus Users als ID nehmen.
Warum aber nicht auf eine ID in USERS verzichten und dafür den usernamen als Foreign key nehmen?

create table USERS (
	pk_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	username varchar(30) NOT NULL, 
	password varchar(50) NOT NULL, 
	is_enabled boolean NOT NULL, 
	primary key(pk_id), 
	unique(username)) 
	ENGINE=InnoDB;


create table USERS_AUTHORITIES (
	pk_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	fk_users_username varchar(30) NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(pk_id),
	foreign key(fk_users_username) references users(username) ON DELETE CASCADE ON UPDATE CASCADE,
	unique(fk_users_username)) 
	ENGINE=InnoDB;

Damit hätte jede Tabelle einen technischen Primary Key und ich hätte trotzdem nicht das Problem, dass ich eh keine zwei auto_increment Spalten nehmen kann. Der Username muss eh eindeutig sein, also warum nicht ihn als FK nehmen? Eine Users-ID brauche ich doch eigentlich gar nicht.

EDIT: Wenn man zwei Datenbanken zusammenlegt, bekommt man Probleme bei identischen Usernamen - aber die bekommt man doch eh, denn bei welcher Anwendung kann man schon zwei gleiche Usernamen haben? Doch eigentlich niemals, oder?

Was hälst du also von meinem zweiten Vorschlag? Sorry, falls ich jetzt schon wieder etwas verkehrt gemacht haben sollte, so ganz habe ich das dann alles evtl. noch nicht verinnerlicht, aber ich gebe mein Bestes :-)

[dunas]

nach weiterem längeren Nachdenken, habe ich schon wieder etwas gefunden, was mich an meinem zweiten Entwurf stört: Ich muss ja überall den Usernamen speichern, der ist definitiv länger als eine ID! Also: Entwurf verworfen!

Bleibt der erste Entwurf, bei dem ich aber immer noch das Problem der beiden IDs habe. Aber vielleicht sollte ich erstmal deine nächste Antwort abwarten: ob ich überhaupt die beiden IDs brauche (so, wie ich es bis jetzt verstanden habe, brauche ich das aber: schließlich soll jede tabelle eine unabhängige id haben. Sonst wäre die User-ID der PK bei der Users-Tabelle und die User-ID soll doch niemals PK sein (deshalb haben die andern Tabellen ja auch eine extra ID als PK - warum sollte das in der Users-Tabelle anders sein?)

Ich würde mich freuen, wenn du mir nochmal antworten könntest - ich hoffe, das man durch meine Beiträge noch durchblickt. :-)

EDIT: ich verwirre mich selber: in meinem Fall ist die User-ID (pk_id) doch ein technischer und kein fachlicher Wert und kann deshalb als PK verwendet werden(?) Kann meine Tabelle denn nun nicht so bleiben?

create table USERS (
	pk_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	username varchar(30) NOT NULL, 
	password varchar(50) NOT NULL, 
	is_enabled boolean NOT NULL, 
	primary key(pk_id), 
	unique(username)) 
	ENGINE=InnoDB;


create table USERS_AUTHORITIES (
	pk_id int UNSIGNED NOT NULL AUTO_INCREMENT,
	fk_users_id int UNSIGNED NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(pk_id),
	foreign key(fk_users_id) references user(pk_id) ON DELETE CASCADE ON UPDATE CASCADE,
	unique(fk_users_id)) 
	ENGINE=InnoDB;

[dunas]

so, jetzt langt es mir aber, ich werde noch wahnsinnig hier. Ich bin inzwischen komplett verwirrt.
Das da war mein erster Entwurf:

create table users (
	id int UNSIGNED NOT NULL AUTO_INCREMENT,
	username varchar(30) NOT NULL, 
	password varchar(50) NOT NULL, 
	enabled boolean NOT NULL, 
	primary key(id), 
	unique(username)) 
	ENGINE=InnoDB;


create table authorities (
	uid int UNSIGNED NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(uid),
	foreign key(uid) references users(id) ON DELETE CASCADE ON UPDATE CASCADE) 
	ENGINE=InnoDB;

Inzwischen verstehe ich nicht mehr, was daran falsch war. die id in Users ist doch ein technischer Wert, kein fachlicher. Warum kann ich die Id aus der Tabelle Users dann nicht in anderen Tabellen als PK (und FK) verwenden? Ist doch technisch :-( Warum in aller Welt sollte ich für "authorities" nun noch eine extra ID, also einen zweiten technischen Wert einführen? Ich versteh gar nichts mehr :-(

[dimitri]

Ruhig Blut
Die Tabelle USERS passt.
Die Tabelle AUTHORITIES benötigt noch ein extra Feld für den Foreign Key. Andernfalls hast Du eine 1:1 Beziehung, da Du z.B. für den User 4711 nur eine einzige Berechtigung vergeben kannst (ein PK ist ja implizit unique).

Eine 1:1 Beziehung wiederum kann man in den meisten Fällen aber auch in einer Tabelle zusammenfassen.

Wenn es also eine 1:n Beziehung sein soll (ein User bekommt eine oder mehrere Berechtigungen) dann brauchst Du zwingend noch ein zusätzliches Feld, in dem Du die zugehörige Userid ablegst. Der PK wäre dann komplett unabhängig und einfach eine laufende Nummer.

Dim

[Falk Prüfer]

Hallo dunas,

wenn deine User-ID ein rein technischer Wert ist, weil sie z.B. über ein Autoincrement generiert wird, dann ist diese ID ein guter Wert für einen PK. Der von dimitri vorgeschlagene zusätzliche technische Wert ist nur dann notwendig, wenn die User-ID eben ein fachlicher Wert ist, weil sie sich z.B. aus Geburtsdatum, Geschlecht und einem Hashwert zusammensetzt.

Für die Fremdschlüsselbeziehung solltest du eigentlich immer den PK verwenden. Der Username mag zwar eindeutig sein, aber er ist fachlich und könnte sich ändern (weil z.B. der User nach zwei Jahren im Forum der Meinung ist, dass "absolutebeginner" nicht mehr passend ist ) Hast du dann eine Fremdschlüsselbeziehung auf dem Usernamen, hast du ein Problem. Und damit dieses Problem nicht entsteht, sollte der PK eben technischer Natur sein und sich demzufolge niemals ändern.

Gruß Falk

[dunas]

danke für deine antwort! da ich so viele verschiedene versionen gepostet habe, weiss ich nun nicht ganz sicher, welche tabelle du meinst. ich gehe aber davon aus, dass ich die ganze zeit blödsinn gemacht habe und doppelte technische werte eingeführt habe, weil ich vergaß, dass meine userid ja nur eingeführt wurde, um als technischer wert zu funktionieren. (die arbeit der letzten tage war also umsonst...)

ich sehe also richtig, dass dies hier korrekt ist(?) mal ein größerer ausschnitt, um wirklich sicher zu gehen. die technischen werte habe ich mal markiert :-)

create table users (
	id int UNSIGNED NOT NULL AUTO_INCREMENT,
	username varchar(30) NOT NULL, 
	password varchar(50) NOT NULL, 
	enabled boolean NOT NULL, 
	primary key(id), 
	unique(username)) 
	ENGINE=InnoDB;


create table authorities (
	uid int UNSIGNED NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(uid),
	foreign key(uid) references users(id) ON DELETE CASCADE ON UPDATE CASCADE) 
	ENGINE=InnoDB;


create table countries (
	id int UNSIGNED NOT NULL AUTO_INCREMENT, 
	country varchar(100), 
	primary key(id)) 
	ENGINE=InnoDB;


create table userinformation (
	uid int UNSIGNED NOT NULL,
	email varchar(50) NOT NULL, 
	gender varchar(1), 
	birth date,
	city varchar(50), 
	state varchar(50), 
	country int UNSIGNED, 
	about varchar(2000), 
	primary key(uid),
	foreign key(uid) references users(id) ON DELETE CASCADE ON UPDATE CASCADE, 
	foreign key(country) references countries(id) ON DELETE CASCADE ON UPDATE CASCADE,
	unique(email)) 
	ENGINE=InnoDB;


create table activationlink (
	link varchar(50) NOT NULL, 
	uid int UNSIGNED NOT NULL, 
	primary key(uid),
	foreign key(uid) references users(id) ON DELETE CASCADE ON UPDATE CASCADE,
	unique(link)) 
	ENGINE=InnoDB;

Damit habe ich einen technischen Wert für einen User-Eintrag erzeugt. In allen weiteren Tabellen verweise ich auf diesen technischen Wert (nutze ihn als PK und als FK). Ist das korrekt?

Wir hatten ja zuerst gesagt, dass die User-ID nicht als PK genutzt werden soll - da hatte ich aber auch vergessen, dass die User-ID bei mir keine ID ist, die vom Anwender genutzt wird, sondern tatsächlich nur ein technischer Wert zur Identifzierung des Users ist.

Ich habe damit aber keine extra-ID in jeder Tabelle, sondern verweise wie gesagt nur auf die Users-Tabelle. (Wir hatten ja gesagt, dass jede Tabelle eine extra-ID braucht - aber da galt auch die Annahme von oben - dass die User-ID ein fachlicher Wert ist - ist sie ja aber nicht!)

Bitte sag mir, dass das jetzt so stimmt! :-)

Achso, Ergänzung: Es ist nur _eine_ Rolle pro User möglich - die Tabelle kann also so bleiben?

[dunas]

wenn deine User-ID ein rein technischer Wert ist, weil sie z.B. über ein Autoincrement generiert wird, dann ist diese ID ein guter Wert für einen PK. Der von dimitri vorgeschlagene zusätzliche technische Wert ist nur dann notwendig, wenn die User-ID eben ein fachlicher Wert ist, weil sie sich z.B. aus Geburtsdatum, Geschlecht und einem Hashwert zusammensetzt.

Für die Fremdschlüsselbeziehung solltest du eigentlich immer den PK verwenden. Der Username mag zwar eindeutig sein, aber er ist fachlich und könnte sich ändern (weil z.B. der User nach zwei Jahren im Forum der Meinung ist, dass "absolutebeginner" nicht mehr passend ist ) Hast du dann eine Fremdschlüsselbeziehung auf dem Usernamen, hast du ein Problem. Und damit dieses Problem nicht entsteht, sollte der PK eben technischer Natur sein und sich demzufolge niemals ändern.

Danke auch für deine Antwort. Du hast recht, das mit dem Usernamen hatte ich mir ja auch schnell anders überlegt. Deine Antwort deutet darauf hin, dass mein letztes Posting nun ja korrekt vom Aufbau sein dürfte, da meine User-Id ja tatsächlich rein technisch ist. Ich bin schon etwas erleichert :-)

[dunas]

In Tabellen, in denen ich nun die Users-ID und einen fachlichen Wert zusammen als PK habe, führe ich aber - wie jetzt gelernt - aber noch einen zusätzlichen technischen Wert ein, damit niemals ein fachlicher Wert Bestandteil eines PKs ist, richtig?

[Falk Prüfer]

...Achso, Ergänzung: Es ist nur _eine_ Rolle pro User möglich - die Tabelle kann also so bleiben?

OK, dass ist im Moment so - bleibt es definitiv immer so? Ich garantiere dir, wenn du es so anlegst WIRD es sich irgendwann ändern und dann hast du ein Problem.

Warum nicht:

create table authorities (
	id int UNSIGNED NOT NULL AUTO_INCREMENT,
        uid int UNSIGNED NOT NULL, 
	authority varchar(50) NOT NULL, 
	primary key(id),
        unique key(uid),
	foreign key(uid) references users(id) ON DELETE CASCADE ON UPDATE CASCADE) 
	ENGINE=InnoDB;

Damit bildest du deine momentane Restriktion "nur _eine_ Rolle pro User" ab, bekommst aber keine Probleme mit dem PK, falls sich das mal ändert.
Das gleiche gilt auch für table userinformation. Wobei die Zusammenstellung der Daten "zwiespältig" ist. Während sowas wie birth und gender eine echte 1:1 Beziehung darstellt, sieht das bei email schon anders aus. Eine echte 1:1 Beziehung macht aus Normierungssicht auch nicht wirklich Sinn. Du solltest also schon Gründe haben, warum du solche Entitäten wie birth und gender in eine separate Tabelle auslagerst und nicht mit am User verankerst.

Gruß Falk

[dunas]

Hallo,

vielen Dank für deine Antwort. Ich denke, ich werde deinen Vorschlag bezüglich den Authorities übernehmen. :-) Erscheint mir sinnvoll, auch wenn ich momentan natürlich wirklich nicht glaube, dass einmal mehr Rollen möglich wären. ;-)

Was die Userinformationen angeht:
warum ich das so gemacht habe, wie jetzt: jeder user kann seine daten selber eingeben und ändern, auch geburtsdatum und gender. Ich überprüfe nicht, ob diese der Wahrheit entsprechen, dies ist für meine applikation nicht von wichtigkeit. jeder kann über seinen account preisgeben, was er möchte. wenn jemand so tun möchte, als wäre er 50 oder 20, oder männlich oder weiblich - überprüfen kann ich das eh schlecht und ich kann mir gut vorstellen, dass user manchmal ihr geburtsdatum angeben und sich dann wieder entshceiden, es rauszunehmen, etc...
also sind eigentlich alle userinformationen veränderlich ... (bis auf username und email, das habe ich momentan verboten - mal schauen :-)) ... und ich wollte nun nicht _alle_ informationen in die users-tabelle packen - die wird ja beim login benötigen und ich wollte nicht x-tausend informationen zusätzlich dann abfragen :-)

[Falk Prüfer]

OK, wenn du dafür Gründe hast - und die angegeben erscheinen nicht total unsinnig, dann ist eine 1:1 Beziehung ja nicht grundlegend schlecht. Aber bedenke, dass man solche Datentrennung auch im Nachhinein über Views erreichen kann, ohne die Nachteile in Kauf nehmen zu müssen.

Beim Anlegen eines neuen Nutzers müssen ja zwei Datensätze angelegt werden (oder die Userinformation wird erst angelegt wenn der Nutzer sie bearbeitet), die auch wieder gelöscht werden müssen, wenn der Nutzer seinen Account nicht aktiviert oder sich abmeldet. Bei allen Abfragen die sowohl Nutzernamen, als auch ein oder mehrere "informative" Angaben benötigen, muss ein Join formuliert werden - und dies muss korrekterweise sogar ein OUTER Join sein, da nicht sichergestellt ist das zu jedem Nutzerdatensatz eine Userinformation existiert (dafür gibt es keinen Constraint).

Also man muss die Vor- und Nachteile gegeneinander abwiegen ...

Gruß Falk

[dunas]

Über Views? Hm, das ist jetzt bestimmt eine dämliche Frage, aber wie meinst du das? Ich kenne unter View nur die Komponente des ModelViewControllers? Dort habe ich zb ein View "Profile", wo die genannten Informationen (bis auf die Email - hm, vielleicht sollte ich die dann aussortieren: die wird mit den allgemeinen Infos angezeigt (Password, Username)) angezeigt werden.
Aber wie gesagt, die Email in der User-Tabelle scheint mir auch nicht so sinnvoll, beim Login (wo die Usertabelle am häufigsten zum Tragen kommt) brauche ich die ja nicht. (?)

Ja, beim Anlegen eines Nutzers werden mehrere Datensätze angelegt. Ich tendiere allerdings dazu, dass ich die Userinformation als default Datensatz anlege, der - solange am Profil nichts geändert wird - abgefragt wird, wenn der User (ohne Profil) sein Profil abfragt. Aktuell mache ich das nicht so, da ja die Emailadresse dort _immer_ beim Registrieren gespeichert wird. Werde mir das nochmal durch den Kopf gehen lassen. (Mit anderen Datensätzen gehe ich bereits so vor.)

Ich arbeite mit Hibernate und momentan löse ich die Abfragen fast komplett ohne Join, was sich aber noch ändern wird. (Zunächst war es nicht erfordert - momentan arbeite ich an dem ersten ;-)) Aktuell gibt es ja auch zu jedem User einen Datensatz. Ich sehe darin aber kein Problem. Sollte ich einen Join formulieren und es gibt keinen entsprechenden Datensatz, bekomme ich "null" zurückgeliefert, was ich nur entsprechend abfangen muss.
Wird ein User gelöscht, wird der Datensatz aus der Users-Tabelle gelöscht und alle davon abhängigen Datensätze. Existieren sie nicht, brauchen sie auch nicht gelöscht werden, das ruft keinen Fehler hervor.

Vielen Dank für deine Informationen :-) Es ist interessant, darüber zu diskutieren - da wird einem so mancher Sinn oder Unsinn seines Projektes erstmal bewusst. ;-)