MySQL 5.0 - Tabellenlayout

[Gunther]

Hallo Selfgemeinde!

Ich hätte gerne mal euren Rat bezüglich eines möglichst optimalem Tabellenlayouts.

Es geht um eine Tabelle, die URLs (Seitennamen/ -titel) von Seiten meiner Website enthalten soll, zum Look-Up bei jedem Request und die eine entsprechende Resource-ID zurückliefern soll.

Ferner soll zusätzlich noch der Fall Berücksichtigung finden, dass sich der Titel/ die URL einer Seite ändert (Status 301), bzw. eine Seite nicht weiter vorhanden ist (Status 410).

Dazu habe ich nach meiner ersten Idee und Überlegung folgende Tabelle erstellt:

Feld          Typ                        Null   Standard        Kommentare
---------------------------------------------------------------------------------------------
url_id        mediumint(8)               Nein                   auto_increment
res_url       varchar(255)               Nein                   unique
id            mediumint(8)               Nein                   Resource-ID
status        enum('301', '307', '410')  Ja     NULL            mögliche Statuscodes
status_add    mediumint(8)               Ja     NULL            bei 301 bspw. aktuelle url_id

Wichtig ist u.a. auch die Unique Eigenschaft der Spalte res_url, da jede URL/ jeder Seitenname ja nur einmal existieren darf.

Irgendwie habe ich das "Gefühl", dass meine Tabellenstruktur eher "suboptimal" ist, komme aber auch nach intensivem Studium diverser Artikel (darunter u.a. die beiden Fachartikel von Rouven und Vinzenz zu JOINS und dem Wikipedia Artikel über Normalisierung) nicht wirklich weiter.

Über ein paar Tipps/ Denkanstöße würde ich mich freuen - danke!

Gruß Gunther

[Vinzenz Mai]

Hallo Gunther,

Es geht um eine Tabelle, die URLs (Seitennamen/ -titel) von Seiten meiner Website enthalten soll, zum Look-Up bei jedem Request und die eine entsprechende Resource-ID zurückliefern soll.

selbst in einer recht flachen Seitenstruktur sind die URLs typischerweise hierarchisch aufgebaut. Um so etwas in einer relationalen Datenbank vernünftig ablegen zu können, solltest Du Dir das Nested-Set-Muster anschauen.

Und ja, der erhöhte Overhead für Einfüge- und Löschoperationen sollte sich bei der Abbildung von Webseitenstrukturen mehr als lohnen.

Freundliche Grüße

Vinzenz

[Gunther]

Hallo Vinzenz,

Es geht um eine Tabelle, die URLs (Seitennamen/ -titel) von Seiten meiner Website enthalten soll, zum Look-Up bei jedem Request und die eine entsprechende Resource-ID zurückliefern soll.

selbst in einer recht flachen Seitenstruktur sind die URLs typischerweise hierarchisch aufgebaut.

Nach reiflicher Überlegung und einigen sehr "speditiven" Diskussionen, Tipps und Meinungen hier im Forum, bin ich zu der Überzeugung gelangt, dass ich eine möglichst flache Struktur für die Website wähle.
Diese sieht so aus, dass der Pfadteil der URL eigentlich nur aus dem jeweiligen Seitentitel/ -namen besteht. Ich verwende also keinerlei weiteren ("Pseudo") Pfadebenen, die etwa solche Dinge wie Kategorie(namen) etc. enthalten. Die Inhalte selber liegen auch nicht in Dateien, sondern ebenfalls in der DB.

Um so etwas in einer relationalen Datenbank vernünftig ablegen zu können, solltest Du Dir das Nested-Set-Muster anschauen.

Oh je! ;-)
Schon wieder "schwere Kost" und das so spät am Abend ... !

Aber danke für das "Stichwort" - ich werde mir das auf jeden Fall nochmal (ich bin da früher schon mal drüber gestolpert) in Ruhe zu Gemüte führen ...

Und ja, der erhöhte Overhead für Einfüge- und Löschoperationen sollte sich bei der Abbildung von Webseitenstrukturen mehr als lohnen.

... und das dabei im Hinterkopf behalten.

Gruß Gunther

[hotti]

hi,

Irgendwie habe ich das "Gefühl", dass meine Tabellenstruktur eher "suboptimal" ist, komme aber auch nach intensivem Studium diverser Artikel (darunter u.a. die beiden Fachartikel von Rouven und Vinzenz zu JOINS und dem Wikipedia Artikel über Normalisierung) nicht wirklich weiter.

Bei einer solchen Tabelle gibt es auch nicht viel zu normalisieren. Eine URL ist eindeutig, die kannst Du durchaus als Primärschlüssel festlegen, da kann die Spalte id (autoincrement) schonmal entfallen. Und mit den Feldern wie

Last_Modified
 Author
 Title
 Description
 Folder
 Status

bekommst Du schon eine Tabelle URLs, die der Normalform entspricht, so dass Du auf weitere Subtables, die einen Join erfordern würden verzichten kannst. Möglich ists jedoch, über die Spalte "Folder" eine Normalisierung vorzunehmen, denn hier gäbe es Redundanzen, sofern Deinen Folder's weitere Eigenschaften (title, descr, author, lastmod usw.) zugeordnet sind. Diese wären dann in einer Subtable besser aufgehoben wobei diese Tabelle dann bspw. per Folder_ID in der Tabelle URLs referenziert ist, das sähe dann so aus:

Tab URLs
Foreign Key: Folder_ID

Tab Folders
Primary Key: ID

Das kannst Du dann soweit treiben, je nach Engine, dass beim Löschen eines Folders auch alle URLs dazu gelöscht werden.

Viele Grüße vom Baumarkt,
Horst Haselhuhn

[Hopsel]

Hi hotti!

Bei einer solchen Tabelle gibt es auch nicht viel zu normalisieren. Eine URL ist eindeutig, die kannst Du durchaus als Primärschlüssel festlegen, da kann die Spalte id (autoincrement) schonmal entfallen.

Prinzipiell hast du ja recht, aber was machst du, wenn plötzlich mehrere, also mindestens zwei, URLs mit einer Resource-ID verknüpft werden?

Auch wenn das in diesem Fall ausdrücklich nicht gewünscht scheint, sollte der Primärschlüssel einer Tabelle nie mit Semantik belegt sein. Er dient ausschließlich der Identifikation einzelner Datensätze.

MfG H☼psel

[hotti]

hi,

Auch wenn das in diesem Fall ausdrücklich nicht gewünscht scheint, sollte der Primärschlüssel einer Tabelle nie mit Semantik belegt sein. Er dient ausschließlich der Identifikation einzelner Datensätze.

Ooops, ja, Du hast Recht. Aber sag niemals "nie". In der Praxis wird ein URL requestet, nicht eine ID. Da geht das Gehacke und Rewrite_Geficke nämlich wieder los, wenn der Auftraggeber plötzlich und unerwartet nach Vanity-URLs schreit ;-)

Hotte

[Gunther]

Hi Hotte,

Auch wenn das in diesem Fall ausdrücklich nicht gewünscht scheint, sollte der Primärschlüssel einer Tabelle nie mit Semantik belegt sein. Er dient ausschließlich der Identifikation einzelner Datensätze.

Ooops, ja, Du hast Recht. Aber sag niemals "nie". In der Praxis wird ein URL requestet, nicht eine ID. Da geht das Gehacke und Rewrite_Geficke nämlich wieder los, wenn der Auftraggeber plötzlich und unerwartet nach Vanity-URLs schreit ;-)

das kann hier nicht passieren, da es sich um meine private Website handelt. ;-)

Und das einzige "Gefrickel & Gehacke" welches ich per mod_rewrite betreibe ist, alles auf meine Index-Datei umzuleiten, die dann den ganzen Rest erledigt.

Deshalb möchte ich diese Tabelle ja auch möglichst "klein" halten. Ich brauche sie lediglich dazu, um herauszufinden ob es eine angeforderte (normale) Seite gibt, und wenn ja, welche ID (dient als interne Kurz-Bezeichnung) die angeforderte Seite hat.

Alle anderen von dir auch aufgezählten Infos kommen in eine/ mehrere andere Tabelle/n.

Das auto_increment Feld 'url_id' dient, wenn du so willst, nur meiner Bequemlichkeit, weil ich dadurch in den Fällen wo es bspw. einen 'status' 301 gibt, bei der erneuten Abfrage dann direkt mit der url_id arbeiten kann und nicht die 'res_url' vewenden muss (selbiges gilt für evt. Einträge in 'status_add').

Gruß Gunther

[hotti]

moin Gunther,

Deshalb möchte ich diese Tabelle ja auch möglichst "klein" halten.

Ja, sollte die, ist bei mir auch so.

Ich brauche sie lediglich dazu, um herauszufinden ob es eine angeforderte (normale) Seite gibt, und wenn ja, welche ID (dient als interne Kurz-Bezeichnung) die angeforderte Seite hat.

Wie schon gesagt, wenn es nur _eine_ Spalte gibt für descr, kann die mit rein in die Haupttabelle, nennen wir die URLs. Aber wenn es mehrere Felder geben sollte hinsichtlich Beschreibung, geht die Normalisierung dahin, diese Daten in eine DetailTabelle auszulagern.

Alle anderen von dir auch aufgezählten Infos kommen in eine/ mehrere andere Tabelle/n.

Der übliche Weg zur Normalisierung nach Codd...

Das auto_increment Feld 'url_id' dient, wenn du so willst, nur meiner Bequemlichkeit, weil ich dadurch in den Fällen wo es bspw. einen 'status' 301 gibt, bei der erneuten Abfrage dann direkt mit der url_id arbeiten kann und nicht die 'res_url' vewenden muss (selbiges gilt für evt. Einträge in 'status_add').

Ich schreib einfach mal, wie ich das mache: Ein Request erzeugt serverseitig die Variable REQUEST_URI. Das ist bei mir der Primärschlüssel in 2 Tabellen, die heißen bei mir "public" und "content".

Als Erstes schaut das Script, was per rewrite angezogen wird, ob es für REQUEST_URI in der Tabelle "public" einen Eintrag gibt und schaut auch nach Last-Modified sowie nach weiteren Attributen die für die angeforderte Seite in Tabelle "public" hinterlegt sind. Daraus ergeben sich die Stati 200 oder 410 oder 304.

Status: 200 OK
Mein Script holt für REQUEST_URI den body aus der Tabelle "content" und liefert die Seite komplett neu aus. Im Header wird Last-Modified mitgegeben

Status: 304 Not Modified
Der Webserver bricht das Senden der Seite ab, da bei Besucher im Cache. Der vom Browser mitgegebene Last-Modified stimmt mit dem Wert in Table "public" überein. Mein Script sendet ebenfalls einen 304 an den Webserver und beendet sich ohne die Tabelle "content" anzufassen.

Status: 410
Die angeforderte URL ist nicht in Tabelle "public". Mein Script sendet einen 410 Gone und beendet sich.

Status: 404
Wird vom Webserver selbst abgehandelt, kommt gar nicht erst zu Rewrite.

Status: 302/301
Benutze ich gerne in diversen anderen reinen CGI-Scripts für das Transit-State Model.

Schönes Wochenende,
Horst Hagebau

[Gunther]

Moin Horst (Hotte oder wie auch immer - entscheide dich doch mal für einen Nick Rolf)!

Ich schreib einfach mal, wie ich das mache: Ein Request erzeugt serverseitig die Variable REQUEST_URI. Das ist bei mir der Primärschlüssel in 2 Tabellen, die heißen bei mir "public" und "content".

Als Erstes schaut das Script, was per rewrite angezogen wird, ob es für REQUEST_URI in der Tabelle "public" einen Eintrag gibt und schaut auch nach Last-Modified sowie nach weiteren Attributen die für die angeforderte Seite in Tabelle "public" hinterlegt sind. Daraus ergeben sich die Stati 200 oder 410 oder 304.

Status: 200 OK
Mein Script holt für REQUEST_URI den body aus der Tabelle "content" und liefert die Seite komplett neu aus. Im Header wird Last-Modified mitgegeben

Status: 304 Not Modified
Der Webserver bricht das Senden der Seite ab, da bei Besucher im Cache. Der vom Browser mitgegebene Last-Modified stimmt mit dem Wert in Table "public" überein. Mein Script sendet ebenfalls einen 304 an den Webserver und beendet sich ohne die Tabelle "content" anzufassen.

Ja, stimmt.
Diese Info sollte ich auch noch mit in meine Tabelle packen, damit gar keine weiteren Aktionen nötig sind.

Status: 410
Die angeforderte URL ist nicht in Tabelle "public". Mein Script sendet einen 410 Gone und beendet sich.

Status: 404
Wird vom Webserver selbst abgehandelt, kommt gar nicht erst zu Rewrite.

Hmmm ..., wie machst du das?
Bei mir wird jeder Request, der nicht auf ein real existierendes Verzeichnis oder eine Datei abzielt ja auf jeden Fall erstmal per rewrite an mein Index-Script umgeleitet. Und erst der Look-Up in meiner URL-Tabelle bringt dann Klarheit, ob der Request erfüllt werden kann oder nicht.
Wobei ich diesen Umstand dann auch dahingehend nutze, um dem User gleich auch noch auf der 404er Antwortseite etwas wie "Meinten Sie vlt. xyz?" mit anzubieten.

Status: 302/301
Benutze ich gerne in diversen anderen reinen CGI-Scripts für das Transit-State Model.

Ich nix CGI.
Einen 302er brauche ich vermutlich auch eher so gut wie nie. Einzig der 301er ist eben u.U. erforderlich.

Schönes Wochenende,

Danke, wünsche ich dir auch.

Gruß Gunther

[hotti]

Status: 404
Wird vom Webserver selbst abgehandelt, kommt gar nicht erst zu Rewrite.
Hmmm ..., wie machst du das?

.htaccess

ErrorDocument 404 /cgi-bin/404.cgi

DirectoryIndex /cgi-bin/show.cgi?html

RewriteEngine on
RewriteRule ^.*.html$              /cgi-bin/show.cgi?html

thats all, game over ;-)

404er ergeben sich z.B. bei einem Request auf otto.htm
410er werden von show.cgi erzeugt, z.B. bei einem Request auf qwertz.html

Ergo: Alles, was es nicht gibt, jedoch nicht auf .html endet wird zu 404.

--Rolf

[Gunther]

Hi hopsel!

Bei einer solchen Tabelle gibt es auch nicht viel zu normalisieren. Eine URL ist eindeutig, die kannst Du durchaus als Primärschlüssel festlegen, da kann die Spalte id (autoincrement) schonmal entfallen.
Prinzipiell hast du ja recht, aber was machst du, wenn plötzlich mehrere, also mindestens zwei, URLs mit einer Resource-ID verknüpft werden?

Auch wenn das in diesem Fall ausdrücklich nicht gewünscht scheint, sollte der Primärschlüssel einer Tabelle nie mit Semantik belegt sein. Er dient ausschließlich der Identifikation einzelner Datensätze.

Nicht gewünscht - ja (aus einem anderen Grund, der nichts mit der DB zu tun hat). ;-)
Das kann aber halt schon vorkommen, bspw. wenn sich ein Seitentitel eben doch mal (oder sogar mehrmals) ändern sollte[1]. Dann gibt es zwei oder mehr verschiedene Einträge in der Spalte 'res_url', die alle einen identischen Wert in der Spalte 'id' haben. Unterschied ist, dass es nur einen Eintrag darunter gibt (geben sollte) dessen Wert in der Spalte 'status' = NULL ist, nämlich in dem Datensatz, mit dem aktuellen Seitentitel.

Die älteren Einträge müssen aber ja weiterhin vorhanden sein, damit solche Requests nicht 'ins Leere' laufen, sondern korrekt per 301 redirected werden.

Gruß Gunther

[1] Das_sollte_natürlich am besten nie vorkommen, aber zumindest würde ich diese Option gerne von vornherein mit einbauen, um hinterher nicht doch vor dem Problem zu stehen, eben genau solch einen Fall doch irgendwie "handeln" zu müssen.

[Hopsel]

Hi Gunther!

Das kann aber halt schon vorkommen, bspw. wenn sich ein Seitentitel eben doch mal (oder sogar mehrmals) ändern sollte[1]. Dann gibt es zwei oder mehr verschiedene Einträge in der Spalte 'res_url', die alle einen identischen Wert in der Spalte 'id' haben.

Ich meinte natürlich nicht deine "id", die du ja als Resource-ID bezeichnest.
Dieses Feld "id" ist ja mit Semantik versehen.

Ich bezog mich einfach auf den Primärschlüssel, der ohne Semantik sein sollte.
Wie der heißt, ist deine Sache. Tatsächlich nenne ich den Primärschlüssel in Relationen immer "id". Die Resource-ID würde bei mir "res_id" genannt werden.

MfG H☼psel

[Gunther]

Hi Hopsel!

Ich meinte natürlich nicht deine "id", die du ja als Resource-ID bezeichnest.
Dieses Feld "id" ist ja mit Semantik versehen.

Ach so - sag' das doch gleich ... ! ;-)

Ich bezog mich einfach auf den Primärschlüssel, der ohne Semantik sein sollte.

ACK

Wie der heißt, ist deine Sache. Tatsächlich nenne ich den Primärschlüssel in Relationen immer "id".

Bei mir normalerweise (was nicht so oft ist, deshalb ja auch mein fehlendes Wissen um die Dinge) auch.

Die Resource-ID würde bei mir "res_id" genannt werden.

Ja, so hieß sie ursprünglich auch, bis ich irgendwo irgendetwas von "ist eine Kombination von zwei Tabellen, in denen Spalten gleichen Namens existieren" gelesen habe, und mir dachte, dass sich dafür dann diese Spalte anbieten würde und deshalb auch für die weitere Behandlung im Script hier die kurze Bezeichnung 'id' praktischer wäre. Denn letztlich geht es ja nur darum, eben diese ID anhand des Dokumentnamens zu ermitteln. Alles weitere basiert ab da an im Prinzip ausschließlich auf dieser ID.

Gruß Gunther

[Ilja]

yo,

immer wieder interessant festzustellen, wie sehr normaliserung doch missverstanden wird.

Ilja

[Tom]

Hello Ilja,

immer wieder interessant festzustellen, wie sehr normaliserung doch missverstanden wird.

es wäre nett, wenn Du die Missverständnisse dann ausräumen könntest und nicht nur oberflächlich an einem Posting herummäkelst. :-)

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Ilja]

yo Tom,

es wäre nett, wenn Du die Missverständnisse dann ausräumen könntest und nicht nur oberflächlich an einem Posting herummäkelst. :-)

ist schwierig, aber klar, vorschläge und erläuterungen ist besser als nur mäkeln. aber das thema kann bücher füllen diese gibt es ja auch. leider auch meiner meinung nach oftmals falsche erklärung. das problem ist, normalisierung ist relativ und abhängig von der jeweiligen situation. und das ist nur meine meinung, jemand anderes wird eventuell was anderes erzählen.

um nun aber nicht ganz so geschwollen zu reden, sondern auch mal was handfestes zu sagen, fange ich mal mit der ersten normalform an. ein attribut muss/sollte Atomar sein. das allein ist birgt schon schwierigkeiten. Als Beispiel will ich mal zwei attribute angeben mit deren inhalten und dann fragen, ob es der ersten normalform entspricht.

entität: test (name ist egal)
attribute: (vorname, nachname, hobby)

beispieldatensatz: "Max" | "Mustermann" | "Tennis, Lesen"

die frage ist nun, welche der drei attribute sind in der ersten normalform.

Ilja

[Tom]

Hello,

um nun aber nicht ganz so geschwollen zu reden, sondern auch mal was handfestes zu sagen, fange ich mal mit der ersten normalform an. ein attribut muss/sollte Atomar sein. das allein ist birgt schon schwierigkeiten. Als Beispiel will ich mal zwei attribute angeben mit deren inhalten und dann fragen, ob es der ersten normalform entspricht.

entität: test (name ist egal)
attribute: (vorname, nachname, hobby)

beispieldatensatz: "Max" | "Mustermann" | "Tennis, Lesen"

die frage ist nun, welche der drei attribute sind in der ersten normalform.

Normales Beispiel.
Hobby ist auf jedenfalls eine mehrwertige Eigenschaft. Das gibt mMn den ersten offensichtlichen Handlungsbedarf.

http://de.wikipedia.org/wiki/Normalisierung_(Datenbank)#Erste_Normalform_.281NF.29

Die Seite finde ich ganz gut.

Nun kann aber ein anderer Knabe durchaus im Vornamen

Thomas Gustav Walter Jürgen

drinstehen haben. Müsste man also das Feld dann auch normalisieren bzw. in die Attribute "Rufname" und "weitere Vornamen" aufteilen, wobei "weitere Vornamen" dann als Relation angelegt werden müsste?

Manchmal hilft da nur nachdenken. :-))

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Ilja]

yo,

Hobby ist auf jedenfalls eine mehrwertige Eigenschaft. Das gibt mMn den ersten offensichtlichen Handlungsbedarf.

Nun kann aber ein anderer Knabe durchaus im Vornamen
    Thomas Gustav Walter Jürgen
drinstehen haben. Müsste man also das Feld dann auch normalisieren bzw. in die Attribute "Rufname" und "weitere Vornamen" aufteilen, wobei "weitere Vornamen" dann als Relation angelegt werden müsste?

und das zeigt ganz gut wie normalisierung missverstanden wird, schon in der ersten normalform, von den anderen mal ganz abgesehen. du kannst mit den infos, die ich dir gegen habe nicht klären, weil es eben relaitiv ist. das attribut hobby kann durchaus konform der ersten normalform sein. und lassen wir mal das beispiel weg, dass ein nachname auch mehrere namen beinhalten kann, so kann es trotzdem vorkommen, das der nachname "Max Mustermann" NICHT atomar ist. zumal noch hinzukommt, dass man normalformen nicht anhand von beispieldaten aufzeigen kann.

klingt im ersten moment schräg, warum "Mustermann" nicht in atomar sein sollte. aber die frage ist ja, woran halte ich fest, was atomar überhaupt ist. nach aufzählungen gleichen typs innerhalb eines feldes oder innerhalb einer entität reicht nicht, bzw. es bedinkt die frage, wie bildet sich ein typ. das kriterium, wann etwas atomar ist und wann nicht, jedenfalls aus meiner sichtweise, kann nur über den zugriff der daten erfolgen. sprich wenn ich immer, und die betonung liegt auf immer, den ganzen string von hobbys nehme und ihn niemals zerpflücke, dann ist er atomar. anders herum, "Mustermann" ist dann nicht mehr atomar, wenn ich ihn weiter aufsplitte.

nun darf man fragen, wieso in aller welt ich "Mustermann" weiter aufteilen will. für die begründung muss ich weiter ausholen. früher habe ich viel radio gehört, es gab da in berlin einen guten sender. wie auch immer, ab und zu war dort ein professor vertreten, der auskunft darüber gab, welche hintergründe ein familienname besitzt, zum beispiel woher er kommt, was er bedeutet, etc. und dazu hatte er teile des names benutzt. in seinem falle wäre der nachname nicht atomar, weil sein zugriff nicht nur den ganzen namen braucht. daran kann  man erkennen, wie stark die normalsierung bezogen auf die jeweilige umgebung ist. genauso verhält es sich mit der zweiten und dritten.

Ilja

[Tom]

Hello,

Wichtig ist u.a. auch die Unique Eigenschaft der Spalte res_url, da jede URL/ jeder Seitenname ja nur einmal existieren darf.

Wieso?
Wie sieht es aus mit Parametern und Ankern? Die gehören ja nicht zur URL, sondern hängen ihr an.
Und wie sieht es aus mit Versionsverwaltung?

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Gunther]

Hello Tom,

Wichtig ist u.a. auch die Unique Eigenschaft der Spalte res_url, da jede URL/ jeder Seitenname ja nur einmal existieren darf.

Wieso?
Wie sieht es aus mit Parametern und Ankern? Die gehören ja nicht zur URL, sondern hängen ihr an.

Diese Informationen, sofern vorhanden, werden von meinem Skript an anderer Stelle "verarbeitet".

Und wie sieht es aus mit Versionsverwaltung?

Diese Dinge wollte ich eher in die/ eine Tabelle mit weiteren Meta-Daten zu einer Seite/ Resource packen.
Oder habe ich dich missverstanden?

Gruß Gunther