MySQL Frage zum Datenbankentwurf

[baserider]

Ich habe mir eine Tablle angelegt für alle Bundesländer und eine Tabelle mit den Städten für Deutschand (aus opengeodb):

bundesland
--------------
-pk
-name

stadt
--------------
-pk
-name
-bundesland_fk

Jetzt zu meiner Frage: (FK = Foreign Key)
Es gibt weiterhin eine Tabelle Location (Club, Disco, Bar usw). Ich überlege gerade, ob in diese Tabelle ein Fremdschlüssel des Bundeslandes gehört oder ob nur der Fremschlüssel der Stadt dort hinkommt.
Wenn man z.B. alle Locations nach Bundesland ausgeben will, würde es ja theoretisch auch über den FK der Stadt gehen (in der stadt-Tabelle ist ja der bundesland_fk + Join), was mir aber etwas unsinnig vorkommt.
Einfacher wäre es ja, den FK der Stadt und den FK des Bundeslandes in die location-Tabelle aufzunehmen oder geht das noch besser?

location
---------------
-pk
-name
-strasse
-plz
-stadt_fk
-bundesland_fk

 

[theHacker]

Einfacher wäre es ja, den FK der Stadt und den FK des Bundeslandes in die location-Tabelle aufzunehmen oder geht das noch besser?

Das wäre eine Denormalisierung und sollte vermieden werden, außer, du weißt, was du tust.

Willst du alle Locations eines Bundeslandes, JOINst du eben einfach die bundesland-Relation dran und schlägst damit nach, welches Bundesland die Stadt hat. Da du überall Indezes gesetzt hast, optimiert die Datenbank den Zugriff dran.

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] ... [B][COLOR=#9932cc]FROM[/COLOR][/B] location
[B][COLOR=#9932cc]JOIN[/COLOR][/B] stadt [B][COLOR=#9932cc]ON[/COLOR][/B] stadt.pk = location.stadt_fk
[I][COLOR=#ff8c00]-- hier Bundesland nachschlagen und aussortieren[/COLOR][/I]
[B][COLOR=#9932cc]JOIN[/COLOR][/B] bundesland [B][COLOR=#9932cc]ON[/COLOR][/B] bundesland.pk = stadt.bundesland_fk 
[B][COLOR=#9932cc]WHERE[/COLOR][/B] bundesland.name = 'Bayern';[/FONT]

Mit einem EXPLAIN SELECT ... kannst du es ausprobieren und siehst, wie die Datenbank arbeitet.

 

[baserider]

Hi,

ok, dann werde ich den bundesland_fk aus der location-Tabelle entfernen.
Ich dachte eben nur daran, das man ja so einen Join sparen könnte und ich ja eigentlich die Stadt gar nicht brauch.

Aber was du gesagt hast leuchtet mir auch ein, denn ich habe den FK des Bundeslandes ja schon in der stadt-Tabelle und jeder Location ist eine Stadt zugeordnet.

Löst man denn datenbanktechnisch solche Sachen immer so bzw. darf ein FK immer nur in einer Tabelle vorkommen?

 

[theHacker]

Ich dachte eben nur daran, das man ja so einen Join sparen könnte und ich ja eigentlich die Stadt gar nicht brauch.

Wer sagt, dass die DB die Stadt überhaupt anlangt in diesem Fall?
Wenn du nichts aus der Stadt-Relation selektierst, d.h. keine Daten verlangt sind, wird die DB maximal die Index-Datei verwenden und von der Daten-Datei ganz die Finger lassen.

Löst man denn datenbanktechnisch solche Sachen immer so bzw. darf ein FK immer nur in einer Tabelle vorkommen?

Du kannst durchaus mehrere Fremdschlüssel in einer Relation haben. Allerdings dürfen die nicht - wie in deinem Fall - transitiv voneinander abhängen (3NF).

 

[baserider]

danke für die hilfreichen Antworten.
Das mit der Abhängigkeit ist mir jetzt klar geworden

Vielleicht kannst du mir kurz noch woanders helfen. Ich habe mir ja nun eine Tabelle mit allen Städten und Bundesländern erzeugt.
Bei Neuanlage einer Location wird zuerst über ein Dropdown des Bundeslandes auf die Städte eingeschränkt (wobei ich den Wert des Bundeslandes nun nicht mehr mit in die location-Tabelle eintragen werde)
Leider erscheinen troztdem meist bis über 1000 Einträge bei den Städten. Wäre es denn besser die Stadt als normales Textfeld zu lassen? Dann könnte ich natürlich Schreibfehler nicht mehr vermeiden und irgendwie wäre die stadt-Tabelle ja dann überflüssig

 

[theHacker]

Leider erscheinen troztdem meist bis über 1000 Einträge bei den Städten. Wäre es denn besser die Stadt als normales Textfeld zu lassen?

Schick wäre eine Vorauswahl über AJAX.
Wir haben hier im Forum über 53.000 User. Geh mal in die Ignorierenliste und fang an, Buchstaben zu tippen. Ab 3 Buchstaben wirst du sehen, was ich meine

Dann könnte ich natürlich Schreibfehler nicht mehr vermeiden und irgendwie wäre die stadt-Tabelle ja dann überflüssig

Du musst dir halt überlegen, ob du es zulassen willst, dass der Benutzer eine unbekannte Stadt eingibt. Ich persönlich würde, wenn ich schon ne GeoDB im Hintergrund hab, unbekannte Städte nicht zulassen.

Wie du es dem User präsentierst, bleibt dir überlassen. Ein freies Textfeld kann auch einen Fehler zurückmelden, wenn dir die Eingabe des Benutzers nicht passt. Die AJAX-Lösung würde da nur "obendrauf gesetzt" werden, die bei Vorhandensein von JavaScript greift und dem Benutzer assistiert und ihm keinen Frust bei einer Fehleingabe gibt, da er schon vor dem Absenden sieht, dass die Stadt falsch is.

 

[baserider]

das is natürlich eine gute Idee, dann schon Werte anzuzeigen, sobald man die ersten Buchstaben eingegeben hat. Ich glaub so werde ich es machen. Dann muss man sich nicht durch alle möglichen Städte durchscrollen und könnte eigentlich auch das Dropdown für das Bundesland weglassen

Um die Eingabe im Textfeld zu prüfen, müsste ich aber auch immer einen Abgleich mit der Datenbank machen, ob es den Ort gibt. Sobald der User den Begriff nicht 100% so schreibt wie in der Datenbank wird es natürlich schwierig (Ich denk an Orte wie Halle / Saale) oder hast du ne Idee wie ich das besser prüfen kann?

 

[theHacker]

Um die Eingabe im Textfeld zu prüfen, müsste ich aber auch immer einen Abgleich mit der Datenbank machen, ob es den Ort gibt.

Ob Textfeld oder Auswahlfeld - das musst du doch so oder so machen

 

[baserider]

ok, das stimmt... ich hatte nur grad nicht daran gedacht, das die Selectbox zwar Werte vorgibt aber man soll ja keiner Benutzereingabe trauen

In der Datenbank gibt es z.B. einen Eintrag "Halle, Kreis Holzminden"
Ich stelle mir nun grad den Nutzer vor, der diese Stadt ins Textfeld eingeben will. Es gibt ja so viele Schreibweisen und der User kann ja nicht wissen wie es in der DB abgespeichert is, spätestens bei der 3. Fehlermeldung hat er wohl die Lust verloren. (Es soll ja auch ohne Ajax funktionieren, dort könnte man sich noch die korrekte Schreibweise einfach auswählen)

 

[theHacker]

In der Datenbank gibt es z.B. einen Eintrag "Halle, Kreis Holzminden"
Ich stelle mir nun grad den Nutzer vor, der diese Stadt ins Textfeld eingeben will.

99% kriegen es per AJAX-Hilfe hin und wählen den Eintrag, der am richtigsten aussieht.

Für das letzte Prozent, die kein JavaScript unterstützen, musst du halt die Auswahl dann per Select-Box vorgeben. Nimm dir ein Beispiel an Google. Egal, was du eintippst, der weiß immer, was du meinst. Du musst es nur auf die Reihe kriegen, dem Benutzer eine

1. überschaubare und
2. vollständige (das, was er eingeben will, muss es enthalten sein)

Auswahl anzubieten.

Ich würd einfach gucken, wo der eingegebene Begriff überall drin is. Wenn dus ganz perfekt machen willst, musst du auch mit Tippfehler "hale" statt "Halle" rechnen und auch ähnliche Treffer mitaufnehmen. Ob das die Mühe wert is, musst du entscheiden.

Ne dritte Möglichkeit wäre Moderation - so wie's mir scheint, willst du ja ne Art POI-Sammlung machen. Nachteil is zusätzliche Arbeit pro Eintrag, dafür kannst du dir die ganze Validierung des Orts sparen, weil du dann von Hand die Drecksarbeit machst. Und dazu kommt, dass die Eingaben dann nicht live zur Verfügung stehen.
Für größere Projekte also nicht wirklich in Frage kommend.

 

[baserider]

Hi,

hab mir auch grad nchmal paar Gedanken gemacht und ich bin ebenfalls zu deinem letzten Tip gekommen, also das die Sachen erst nach Freischaltung auf der Seite zu sehen sind. Dann is es im ersten Moment egal was in dem Feld steht.

Aber daraus ergibt sich für mich noch was anderes:

In der Datenbank müsste ich ja dazu noch ein zusätzliches Feld "stadt", was die Eingabe des Users aufnimmt, anlegen und die ID aus der GeoDB wird bei der Freischaltung zugeordnet. Die Tabelle hat also dann eine zusäzliche Spalte, deren Wert nach Freischaltung gelöscht werden kann. Is es denn ok wenn ich diese Spalte mit in die Tabelle einfüge? Irgendwo muss ich ja die Usereingabe temporär ablegen

 

[theHacker]

Irgendwo muss ich ja die Usereingabe temporär ablegen

Jo klar, ne zweite Tabelle.
Damit kann du auch bequem per INSERT INTO SELECT die Moderation durchführen.

 

[baserider]

ach du meinst alle Angaben erstmal in ne zweite tabelle oder nur das eine Feld? Im Backend kann ich doch die Werte direkt in der richtigen Tabelle ändern und dann freischalten. Oder wie hast du das mit einer zweiten Tabelle gemeint?

 

[theHacker]

ach du meinst alle Angaben erstmal in ne zweite tabelle oder nur das eine Feld?

Natürlich alle. Willst du den Datensatz splitten oder wie? Quark

Beiträge, die du noch nicht freigeschaltet hast, dürfen ja sonst nirgendwo auftauchen. Oder willst du dann bei allen Funktionen (Suche, Anzeige, Bewertung, kp, was es da alles gibt) Fallunterscheidungen einbauen, ob der Datensatz sichtbar is oder nicht.

Zusätzlich kommt noch die Problematik, dass der unbestätigte Ort ein String is, der bestätigte dann ein Integer (nämlich der Fremdschlüssel in die stadt-Tabelle). Du wirst 'Nürnberg' wohl kaum in ein INT(11)-Attribut stecken können

 

[baserider]

ok, dann hätte ich 2 (fast identische) Tabellen.
Naja was die Fallunterscheidung betrifft, müsste ich bei jeder Ausgabe der Locations noch die SQL-Abfrage erweitern. Aber das seh ich nicht so als Problem, noch ne WHERE-Klausel ranzuhängen. Deswegen hatte ich vorhin von einer zusätzlichen Spalte gesprochen, weil es einmal die Benutzereingabe (als String) und dann die ID (als Int) gibt.

 

[theHacker]

ok, dann hätte ich 2 (fast identische) Tabellen.

Und?

Naja was die Fallunterscheidung betrifft, müsste ich bei jeder Ausgabe der Locations noch die SQL-Abfrage erweitern.

Richtig. Und warum sollen beispielsweise 1.000.000 Abfragen drunter leiden, obwohl nicht 1 Datensatz auf Bearbeitung wartet?!

Deswegen hatte ich vorhin von einer zusätzlichen Spalte gesprochen, weil es einmal die Benutzereingabe (als String) und dann die ID (als Int) gibt.

Noch schlimmer: Dann verwendest du noch zusätzlich Speicherplatz. Da stehen 1.000.000 VARCHAR-Felder leer. Die werden einmal gebraucht und danach nie wieder. Willst einen auf Müllsammler machen?

Tabellen tun nicht weh. Wenn man eine neue braucht, gibts null Grund zu geizen. Dann legt man eben eine neue an.

 

[baserider]

ok, hast mich überzeugt
Aber z.B. bei Typo3 ist es generell so, das an jede Abfrage ein
"...WHERE hidden = 0" angehangen wird

Mit INSERT INTO SELECT ist es jedoch auch ein leichtes, den zuletzt bearbeiteten Datensatz in die richtige Tabelle zu schieben.

Danke dir

 

[theHacker]

Aber z.B. bei Typo3 ist es generell so, das an jede Abfrage ein
"...WHERE hidden = 0" angehangen wird

Das macht das Forum hier ähnlich, aber da is das ja wirklich ein sichtbar/gelöscht/modQueue-Flag.
In deinem Fall musst du die Daten ja noch bearbeiten: Zuordnung Ort-String -> Ort-PK.