MySQL 5 - ISBN 3-8273-2253-7 - © 2005 by ... · 8 Datenbankdesign Am Beginn jeder Datenbankanwendung steht das Design der Datenbank. Es hat großen Einfluss darauf,

8 Datenbankdesign Am Beginn jeder Datenbankanwendung steht das Design der Datenbank. Es hat großen Einfluss darauf, wie effizient die Anwendung wird, wie einfach oder kompliziert Programmierung und Wartung und wie flexibel die Lösungen bei eventuellen Änderungswünschen werden. Fehler, die in der Designphase passie-ren, lassen sich später nur mit sehr großem Aufwand korrigieren. Erwarten Sie aber keine Patentrezepte! Datenbankdesign hat viel mit Praxiserfahrung zu tun, und die kann in einem einzigen Kapitel nur in Form einer Einstiegshilfe vermittelt werden.

Dieses Kapitel geht auf die Grundlagen relationaler Datenbanken ein, fasst die un-ter MySQL zur Auswahl stehenden Daten- und Tabellentypen zusammen und de-monstriert anhand der mylibrary-Datenbank die Anwendung der so genannten Normalisierungsregeln. (Die mylibrary-Datenbank dient zur Verwaltung von Bü-chern, Autoren und Verlagen und kommt in vielen Beispielen dieses Buchs zum Einsatz.) Weitere Themen sind die richtige Anwendung von Indizes sowie Integri-tätsregeln (Foreign-Key-Regeln).

Verweis

Dieses Kapitel vermittelt ein Fundament für das Datenbankdesign, es erklärt aber nicht, wie eine neue Datenbank und deren Tabellen konkret erzeugt werden. Dazu bestehen zwei Möglichkeiten:

Am komfortabelsten ist es, eine Benutzeroberfläche wie MySQL Administrator oder phpMyAdmin einzusetzen (siehe Kapitel 5 bzw. 6). Damit können Sie die Eigenschaften neuer Tabellen einfach durch ein paar Mausklicks definieren.

Die andere Möglichkeit besteht darin, Datenbanken und Tabellen durch entsprechende SQL-Kommandos zu erzeugen (z.B. CREATE TABLE name ..., siehe Kapitel 9). Zwar ist die Formulierung solcher Kommandos relativ mühsam, der Vorteil besteht aber darin, dass solche Kommandos auch in einem PHP-Script ausgeführt werden können. Das kann praktisch sein, wenn Sie eine temporäre Tabelle erzeugen möchten.

http://www.awl.de/main/main.asp?page=bookdetails&ISBN=3827322537

http://www.pearsoned.de

8 Datenbankdesign

208 MySQL 5 (3. Auflage)MySQL 5 (3. Auflage)MySQL 5 (3. Auflage)MySQL 5 (3. Auflage) ISBN 38273-2253-7 [ www.kofler.cc / www.addison-wesley.de]

Weiterführende Literatur

Es gibt zahllose Bücher, die sich – unabhängig von spezifischen Datenbanksys-temen – ausschließlich mit dem Datenbankdesign und mit SQL beschäftigen. Natürlich gehen die Meinungen darüber auseinander, welche Bücher gut sind. Betrachten Sie die folgenden Empfehlungen daher einfach als meine persönliche Hitliste.

■ Joe Celko: SQL for Smarties, Morgan Kaufmann Publishers, 1999. (Nicht für SQL-Einsteiger; viele Beispiele sind in MySQL zurzeit nicht nachvollziehbar, weil MySQL noch nicht ausreichend ANSI-SQL/92-kompatibel ist; dennoch ein tolles, beispielorientiertes SQL-Buch mit Niveau.)

■ Judith S. Bowman et al: The Practical SQL Handbook, Addison-Wesley, 2001.

■ Michael J. Hernandez: Database Design for Mere Mortals, Addison-Wesley, 2003. (Die erste Hälfte ist langatmig, aber die zweite Hälfte exzellent und gut verständlich.)

Ebenfalls oft empfohlen wird das folgende Buch (das ich persönlich aber nicht kenne):

■ Peter Gulutzan, Trudy Pelzer: SQL-99 Complete, Really, R&D Books 1999.

Wenn Sie kein Geld für ein weiteres Buch ausgeben möchten und sich vorerst nur für das Datenbankdesign interessieren, reicht vielleicht auch schon die recht kom-pakte Einführung in das Design relationaler Datenbanken von Fernando Lozano (Englisch):

http://www.edm2.com/0612/msql7.html

Sehr viel ausführlicher, aber auch recht wissenschaftlich, ist die folgende Einfüh-rung in die Grundlagen relationaler Datenbanken von Andreas Kelz:

http://v.hdm-stuttgart.de/~riekert/lehre/db-kelz/

8.1 Tabellentypen Eine Besonderheit von MySQL besteht darin, dass Sie beim Erzeugen einer neuen Tabelle deren Typ angeben können. MySQL unterstützt mehrere verschiedene Ta-bellentypen, die sich durch eine Vielzahl von Eigenschaften unterscheiden. Die drei wichtigsten Typen heißen MyISAM, InnoDB und HEAP.

Wenn Sie beim Erzeugen einer neuen Tabelle keinen Typ angeben, entscheidet sich der MySQL-Server je nach Konfiguration für den Defaulttyp MyISAM oder InnoDB. Der Defaulttyp wird durch die Option default-table-type in der MySQL-Konfigurationsdatei eingestellt.

Dieser Abschnitt gibt einen kurzen Überblick darüber, welche Tabellentypen MySQL kennt, welche Eigenschaften diese haben und wann sich ihr Einsatz empfiehlt.

8.1 Tabellentypen

MySQL 5 (3. Auflage)MySQL 5 (3. Auflage)MySQL 5 (3. Auflage)MySQL 5 (3. Auflage) ISBN 38273-2253-7 [ www.kofler.cc / www.addison-wesley.de] 209

8.1.1 MyISAM-Tabellen

Der MyISAM-Tabellentyp ist ausgereift, stabil und einfach zu administrieren. Solange keine besonderen Gründe für einen anderen Tabellentyp sprechen, sollten Sie diesen Typ verwenden. Intern kommen zwei verschiedene Varianten dieses Ta-bellentyps zum Einsatz, wobei der MySQL-Server sich selbstständig für den pas-senden Typ entscheidet:

■ MyISAM – Static: Diese Tabellenvariante kommt zum Einsatz, wenn alle Spal-ten der Tabelle eine feste, vorgegebene Größe haben. Der Zugriff in solchen Tabellen ist besonders effizient. Das gilt selbst dann, wenn die Tabelle oft ge-ändert wird (wenn es also viele INSERT-, UPDATE- und DELETE-Kommandos gibt). Außerdem ist die Datensicherheit sehr hoch, weil es selbst bei be-schädigten Dateien oder anderen schwerwiegenden Problemen vergleichsweise einfach ist, Datensätze zu extrahieren.

■ MyISAM – Dynamic: Sobald es in der Deklaration einer Tabelle auch nur ein einziges VARCHAR-, xxxTEXT- oder xxxBLOB-Feld gibt, wählt MySQL auto-matisch diesen Tabellentyp. Der wesentliche Vorteil gegenüber der statischen MyISAM-Variante besteht darin, dass der Platzbedarf meist deutlich geringer ist: Zeichenketten und Binärobjekte beanspruchen nur entsprechend ihrer tat-sächlichen Größe Speicherplatz (plus ein paar Verwaltungsbytes).

Daraus ergibt sich aber, dass Datensätze unterschiedlich groß sind. Werden einzelne Datensätze später geändert, muss unter Umständen auch ihr Ort inner-halb der Datenbankdatei geändert werden. Am alten Platz entsteht dann ein Loch in der Datenbankdatei. Außerdem ist es möglich, dass die Felder eines Datensatzes nicht immer in einem zusammenhängenden Block innerhalb der Datenbankdatei gespeichert werden, sondern an unterschiedlichen Orten. All das bedingt, dass der Zugriff auf sich stark verändernde Tabellen zunehmend langsamer wird, wenn nicht hin und wieder ein OPTIMIZE TABLE oder ein Optimierungsprogramm ausgeführt wird (myisamchk, siehe Kapitel 14).

MyISAM – Compressed: Sowohl statische als auch dynamische MyISAM-Tabellen können mit dem Zusatzprogramm myisampack komprimiert werden. Damit sinkt der Platzbedarf der Tabelle zumeist auf weniger als die Hälfte (abhängig vom Inhalt der Tabelle). Zwar muss in der Folge jeder Datensatz beim Auslesen dekom-primiert werden, unter Umständen ist der Zugriff auf die Tabelle aber dennoch schneller – etwa bei der Kombination eines langsamen Laufwerks und eines schnellen Prozessors.

Der entscheidende Nachteil von komprimierten MyISAM-Tabellen besteht darin, dass diese nicht mehr geändert werden können (d.h., es handelt sich jetzt um Read-only-Tabellen!).

8 Datenbankdesign


8.1.2 InnoDB-Tabellen

Neben dem MyISAM-Format unterstützt MySQL mit InnoDB ein zweites Tabellen-format. Dabei handelt es sich um eine moderne Alternative zu MyISAM, die vor allem durch die folgenden Zusatzfunktionen überzeugt:

■ Transaktionen: Datenbankoperationen in InnoDB-Tabellen können als Trans-aktionen ausgeführt werden. Damit können Sie mehrere, logisch zusammen-hängende SQL-Kommandos als Einheit ausführen. Tritt dabei ein Fehler auf, werden sämtliche Kommandos (nicht nur das letzte) widerrufen. Daneben bie-ten Transaktionen eine Reihe weiterer Vorteile, um die Sicherheit von Daten-bankanwendungen zu verbessern.

Transaktionen können in allen vier durch den ANSI-SQL/92-Standard definier-ten Isolationsstufen durchgeführt werden (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ und SERIALIZABLE, siehe auch Abschnitt 10.15).

■ Row Level Locking: Zur Realisierung von Transaktionen verwendet der InnoDB-Tabellentreiber intern das Row Level Locking. Das bedeutet, dass während einer Transaktion nicht die gesamte Tabelle für andere Benutzer blockiert werden muss (wie dies bei einem LOCK-TABLE-Kommando für eine MyISAM-Tabelle der Fall ist), sondern nur die wirklich betroffenen Datensätze. Wenn viele Anwender gleichzeitig Änderungen an einer großen Tabelle durchführen, kann Row Level Locking einen enormen Geschwindigkeitsvorteil mit sich bringen.

Der InnoDB-Tabellentreiber erkennt automatisch Deadlocks (also den Zustand, dass sich zwei Prozesse gegenseitig blockieren) und beendet in diesem Fall automatisch einen der beiden Prozesse.

■ Foreign Key Constraints: Wenn Sie die Verknüpfungen zwischen Ihren Tabellen entsprechend definieren, kümmert sich der InnoDB-Tabellentreiber automatisch darum, dass die referenzielle Integrität der Tabellen bei Löschkom-mandos sichergestellt wird. Damit ist es ausgeschlossen, dass ein Datensatz der Tabelle A auf einen nicht (mehr) existierenden Datensatz der Tabelle B ver-weist. (In der Datenbanksprache heißt diese Funktion Foreign Key Constraint.)

■ Crash Recovery: InnoDB-Tabellen werden nach einem Absturz automatisch und sehr rasch wieder in einen konsistenten Zustand gebracht (sofern das Dateisystem des Rechners nicht beschädigt wurde). Diese Funktion habe ich allerdings nicht getestet.

Der InnoDB-Tabellentreiber ist seit Version 3.23.34 ein integraler Bestandteil von MySQL. Die Entwicklung des Tabellentreibers und der kommerzielle Support er-folgen durch die eigenständige Firma Innobase (siehe http://www.innodb.com).

8.1 Tabellentypen


Einschränkungen bzw. Nachteile

Wenn InnoDB-Tabellen nur Vorteile hätten, könnte man auf MyISAM-Tabellen ja gleich verzichten. Dazu besteht vorerst aber kein Anlass, wie die folgende Liste be-weist.

■ Tablespace-Administration: Während beim MyISAM-Tabellentreiber jede Tabelle in einer eigenen Datei gespeichert wird, die nach Bedarf wächst bzw. schrumpft, speichert der InnoDB-Tabellentreiber alle Daten und Indizes in einem so genannten Tablespace. Dabei handelt es sich um eine oder mehrere Dateien, die eine Art virtuelles Dateisystem bilden. Diese Dateien können nachträglich nicht mehr verkleinert werden.

Ebenso ist es nicht möglich, den MySQL-Server zu stoppen und anschließend eine Tabelle zu kopieren, indem einfach dessen Datei kopiert wird. Zur Ad-ministration von InnoDB-Tabellen muss daher häufiger als bei MyISAM-Tabellen das Kommando mysqldump zu Hilfe genommen werden.

■ Datensatzgröße: Jeder Datensatz darf maximal 8000 Bytes Daten enthalten. Diese Grenze gilt nicht für TEXT- und BLOB-Spalten, von denen lediglich die ersten 512 Byte in der Datenbankseite gespeichert werden.

■ Platzbedarf: Der Platzbedarf für InnoDB-Tabellen ist deutlich höher als für äquivalente MyISAM-Tabellen (bis zu doppelt so groß).

■ Volltextindex: Für InnoDB-Tabellen kann kein Volltextindex verwendet wer-den.

■ GIS-Daten: In InnoDB-Tabellen können keine Geometriedaten gespeichert werden.

■ COUNT-Problem: Aufgrund offener Transaktionen ist es für den InnoDB-Ta-bellentreiber relativ aufwändig, die Anzahl der Datensätze einer Tabelle festzu-stellen. Die Ausführung von SELECT COUNT(*) FROM TABLE ist daher viel langsamer als bei MyISAM-Tabellen. Diese Einschränkung soll aber demnächst behoben werden.

■ Tabellen-Locking: InnoDB verwendet zur Durchführung von Transaktionen eigene Locking-Algorithmen. Daher sollten Sie den Einsatz von LOCK TABLE ... READ/WRITE vermeiden. Verwenden Sie stattdessen SELECT ... IN SHARE MODE oder SELECT ... FOR UPDATE. Diese Kommandos haben zudem den Vorteil, dass sie nur einzelne Datensätze und nicht gleich die ganze Tabelle blockieren. Für künftige MySQL-Versionen sind die InnoDB-spezifischen Kom-mandos LOCK TABLE ... IN SHARE MODE bzw. IN EXCLUSIVE MODE ge-plant.

■ mysql-Tabellen: Die mysql-Tabellen zur Verwaltung der MySQL-Zugriffsrechte und -Privilegien dürfen nicht in InnoDB-Tabellen umgewandelt werden. Sie müssen im MyISAM-Format bleiben.

8 Datenbankdesign


■ Lizenzkosten: Eine kommerzielle MySQL-Lizenz mit InnoDB-Unterstützung kostet doppelt so viel wie ohne. (Das ist nur relevant, wenn Sie ein kommerziel-les Produkt entwickeln. Bei Open-Source-Programmen, Indoor-Projekten und natürlich bei gewöhnlichen Webseiten reicht die kostenlose MySQL-Version aus – siehe auch Kapitel 1.)

Verweis

Weitere Details zu den Einschränkungen von InnoDB-Tabellen gegenüber MyISAM-Tabellen finden Sie hier:

http://dev.mysql.com/doc/mysql/en/innodb-restrictions.html

MyISAM oder InnoDB?

Sie können für jede Tabelle einer Datenbank getrennt angeben, welcher Tabellen-treiber verwendet werden soll, d.h., es ist durchaus erlaubt, innerhalb einer Daten-bank sowohl MyISAM- als auch InnoDB-Tabellen einzusetzen. Damit können Sie je nach Inhalt und Verwendung einer Tabelle den optimalen Tabellentreiber einsetzen.

MyISAM-Tabellen bieten sich immer dann an, wenn Sie Tabellen möglichst Platz sparend und effizient verwalten möchten. InnoDB-Tabellen sind hingegen dann zu empfehlen, wenn Sie aufgrund Ihrer Anwendung auf Transaktionen angewiesen sind, höhere Sicherheitsanforderungen stellen oder wenn viele Anwender gleich-zeitig Änderungen an Tabellen durchführen müssen.

Auf die Frage, welcher Tabellentyp denn nun schneller ist, gibt es leider keine all-gemein gültige Antwort. Grundsätzlich kostet der Verwaltungsaufwand für Trans-aktionen Zeit und Speicherplatz auf der Festplatte – insofern haben MyISAM-Tabellen einen prinzipbedingten Vorteil. Wenn Sie dank InnoDB-Tabellen aber auf LOCK-TABLE-Kommandos verzichten können, wendet sich das Blatt zugunsten von InnoDB; auch sind manche Datenbankoperationen bei InnoDB besser optimiert.

Von diesen grundsätzlichen Faktoren abgesehen, hängt die Geschwindigkeit stark von der Hardware (insbesondere von der RAM-Größe!), von den Einstellungen in der MySQL-Konfigurationsdatei etc. ab. Daher kann ich hier nur den Tipp geben, bei geschwindigkeitskritischen Anwendungen selbst einige Tests mit beiden Tabel-lentypen durchzuführen.

8.1 Tabellentypen


8.1.3 HEAP-Tabellen

HEAP-Tabellen werden nur im RAM angelegt (nicht auf der Festplatte) und ver-wenden einen so genannten Hash-Index, der einen besonders schnellen Zugriff auf einzelne Datensätze ermöglicht. Oft werden HEAP-Tabellen nur temporär erzeugt (siehe den folgenden Abschnitt).

Gegenüber normalen Tabellen gibt es bei HEAP-Tabellen eine ganze Reihe funk-tionaler Einschränkungen, von denen hier nur die wichtigsten genannt sind: Es dürfen keine xxxTEXT- oder xxxBLOB-Datentypen verwendet werden, nach Datensätzen kann nur mit = oder <=> gesucht werden (nicht mit <, >, <=, >=), AUTO_INCREMENT wird nicht unterstützt, und Indizes können nur für NOT-NULL-Spalten erstellt werden.

HEAP-Tabellen sollten immer dann eingesetzt werden, wenn verhältnismäßig kleine Datenmengen mit maximaler Geschwindigkeit verwaltet werden. Da HEAP-Tabellen nur im RAM gespeichert werden, gehen sie verloren, sobald My-SQL beendet wird. Die maximale Größe von HEAP-Tabellen wird in der MySQL-Konfigurationsdatei durch den Parameter max_heap_table_size festgelegt.

8.1.4 Temporäre Tabellen

Bei allen oben aufgezählten Tabellentypen besteht die Möglichkeit, die Tabellen temporär zu erzeugen. Solche Tabellen werden automatisch wieder gelöscht, so-bald die Verbindung zu MySQL beendet wird. Zudem sind temporäre Tabellen für andere MySQL-Verbindungen unsichtbar (so dass es möglich ist, dass zwei An-wender temporäre Tabellen mit denselben Namen verwenden, ohne sich dabei in die Quere zu kommen).

Temporäre Tabellen sind kein eigener Tabellentyp, sondern nur eine Variante zu den oben beschriebenen Typen. Temporäre Tabellen werden manchmal auto-matisch von MySQL angelegt, um komplexe SELECT-Abfragen auszuführen.

Temporäre Tabellen werden nicht im selben Verzeichnis wie die anderen MySQL-Tabellen gespeichert, sondern in einem eigenen temporären Verzeichnis (unter Windows zumeist C:\Windows\Temp, unter Unix/Linux zumeist /tmp oder /var /tmp oder /usr/tmp). Das Verzeichnis kann beim Start von MySQL eingestellt wer-den.

8 Datenbankdesign


8.1.5 Andere Tabellentypen

MySQL kennt noch eine Reihe weiterer Tabellentypen, von denen im Folgenden die wichtigsten Varianten kurz aufgezählt werden. Beachten Sie, dass diese Ta-bellentypen nur in der Max-Version bzw. in eigens kompilierten MySQL-Versio-nen zur Verfügung stehen. Welche Tabellentypen Ihre MySQL-Version unterstützt, können Sie ganz einfach mit SHOW ENGINES feststellen.

■ BDB-Tabellen: BDB-Tabellen waren historisch der erste transaktionsfähige MySQL-Tabellentyp. Seit der InnoDB-Tabellentreiber ausgereift ist, besteht aber kaum noch ein Grund, BDB-Tabellen zu verwenden.

■ Komprimierte Tabellen (Tabellentyp ARCHIVE, ab MySQL 4.1): Dieser Tabellentyp ist zur Archivierung bzw. Protokollierung großer Datenmengen gedacht. Der Vorteil des Tabellentyps besteht darin, dass die Datensätze sofort beim Speichern komprimiert werden.

ARCHIVE-Tabellen sind allerdings nur zweckmäßig, wenn sich vorhandene Datensätze nicht mehr ändern. (INSERT ist zulässig, UPDATE und DELETE können aber nicht verwendet werden!)

ARCHIVE-Tabellen können nicht indiziert werden. Für jedes SELECT-Kom-mando müssen daher alle Datensätze gelesen werden! Verwenden Sie den Ta-bellentyp also nur, wenn Sie nur relativ selten Datenzugriffe erwarten.

■ Tabellen im Textformat (Tabellentyp CSV ab MySQL 4.1): Datensätze von CSV-Tabellen werden einfach als Textdateien im Format Comma Separated Values gespeichert, also z.B. so:

"123","Zeichenkette"

CSV-Tabellen können nicht indiziert werden.

■ NDB-Tabellen (MySQL Cluster, ab MySQL 4.1): Der NDB-Tabellentyp ist Teil der MySQL-Cluster-Funktionen, die in die MySQL-Max-Version integriert sind. (NDB steht für Network Database.) Der Tabellentyp ist transaktionsfähig und eignet sich besonders gut für Datenbanken, die über mehrere (viele!) Rechner verteilt sind. Allerdings erfordert die Anwendung dieses Tabellentyps, dass zu-vor mehrere MySQL-Max-Installationen speziell für den Cluster-Betrieb konfi-guriert werden. Detaillierte Informationen finden Sie unter:

http://dev.mysql.com/doc/mysql/en/ndbcluster.html

■ Externe Tabellen (Tabellentyp FEDERATED, ab MySQL 5.0): Dieser Tabellen-typ ermöglicht es, auf eine Tabelle in einer externen Datenbank zuzugreifen. Das Datenbanksystem kann sich beispielsweise auf einem anderen Rechner im lokalen Netzwerk befinden. Momentan muss es sich bei der externen Daten-bank um MySQL handeln, es wäre aber denkbar, dass MySQL in Zukunft auch die Einbindung anderer Datenbanksysteme erlaubt.

8.1 Tabellentypen


Beim Zugriff auf FEDERATED-Tabellen gelten einige Einschränkungen: Weder Transaktionen noch die Abfrageoptimierung durch den Query Cache sind mög-lich. Der Aufbau der externen Tabelle darf nicht verändert werden (wohl aber deren Inhalt). Mit anderen Worten: ALTER TABLE ist nicht zulässig, INSERT, UPDATE und DELETE können dagegen verwendet werden.

Weitere Informationen zu sämtlichen MySQL-Tabellentypen finden Sie in einem eigenen Kapitel des MySQL-Handbuchs:

http://dev.mysql.com/doc/mysql/en/storage-engines.html

8.1.6 Tabellendateien

Der Ort für Datenbankdateien kann beim Start von MySQL vorgegeben werden. (Unter Unix/Linux wird oft /var/lib/mysql verwendet, unter Windows meist C:\Programme\MySQL\MySQL Server n.n\data.) Alle weiteren Angaben gelten relativ zu diesem Verzeichnis.

Die Beschreibung jeder Tabelle wird in einer *.frm-Datei gespeichert. Die *.frm-Dateien befinden sich wiederum in Verzeichnissen, die dem Datenbanknamen entsprechen:

data/dbname/tablename.frm Tabellenschema (Datentypen der Spalten etc.)

Ab MySQL 4.1 wird im Datenbankverzeichnis eine weitere Datei db.opt gespei-chert. Sie enthält Einstellungen, die für die gesamte Datenbank gelten.

data/dbname/db.opt Datenbankeinstellungen ab MySQL 4.1

Für jede MyISAM-Tabelle werden zwei weitere Dateien angelegt:

data/dbname/tablename.MYD MyISAM-Tabellendaten data/dbname/tablename.MYI MyISAM-Indizes (alle Indizes der Tabelle)

InnoDB-Tabellen werden in individuellen Dateien für jede Tabelle oder gemein-sam im so genannten Tablespace gespeichert (je nachdem, ob in der MySQL-Konfi-gurationsdatei innodb_file_per_table angegeben wurde oder nicht). Auch der Ort und Name der Tablespace-Datei(en) wird durch Konfigurationseinstellungen ge-steuert. Bei aktuellen MySQL-Installationen gilt per Default:

data/dbname/tablename.ibd InnoDB-Tabellendaten (Daten und Indizes) data/ibdata1, -2, -3 Tablespace, Undo-Logs data/ib_logfile0, -1, -2 InnoDB-Protokolldateien (Logging)

Falls für die Tabellen Trigger definiert sind (siehe Kapitel 13), wird deren Code momentan ebenfalls in einer Datei gespeichert. Es ist aber möglich, dass sich das in künftigen MySQL-Versionen ändert.

data/dbname/tablename.TRG Trigger-Code

8 Datenbankdesign


8.2 MySQL-Datentypen Jede Tabelle setzt sich aus mehreren Spalten zusammen. Für jede Spalte müssen Sie den gewünschten Datentyp angeben. Dieser Abschnitt gibt einen Überblick über die in MySQL verfügbaren Datentypen.

8.2.1 Integerzahlen (xxxINT)

Bei den INT-Datentypen sind normalerweise sowohl positive als auch negative Zahlen erlaubt. Mit dem Attribut UNSIGNED kann der Zahlenbereich auf aus-schließlich positive Zahlen eingeschränkt werden. Beachten Sie aber, dass dann auch Subtraktionen UNSIGNED-Zahlen liefern, was bisweilen zu irreführenden Resultaten führt.

Bei TINYINT sind also Zahlen zwischen -128 und +127 erlaubt. Mit dem Attribut UNSIGNED verändert sich der Zahlenbereich dagegen zu 0-255. Beim Versuch, Zahlen zu speichern, die außerhalb des zulässigen Bereichs liegen, ersetzt MySQL die Werte einfach durch die größte bzw. kleinste zulässige Zahl.

MySQL-Datentyp Bedeutung

TINYINT(m) 8-Bit-Integer (1 Byte, -128 bis +127); der optionale Wert m gibt die gewünschte Spaltenbreite in SELECT-Ergebnissen an (Maximum Display Width), hat aber keinen Einfluss auf den zulässigen Wertebereich der Zahlen.

SMALLINT(m) 16-Bit-Integer (2 Byte, -32.768 bis + 32.767)

MEDIUMINT(m) 24-Bit-Integer (3 Byte, -8.388.608 bis +8.388.607)

INT(m), INTEGER(m) 32-Bit-Integer (4 Byte, -2.147.483.648 bis +2.147.483.647)

BIGINT(m) 64-Bit-Integer (8 Byte, ±9,22*1018)

SERIAL Synonym für BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY

Tabelle 8.1: Datentypen für ganze Zahlen

Optional kann bei der Definition eines Integerfelds die gewünschte Spaltenbreite (Stellenanzahl) angegeben werden, also z.B. INT(4). Der Parameter wird in der Literatur mit M bezeichnet (Maximum Display Size). Er hilft MySQL bzw. diversen Benutzeroberflächen bei der übersichtlichen Formatierung von Abfrageergebnis-sen.

Beachten Sie bitte, dass M bei den INT-Datentypen weder den zulässigen Zahlen-bereich noch die erlaubte Stellenanzahl einschränkt. Trotz INT(4) können Sie also beispielsweise Zahlen größer als 9999 speichern. Allerdings kann es in seltenen Fällen (etwa bei komplizierten Abfragen, zu deren Auswertung MySQL automa-tisch temporäre Tabellen bildet) dazu kommen, dass Zahlenwerte in den tempo-rären Tabellen abgeschnitten werden und so falsche Ergebnisse zustande kommen.

8.2 MySQL-Datentypen


AUTO_INCREMENT-Integerzahlen

Mit dem optionalen Attribut AUTO_INCREMENT für Integerzahlen wird erreicht, dass MySQL beim Anlegen eines neuen Datensatzes für dieses Feld automatisch eine Zahl einfügt, die um eins größer ist als der zurzeit größte Wert in dieser Spalte. AUTO_INCREMENT wird zumeist bei der Definition von Feldern verwen-det, die als Primärschlüssel einer Tabelle dienen sollen.

Im Zusammenhang mit AUTO_INCREMENT gelten einige Sonderregeln:

■ Das Attribut ist nur zulässig, wenn gleichzeitig die Attribute NOT NULL sowie PRIMARY KEY oder UNIQUE verwendet werden.

■ Es ist nicht zulässig, dass eine Tabelle mehrere AUTO_INCREMENT-Spalten besitzt.

■ Die automatische Generierung eines ID-Werts funktioniert nur, wenn beim Ein-fügen eines neuen Datensatzes durch INSERT kein spezifischer Wert oder NULL für das Feld angegeben wird. Es ist aber möglich, einen neuen Datensatz mit einem ganz bestimmten ID-Wert zu erzeugen (sofern der Wert noch nicht vergeben ist).

■ Wenn Sie herausfinden möchten, welchen AUTO_INCREMENT-Wert ein neu eingefügter Datensatz bekommen hat, führen Sie nach dem INSERT-Kommando (aber noch innerhalb derselben Verbindung oder Transaktion) das Kommando SELECT LAST_INSERT_ID() aus.

■ Wenn der AUTO_INCREMENT-Zähler den Maximalwert des jeweiligen Inte-gerzahlenformats erreicht hat, wird er nicht mehr weiter erhöht. Es sind dann keine Einfügeoperationen mehr möglich. Bei Tabellen mit sehr vielen Einfüge- und Löschoperationen kann es daher passieren, dass der 32-Bit-INT-Zahlen-raum mit der Zeit aufgebraucht wird, auch wenn sich tatsächlich weitaus weniger als ca. 2 Milliarden Datensätze in der Tabelle befinden. Verwenden Sie gegebenenfalls eine BIGINT-Spalte!

Binäre Daten (BIT, BOOL)

Das Schlüsselwort BOOL gilt in MySQL als Synonym für TINYINT. Dasselbe galt bis Version 5.0.2 auch für BIT. Beginnend mit Version 5.0.3 ist BIT aber ein echter Datentyp zur Speicherung von binären Werten mit bis zu 64 Bits (siehe Abschnitt 8.2.6).

8 Datenbankdesign


8.2.2 Fließkommazahlen (FLOAT und DOUBLE)

Seit der Version MySQL 3.23 entsprechen die Typen FLOAT und DOUBLE den in vielen Programmiersprachen verfügbaren IEEE-Zahlentypen für einfache und doppelte Genauigkeit.

Optional kann die Anzahl der Stellen bei FLOAT und DOUBLE durch die zwei Parameter M und D eingestellt werden. In diesem Fall gibt M die Anzahl der Stellen vor dem Dezimalpunkt an, D jene nach dem Dezimalpunkt.

M hilft lediglich bei der Formatierung der Zahlen, schränkt aber den Zahlenbereich nicht ein. D bewirkt hingegen eine Rundung der Zahlen beim Speichern. Wenn Sie in einem Feld mit DOUBLE(6,3) die Zahl 123456.789012 zu speichern versuchen, wird tatsächlich 123456.789 gespeichert. Aus 123456.789888 wird 123456.790.

Datentyp Bedeutung

FLOAT(m, d) Fließkommazahl, 8 Stellen Genauigkeit (4 Byte); die optionalen Werte m und d geben die gewünschte Anzahl von Stellen vor und nach dem Dezimalpunkt in SELECT-Ergebnissen an; die Werte haben aber keinen Einfluss auf die tatsächliche Speicherung der Zahlen.

DOUBLE(m, d) Fließkommazahl, 16 Stellen Genauigkeit (8 Byte)

REAL(m, d) Synonym für DOUBLE

Tabelle 8.2: Datentypen für Fließkommazahlen

MySQL erwartet Fließkommazahlen in der internationalen Notation, also mit einem Dezimalpunkt (kein Komma!). Aus diesem Grund werden auch in diesem Buch Dezimalzahlen mit einem Punkt angegeben. Ergebnisse von Abfragen werden ebenfalls in dieser Notation geliefert, sehr große bzw. sehr kleine Zahlen auch in der wissenschaftlichen Notation (z.B. 1.2345678901279e+017).

Wenn Sie Fließkommazahlen anders formatieren möchten, müssen Sie dazu ent-weder in der SQL-Abfrage die Funktion FORMAT einsetzen (diese Funktion hilft aber nur bei der Tausendergruppierung von Zahlen) oder die Formatierung in der Client-Programmiersprache durchführen (also in PHP, Perl etc.).

8.2.3 Festkommazahlen (DECIMAL)

Der Zahlentyp DECIMAL ist immer dann zu empfehlen, wenn Rundungsfehler durch die interne binäre Darstellung von Zahlen bei FLOAT oder DOUBLE ausgeschlossen werden sollen – etwa bei Geldbeträgen. Da die Zahlen als Zeichenketten gespeichert werden, ist der Speicherbedarf viel größer. Gleichzeitig ist der mögliche Wertebereich aber deutlich kleiner, weil keine Exponentialschreib-weise vorgesehen ist.



Datentyp Bedeutung

DECIMAL(p, s) Festkommazahl, wird als Zeichenkette gespeichert; beliebige Stellenanzahl (ein Byte pro Ziffer + 2 Byte Verwaltung); p gibt die gesamte Stellenanzahl an, s die Anzahl der Stellen hinter dem Komma; p und s bestimmen den zulässigen Wertebereich für die Zahl (im Gegensatz zu den Parametern m und d, die nur für die SELECT-Ausgabe relevant sind); per Default gilt DECIMAL(10,0).

NUMERIC, DEC Synonyme für DECIMAL

Tabelle 8.3: Datentypen für Festkommazahlen

Die beiden Parameter P und S geben die gesamte Stellenanzahl (Precision) bzw. die Anzahl der Stellen hinter dem Dezimalpunkt (Scale) an. Der Zahlenbereich bei DECIMAL(6,3) reicht von 9999.999 bis -999.999. Dieser merkwürdige Bereich ergibt sich daraus, dass offensichtlich sechs Stellen plus ein Zeichen für das negative Vorzeichen reserviert werden. Bei positiven Zahlen wird auch die Stelle für das Vorzeichen zur Speicherung einer Ziffer benutzt. Wenn P und S nicht angegeben werden, verwendet MySQL automatisch (10,0), so dass positive Zahlen mit elf Stellen, negative mit zehn Stellen gespeichert werden können.

8.2.4 Datum und Uhrzeit (DATE, TIME, DATETIME, TIMESTAMP)

Die folgende Tabelle fasst die Datentypen zur Speicherung von Zeitwerten zusam-men.

MySQL-Schlüsselwort Bedeutung

DATE Datum in der Form '2006-12-31' (3 Byte); Bereich 1000-01-01 bis 9999-12-31

TIME Zeit in der Form '23:59:59' (3 Byte); Bereich +/-838:59:59

DATETIME Kombination aus DATE und TIME in der Form '2006-12-31 23:59:59' (8 Byte)

YEAR Jahreszahl 1900-2155 (1 Byte)

TIMESTAMP Datum und Zeit in der Form '2006-12-31 23:59:59' für Zeiten zwischen 1970 und 2038 (4 Byte); der Inhalt der TIMESTAMP-Spalte wird von MySQL automatisch bei jeder Änderung aktualisiert.

Tabelle 8.4: Datentypen für Datum und Uhrzeit

8 Datenbankdesign


Validierung der Daten

Bei älteren MySQL-Versionen wurde bei den DATE- und den DATETIME-Daten-typen nur eine teilweise Validierung durchgeführt. Monatsangaben zwischen 0 und 12 sowie Tagesangaben zwischen 0 und 31 waren generell erlaubt. Es liegt in der Verantwortung des Client-Programms, korrekte Daten zu übergeben. (0 als Monats- oder Tagesnummer gibt die Möglichkeit, auch unvollständige bzw. teil-weise unbekannte Daten zu speichern.)

Beginnend mit MySQL 5.0.2 findet eine gründlichere Validierung statt, so dass nur noch gültige Daten gespeichert werden können. Weiterhin zulässig sind die Monats- und Tagesangaben 0 sowie das Datum 0000-00-00.

Die Validierung kann durch die MySQL-Systemvariable sql_mode gesteuert werden (siehe auch Abschnitt 14.2.7). Die folgende Tabelle fasst die sql_mode-Werte zusam-men, die für die Zeitvalidierung relevant sind.

Einstellung Bedeutung

ALLOW_INVALID_DATES Offensichtlich falsche Datumsangaben (z.B. 2005-02-31) werden akzeptiert.

NO_ZERO_DATE 0000-00-00 wird nicht mehr als gültiges Datum akzeptiert.

NO_ZERO_IN_DATE 0 wird nicht als gültige Monats- oder Tagesangabe akzeptiert.

Tabelle 8.5: sql_mode-Einstellungen

TIMESTAMP-Besonderheiten

Unter den Datentypen für Datum und Zeit nimmt TIMESTAMP eine Sonderrolle ein. Felder dieses Typs werden bei normalerweise jeder Veränderung des Daten-satzes automatisch aktualisiert und spiegeln damit den Zeitpunkt der letzten Ver-änderung wider. TIMESTAMP-Felder werden daher im Regelfall nur als Hilfs-mittel für die interne Verwaltung verwendet, nicht aber dazu, um dort eigene Daten zu speichern (obwohl auch das möglich ist).

Manche Datenbankoperationen mit bestimmten Client-Bibliotheken (etwa mit Connector/ODBC) funktionieren nur dann, wenn jede Tabelle der Datenbank eine TIMESTAMP-Spalte aufweist. Der Zeitpunkt der letzten Änderung wird also oft zur internen Administration der Daten benötigt.

Damit die automatische TIMESTAMP-Aktualisierung funktioniert, muss der Spalte bei INSERT- oder UPDATE-Kommandos entweder gar kein expliziter Wert oder NULL zugewiesen werden. In beiden Fällen setzt MySQL selbst die aktuelle Zeit ein. Falls es in einer Tabelle mehrere TIMESTAMP-Spalten gibt, wird die erste Spalte aktualisiert, für die nicht explizit ein konstanter Zeitweit definiert ist (DEFAULT 0).



Seit MySQL 4.1.3 ist es möglich, das Verhalten von TIMESTAMP-Spalten durch zwei Attribute genauer zu steuern. Mögliche Attributkombinationen sind in der folgenden Tabelle zusammengefasst:

Einstellung Bedeutung

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Die Spalte wird sowohl beim Erzeugen eines neuen Datensatzes als auch bei jeder Änderung automatisch aktualisiert.

TIMESTAMP Wie oben, aber mit weniger Tippaufwand.

TIMESTAMP DEFAULT CURRENT_TIMESTAMP Die Spalte wird beim Erzeugen eines neuen Datensatzes initialisiert, aber später nicht mehr geändert.

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Die Spalte wird beim Erzeugen mit 0 initialisiert; erst bei späteren Änderungen wird die aktuelle Zeit gespeichert.

TIMESTAMP DEFAULT 'yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

Die Spalte wird beim Erzeugen mit der angegebenen Zeit initialisiert; erst bei späteren Änderungen wird die aktuelle Zeit gespeichert.

Tabelle 8.6: TIMESTAMP-Varianten

Achtung

Verwenden Sie keine TIMESTAMP-Spalte, wenn Sie Datum und Uhrzeit selbst speichern wollen – für solche Zwecke ist der Datentyp DATETIME vorgesehen!

Falls Sie abweichend vom Defaultverhalten bei einer Änderung des Datensatzes keine Veränderung der TIMESTAMP-Spalte wünschen, müssen Sie das gewünschte Datum bzw. die Uhrzeit explizit an-geben:

UPDATE tablename SET col='neuer wert', ts=ts;

MySQL-Versionen bis einschließlich 4.0 formatierten TIMESTAMP-Werte in der Form YYYYMMDDHHMMDD (anstatt nunmehr in der Form YYYY-MM-DD HH:MM:DD). Daraus können eventuell Inkompatibilitäten bei der Weiterverarbeitung entstehen. Addieren Sie 0, wenn Sie die alte Schreibweise bevorzugen (also SELECT ts+0 FROM table).

8 Datenbankdesign


Mikrosekunden: In künftigen MySQL-Versionen wird es voraussichtlich möglich sein, in TIMESTAMP-Spalten auch Mikrosekunden zu speichern. Die Syntax für die Zeitangaben ist bereits festgelegt (2005-31-12 23:59:59.nnnnnn), und es gibt auch schon diverse MICROSECOND-Funktionen zur Verarbeitung dieser Daten. Eine tatsächliche Speicherung ist aber in MySQL 5.0 noch nicht möglich und wird wohl erst MySQL 5.1 funktionieren.

Funktionen zur Bearbeitung und Formatierung von Daten und Zeiten

MySQL liefert Datumsangaben in der Form 2005-12-31. Bei INSERT- oder UP-DATE-Operationen kommt MySQL aber auch mit anderen Formaten zurecht, so-fern die Reihenfolge Jahr/Monat/Tag eingehalten wird und die Angaben nume-risch erfolgen. Wenn die Jahreszahl zweistellig angegeben wird, gelten folgende Interpretationsregeln:

70-99: 1970-1999 00-69: 2000-2069

Wenn Abfrageergebnisse speziell formatiert werden sollen, helfen die diversen MySQL-Funktionen zur Bearbeitung von Datums- und Zeitwerten. Am flexibels-ten ist DATE_FORMAT, deren Anwendung im folgenden Beispiel demonstriert wird:

SELECT DATE_FORMAT(birthdate, '%Y %M %e') FROM students

1977 September 3 1981 October 25 ..

Verweis

DATE_FORMAT sowie zahlreiche weitere SQL-Funktionen zur Ver-arbeitung bzw. zur Durchführung von Berechnungen mit Daten/ Zeiten werden in Abschnitt 10.2 vorgestellt. Dort lernen Sie auch, wie Sie Zeiten in verschiedene Zeitzonen umrechnen. Eine Referenz aller Zeit- und Datumsfunktionen gibt Abschnitt 21.7.



8.2.5 Zeichenketten (CHAR, VARCHAR, xxxTEXT)

Die folgende Tabelle fasst die Datentypen zur Speicherung von Zeichenketten zu-sammen.


CHAR(n) Zeichenkette mit vorgegebener Länge, maximal 255 Zeichen

VARCHAR(n) Zeichenkette mit variabler Länge, maximal n Zeichen (MySQL bis 4.1: n<256; MySQL ab 5.0.3: n<65.535)

TINYTEXT Zeichenkette mit variabler Länge, maximal 255 Zeichen

TEXT Zeichenkette mit variabler Länge, maximal 216-1 Zeichen

MEDIUMTEXT Zeichenkette mit variabler Länge, maximal 224-1 Zeichen

LONGTEXT Zeichenkette mit variabler Länge, maximal 232-1 Zeichen

Tabelle 8.7: Datentypen für Zeichenketten

Bei CHAR ist die Länge der Zeichenkette starr vorgegeben. CHAR(20) beansprucht in jedem Datensatz 20 Byte, egal wie lang die tatsächlich gespeicherte Zeichenkette ist. (Leerzeichen am Beginn der Zeichenkette werden vor dem Speichern elimi-niert. Zu kurze Zeichenketten werden am Ende mit Leerzeichen verlängert. Diese Leerzeichen werden beim Auslesen der Daten automatisch wieder entfernt, so dass es also nicht möglich ist, Leerzeichen am Ende einer Zeichenkette zu speichern.)

Im Gegensatz dazu ist die Länge der Zeichenkette bei VARCHAR und bei den vier TEXT-Typen variabel. Der Speicherbedarf hängt von der tatsächlichen Länge der Zeichenkette ab.

Obwohl VARCHAR und TEXT, die beide bis zu 65.535 Zeichen aufnehmen können, auf den ersten Blick gleichwertig aussehen, gibt es einen wesentlichen Unterschied: Die Maximalanzahl der Zeichen in VARCHAR-Spalten muss bereits bei der Deklaration der Tabelle vorgegeben werden (im Bereich 0 bis 65.535). Zu lange Zeichenketten werden beim Speichern ohne Fehlermeldung abgeschnitten. Im Gegensatz dazu ist bei xxxTEXT-Spalten keine Längenbegrenzung möglich. (Die einzige Begrenzung besteht in der Maximallänge des jeweiligen Texttyps.)

VARCHAR-Neuerungen: In MySQL 5.0 gibt es zwei wesentliche Neuerungen für den Datentyp VARCHAR. (In der getesteten MySQL-Version 5.0.3 stand die neue VARCHAR-Implementierung allerdings erst für MyISAM-Tabellen zur Verfügung, nicht aber für InnoDB-Tabellen.)

■ Die maximale Spaltengröße beträgt in MyISAM-Tabellen nun 65.535 Byte (bis-her 255 Zeichen). Die maximale Zeichenanzahl hängt vom Zeichensatz ab, weil bei manchen Zeichensätzen mehrere Byte pro Zeichen erforderlich sind.

8 Datenbankdesign


■ Leerzeichen am Beginn und Ende von VARCHAR-Werten werden nun in der Tabelle gespeichert. Durch INSERT INTO table (varcharcolumn) VALUES (' abc ') wird also tatsächlich ' abc ' in der Spalte gespeichert, also ein Leerzeichen, die Zeichen a, b und c und schließlich noch ein Leerzeichen. (Bisher hat MySQL Leerzeichen am Ende von VARCHAR-Werten gelöscht, was dem ANSI-Stan-dard widersprach.)

BINARY-Attribut: CHAR- und VARCHAR-Spalten können optional mit dem Attri-but BINARY versehen werden. Sie verhalten sich dann im Wesentlichen wie BLOB-Spalten (siehe den nächsten Abschnitt). Das Attribut BINARY kann aber auch bei der Verarbeitung von Text sinnvoll sein: Sie erreichen damit, dass beim Sortieren ausschließlich der Binärcode der Zeichen berücksichtigt wird (keine Sortiertabel-len). Ebenso wird bei Vergleichen zwischen Groß- und Kleinschreibung differen-ziert, was sonst nicht der Fall ist. Das macht die interne Handhabung binärer Zeichenketten einfacher und daher schneller als die gewöhnlicher Zeichenketten.

Achtung

Beim Erzeugen neuer Tabellen ändert MySQL in manchen Fällen die Spaltendefinition in eine Form, die für MySQL effizienter ist (siehe auch Abschnitt 9.9). Diese automatischen Änderungen werden vom MySQL-Handbuch als Silent Column Changes bezeichnet und betref-fen auch CHAR- und VARCHAR-Spalten:

■ VARCHAR(n) mit n<4 wird immer in CHAR(n) umgewandelt.

■ CHAR(n) mit n>3 wird in VARCHAR(n) umgewandelt, sofern es in der Tabelle weitere VARCHAR-, TEXT- oder BLOB-Spalten gibt. Wenn die Tabelle dagegen ausschließlich Spalten mit kon-stanter Länge enthält, dann bleibt CHAR(n) unverändert.

Zeichensatzgrundlagen

Bei allen Textspalten können Sie durch die zusätzlichen Attribute CHARACTER SET zeichensatzname COLLATE sortierordnung den gewünschten Zeichensatz und die Sortierordnung angeben. Zeichensätze bestimmen, welche Codes zur Darstel-lung von Zeichen verwendet werden. Bei den 128 US-ASCII-Zeichen sind sich die meisten Zeichensätze einig (z.B. Code 65 für den Buchstaben A). Problematischer ist die Darstellung internationaler Zeichen.

Latin-Zeichensätze: In der Vergangenheit wurden je nach Sprachraum verschie-dene 1-Byte-Zeichensätze entwickelt, von denen die Latin-Zeichensätze die größte Verbreitung gefunden haben: Latin1 alias ISO-8859-1 enthält alle in Westeuropa üblichen Zeichen (äöüßáàå etc.)., Latin2 alias ISO-8859-2 Zeichen aus Zentral- und Osteuropa etc. Latin0 alias Latin9 alias ISO-8859-15 entspricht Latin1, enthält aber zusätzlich das Euro-Zeichen.



Das Problem bei diesen Zeichensätzen ist offensichtlich: Ihre Anwendung kommt nie mit allen Zeichen aus ganz Europa zurecht, weil jeder Latin-Zeichensatz nur eine Teilmenge der Zeichen enthält.

Unicode-Varianten: Als Lösung wurde der 2-Byte-Zeichensatz Unicode ent-wickelt. Mit 65.535 möglichen Zeichen deckt er nicht nur alle Zeichen ganz Euro-pas ab, sondern darüber hinaus auch noch die der meisten asiatischen Sprachen.

Unicode regelt allerdings nur, welcher Code welchem Zeichen zugeordnet ist, es regelt nicht, wie die Codes tatsächlich gespeichert werden. Hierfür bestehen wieder mehrere Varianten, von denen UCS-2 und UTF-8 die beiden wichtigsten sind. (UTF steht für Unicode Transfer Format, UCS für Universal Character Set.)

■ UCS-2 alias UTF-16: Die einfachste Lösung scheint auf den ersten Blick darin zu bestehen, jedes Zeichen einfach durch 2 Byte (also 16 Bit) darzustellen. Diese Formatierung wird UTF-16 oder UCS-2 genannt. Fast alle Betriebssystem-funktionen von Microsoft Windows verwenden diese Darstellung.

Sie hat allerdings zwei Nachteile: Erstens verdoppelt sich der Speicherbedarf, und zwar auch in solchen Fällen, wo überwiegend europäische Zeichen oder gar nur US-ASCII-Zeichen gespeichert werden sollen. Zweitens tritt der Byte-code 0 an beliebigen Stellen in Unicode-Zeichenketten auf. Bei Texten mit US-ASCII-Zeichen ist sogar jedes 2. Byte 0. Viele C-Programme, E-Mail-Server etc. setzen aber voraus, dass das Byte 0 das Ende einer Zeichenkette markiert.

■ UTF-8: Die bei weitem populärste Alternative zu UTF-16 ist UTF-8. Dabei werden die US-ASCII-Zeichen (7 Bit) wie bisher durch ein Byte dargestellt, deren oberstes Bit 0 ist. Alle anderen Unicode-Zeichen werden durch zwei bis vier Byte lange Byte-Ketten dargestellt.

Der offensichtliche Nachteil dieses Formats besteht darin, dass es keinen unmit-telbaren Zusammenhang zwischen der Byteanzahl und der Anzahl der Zeichen eines Dokuments gibt. Wegen der größeren Kompatibilität zu existierenden Programmen und einer Reihe anderer Vorteile hat sich UTF-8 unter Unix/ Linux und bei den meisten für die Webentwicklung wichtigen Komponenten als Standard etabliert. Wenn in diesem Buch von Unicode die Rede ist, ist in Zu-kunft immer Unicode im UTF-8-Format gemeint.

Trotz der offensichtlichen Vorteile von Unicode – egal, in welcher Darstellung – gibt es zwei Gründe, die gegen den sofortigen Umstieg sprechen: Zum einen ist der Unicode-Zeichensatz inkompatibel mit den bekannten 1-Byte-Zeichensätzen. Zum anderen ist die Unicode-Unterstützung der Komponenten, die bei der Web-entwicklung zum Einsatz kommen, noch alles andere als perfekt. (Das schwächste Glied der Kette ist übrigens PHP, und das wird sich voraussichtlich erst mit Ver-sion 5.2 ändern.)

8 Datenbankdesign


MySQL-Zeichensatzunterstützung

Bis einschließlich MySQL 4.0 waren der Zeichensatz und die Sortierordnung für sämtliche Textfelder durch den MySQL-Server vorgegeben. Per Default galten der Latin1-Zeichensatz und die schwedische Sortierordnung. In der MySQL-Konfigu-rationsdatei konnte zwar ein anderer Zeichensatz bzw. eine andere Sortierordnung eingestellt werden, diese Einstellung galt dann aber für alle Datenbanken; außer-dem erforderte jede Änderung einen Neustart des Datenbank-Servers und eine Neugenerierung der Indizes. Unicode wurde überhaupt nicht unterstützt.

Beginnend mit MySQL 4.1 hat sich die Situation wesentlich verbessert: Der Zeichensatz und die Sortierordnung können nun für jede Spalte individuell ange-geben werden. Gleichzeitig ist auch die Auswahl der Zeichensätze und Sortierord-nungen größer als bisher (inklusive Unicode UTF-8 und UCS-2).

Eine lange Liste aller Zeichensätze sowie der zu den jeweiligen Zeichensätzen ver-fügbaren Sortierordnungen gibt das SQL-Kommando SHOW COLLATION, dessen Ergebnis hier aber nur auszugsweise wiedergegeben ist. (Beachten Sie, dass Sie Zeichensätze und Sortierordnungen nicht beliebig mischen können. Für jeden Zeichensatz stehen nur bestimmte Sortierordnungen zur Auswahl. Die Default-sortierordnung pro Zeichensatz ist durch Yes in der Spalte Default gekennzeichnet.)

SHOW COLLATION

Collation Charset Id Default Compiled Sortlen

ascii_bin ascii 65 0 ascii_general_ci ascii 11 Yes 0 binary binary 63 Yes Yes 1 latin1_bin latin1 47 Yes 1 latin1_danish_ci latin1 15 0 latin1_general_ci latin1 48 0 latin1_general_cs latin1 49 0 latin1_german1_ci latin1 5 0 latin1_german2_ci latin1 31 Yes 2 latin1_spanish_ci latin1 94 0 latin1_swedish_ci latin1 8 Yes Yes 1 latin2_bin latin2 77 0 latin2_croatian_ci latin2 27 0 latin2_general_ci latin2 9 Yes 0 latin2_hungarian_ci latin2 21 0 latin5_bin latin5 78 0 latin5_turkish_ci latin5 30 Yes 0 latin7_bin latin7 79 0 latin7_estonian_cs latin7 20 0 latin7_general_ci latin7 41 Yes 0 latin7_general_cs latin7 42 0 utf8_bin utf8 83 Yes 1 utf8_czech_ci utf8 202 Yes 8



utf8_danish_ci utf8 203 Yes 8 utf8_estonian_ci utf8 198 Yes 8 utf8_general_ci utf8 33 Yes Yes 1 utf8_icelandic_ci utf8 193 Yes 8 utf8_latvian_ci utf8 194 Yes 8 utf8_lithuanian_ci utf8 204 Yes 8 utf8_persian_ci utf8 208 Yes 8 utf8_polish_ci utf8 197 Yes 8 utf8_roman_ci utf8 207 Yes 8 utf8_romanian_ci utf8 195 Yes 8 utf8_slovak_ci utf8 205 Yes 8 utf8_slovenian_ci utf8 196 Yes 8 utf8_spanish2_ci utf8 206 Yes 8 utf8_spanish_ci utf8 199 Yes 8 utf8_swedish_ci utf8 200 Yes 8 utf8_turkish_ci utf8 201 Yes 8 utf8_unicode_ci utf8 192 Yes 8 ...

Die folgende Tabelle gibt weitere Informationen zu den wichtigsten Kombina-tionen aus Zeichensatz und Sortierordnung. Zeichensatz Sortierordnung Bedeutung

latin1 latin1_swedish_ci Schwedische Sortierordnung, gilt in MySQL per Default für latin1-Spalten; ci steht für case-insensitive, d.h. Groß- und Kleinbuchstaben werden als gleichwertig betrachtet.

latin1 latin1_general_ci Allgemeine Sortierordnung, geeignet für viele westeuropäische Sprachen, aber ohne Berücksichtigung landesspezifischer Besonderheiten.

latin1 latin1_general_cs Wie oben, aber case-sensitive, d.h., Großbuchstaben werden vor Kleinbuchstaben sortiert.

latin1 latin1_german1_ci Deutsche Sortierordnung gemäß DIN-1-Standard (ä=a, ö=o, ü=u, ß=s)

latin1 latin1_german2_ci Deutsche Sortierordnung gemäß DIN-2-Standard (Telefonbuchregeln, also ä=ae, ö=oe, ü=ue, ß=ss)

utf8 utf8_general_ci Allgemeine Sortierordnung, geeignet für viele westeuropäische Sprachen, aber ohne Berücksichtigung landesspezifischer Besonderheiten; gilt in MySQL per Default für utf8-Spalten.

Tabelle 8.8: Die wichtigsten Zeichensätze und Sortierordnungen

Wenn Sie bei der Definition einer Spalte auf die Angabe von Zeichensatz und Sortierordnung verzichten, gilt der Defaultzeichensatz der Tabelle, der Datenbank oder des MySQL-Servers, je nachdem, auf welcher Ebene Defaulteinstellungen de-finiert sind.

8 Datenbankdesign


Welcher Zeichensatz für Ihre Datenbank optimal ist, hängt von der Anwendung ab. Solange es keine Notwendigkeit gibt, Zeichen außerhalb des westeuropäischen Sprachraums zu verarbeiten, sollten Sie beim Zeichensatz latin1 bleiben. Dieser Zeichensatz verursacht in der Regel keinerlei Probleme bei der Weiterverarbei-tung.

Verweis

Zum Thema Zeichensätze und Sortierordnungen finden Sie in die-sem Buch eine Menge weiterer Informationen:

Sortieren in unterschiedlichen Sortierordnungen: Abschnitt 9.3 Zeichensatz einstellen, Zeichensatzvariablen: Abschnitt 10.1 MySQL-Konfiguration: Abschnitt 14.2 Zeichenkettensyntax: Abschnitt 21.1

Außerdem gehen alle Programmierkapitel darauf ein, welche Beson-derheiten bei der Verarbeitung von Unicode-Zeichenketten zu be-achten sind. Werfen Sie einen Blick in das Stichwortverzeichnis (Ein-trag Unicode)!

8.2.6 Binärdaten (xxxBLOB und BIT)

Zur Speicherung binärer Daten sind vier BLOB-Datentypen vorgesehen, die beinahe dieselben Eigenschaften aufweisen wie die TEXT-Datentypen. (BLOB steht übrigens für Binary Large Object.) Der einzige Unterschied besteht darin, dass Textdaten üblicherweise im Textmodus verglichen und sortiert werden (ohne Berücksichtigung von Groß- und Kleinschreibung), Binärdaten dagegen binär.

Generell ist es umstritten, ob große binäre Objekte tatsächlich in der Datenbank ge-speichert werden sollen. Die Alternative besteht darin, die Daten (z.B. Bilder) in externen Dateien zu speichern und in der Datenbank nur Links auf diese Dateien abzulegen.

Der Vorteil der Verwendung von BLOBs ist die Integration in die Datenbank (mehr Sicherheit, einfachere Backups, einheitlicher Zugriff auf alle Daten), der Nachteil ist die zumeist deutlich geringere Geschwindigkeit. Besonders ungünstig ist, dass kleine und große Datenelemente – also Strings, Integerzahlen etc. auf der einen Seite, BLOBs und lange Texte auf der anderen Seite – bunt gemischt in einer Tabellendatei gespeichert werden müssen. Das verlangsamt auch den Zugriff auf alle anderen Datensätze.

Beachten Sie außerdem, dass BLOBs generell nur vollständig gelesen werden kön-nen. (Es ist unmöglich, aus einem 800 kByte großen BLOB die letzten 100 kByte zu lesen – es muss immer das gesamte BLOB übertragen werden.)




BIT(n) Bit-Daten, wobei n die Bitanzahl angibt (bis zu 64)

TINYBLOB Binärdaten mit variabler Länge, maximal 255 Byte

BLOB Binärdaten mit variabler Länge, maximal 216-1 Byte

MEDIUMBLOB Binärdaten mit variabler Länge, maximal 224-1 Byte

LONGBLOB Binärdaten mit variabler Länge, maximal 232-1 Byte

Tabelle 8.9: Datentypen für Binärdaten

BIT-Daten

Neu ab MySQL 5.0.3 ist die Möglichkeit, Spalten mit bis zu 64 Bits zu definieren. Zwar gab es den Datentyp BIT schon bisher, allerdings galt BIT in früheren My-SQL-Versionen als Synonym für TINYINT(1).

Um Bit-Werte anzuschreiben, gibt es die neue Syntax b'0101'. SELECT-Abfragen liefern für BIT-Spalten Binärwerte, die getesteten Client-Programme waren aller-dings nicht in der Lage, diese Werte korrekt anzuzeigen. Verwenden Sie gegebe-nenfalls SELECT bitcolumn+0, um die Binärwerte in Integerzahlen umzuwandeln, oder SELECT BIN(bitcolumn+0), um diese Integerzahlen in binärer Notation anzu-zeigen.

Wenn Sie Zahlen in BIT-Spalten einfügen und dabei den Zahlenraum über- oder unterschreiten, werden alle Bits auf 1 gesetzt. Wenn Sie also beispielsweise die Zahlen -1, 0, 1, 7, 8 in eine BIT(3)-Spalte einfügen, werden die folgenden Binär-werte gespeichert: b'111', b'000', b'001', b'111', b'111'.

8.2.7 Sonstige Datentypen

Die beiden Datentypen ENUM und SET sind eine Besonderheit von MySQL. Sie er-möglichen eine für den MySQL-Server besonders effiziente Verwaltung von Zei-chenketten-Aufzählungen bzw. -Kombinationen.

Mit ENUM können Sie eine Liste von bis zu 65.535 Zeichenketten verwalten, denen durchlaufende Nummern zugeordnet werden. Im Feld kann dann eine dieser Zeichenketten ausgewählt werden.

SET verfolgt eine ähnliche Idee, allerdings sind hier beliebige Kombinationen mög-lich. Intern werden den Zeichenketten dazu Zweierpotenzen zugeordnet (1, 2, 4, 8 etc.), so dass eine bitweise Kombination möglich ist. Dementsprechend größer ist auch der Speicherbedarf (ein Bit pro Zeichenkette). Maximal dürfen 64 Zeichenket-ten kombiniert werden. (Der Speicherbedarf beträgt dann acht Byte.)

Die Datentypen haben allerdings auch Nachteile: Zum einen ist deren Verwaltung durch PHP-Code relativ umständlich (etwa, wenn Sie die zur Auswahl stehenden Zeichenketten eines ENUM-Felds ermitteln möchten). Zum anderen kennen die meisten anderen Datenbanksysteme weder ENUM noch SET, was einen späteren

8 Datenbankdesign


Wechsel des Datenbanksystems sehr erschwert. Deswegen ist es oft zweckmäßiger, statt ENUMs bzw. SETs zusätzliche verknüpfte Tabellen zu verwenden. MySQL-Schlüsselwort Bedeutung

ENUM Aufzählung von maximal 65.535 Zeichenketten (1 oder 2 Byte, siehe Abschnitt 10.3)

SET Kombination von maximal 255 Zeichenketten (1 bis 8 Byte, siehe ebenfalls Abschnitt 10.3)

GEOMETRY, POINT etc. Geometrieobjekte (ab MySQL 4.1, siehe Kapitel 12)

Tabelle 8.10: Sonstige Datentypen

8.2.8 Optionen und Attribute

Bei der Definition jeder Spalte können verschiedene Optionen bzw. Zusatzattribute angegeben werden. Die folgende Tabelle fasst die wichtigsten Optionen nochmals zusammen. Beachten Sie, dass viele Attribute nur für bestimmte Datentypen geeig-net sind! MySQL-Schlüsselwort Bedeutung

NULL gibt an, dass die Spalte auch den Wert NULL enthalten darf. (Diese Einstellung gilt per Default.)

NOT NULL lässt den Wert NULL nicht zu.

DEFAULT xxx gibt den Defaultwert xxx an, der verwendet wird, wenn beim Einfügen kein anderer Wert angegeben wird.

DEFAULT CURRENT_TIMESTAMP

bewirkt bei TIMESTAMP-Spalten, dass beim Einfügen neuer Datensätze automatisch die aktuelle Zeit gespeichert wird.

ON UPDATE CURRENT_TIMESTAMP

bewirkt bei TIMESTAMP-Spalten, dass bei Änderungen (UPDATE) automatisch die aktuelle Zeit gespeichert wird.

PRIMARY KEY definiert die Spalte als Primärschlüssel.

AUTO_INCREMENT bewirkt, dass in die Spalte automatisch ein durchlaufender Zähler eingefügt wird. AUTO_INCREMENT kann nur für eine Spalte für ganze Zahlen verwendet werden. Außerdem müssen die Optionen NOT NULL und PRIMARY KEY angegeben werden. (Statt PRIMARY KEY kann die Spalte auch mit einem UNIQUE-Index ausgestattet werden.)

UNSIGNED bewirkt bei ganzen Zahlen, dass diese ohne Vorzeichen gespeichert werden. Achtung: Auch Berechnungen werden dann vorzeichenlos durchgeführt.

CHARACTER SET name [COLLATE sort]

gibt bei Zeichenketten den Zeichensatz und optional die gewünschte Sortierordnung an.

Tabelle 8.11: Wichtige Spaltenattribute und -optionen

8.3 Tipps und Tricks zum Datenbankdesign


MySQL lässt es leider nicht zu, als Defaultwert eine Funktion anzugeben. Es ist also nicht möglich, beispielsweise DEFAULT RAND() anzugeben, wenn Sie wollen, dass automatisch eine Zufallszahl in die Spalte eingefügt wird. Ebenso wenig ist es möglich, Validitätsregeln für Spalten zu definieren (so dass beispielsweise nur Werte zwischen 0 und 100 gespeichert werden können).

8.3 Tipps und Tricks zum Datenbankdesign

Regeln für gutes Datenbankdesign

■ Tabellen sollen keine redundanten (sich wiederholende) Daten enthalten. (Wenn Sie also in einzelne Felder einer Tabelle immer wieder dieselben Zahlen oder Zeichenketten eingeben müssen, stimmt etwas nicht.)

■ Tabellen sollten ohne Spalten der Art bestellung1, bestellung2, bestellung3 etc. auskommen. Selbst wenn Sie zehn derartige Spalten vorsehen, wird irgend-wann der Fall eintreten, dass ein Kunde elf Artikel bestellen möchte.

■ Der Platzbedarf zur Speicherung aller Tabellen soll so klein wie möglich sein.

■ Häufig benötigte Datenbankabfragen sollen einfach und effizient ausführbar sein. (Verstöße gegen diese Regel bemerkt man leider erst dann, wenn die Datenbank nicht nur ein paar Testdatensätze, sondern Tausende oder vielleicht sogar Millionen von Datensätzen enthält. Dann sind Änderungen am Design aber oft nicht mehr möglich ...)

Die obigen Regeln haben im Prinzip dieselbe Konsequenz wie die im folgenden Abschnitt vorgestellten Normalisierungsregeln, sind aber oft leichter nachvollzieh-bar.

Tipps zur Namensgebung

■ MySQL unterscheidet bei Datenbank- und Tabellennamen zwischen Groß- und Kleinschreibung, bei Spaltennamen dagegen nicht. Insofern ist es zumindest bei Datenbanken und Tabellen wichtig, auf eine einheitliche Groß- und Kleinschrei-bung zu achten. (In den Beispieldatenbanken dieses Buchs werden für Daten-bank- und Tabellennamen grundsätzlich nur Kleinbuchstaben verwendet.)

■ Namen von Datenbanken, Tabellen und Spalten dürfen maximal 64 Zeichen lang sein.

■ Vermeiden Sie Sonderzeichen (z.B. äöüß) in den Namen! MySQL erlaubt zwar grundsätzlich alle alphanumerischen Zeichen; je nach Betriebssystem bzw. Linux-Distribution gelten aber unterschiedliche Defaultzeichensätze, die vor al-lem bei einem Systemwechsel leicht zu Stolperfallen werden.

8 Datenbankdesign


■ Wählen Sie möglichst klare Feld- und Tabellennamen. Achten Sie bei Feld-namen darauf, dass diese den Inhalt eindeutig beschreiben. (authName ist also besser als name.)

■ Ein einheitliches Schema für die Benennung von Feldern erspart viele Flüchtig-keitsfehler: Egal ob Ihnen nun author_name oder authName sympathischer ist – bleiben Sie konsequent.

■ Ebenso sollten Sie sich überlegen, wie Sie mit Singular und Plural umgehen: Im vorliegenden Beispiel habe ich beispielsweise alle Tabellen im Plural bezeich-net. Es gibt hier keine Regel, was richtig ist, aber es ist auf jeden Fall verwirrend, wenn die Hälfte der Tabellen den Singular, die andere den Plural verwendet.

Tipps zum Designprozess

Grundsätzlich ist es kein triviales Unterfangen, vorhandene Daten möglichst ge-schickt auf mehrere Tabellen zu verteilen. Gerade Einsteiger in die Datenbankwelt sollten die beiden folgenden Empfehlungen beherzigen:

■ Beginnen Sie mit einer relativ kleinen Anzahl von Testdaten, und versuchen Sie, diese in eine oder mehrere Tabellen einzutragen. (Der Umfang der Test-daten sollte nicht zu klein sein, damit offensichtliche Designprobleme von An-fang an sichtbar werden; er sollte aber auch nicht zu groß sein, damit der Zeit-aufwand für das Datenbankdesign nicht zu groß ist.)

Abbildung 8.1: Datenbankdesign mit einem Tabellenkalkulationsprogramm

8.4 Normalisierungsregeln


■ Führen Sie Ihre ersten Experimente nicht mit realen MySQL-Tabellen durch, sondern verwenden Sie stattdessen einige Blätter eines Tabellenkalkulations-programms, also z.B. Excel oder OpenOffice Calc. (Abbildung 8.1 ist schon eine Vorschau auf den nächsten Abschnitt.) Damit können Sie viel unkomplizierter arbeiten. Vorläufig geht es ja nur um die Verteilung der Daten auf Tabellen und deren Spalten, nicht um datenbankspezifische Details wie Spaltenformate, Indizes etc.

8.4 Normalisierungsregeln Buchautoren denken immer nur an Bücher! Wen wundert es daher, dass auch das Beispiel dieses Abschnitts von Büchern handelt? Das Ziel des Beispiels ist es, eine kleine Datenbank zu erstellen, in der Daten über Bücher gespeichert werden: Titel des Buchs, Verlag, Autoren, Erscheinungsdatum etc. Diese Daten können natürlich ganz ohne Datenbank gespeichert werden – in einer einfachen Liste im Textformat, wie Sie sie zum Beispiel am Ende dieses Buchs bei den Quellenangaben finden:

■ Michael Kofler: Linux, 7. Auflage. Addison-Wesley 2004.

■ Michael Kofler, David Kramer: Definitive Guide to Excel VBA, 2nd ed. Apress 2003.

■ Robert Orfali, Dan Harkey, Jeri Edwards: Client/Server Survival Guide. Addison-Wesley 1997.

■ Tobias Ratschiller, Till Gerken: Web Application Development with PHP 4.0. New Riders 2000.

An sich enthält diese Liste alle erforderlichen Informationen. Warum sollten Sie sich also überhaupt die Mühe machen, den Text (der vielleicht als Word-Doku-ment vorliegt) in eine Datenbank umzuwandeln?

Nun, es gibt eine Menge Gründe: Die Liste ermöglicht zwar ein einfaches Suchen, es ist aber nicht möglich, die Daten anders zu organisieren: beispielsweise um eine Teilliste aller Bücher des Autors x zu erstellen oder um eine neue Liste zu erstellen, die nicht nach Autorennamen, sondern nach Titeln geordnet ist.

Die resultierende Datenbank mylibrary wird im weiteren Verlauf dieses Kapitels schrittweise verbessert. Sie finden die fertige Datenbank auch auf der beiliegenden CD. Die mylibrary-Datenbank dient darüber hinaus in zahlreichen Beispielen dieses Buchs als Grundlage.

Der erste Versuch

Also denken Sie sich: Nichts ist einfacher, als diese Liste in eine Datenbanktabelle umzuwandeln. (Im Folgenden werden Titel und Autorennamen aus Platzgründen abgekürzt.) Sie erstellen also eine neue Tabelle mit den folgenden Spalten:

8 Datenbankdesign


titletitletitletitle publNamepublNamepublNamepublName yearyearyearyear authName1authName1authName1authName1 authNamauthNamauthNamauthName2e2e2e2 authName3authName3authName3authName3

Linux Addison-Wesley 2004 Kofler M. Definitive Guide ... Apress 2003 Kofler M. Kramer D. Client/Server ... Addison-Wesley 1997 Orfali R. Harkey D. Edwards E. Web Application ... New Riders 2000 Ratschiller T. Gerken T.

Abbildung 8.2: Buchdatenbank, erster Versuch

Diese Tabelle ist offensichtlich problematisch: Schon auf den ersten Blick fällt auf, dass die Limitierung auf drei Autoren willkürlich ist. Was tun Sie mit einem Buch, das von vier oder fünf Autoren geschrieben wurde? Einfach immer mehr authorN-Spalten einfügen, von denen dann die meisten leer bleiben werden?

8.4.1 Die erste Normalform

Die Datenbanktheoretiker haben zum Glück ein Rezept für solche Probleme gefun-den: Wenden Sie auf Ihre Datenbank der Reihe nach die Regeln für die drei so ge-nannten Normalformen an! Die Regeln für die erste Normalform lauten (aus der Sprache der Theoretiker in verständliches Deutsch übersetzt):

■ Spalten mit gleichartigem Inhalt müssen entfernt werden.

■ Für jede Gruppe zusammengehöriger Daten muss eine eigene Tabelle gebildet werden.

■ Jeder Datensatz muss durch einen so genannten Primärschlüssel eindeutig identifizierbar sein.

Die erste Regel bezieht sich in diesem Beispiel offensichtlich auf die authorN-Spal-ten.

Die zweite Regel scheint hier nicht zuzutreffen. Es handelt sich bei diesem Beispiel ausschließlich um Daten, die spezifisch zum jeweiligen Buch gehören. Eine Tabelle reicht daher. (Es wird sich bald herausstellen, dass das ein Irrtum ist.)

Die dritte Regel bedeutet in der Praxis, dass eine durchlaufende und vor allem ein-deutige Nummer eingeführt werden muss, die jede Zeile identifiziert. (Genau ge-nommen ist es nicht notwendig, dass eine Zahl als Primärschlüssel verwendet wird. Formal ist nur die Eindeutigkeit wichtig. Aus Effizienzgründen sollte der Primärschlüssel aber auch möglichst klein sein. Eine Integerzahl ist daher viel bes-ser geeignet als eine Zeichenkette variabler Länge.)

Eine Neugestaltung der Tabelle gemäß der ersten und dritten Regel liefert das in Abbildung 8.3 dargestellte Ergebnis.



titleIDtitleIDtitleIDtitleID titletitletitletitle publNamepublNamepublNamepublName yearyearyearyear authNameauthNameauthNameauthName

1 Linux Addison-Wesley 2004 Kofler M. 2 Definitive Guide ... Apress 2003 Kofler M. 3 Definitive Guide ... Apress 2003 Kramer D. 4 Client/Server ... Addison-Wesley 1997 Orfali R. 5 Client/Server ... Addison-Wesley 1997 Harkey D. 6 Client/Server ... Addison-Wesley 1997 Edwards E. 7 Web Application ... New Riders 2000 Ratschiller T. 8 Web Application ... New Riders 2000 Gerken T.

Abbildung 8.3: Buchdatenbank, erste Normalform

Es ist leicht erkennbar, dass es nun keine Probleme mehr mit der Anzahl der Autoren gibt. Egal wie viele Autoren ein Buch hat – es kann in dieser Tabelle ge-speichert werden. Der Preis dafür ist allerdings hoch: Die Inhalte der Spalten title, publName und year wiederholen sich mit jedem Autor. Das kann nicht der Weisheit letzter Schluss sein!

8.4.2 Die zweite Normalform

Die Regeln für die zweite Normalform lauten:

■ Immer dann, wenn sich Inhalte in Spalten wiederholen, müssen die Tabellen in mehrere Teiltabellen zerlegt werden.

■ Die Tabellen müssen durch so genannte Fremdschlüssel (Foreign Keys) mit-einander verbunden werden.

Wenn Sie sich noch nicht in die Datenbanksprache eingelesen haben, ist Fremd-schlüssel für Sie wahrscheinlich ein denkbar merkwürdiger Begriff. Umgangs-sprachlich würde man »Fremdschlüssel« wohl besser mit »Querverweis« über-setzen: Es handelt sich ganz einfach um einen Verweis auf eine Zeile in einer anderen Tabelle. Für Programmierer wäre die Bezeichnung Zeiger (Pointer) nahe liegend, im Internet-Jargon könnte man auch von einem Link sprechen.

In Abbildung 8.3 wiederholen sich Daten in beinahe allen Spalten. Die Ursache da-für liegt offensichtlich in der Autorenspalte. Der erste Versuch, die Autoren in eine eigene Tabelle auszulagern, führt zu dem Ergebnis, das Sie in Abbildung 8.4 und Abbildung 8.5 sehen.

titleIDtitleIDtitleIDtitleID titletitletitletitle publNamepublNamepublNamepublName yearyearyearyear

1 Linux Addison-Wesley 2004 2 Definitive Guide ... Apress 2003 3 Client/Server ... Addison-Wesley 1997 4 Web Application ... New Riders 2000

Abbildung 8.4: titles-Tabelle, zweite Normalform

8 Datenbankdesign


authIDauthIDauthIDauthID titleIDtitleIDtitleIDtitleID authNameauthNameauthNameauthName

1 1 Kofler M. 2 2 Kofler M. 3 2 Kramer D. 4 3 Orfali R. 5 3 Harkey D. 6 3 Edwards E. 7 4 Ratschiller T. 8 4 Gerken T.

Abbildung 8.5: authors-Tabelle, zweite Normalform

Bei der authors-Tabelle dient die erste Spalte mit den durchnummerierten authID-Werten als Primärschlüssel. Die zweite Spalte übernimmt die Aufgabe des Fremd-schlüssels: Sie verweist auf Zeilen der titles-Tabelle. Beispielsweise bedeutet Zeile 7 der authors-Tabelle, dass Ratschiller T. ein Autor des Buchs mit titleID=4 ist (also des Buchs Web Application ...).

Zweite Normalform, zweiter Versuch

Optimal ist das Ergebnis noch nicht. In der authors-Tabelle kommt Kofler M. zwei-mal vor. Je mehr Bücher in diese Datenbank eingefügt werden, desto öfter wird es zu derartigen Redundanzen kommen – immer dann, wenn ein Autor an mehr als einem Buch mitgearbeitet hat.

Die einzig mögliche Lösung zu diesem Problem besteht darin, die authors-Tabelle nochmals zu zerlegen und dabei auf die titleID-Spalte zu verzichten. Die Infor-mation, welches Buch zu welchem Autor gehört, muss in einer dritten Tabelle an-gegeben werden. Die drei Tabellen sehen dann so aus wie in Abbildung 8.6 bis Ab-bildung 8.8.

titleIDtitleIDtitleIDtitleID titletitletitletitle publNamepublNamepublNamepublName yearyearyearyear

1 Linux Addison-Wesley 2004 2 Definitive Guide ... Apress 2003 3 Client/Server ... Addison-Wesley 1997 4 Web Application ... New Riders 2000

Abbildung 8.6: titles-Tabelle, zweite Normalform



authIDauthIDauthIDauthID authNameauthNameauthNameauthName

1 Kofler M. 2 Kramer D. 3 Orfali R. 4 Harkey D. 5 Edwards E. 6 Ratschiller T. 7 Gerken T.

Abbildung 8.7: authors-Tabelle, zweite Normalform

titletitletitletitleIIIIDDDD authIDauthIDauthIDauthID

1 1 2 1 2 2 3 3 3 4 3 5 4 6 4 7

Abbildung 8.8: rel_title_author-Tabelle, zweite Normalform

Dieser Schritt ist zweifellos der schwierigste und abstrakteste – wahrscheinlich deswegen, weil eine Tabelle der Form rel_title_author keiner menschlichen Vor-gehensweise entspricht. Für eine manuelle Verwaltung der Daten wäre rel_title_-author vollkommen ungeeignet. Aber Computer sind eben keine Menschen!

Für den Computer ist es kein Problem, die Daten zusammenzufügen (zumindest dann nicht, wenn ihm ein Programm wie MySQL dabei hilft). Angenommen, Sie wollten eine Liste aller Autoren des Buchs Client/Server ... haben. MySQL würde dann zuerst in der titles-Tabelle nachsehen, welche titleID-Nummer dieses Buch hat. Dann würde es in der rel_title_author-Tabelle nach Datensätzen suchen, in denen diese Nummer in der titleID-Spalte vorkommt. Die dazugehörigen authID-Nummern führen dann zu den Autoren.

Hinweis

Vielleicht fragen Sie sich, warum es in der rel_title_author-Tabelle keine eigene ID-Spalte gibt, also z.B. rel_title_author_ID. Auf eine solche Spalte wird üblicherweise verzichtet, weil die Kombination aus titleID und authID bereits ein optimaler Primärschlüssel ist. (Relationale Datenbanksysteme erlauben auch Primärschlüssel, die aus mehreren Spalten zusammengesetzt sind.)

8 Datenbankdesign


8.4.3 Die dritte Normalform

Die einzige Regel der dritten Normalform lautet:

■ Spalten, die nicht in unmittelbarer Abhängigkeit zum Primärschlüssel einer Tabelle stehen, müssen eliminiert werden (also in eine eigene Tabelle ausge-lagert werden).

Im vorliegenden Beispiel betrifft das die Spalte publisher in der titles-Tabelle. Die Liste der Verlage und die Liste der Buchtitel sind voneinander unabhängige Infor-mationseinheiten und sollten daher getrennt werden. Natürlich soll auch weiterhin bei jedem Titel vermerkt werden, in welchem Verlag er erschienen ist – aber dazu muss nicht jedes Mal der ganze Verlag genannt werden, es reicht ein Fremd-schlüssel (also ein Verweis).

titleIDtitleIDtitleIDtitleID titletitletitletitle publIDpublIDpublIDpublID yearyearyearyear

1 Linux 1 2001 2 Definitive Guide ... 2 2003 3 Client/Server ... 1 1997 4 Web Application ... 3 2000

Abbildung 8.9: titles-Tabelle, dritte Normalform

publIDpublIDpublIDpublID publNamepublNamepublNamepublName

1 Addison-Wesley 2 Apress 3 New Riders

Abbildung 8.10: publishers-Tabelle, dritte Normalform

Die Tabellen authors und rel_title_author ändern sich in der dritten Normalform nicht mehr. Die gesamte Bücherdatenbank besteht jetzt also aus vier Tabellen. Bei genauerem Studium der Regeln der ersten Normalform (zusammengehörende Daten gehören in eigene Tabellen) hätten natürlich einige Zwischenschritte gespart werden können – aber das hätte den didaktischen Wert des Beispiels gemindert. Tatsächlich ist es auch in der Praxis oft so, dass man erst beim Einfügen von Testdaten und anhand der dabei auftretenden Redundanzen bemerkt, an welchen Stellen Tabellen zerlegt werden müssen.

Erweiterungen

Die mylibrary-Datenbank ist noch ein wenig komplexer, als aus den vorangegan-genen Abschnitten hervorgeht. Die titles-Tabelle enthält einige weitere Felder, z.B. zur Speicherung des Untertitels oder eines Kommentars. Eine weitere languages-Tabelle enthält eine Liste aller Sprachen, in denen die Bücher verfasst wurden. Das langID-Feld der titles-Tabelle enthält für jeden Titel die Sprache. Schließlich enthält die categories-Tabelle eine hierarchische Liste aller Buchkategorien (z.B. Computer books). Das catID-Feld der titels-Tabelle gibt für jeden Titel die Kategorie an.



Der Aufbau der categories-Tabelle wird im nächsten Abschnitt behandelt. Eine Zu-sammenfassung aller Datenbankeigenschaften finden Sie schließlich am Ende dieses Kapitels.

8.4.4 Normalisierungstheorie

Mehr Theorie ...

Die drei Normalformen für relationale Datenbanken wurden erstmalig von dem Wissenschaftler E. F. Codd formuliert. Sie stellen noch immer die Basis für einen eigenen Forschungszweig dar, der sich mit der formalen Beschreibung von mathe-matischen Mengen im Allgemeinen und von relationalen Datenbanken im Besonderen beschäftigt.

Je nach Literatur kommen zu den drei hier beschriebenen Normalformen bis zu drei weitere hinzu, die in der Praxis aber von untergeordneter Bedeutung sind. Die Normalformen und ihre Regeln sind dabei sehr viel exakter beschrieben als hier. Allerdings wimmelt es in solchen Beschreibungen derart von Enitäten, Attributen etc., dass der Zusammenhang mit relationalen Datenbanken leicht verloren geht.

Wenn Sie sich für mehr Details interessieren, werfen Sie bitte einen Blick in ein gutes Buch zum Thema Datenbankdesign (siehe auch die Hinweise am Beginn dieses Kapitels).

Weniger Theorie ...

Ich habe mich bemüht, die drei ersten Normalformen hier so einfach und beispiel-orientiert wie möglich darzustellen – aber vielleicht war es Ihnen noch immer zu theoretisch. Tatsächlich helfen die Normalformen gerade Datenbankeinsteigern nur bedingt, weil die richtige Interpretation der Regeln oft schwierig ist. Hier einige Regeln, die den Einstieg vielleicht erleichtern:

■ Lassen Sie sich für den Datenbankentwurf Zeit. (Wenn Sie das Datenbank-schema zu einem späteren Zeitpunkt ändern müssen – wenn die Datenbank also schon mit realen Daten gefüllt ist und es bereits Client-Code gibt –, ist der dafür erforderliche Zeitaufwand viel größer!)

■ Vermeiden Sie Spalten mit Aufzählungen (name1, name2 oder objekt1, objekt2 etc.). Hier gibt es mit Sicherheit eine bessere Lösung durch eine zweite Tabelle.

■ Füllen Sie Ihre Datenbank sofort mit einigen Testdaten. Versuchen Sie dabei, möglichst viele Sonderfälle abzudecken. Wenn es dabei zu Redundanzen kommt, d.h., wenn innerhalb einer Spalte mehrmals derselbe Inhalt auftritt, ist dies fast immer ein Wink mit dem Zaunpfahl! Zerlegen Sie die Tabelle in zwei (oder mehr) neue Tabellen.

■ Versuchen Sie, das Konzept der Relationen zu verstehen (siehe auch Abschnitt 8.6).

8 Datenbankdesign


■ Ein gutes Datenbankdesign ist unmöglich, wenn Sie nicht schon etwas Er-fahrung mit SQL gewonnen haben (siehe die folgenden beiden Kapitel). Erst wenn Sie die Abfragemöglichkeiten von SQL kennen und verstehen, können Sie die Konsequenzen einschätzen, die sich aus der Organisation der Daten er-geben.

■ Orientieren Sie sich an Beispieldatenbanken (in diesem Buch oder in anderen Datenbankbüchern).

Verweis

Ein schönes Beispiel für die Normalisierung einer Datenbank finden Sie an der folgenden Stelle im Internet:

http://www.phpbuilder.com/columns/barry20000731.php3

Normalformen – Pro und Contra

Normalformen sind ein Mittel zum Zweck, nicht mehr und nicht weniger! Normal-formen sollen eine Hilfestellung beim Design von Datenbanken sein – aber sie können nicht den gesunden Menschenverstand ersetzen. Zudem ist es nicht immer sinnvoll, Normalformen auf Biegen und Brechen einzusetzen, d.h. wirklich jede Redundanz aufzulösen.

Contra: Die Eingabe neuer Daten – etwa in Webformularen – ist umso kompli-zierter, je mehr Tabellen es gibt, auf die die Eingaben verteilt werden müssen. Das gilt gleichermaßen für den Endanwender (der dabei meist von einer Seite zur nächsten geleitet wird) wie auch für den Programmierer.

Auch aus Gründen der Abfrageeffizienz ist es manchmal besser, ein wenig Redun-danz in Kauf zu nehmen. Das Zusammenfügen von Daten aus mehreren Tabellen ist in den meisten Fällen langsamer als das Auslesen der Daten aus einer einzigen Tabelle. Dies gilt besonders bei Datenbanken, die sich wenig ändern, für die aber häufig komplexe Abfragen durchgeführt werden. (Bei speziellen Datenbanken, den so genannten Data Warehouses, wird Redundanz oft ganz bewusst in Kauf ge-nommen, um eine höhere Geschwindigkeit zu erzielen. Das Ziel von Data Ware-houses ist die Analyse komplexer Daten nach unterschiedlichen Kriterien. MySQL ist aber ohnedies kein sinnvolles Datenbanksystem für derartige Aufgaben, wes-wegen hier nicht weiter auf die Besonderheiten dieser Spezialanwendung einge-gangen wird.)

Pro: Redundanz ist im Regelfall eine Verschwendung von Speicherplatz. Sie mögen der Ansicht sein, dass das im Zeitalter von 400-GByte-Festplatten gleich-gültig ist – aber eine große Datenbank wird zwangsläufig zu einer langsamen Datenbank (spätestens, sobald die Datenbankgröße die des RAMs übersteigt).

8.5 Verwaltung von Hierarchien


Im Regelfall bieten Datenbanken in Normalform weitaus flexiblere Abfragemög-lichkeiten. (Leider bemerkt man das fast immer erst dann, wenn eine neue Form der Datenabfrage oder -gruppierung benötigt wird – oft nachdem die Datenbank schon monatelang in Betrieb ist.)


In der mylibrary-Datenbank hilft die Tabelle categories dabei, die Bücher in unter-schiedliche Kategorien einzuordnen (Sachbücher, Kinderbücher etc.). Die Beson-derheit besteht darin, dass die Kategorien hierarchisch strukturiert sind. Vom Datenbankdesign her ist das einfach: Das Feld parentID verweist bei jeder Kate-gorie auf die übergeordnete Kategorie. (Bei der Startkategorie All books enthält parentID den Wert NULL. Dieser Sonderfall muss bei der Verwaltung bzw. Aus-wertung der categories-Tabelle immer berücksichtigt werden!) Abbildung 8.12 zeigt, wie die Hierarchie von Abbildung 8.11 datenbankmäßig abgebildet wird.

All books ├─ Children's books ├─ Computer books │ ├─ Databases │ │ ├─ Object-oriented databases

│ │ ├─ Relational Databases │ │ └─ SQL

│ └─ Programming │ ├─ Perl │ └─ PHP └─ Literature and fiction

Abbildung 8.11: Beispieldaten für die categories-Tabelle

catID CatName parentCatID

1 Computer books 11

2 Databases 1 3 Programming 1

4 Relational Databases 2 5 Object-oriented databases 2 6 PHP 3

7 Perl 3 8 SQL 2

9 Children's books 11 10 Literature and fiction 11 11 All books NULL

Abbildung 8.12: Datenbankmäßige Darstellung der Hierarchie aus Abbildung 8.11

8 Datenbankdesign


Hierarchieprobleme

Um es gleich vorwegzunehmen: So elegant und einfach die Darstellung derartiger Hierarchien auf den ersten Blick wirkt, so viele Probleme verursacht sie bei der Programmierung: Beispielsweise ist es mit einfachen SELECT-Abfragen unmög-lich, alle über- oder untergeordneten Kategorien zu ermitteln. Daher müssen zu-meist im Client-Programm mehrere Abfragen durchgeführt werden, um die Hier-archie aufzubauen. Entsprechende Programmiertechniken werden für die Pro-grammiersprache PHP ausführlich in Kapitel 15 beschrieben. Sie können bei der Auswertung von Hierarchien aber auch Stored Procedures zu Hilfe nehmen (siehe Kapitel 13).

Verweis

Eigentlich geht es in diesem Kapitel ja um das Datenbankdesign und nicht um SQL – aber ganz sind diese beiden Themen nicht voneinan-der zu trennen: Es hilft ja nichts, ein tolles Datenbankdesign zu ent-wickeln, wenn dann die Möglichkeiten von SQL nicht ausreichen, um aus den Tabellen die gesuchten Daten in angemessener Zeit zu extrahieren.

Wenn Sie noch keinerlei Erfahrung mit SQL haben, sollten Sie sich zuerst im folgenden Kapitel ein wenig einlesen. Betrachten Sie die folgenden Ausführungen gewissermaßen als Datenbankdesign für Fortgeschrittene.

Fast alle Probleme mit Hierarchien haben damit zu tun, dass SQL keine Möglich-keit für rekursive Abfragen vorsieht:

■ Es ist mit einer einzelnen Abfrage nicht möglich, alle übergeordneten Katego-rien zu einer gegebenen Kategorie zu finden.

Beispiel: Die Startkategorie lautet Relational databases (parentCatID=2). Sie wollen daraus eine Liste erstellen, die Computer books → Databases → Relational databases enthält.

Mit SELECT * FROM categories WHERE catID=2 finden Sie zwar Databases, nicht aber den zweifach übergeordneten Begriff Computer books. Dazu müssen Sie eine weitere Abfrage SELECT * FROM categories WHERE catID=1 ausführen. All das kann zwar in einer Schleife in einer beliebigen Programmiersprache (Perl, PHP etc.) erledigt werden, nicht aber in einer einzigen SQL-Anweisung.

■ Genauso schwierig ist es, die gesamte Tabelle in hierarchischer Form (als Baum) darzustellen. Abermals sind eine Menge Abfragen erforderlich.



■ Es ist nicht ohne weiteres möglich, nach allen Büchern einer übergeordneten Kategorie zu suchen.

Beispiel: Sie wollen alle Bücher der Kategorie Computer books finden.

Mit SELECT * FROM titles WHERE catID=1 finden Sie aber nur jene Titel, denen direkt die Kategorie Computer books zugeordnet ist, nicht aber die Titel der Kate-gorien Databases, Relational databases, Object-oriented databases etc. Die Abfrage müsste lauten: SELECT * FROM titles WHERE catID IN (1, 2 ...), wobei 1, 2 ... die ID-Nummern der untergeordneten Kategorien sind. Das eigentliche Problem besteht darin, diese Nummern zu ermitteln.

■ In der hier gewählten, relativ einfachen Darstellungsform ist es nicht möglich, dieselbe Subkategorie zwei oder mehreren übergeordneten Kategorien zuzu-ordnen.

Beispiel: Die Programmiersprache SQL ist in der obigen Hierarchie der überge-ordneten Kategorie Databases zugeordnet. Ebenso logisch wäre eine Zuordnung zu Programming. Daher wäre es optimal, wenn SQL sowohl bei Databases als auch bei Programming als Unterkategorie erscheinen würde.

■ Es besteht die Gefahr zirkulärer Verweise. Derartige Verweise können zwar nur durch Eingabefehler entstehen, aber wo Menschen Daten eingeben (oder Pro-gramme erstellen!), da passieren auch Fehler. Ist einmal ein zirkulärer Verweis entstanden, landen die meisten Datenbankprogramme in einer Endlosschleife. Die Auflösung solcher Verweise ist schwierig.

Keines dieser Probleme ist unüberwindlich. Hierarchien führen aber oft dazu, dass zur Beantwortung scheinbar relativ einfacher Fragen oft eine ganze Reihe von SQL-Abfragen durchgeführt werden müssen – und das dauert lange. Viele Probleme können vermieden werden, wenn auf echte Hierarchien verzichtet wird (z.B. indem maximal zwei Hierarchiestufen zugelassen werden) oder wenn Zusatz-informationen zur einfacheren Auflösung von Hierarchien in zusätzlichen Spalten oder Tabellen gespeichert werden (siehe den folgenden Abschnitt).

Hierarchiebaum bilden

Wenn Sie den Abschnitt über die Normalisierung von Datenbanken verinnerlicht haben, wissen Sie: Redundanz ist schlecht! Sie führt zu unnötigem Speicherbedarf, Wartungsproblemen bei Änderungen etc. Dennoch gibt es Fälle, wo man Redun-danz ganz bewusst in Kauf nimmt, um die Effizienz einer Anwendung zu steigern. Die folgenden Zeilen sollen verdeutlichen, dass Datenbankdesign ein vielschich-tiges Thema ist. Meistens gibt es mehrere Wege, die zum Ziel führen, und jeder dieser Wege ist eigentlich nur ein Kompromiss. Welches der beste Kompromiss ist, hängt stark vom Anwendungsprofil ab: Welche Typen von Abfragen treten am häufigsten auf? Werden Daten oft geändert?

8 Datenbankdesign


Um die categories-Tabelle in einer mit Abbildung 8.11 vergleichbaren Hierarchie anzuzeigen, müssen Sie entweder zahllose SQL-Abfragen ausführen oder einen aufwändigen client-seitigen Code entwickeln (siehe Abschnitt 15.9). Eine mögliche Lösung können die zwei zusätzlichen Spalten hierNr und hierIndent darstellen. hierNr gibt die Zeilennummer an, in der der Datensatz in einer hierarchischen Dar-stellung landen würde. (Dabei wird vorausgesetzt, dass Datensätze innerhalb einer Hierarchieebene alphabetisch nach catName sortiert werden.) hierIndent bestimmt die Einrücktiefe. In Abbildung 8.13 sehen Sie für diese beiden Spalten die Werte, die der Darstellung aus Abbildung 8.11 entsprechen.

catIDcatIDcatIDcatID CatNameCatNameCatNameCatName parentCatIDparentCatIDparentCatIDparentCatID HierNrHierNrHierNrHierNr hierIndenthierIndenthierIndenthierIndent

1 Computer books 11 2 1 2 Databases 1 3 2 3 Programming 1 7 2 4 Relational databases 2 5 3 5 Object-oriented databases 2 4 3 6 PHP 3 9 3 7 Perl 3 8 3 8 SQL 2 6 3 9 Children's books 11 1 1 10 Literature and fiction 11 10 1 11 All books NULL 0 0

Abbildung 8.13: categories-Tabelle mit hierNr-Spalte

Eine einfache Abfrage in mysql beweist, dass diese Anordnung sinnvoll ist. Dazu einige Anmerkungen zu den eingesetzten SQL-Funktionen: CONCAT verbindet zwei Zeichenketten. SPACE erzeugt die angegebene Anzahl von Leerzeichen. AS gibt dem gesamten Ausdruck den neuen Aliasnamen category.

SELECT CONCAT(SPACE(hierIndent*2), catName) AS category, hierNr, hierIndent FROM categories ORDER BY hierNr

category hierNr hierIndent

All books 0 0 Children's books 1 1 Computer books 2 1 Databases 3 2 Object-oriented databases 4 3 Relational databases 5 3 SQL 6 3 Programming 7 2 Perl 8 3 PHP 9 3 Literature and fiction 10 1



Jetzt werden Sie sich wahrscheinlich fragen, wie die Zahlenwerte hierNr und hierIndent eigentlich zustande kommen. Die folgende, beispielorientierte Anleitung zeigt, wie ein neuer Datensatz (die Computerbuchkategorie Operating systems) in die Tabelle eingefügt wird.

1. Die Daten des übergeordneten Ausgangsdatensatzes (also Computer books) sind bekannt: catID=1, parentCatID=11, hierNr=2, hierIndent=1.

2a. Nun wird innerhalb der Computer books-Gruppe der erste Datensatz gesucht, der in der Hierarchie unmittelbar hinter dem neu einzufügenden Datensatz liegt (das ist hier also Programming). Genau genommen interessiert von diesem Datensatz nur hierNr.

Eine kurze Erklärung zum SQL-Kommando:

WHERE parentCat_ID=1 findet alle Datensätze, die Computer books unmittelbar untergeordnet sind (also Databases und Programming).

catName>'Operating Systems' beschränkt die Liste auf Datensätze, die in der Liste hinter dem neuen Datensatz Operating Systems liegen.

ORDER BY catname sortiert die gefundenen Datensätze.

LIMIT 1 reduziert das Ergebnis schließlich auf den ersten Datensatz.

SELECT hierNr FROM categories WHERE parentCatID=1 AND catName>'Operating Systems' ORDER BY catName LIMIT 1

Die obige Abfrage liefert das Ergebnis hierNr=7. Damit ist klar, dass der neue Datensatz diese Hierarchienummer bekommen soll. Vorher muss bei allen vor-handenen Datensätzen mit hierNr>=7 der Wert von hierNr um eins vergrößert werden.

2b. Es kann aber auch passieren, dass die Abfrage kein Ergebnis liefert – nämlich immer dann, wenn es in der übergeordneten Kategorie noch gar keine Einträge gibt oder wenn diese alphabetisch alle über dem neuen Eintrag liegen. (Das wäre der Fall, wenn Sie die neue Computerbuchkategorie Software Engineering einfügen wollten.)

In diesem Fall muss der nächste Datensatz gesucht werden, dessen hierNr größer als die hierNr des Ausgangsdatensatzes ist und dessen hierIndent kleiner oder gleich als das hierIndent des Ausgangsdatensatzes ist. (Auf diese Weise wird der Beginn der nächsten gleichrangigen oder übergeordneten Hierarchiegruppe gesucht.)

SELECT hierNr FROM categories WHERE hierNr>1 AND hierIndent<=1 ORDER BY hierNr LIMIT 1

8 Datenbankdesign


Diese Abfrage liefert das Ergebnis 10 (also hierNr zum Datensatz Literatur and fiction). Der neue Datensatz wird diese Hierarchienummer erhalten. Bei allen vorhandenen Datensätzen mit hierNr>=10 muss hierNr um 1 vergrößert werden.

2c. Liefert auch diese Abfrage kein Ergebnis, muss der neue Datensatz am Ende der Hierarchieliste eingefügt werden. Der zurzeit größte hierNr-Wert kann leicht ermittelt werden:

SELECT MAX(hiernr) FROM categories

3. Um hierNr bei den vorhandenen Datensätzen zu erhöhen, wird folgendes Kom-mando ausgeführt (für den Fall 2a):

UPDATE categories SET hierNr=hierNr+1 WHERE hiernr>=7

4. Jetzt kann der neue Datensatz eingefügt werden. Für parentCatID wird catID des Ausgangsdatensatzes verwendet. hierNr=7 wurde oben ermittelt. hierIndent muss um eins größer sein als beim Ausgangsdatensatz.

INSERT INTO categories (catName, parentCatID, hierNr, hierIndent) VALUES ('Operating systems', 1, 7, 2)

Die neuen Spalten in categories vereinfachen also manche Leseoperationen. Dafür sind Einfügeoperationen jetzt sehr aufwändig. Noch komplizierter ist es, die Hierarchie nachträglich zu verändern. Stellen Sie sich vor, Sie wollten die Schreib-weise einer Kategorie ändern und dadurch würde sich die Sortierordnung ändern. Davon wäre nicht nur der eine Datensatz betroffen, sondern viele andere auch. hierNr müsste für große Teile der Tabelle neu ermittelt werden. Sie sehen also: Re-dundanz ist schlecht!

Fazit: Sie müssen sich entscheiden, ob Ihnen die Optimierung von Lese- oder Schreiboperationen wichtiger ist. Für dieses Buch habe ich categories ohne die beiden Zusatzspalten hierNr und hierIndent belassen. Zum einen hat dies didak-tische Gründe: So kann ich in den Kapiteln 13 (Stored Procedures) und 15 (PHP) verschiedene Programmiertechniken demonstrieren, um hierarchische Daten den-noch so effizient wie möglich zu verarbeiten. Zum anderen hatte ich den Eindruck, dass die Vorteile des einfacheren Datenbankdesigns selbst bei einer realen Anwen-dung überwiegen würden: Selbst wenn Sie eine riesige Buchdatenbank wie bei amazon.com verwalten müssten, würden ein paar tausend Buchkategorien aus-reichen. Ernsthafte Geschwindigkeitsprobleme sind aber in der Regel nur bei sehr viel größeren Tabellen zu erwarten.

Untergeordnete Kategorien in der categories-Tabelle suchen

Angenommen, Sie wollten in der Titeltabelle alle Databases-Titel suchen: Dann reicht es nicht aus, nach allen Titeln mit catID=2 zu suchen – Sie wollen natürlich auch die Spezialtitel zu Relational databases, Object-oriented databases und SQL sehen (also auch die Titel mit catID=4, 5 und 8). Die Gesamtheit all dieser Kategorien wird im Folgenden als Suchkategoriegruppe bezeichnet.



Es sind also zwei Probleme zu lösen: Zuerst müssen Sie die Liste der catID-Werte der Suchkategoriegruppe ermitteln. Dazu ist eine Reihe von SELECT-Abfragen erforderlich, auf die hier nicht weiter eingegangen wird. Anschließend müssen Sie aus der titles-Tabelle jene Datensätze ermitteln, deren catID-Nummern mit den soeben gefundenen Werten übereinstimmen. Grundsätzlich kann die Titelsuche also durchgeführt werden, aber der Weg dorthin ist aufwändig. Es sind mehrere SQL-Abfragen und viel client-seitiger Code erforderlich.

Der andere Lösungsweg besteht darin, eine neue (redundante) Tabelle einzu-führen, in der zu jedem categories-Datensatz alle übergeordneten Datensätze ge-speichert werden. Diese Tabelle könnte den Namen rel_cat_parent tragen und wür-de aus zwei Spalten bestehen: catID und parentID (siehe Abbildung 8.14). Aus der Abbildung geht beispielsweise hervor, dass die Kategorie Relational databases (catID=4) den Kategorien All books, Computer books und Databases untergeordnet ist (parentID=11, 1, 2).

catID catID catID catID pareparepareparennnntIDtIDtIDtID

1 11 2 1 2 11 3 1 3 11 4 1 4 2 4 11 ... ...

Abbildung 8.14: Einige Einträge der rel_cat_parent-Tabelle

Der wesentliche Nachteil der rel_cat_parent-Tabelle besteht darin, dass die Tabelle bei jeder Änderung in der categories-Tabelle synchronisiert werden muss. Das ist aber relativ einfach durchzuführen.

Dafür kann jetzt die Frage nach allen untergeordneten Kategorien zu Databases ganz leicht beantwortet werden:

SELECT catID FROM rel_cat_parent WHERE parentID=2

Wenn Sie alle Buchtitel ermitteln möchten, die zur Kategorie Databases oder ihren Unterkategorien gehören, sieht die erforderliche Abfrage wie folgt aus. Das Schlüs-selwort DISTINCT ist hier erforderlich, weil die Abfrage sonst manche Titel mehrfach erfasst.

SELECT DISTINCT titles.title FROM titles, rel_cat_parent WHERE (rel_cat_parent.parentID = 2 OR titles.catID = 2) AND titles.catID = rel_cat_parent.catID

8 Datenbankdesign


Damit stehen Sie einmal mehr vor der Wahl zwischen Effizienz und Normali-sierung: Die gesamte Tabelle rel_cat_parent enthält lediglich Daten, die auch direkt aus categories ermittelt werden können. In der konkreten Realisierung der mylib-rary-Bibliothek habe ich auf rel_cat_parent schließlich verzichtet.

Übergeordnete Kategorien in der categories-Tabelle suchen

Auch die im Vergleich zum vorigen Abschnitt umgekehrte Fragestellung kommt vor: Welches sind die übergeordneten Kategorien einer Startkategorie? Wenn die Startkategorie Perl (catID=7) ist, dann sind die übergeordneten Kategorien zuerst Programming, dann Computer books und schließlich All books.

Wenn es die oben beschriebene Tabelle rel_cat_parent gibt, kann diese Frage durch eine einfache Abfrage beantwortet werden:

SELECT CONCAT(SPACE(hierIndent*2), catName) AS category FROM categories, rel_cat_parent WHERE rel_cat_parent.catID = 7 AND categories.catID = rel_cat_parent.parentID ORDER BY hierNr

category

All books Computer books Programming

Steht rel_cat_parent dagegen nicht zur Verfügung, muss mit einer Reihe von SELECT-Anweisungen in einer Schleife categories.parentCatID so lange verfolgt werden, bis der Wert NULL enthält.

8.6 Relationen Sobald Sie eine Datenbank in Normalformen umwandeln, müssen Sie mehrere Ta-bellen miteinander verknüpfen. Diese Verknüpfungen heißen in der Datenbank-sprache Relationen. Grundsätzlich gibt es drei mögliche Relationen zwischen zwei Tabellen:

1:1 Eindeutige Beziehung zwischen zwei Tabellen: Jeder Datensatz der einen Tabelle entspricht genau einem Datensatz der anderen Tabelle. Solche Beziehungen sind selten, weil die Informationen beider Tabellen dann ebenso in einer einzigen Tabelle gespeichert werden könnten.

8.6 Relationen


1:n Ein Datensatz der ersten Tabelle kann in mehreren Datensätzen der zwei-ten Tabelle auftreten (z.B. ein Verkäufer bei mehreren Bestellungen). Um-gekehrt ist keine Mehrdeutigkeit möglich, eine Bestellung kann nur von einem Verkäufer angenommen werden. Gelegentlich wird auch von einer n:1-Beziehung gesprochen, die aber mit einer 1:n-Beziehung identisch ist (lediglich die Blickrichtung ist anders).

n:m Ein Datensatz der einen Tabelle kann in mehreren Datensätzen der an-deren Tabelle vorkommen und umgekehrt (z.B. mehrere verschiedene Artikel in einer Bestellung, ein Artikel in mehreren verschiedenen Bestel-lungen, Bücher und ihre Autoren).

1:1-Relationen

Eine 1:1-Relation wird in der Praxis am ehesten dann eingesetzt, wenn eine Tabelle in zwei Teile zerlegt wird, die einen gemeinsamen Primärschlüssel verwenden. Das ist am einfachsten anhand eines Beispiels zu verstehen: Eine Tabelle mit Informationen zum Personal einer Firma enthält sehr viele Informationen: Name, Abteilung, Geburtsdatum, Einstelldatum, Gehalt etc. Man könnte diese Tabelle nun in personal und personal_extra zerlegen, wobei personal die oft benötigten und allgemein zugänglichen Informationen enthält, personal_extra zusätzliche, seltener benötigte und privatere Daten.

Es kann zwei Gründe für eine derartige Trennung geben. Der eine ist der Sicher-heitsaspekt – es ist einfach, die personal_extra-Tabelle vor allgemeinem Zugriff zu schützen. (Für MySQL ist das weniger wichtig, weil die Zugriffsrechte ohnedies für jede einzelne Spalte einer Tabelle getrennt eingestellt werden können. Ab My-SQL 5.0 kann das Sicherheitsproblem zudem auch durch Views gelöst werden.)

Der andere Grund ist die Geschwindigkeit: Wenn eine Tabelle sehr viele Spalten enthält, von denen bei den meisten Abfragen aber nur wenige Spalten benötigt werden, ist es effizienter, diese Spalten in einer Tabelle zusammenzufassen. (Im Idealfall enthält die erste Tabelle ausschließlich Spalten mit vorgegebener Größe. Derartige Tabellen sind effizienter zu verwalten als Tabellen, die auch Spalten variabler Größe enthalten. Mehr Informationen dazu finden Sie in Abschnitt 8.1, der einen Überblick über die von MySQL unterstützten Tabellentypen gibt.)

Der wesentliche Nachteil einer Trennung der Tabellen besteht darin, dass ständig darauf geachtet werden muss, die Tabellen synchron zu halten.

8 Datenbankdesign


1:n-Relationen

1:n-Relationen kommen immer dann zum Einsatz, wenn ein bestimmtes Feld eines Datensatzes einer Detailtabelle auf unterschiedliche Spalten einer anderen Tabelle (Mastertabelle) verweisen kann.

Die Verknüpfung erfolgt über Schlüsselfelder. Die Spalten der Mastertabelle sind dabei durch einen Primärschlüssel gekennzeichnet. Die Detailtabelle enthält ein Fremdschlüsselfeld, dessen Inhalt auf die Mastertabelle verweist. Beispiele:

■ mylibrary-Datenbank: Hier gibt es eine 1:n-Relation zwischen der title- und der publishers-Tabelle: Die Verlagstabelle publishers ist die Mastertabelle mit dem Primärschlüssel publishers.publID. Die Titeltabelle ist die Detailtabelle mit dem Fremdschlüssel title.publID. Jeder Verlag (1) kann mehrere Bücher veröffent-lichen (n).

Die mylibrary-Datenbank enthält noch zwei weitere 1:n-Relationen: zwischen titles und languages (Feld langID) und zwischen titles und categories (Feld catID). Alle drei 1:n-Relationen sind auch gut in Abbildung 8.15 auf Seite 270 sichtbar.

■ Geschäftsanwendung, Tabelle mit Bestellungen: Die Detailtabelle enthält Daten zu allen eingegangenen Bestellungen. Darin verweist ein Fremdschlüsselfeld auf die Mastertabelle mit der Liste aller Kunden.

Jeder Kunde (1) kann beliebig viele Bestellungen (n) durchführen.

■ Diskussionsforum, Tabelle mit Nachrichten: Die Detailtabelle enthält die Daten jedes auf der Website verfügbaren Diskussionsbeitrags (Titel, Text, Datum, Autor, Gruppe etc.). Zwei mögliche Mastertabellen wären die Gruppentabelle mit der Liste aller Diskussionsgruppen und die Autorentabelle mit der Liste aller Website-Mitglieder, die Beiträge verfassen dürfen.

Jeder Autor (1) kann beliebig viele Diskussionsbeiträge (n) verfassen. In jeder Diskussionsgruppe (1) können beliebig viele Diskussionsbeiträge (n) enthalten sein.

■ CD-Datenbank, Tabelle mit CDs: Die Detailtabelle enthält die Daten jeder ge-speicherten CD (Titel, Bandname, Anzahl der CDs etc.). Zwei dazu passende Mastertabellen wären die Band-Tabelle mit einer Liste aller in der Datenbank vertretenen Musikgruppen und eine Label-Tabelle mit einer Liste der Platten-Label.

Jede Band (1) kann beliebig viele CDs (n) veröffentlichen. Jedes Label (1) kann beliebig viele CDs (n) auf den Markt bringen.

Oft wird beim Entwurf einer Datenbank versucht, Felder zweier Tabellen, die später durch eine Relation verknüpft werden sollen, gleich zu benennen. Das fördert die Übersichtlichkeit, ist aber keineswegs Bedingung.

8.6 Relationen


Grundsätzlich ist es auch möglich, dass sich der Primär- und der Fremdschlüssel in ein und derselben Tabelle befinden. Dann verweist ein Datensatz einer Tabelle auf einen anderen Datensatz derselben Tabelle. Das ist immer dann sinnvoll, wenn Hierarchien dargestellt werden sollen. Auch dazu einige Beispiele:

■ mylibrary-Datenbank: Jede Kategorie in der categories-Tabelle verweist durch das Feld parentID auf eine übergeordnete Kategorie (oder auf NULL).

■ Personaltabelle: Bei jedem Mitarbeiter (außer dem Chef bzw. der Chefin) verweist ein Feld auf die jeweils übergeordnete Person (den Gruppenleiter, Abteilungsleiter etc.).

■ Diskussionsforum, Tabelle mit Nachrichten: Bei jeder Nachricht verweist ein Feld auf die jeweils übergeordnete Nachricht (auf die hier geantwortet wurde).

■ Musikdatenbank, Tabelle mit Musikstilen: Bei jeder Stilrichtung verweist ein Feld auf die übergeordnete Gruppe – z.B. Bebop als eine Stilrichtung innerhalb der Kategorie Jazz.

n:m-Relationen

Für n:m-Relationen ist zwischen den beiden Ausgangstabellen eine zusätzliche Tabelle erforderlich, über die die n:m-Relation auf zwei 1:n-Relationen zurückge-führt wird. Beispiele:

■ mylibrary-Datenbank: Hier gibt es eine n:m-Beziehung zwischen Buchtiteln und Autoren. Die Relation wird über die rel_title_author-Tabelle hergestellt.

Eine mögliche Ergänzung dieser Tabelle könnte ein zusätzliches Feld sein, das die Reihenfolge der Autoren bestimmt (falls diese nicht einfach in alphabe-tischer Reihenfolge dargestellt werden sollen).

■ Geschäftsanwendung, Tabelle mit Bestellungen: Um eine Beziehung zwischen der Bestellung und den bestellten Artikeln herzustellen, gibt die dazwischen befindliche Tabelle an, wie viel Stück des Artikels x bei der Bestellung y geliefert werden sollen.

Die Tabelle könnte also aus den Spalten articleID, orderID, quantity und even-tuell auch price bestehen. Der Preis ist zwar sicher auch in der Artikeltabelle enthalten, er kann sich aber mit der Zeit ändern. In diesem Fall enthält die Bestelltabelle weiterhin den Preis, der zum Zeitpunkt der Bestellung gültig war.

■ Universitätsverwaltung, Prüfungslisten: Um zu speichern, welcher Student welche Prüfung (wann und mit welcher Note) absolviert hat, ist eine Tabelle erforderlich, die zwischen der Studententabelle und der Prüfungstabelle angesiedelt ist.

8 Datenbankdesign


8.7 Primär- und Fremdschlüssel Relationen hängen unmittelbar mit Primär- und Fremdschlüsseln zusammen. Dieser Abschnitt gibt eine zusammenfassende Erläuterung zu diesen beiden Begriffen und ihrer Anwendung. Es lässt sich leider nicht ganz vermeiden, dass dieser Abschnitt auf SQL-Kommandos vorgreift, die erst am Ende dieses Kapitels bzw. im nächsten Kapitel vorgestellt werden.

8.7.1 Primärschlüssel (Primary Key)

Die Aufgabe des Primärschlüssels besteht darin, möglichst rasch einen ganz be-stimmten Datensatz in der Tabelle zu finden (z.B. den Datensatz mit id=123456 aus einer Million Datensätzen). Diese Operation muss jedes Mal durchgeführt werden, wenn Daten aus mehreren Tabellen zusammengefügt werden – also sehr oft!

Bei den meisten Datenbanksystemen – inklusive MySQL – sind auch Primär-schlüssel erlaubt, die aus mehreren Feldern einer Tabelle zusammengesetzt sind. Unabhängig davon, ob nun eines oder mehrere Felder als Primärschlüssel dienen, sollten folgende Eigenschaften gelten:

■ Der Primärschlüssel muss eindeutig sein. Es muss ausgeschlossen sein, dass zwei Datensätze denselben Inhalt im Primärschlüsselfeld haben.

■ Das Primärschlüsselfeld soll kompakt sein. Das hat gleich zwei Gründe:

Erstens muss für das Primärschlüsselfeld ein Index (der Primärindex) verwaltet werden, damit die Suche nach Datensätzen (id = 123456) möglichst rasch er-folgt. Die Verwaltung dieses Index ist umso effizienter, je kompakter das Primärschlüsselfeld ist. Daher eignet sich eine Integerzahl besser als eine Zeichenkette variabler Länge zur Verwendung als Primärschlüsselfeld.

Zweitens wird der Inhalt des Primärschlüsselfelds in anderen Tabellen als Fremdschlüssel verwendet – und auch dort ist es effizient, wenn der Fremdschlüssel möglichst kompakt ist. (Relationen zwischen Tabellen werden ja nicht zuletzt deswegen eingeführt, um eine Platzverschwendung durch etwaige Redundanzen zu vermeiden. Das ist natürlich nur dann sinnvoll, wenn die Verwaltung von Schlüsselfeldern nicht noch mehr Platz beansprucht!)

Bei den meisten Datenbanksystemen hat es sich eingebürgert, als Primärschlüssel-feld eine 32- oder 64-Bit-Integerzahl zu verwenden, die vom Datenbanksystem automatisch als durchlaufende Nummer (1, 2, 3 ...) erzeugt wird. Daher brauchen sich weder Programmierer noch Anwender darum zu kümmern, wie bei jedem neuen Datensatz ein neuer und eindeutiger Primärschlüsselwert gefunden wird.

Bei MySQL werden solche Felder folgendermaßen deklariert:

CREATE TABLE publishers (publID INT NOT NULL AUTO_INCREMENT, othercolumns ..., PRIMARY KEY (publID))

8.7 Primär- und Fremdschlüssel


Von SQL in Deutsch übersetzt heißt das: Das Feld publID darf nicht NULL enthal-ten. Sein Inhalt wird von der Datenbank erzeugt (wenn beim Einfügen nicht expli-zit ein anderer Wert vorgegeben wird). Das Feld dient als Primärschlüssel, d.h., MySQL legt einen Index an, um eine schnelle Suche zu ermöglichen. Dadurch wird auch sichergestellt, dass der publID-Wert beim Einfügen neuer Datensätze ein-deutig ist. Bei Tabellen, in denen sehr viele Einträge oder Änderungen zu erwarten sind, sollte statt INT (32 Bit) meist BIGINT (64 Bit) verwendet werden.

Hinweis

Der Name des Primärschlüsselfelds spielt übrigens keine Rolle. In diesem Buch wird meist id oder tablenameID verwendet. Oft werden Sie auch Kombinationen mit no oder nr vorfinden, etwa kundenNr oder customerNo.

8.7.2 Fremdschlüssel (Foreign Key)

Die Aufgabe eines Fremdschlüsselfelds besteht darin, auf einen Datensatz in der Detailtabelle zu verweisen. Dieser Verweis kommt allerdings erst durch die Formulierung einer Datenbankabfrage zustande, etwa in der folgenden Form:

SELECT titles.title, publishers.publName FROM titles, publishers WHERE titles.publID = publishers.publID ORDER BY title

Damit wird eine alphabetische Liste aller Buchtitel erzeugt, bei der in der zweiten Spalte der Verlag angegeben wird, in dem das Buch erschienen ist. Das Ergebnis würde also so aussehen:

title publName Client/Server ... Addison-Wesley Definitive Guide ... Apress Linux Addison-Wesley Web Application ... New Riders

Der entscheidende Punkt bei der Abfrage ist die Klausel WHERE titles.publID = publishers.publID. Damit wird die Verbindung zwischen den beiden Tabellen her-gestellt. Andere Möglichkeiten, zwei Tabellen bei Abfragen miteinander zu ver-knüpfen, fasst Abschnitt 9.5 zusammen.

Bei der Deklaration einer Tabelle spielt das Fremdschlüsselfeld keine besondere Rolle. Für MySQL ist ein Fremdschlüsselfeld ein ganz gewöhnliches Tabellenfeld. Es sind keine besonderen Schlüsselwörter erforderlich. Sie sollten aber unbedingt darauf achten, dass das Fremdschlüsselfeld im gleichen Datentyp wie das Primär-schlüsselfeld deklariert wird – andernfalls wird die Verknüpfung sehr langsam.

8 Datenbankdesign


Das Attribut AUTO_INCREMENT darf nicht verwendet werden – Sie wollen ja selbst angeben, auf welchen Datensatz das Feld verweist!

Für das Schlüsselfeld braucht kein Index deklariert zu werden. Falls Sie aber häu-fig Abfragen durchführen, in denen Sie nach einem bestimmten Fremdschlüssel suchen, kann ein Index (wie bei jedem anderen Feld) die Effizienz der Suche ver-bessern.

CREATE TABLE titles (othercolumns ..., publID INT NOT NULL)

Ob Sie das Attribut NOT NULL angeben, hängt vom Kontext ab. In den meisten Fällen ist NOT NULL empfehlenswert, um unvollständige Eingaben von vornher-ein zu vermeiden. Wenn Sie aber zum Beispiel in der Buchdatenbank zulassen möchten, dass Bücher auch ohne Verlagsangabe gespeichert werden dürfen, kön-nen Sie auf NOT NULL verzichten.

8.7.3 Referenzielle Integrität (Foreign-Key-Regeln)

Wenn Sie aus der authors-Tabelle der mylibrary-Datenbank den Autor Kofler löschen, werden Sie in der Folge bei vielen SQL-Abfragen Probleme bekommen, die auf die Bücher Linux und Definitive Guide ... zugreifen: Die in der rel_title_-author-Tabelle angegebene authID-Nummer 1 existiert in der authors-Tabelle nicht mehr. In der Datenbanksprache würde man das so formulieren: Die referenzielle Integrität der Datenbank ist verletzt.

Als Datenbankentwickler sind Sie dafür verantwortlich, dass so etwas nicht passie-ren kann. Sie müssen also vor dem Löschen eines Datensatzes immer überprüfen, ob es nicht in einer anderen Tabelle einen Verweis auf gerade diesen Datensatz gibt.

Da auf Programmierer nicht immer Verlass ist (und Datenbanken manchmal auch manuell verändert werden müssen), sehen viele Datenbanken Regeln zur Einhal-tung der referenziellen Integrität vor. So genannte Foreign-Key-Regeln (Integritäts-regeln) überprüfen bei jeder Veränderung der Datenbank, ob dadurch Querver-weise zwischen Tabellen betroffen sind. Je nach Deklaration des Fremdschlüssels gibt es dann zwei mögliche Konsequenzen: Entweder wird die betreffende Opera-tion einfach nicht ausgeführt (Fehlermeldung), oder es werden gleich auch alle be-troffenen Datensätze in abhängigen Tabellen gelöscht. Welche Vorgehensweise günstiger ist, hängt vom Inhalt der Daten ab.

Auch MySQL bietet einen derartigen Kontrollmechanismus, momentan allerdings nur dann, wenn Sie InnoDB-Tabellen einsetzen. Die folgenden Zeilen zeigen aus-zugsweise den SQL-Code zur Deklaration eines Fremdschlüssels mit Integritäts-regel. (SQL-Kommandos zum Erzeugen und Verändern von Tabellen werden in Abschnitt 9.9 behandelt.)



CREATE TABLE titles (column1, column2, ..., publID INT, FOREIGN KEY (publID) REFERENCES publishers (publID) )

Das bedeutet, dass titles.publID ein Fremdschlüssel (Foreign Key) ist, der auf den Primärschlüssel publishers.publID verweist. Die Integritätsregel hat Auswirkungen auf beide Tabellen:

■ In titles können Sie keine Titel mit einer publID-Nummer einfügen, die es in der publishers-Tabelle nicht gibt. (Ebenso wenig dürfen Sie in titles den Wert von publID bei einem vorhandenen Titel ändern, wenn es keinen entsprechenden publishers-Datensatz gibt.)

■ Aus publishers dürfen Sie keinen Verlag löschen, auf den die titles-Tabelle ver-weist. (Auch hier gilt die Einschränkung ebenso für UPDATE-Kommandos.)

Daraus ergeben sich auch Konsequenzen für die Reihenfolge von Operationen: Wenn Sie einen neuen Titel für einen neuen Verlag speichern möchten, müssen Sie zuerst den Verlag und dann den Titel eintragen. Wenn Sie Verlag und Titel wieder löschen möchten, müssen Sie dagegen zuerst den Titel und dann den Verlag löschen. (Letzteres ist natürlich nur möglich, wenn es keinen anderen Titel gibt, der in diesem Verlag erschienen ist.)

Syntax

Die allgemeine Syntax zur Definition einer Integritätsregel für ein Fremdschlüssel-feld table1.column1 sieht so aus:

FOREIGN KEY [name] (column1) REFERENCES table2 (column2) [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

Durch name kann die Integritätsregel benannt werden. table1.column1 ist der Fremdschlüssel, für den die Integritätsregel definiert wird. table2.column2 ist das Feld der zweiten Tabelle, auf das sich die Regel bezieht. (In vielen Fällen ist column2 der Primärschlüssel von table2. Das ist keine Bedingung. column2 muss aber auf jeden Fall mit einem Index ausgestattet sein.)

Es sind auch Integritätsregeln erlaubt, bei denen table1 und table2 die gleiche Tabelle ist. Das ist dann sinnvoll, wenn eine Tabelle Verweise auf sich selbst ent-hält. Das ist z.B. bei der categories-Tabelle aus mylibrary der Fall, wo parentCatID auf catID verweist und so den hierarchischen Zusammenhang der Kategorien herstellt.

8 Datenbankdesign


Aktionen bei Integritätsverletzungen

Die optionale Klausel ON DELETE bestimmt, wie sich der Tabellentreiber ver-halten soll, wenn aus table2 ein Datensatz gelöscht wird, auf den table1 verweist. Es gibt vier Möglichkeiten:

■ RESTRICT ist das Defaultverhalten. Das DELETE-Kommando verursacht einen Fehler, der Datensatz wird nicht gelöscht. (Durch den Fehler kommt es übrigens nicht automatisch zum Ende einer gerade laufenden Transaktion. Das Kommando wird einfach nicht ausgeführt. Die Transaktion muss wie üblich durch COMMIT oder ROLLBACK abgeschlossen werden.)

■ SET NULL bewirkt, dass der Datensatz aus table2 gelöscht werden darf. In table1 wird bei allen Datensätzen, die auf den gelöschten Datensatz verweisen, column1 auf NULL gesetzt. Diese Regel setzt voraus, dass NULL ein zulässiger Wert für table1.column1 ist.

Für das titles/publishers-Beispiel bedeutet das: Wenn Sie den Verlag x aus pub-lishers löschen, wird bei allen Datensätzen aus titles, die in diesem Verlag er-schienen sind, publID auf NULL gesetzt.

■ CASCADE bewirkt, dass der Datensatz aus table2 gelöscht werden darf. Gleich-zeitig werden aber auch alle Datensätze aus table1 gelöscht, die darauf ver-weisen!

Für das titles/publishers-Beispiel bedeutet das: Wenn Sie den Verlag x aus pub-lishers löschen, werden auch alle Datensätze aus titles gelöscht, die im Verlag x erschienen sind.

■ NO ACTION bewirkt, dass die Integritätsverletzung akzeptiert wird. Das wird aber nur selten sinnvoll sein – dann ist es einfacher, auf die Integritätsregel ganz zu verzichten.

Analog können diese vier Aktionen auch für ON UPDATE angegeben werden (wobei per Default wiederum RESTRICT gilt). Die UPDATE-Regeln kommen zur Anwendung, wenn in table2 bei einem vorhandenen Datensatz das Schlüsselfeld verändert wird. In diesem Fall ist die Wirkung von RESTRICT, SET NULL und NO ACTION wie bei ON DELETE.

Ein wenig anders ist dagegen die Wirkung von CASCADE: Die Änderung des Schlüsselfelds in table2 wird nun auch im Fremdschlüsselfeld in table1 durchge-führt. Für das titles/publishers-Beispiel bedeutet das: Wenn Sie in publishers für den Verlag x das publID-Feld ändern, wird auch in allen Datensätzen aus titles das publID-Feld entsprechend aktualisiert.



Hinweis

Die Integritätsregeln verhindern nicht, dass Sie eine ganze Tabelle löschen können! Sie können also z.B. auf jeden Fall DROP TABLE publishers ausführen, auch wenn dadurch Integritätsregeln verletzt werden.

Voraussetzungen für das Aufstellen von Integritätsregeln

Integritätsregeln können nur aufgestellt werden, wenn eine Reihe von Vorausset-zungen erfüllt sind. Wenn das nicht der Fall ist, kommt es zu einer Fehlermeldung – meist Error 1005: Can't create table xxx (errno: 150) – und die Integritätsregel wird nicht gespeichert. (Die Ursache für die Fehlermeldung kann aber auch ganz trivial sein, z.B. ein Tippfehler bei einem Spaltennamen!)

■ Sowohl table1.column1 als auch table2.column2 müssen zumindest mit einem ge-wöhnlichen Index ausgestattet sein. Dieser Index wird durch FOREIGN KEY nicht erzeugt und muss daher explizit in CREATE TABLE vorgesehen bzw. nachträglich durch ALTER TABLE hinzugefügt werden.

table2.column2 ist oft das Primärschlüsselfeld von table2, das ist aber keine Vor-aussetzung.

Falls Sie Schlüssel über mehrere Felder (INDEX(columnA, columnB)) verwenden, muss das Schlüsselfeld aus der Integritätsregel an erster Stelle stehen. Andern-falls muss ein zusätzlicher, eigener Index für das Feld erzeugt werden.

■ Die Datentypen von table1.column1 und table2.column2 müssen so weit überein-stimmen, dass ein direkter Vergleich ohne Datentypumwandlung möglich ist. Am effizientesten ist es, wenn beide Felder mit INT oder BIGINT deklariert sind. Beide Spalten müssen den gleichen Vorzeichentyp (SIGNED oder UN-SIGNED) aufweisen.

■ In table1.column1 muss der Wert NULL erlaubt sein, wenn die optionale Regel ON DELETE/UPDATE SET NULL definiert wird.

■ Die Integritätsregel muss von Anfang an erfüllt sein: Wenn die Tabellen bereits mit Daten gefüllt sind, kann es passieren, dass einzelne Datensätze den Integritätsregeln widersprechen. In diesem Fall kommt es beim ALTER-TABLE-Kommando zum Fehler 1216 (A foreign key constraint fails). Die Datensätze müssen korrigiert werden, bevor die Integritätsregel eingerichtet werden kann.

8 Datenbankdesign


Tipp

Wenn beim Erstellen einer Integritätsregel ein Fehler auftritt, können Sie genauere Informationen über die Ursache des Fehlers mit SHOW INNODB STATUS ermitteln.

Nicht erfüllte Integritätsregeln finden

Die offensichtliche Frage nach einem Fehler 1216 lautet natürlich: Wie können die fehlerhaften Datensätze gefunden werden? Ein einfaches Sub-SELECT-Kommando ermittelt alle Datensätze aus der titles-Tabelle, bei denen titles.publID einen Wert enthält, zu dem es in publishers.publID keine Entsprechung gibt.

SELECT titleID, publID FROM titles WHERE publID NOT IN (SELECT publID FROM publishers)

titleID publID publishers.publID

66 99 NULL

Der Titel mit titleID=66 verweist also auf einen Verlag mit publID=99 in der pub-lishers-Tabelle, den es dort aber nicht gibt. Sie müssen nun entweder den fehlenden Verlag einfügen oder den Titel Nummer 66 löschen. (In der titles-Tabelle können Sie beim fehlerhaften Titel publID auch auf NULL setzen – das ist in der Praxis bei verknüpften Tabellen aber oft nicht erlaubt.)

Integritätsregeln entfernen

Um eine Integritätsregel wieder zu löschen, führen Sie ALTER TABLE aus:

ALTER TABLE tablename DROP FOREIGN KEY foreign_key_id

Den foreign_key_id des zu löschenden Index können Sie mit SHOW CREATE TABLE ermitteln. Beachten Sie, dass das Löschen von FOREIGN-KEY-Regeln Pro-bleme bereiten kann, wenn Sie gleichzeitig auch Replikation nutzen! Der Grund: Der Foreign-Key-Index kann auf der replizierten Datenbank einen anderen Namen aufweisen als auf der ursprünglichen Datenbank.

Integritätskontrolle vorübergehend deaktivieren

Durch SET foreign_key_checks=0 können Sie die automatische Kontrolle der Integri-tätsregeln abschalten. Das kann beispielsweise sinnvoll sein, um große Backup-Tabellen möglichst effizient wieder einzulesen.

SET foreign_key_checks=1 aktiviert die Regeln wieder. Vorsicht: In der Zwischenzeit durchgeführte Änderungen an der Datenbank werden dabei nicht überprüft. Wenn

8.8 Indizes


dabei Integritätsregeln verletzt wurden, werden diese Fehler nicht automatisch er-kannt!

Foreign-Key-Regeln in der mylibrary-Datenbank

Die mylibrary-Datenbank besteht ausschließlich aus InnoDB-Tabellen. Sämtliche Relationen zwischen den Tabellen sind durch Integritätsregeln abgesichert: Fremdschlüssel Referenzierter Schlüssel

titles.publID publishers.publID

titles.langID languages.langID

titles.catID categories.catID

categories.parentCatID categories.catID

rel_title_author.titleID titles.titleID

rel_title_author.authID authors.authID

Tabelle 8.12: Fremdschlüssel und referenzierte Schlüssel in der mylibrary-Datenbank

8.8 Indizes

8.8.1 Einführung

Wenn Sie einen bestimmten Datensatz einer Tabelle suchen oder eine geordnete Tabelle einer Reihe von Datensätzen erstellen möchten, muss MySQL alle Datensätze der Tabelle laden. Die folgenden Zeilen zeigen einige entsprechende SELECT-Kommandos (Details dazu folgen im nächsten Kapitel):

SELECT column1, column2 ... FROM table WHERE column3=12345 SELECT column1, column2 ... FROM table ORDER BY column3 SELECT column1, column2 ... FROM table WHERE column3 LIKE 'Smith%' SELECT column1, column2 ... FROM table WHERE column3 > 2000

Bei großen Tabellen leidet unter solchen ganz alltäglichen Abfragen natürlich die Performance. Zum Glück gibt es eine einfache Abhilfe: Sie benötigen einen Index für die betroffene Spalte (in den obigen Beispielen also für column3)!

Ein Index ist eine zusätzliche Datei oder ein zusätzlicher Dateibereich (InnoDB) mit sortierten Querverweisen auf die Datensätze einer Tabelle. (Ein Datenbankindex funktioniert also ganz ähnlich wie das Stichwortverzeichnis dieses Buchs. Das Stichwortverzeichnis erspart Ihnen die Mühe, das ganze Buch von vorn bis hinten durchzulesen, wenn Sie lediglich herausfinden möchten, wo ein bestimmtes Thema beschrieben wird.)

8 Datenbankdesign


Achtung

Indizes sind kein Allheilmittel! Zwar beschleunigen sie den Zugriff auf Daten, sie verlangsamen aber gleichzeitig jede Änderung in der Datenbank. Jedes Mal, wenn ein Datensatz verändert wird, muss zu-sätzlich auch der Index verändert werden. Dieser Nachteil kann durch die Option DELAY_KEY_WRITE bei diversen SQL-Komman-dos etwas gemildert werden. Die Option bewirkt, dass der Index nicht bei jedem neuen bzw. geänderten Datensatz aktualisiert wird, sondern nur hin und wieder. DELAY_KEY_WRITE bietet sich bei-spielsweise dann an, wenn möglichst rasch möglichst viele neue Datensätze in die Tabelle eingefügt werden sollen. Ein weiterer offensichtlicher Nachteil von Indizes besteht darin, dass sie natürlich zusätzlichen Platz auf der Festplatte benötigen.

Setzen Sie Indizes also nur für solche Spalten ein, nach denen Sie oft suchen oder sortieren. Indizes bleiben weitgehend nutzlos, wenn die Spalte sehr viele gleiche Einträge enthält. (In solchen Fällen sollten Sie sich eher die Frage stellen, ob die Normalisierung der Datenbank nicht noch optimiert werden könnte.)

Prinzipiell kann für jedes Feld der Tabelle ein Index eingerichtet werden – bis zu einer Maximalanzahl von 16 Indizes pro Tabelle. (MySQL erlaubt auch Indizes für mehrere Felder gleichzeitig. Das ist dann sinnvoll, wenn häufig nach einer Kombi-nation von Feldern gesucht oder sortiert wird – etwa WHERE country='Austria' AND city='Graz').

Indizes bei InnoDB-Tabellen

Noch wichtiger als bei MyISAM-Tabellen sind Indizes bei InnoDB-Tabellen. Dort werden Indizes nämlich nicht nur zur Suche nach Datensätzen verwendet, sondern auch zum so genannten Row-Level-Locking. Das bedeutet, dass während einer Transaktion einzelne Datensätze vorübergehend für den Zugriff durch andere Be-nutzer gesperrt werden. Das betrifft beispielsweise die Kommandos SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE sowie INSERT, UPDATE und DELETE. (Details zum Thema Transaktionen und Locking folgen in Abschnitt 10.15.)

Die interne Kennzeichnung der gesperrten Datensätze erfolgt aus Effizienzgrün-den nicht in den eigentlichen Tabellen, sondern im Index. Das funktioniert natür-lich nur, wenn ein geeigneter Index zur Verfügung steht!

8.8 Indizes


Einschränkungen

■ MySQL kann Indizes nicht anwenden, wenn Sie Ungleichheitsoperatoren ein-setzen (WHERE column != ...).

■ Ebenso können Indizes nicht für Vergleiche eingesetzt werden, bei denen der Spalteninhalt mit einer Funktion bearbeitet wird (WHERE DAY(column)= ...).

■ Bei JOIN-Operationen (also beim Zusammenfügen von Daten aus mehreren Tabellen) helfen Indizes nur dann, wenn Primär- und Fremdschlüssel den-selben Datentyp aufweisen.

■ Wenn die Vergleichsoperatoren LIKE oder REGEXP eingesetzt werden, hilft ein Index nur dann, wenn am Beginn des Suchmusters kein Jokerzeichen steht. Bei LIKE 'abc%' hilft ein Index, bei LIKE '%abc' dagegen nicht.

■ Indizes kommen schließlich nur dann bei ORDER-BY-Operationen zum Ein-satz, wenn die Datensätze nicht vorher nach anderen Kriterien ausgewählt wer-den müssen. (Gerade bei Abfragen, bei denen die Datensätze aus mehreren Tabellen zusammengesetzt werden, hilft ein Index leider nur in seltenen Fällen, ORDER BY zu beschleunigen.)

■ Indizes sind wirkungslos, wenn eine Spalte sehr häufig dieselben Werte enthält. Es ist daher nicht sinnvoll, eine Spalte mit 0/1- oder Y/N-Werten zu indizieren.

8.8.2 Gewöhnlicher Index, Unique-Index, Primärindex

Gewöhnlicher Index

Die einzige Aufgabe eines gewöhnlichen Index (Definition mit dem Schlüsselwort KEY oder INDEX) ist es, den Zugriff auf die Daten zu beschleunigen. Sie sollten damit Spalten indizieren, die Sie in Bedingungen (WHERE column=...) oder zum Sortieren (ORDER BY column) häufig benötigen. Indizieren Sie nach Möglichkeit Spalten mit kompakten Daten (z.B. Integerzahlen).

Unique-Index

Bei einem gewöhnlichen Index ist es erlaubt, dass mehrere Datensätze der Tabelle im indizierten Feld denselben Wert aufweisen. (In einer Personaltabelle kann es beispielsweise zweimal denselben Namen geben, obwohl es sich um unterschied-liche Personen handelt.)

Wenn aus dem Kontext klar ist, dass eine Spalte eindeutige Werte enthält, sollten Sie den Index mit dem Schlüsselwort UNIQUE definieren. Das hat zweierlei Kon-sequenzen:

8 Datenbankdesign


■ Zum einen fällt MySQL jetzt die Verwaltung des Index leichter, d.h., der Index ist noch effizienter.

■ Zum anderen stellt MySQL jetzt sicher, dass Sie keinen neuen Datensatz einfügen, wenn es schon einen anderen Datensatz gibt, der im indizierten Feld denselben Wert aufweist. (Manchmal wird ein UNIQUE-Index nur aus diesem Grund definiert – also nicht zur Zugriffsoptimierung, sondern zur Vermeidung von Doppelgängern.)

Primärindex

Für die im vorigen Abschnitt immer wieder erwähnten Primärschlüsselfelder muss ein Primärindex definiert werden. Dabei handelt es sich um einen UNIQUE-Index, dessen einzige Besonderheit darin besteht, dass der Index den Namen PRIMARY hat.

Foreign-Key-Index

Auch wenn Sie für ein Fremdschlüsselfeld (siehe ebenfalls den vorigen Abschnitt) eine Integritätsregel definieren, erzeugt MySQL intern einen Index. Dieser Index dient dazu, die Einhaltung der Foreign-Key-Regel möglichst effizient sicherzu-stellen.

Zusammengesetzte Indizes

Ein Index darf auch mehrere Spalten umfassen – also etwa INDEX(columnA, columnB). Eine Besonderheit bei solchen Indizes besteht darin, dass MySQL einen derartigen Index selektiv einsetzen kann.

Wenn also für eine Abfrage nur ein Index für columnA benötigt wird, kann dafür der zusammengesetzte Index für INDEX(columnA, columnB) eingesetzt werden. Dies gilt allerdings nur für Teilindizes am Beginn der Indexreihenfolge. INDEX(A, B, C) kann also auch als Index für A oder für (A,B) verwendet werden, nicht aber als Index für B oder C oder (B,C) etc.

Beschränkung der Indexlänge

Bei der Definition eines Index für CHAR- und VARCHAR-Spalten können Sie den Index auf eine bestimmte Zeichenanzahl beschränken (die kleiner ist als die maximal in diesem Feld erlaubte Zeichenanzahl). Sie erreichen damit, dass die resultierende Indexdatei kleiner und ihre Auswertung schneller wird. In den meisten Anwendungsfällen – d.h. bei Zeichenketten mit Namen – reichen bereits 10 bis 15 Zeichen vollkommen, um die Suchmenge auf ganz wenige Datensätze zu reduzieren.

Bei BLOB- und TEXT-Spalten müssen Sie diese Beschränkung durchführen, wobei MySQL eine maximale Indexlänge von 255 Zeichen zulässt.

8.8 Indizes


8.8.3 Volltextindex

Ein gewöhnlicher Index für Textfelder hilft nur bei der Suche nach Zeichenketten, die am Beginn des Feldes stehen (d.h., deren Anfangsbuchstaben bekannt sind). Wenn Sie in Textfeldern hingegen Texte speichern, die aus mehreren – womöglich sehr vielen – Wörtern bestehen, bleibt ein herkömmlicher Index wirkungslos. Die Suche muss in der Art LIKE '%wort%' formuliert werden, was für MySQL denkbar aufwändig ist und bei großen Datenmengen zu unerträglich langen Antwortzeiten führt.

In solchen Fällen bietet sich der Einsatz eines Volltextindex an. MySQL erstellt bei dieser Form des Index eine Liste aller Wörter, die im Text vorkommen. Ein Volltextindex kann während des Datenbankdesigns oder auch nachträglich eingerichtet werden:

ALTER TABLE tablename ADD FULLTEXT(column1, column2)

In SELECT-Abfragen kann nun nach Datensätzen gesucht werden, die ein oder mehrere Wörter enthalten. Dabei gilt eine eigene Abfragesyntax:

SELECT * FROM tablename WHERE MATCH(column1, column2) AGAINST('wort1', 'wort2', 'wort3')

Damit werden alle Datensätze gefunden, bei denen in den Spalten column1 und column2 die Wörter wort1, wort2 und wort3 vorkommen.

Hinweis

Der InnoDB-Tabellentreiber unterstützt leider noch keinen Volltext-index.

Eine ausführliche Beschreibung der SQL-Syntax für die Volltext-suche sowie eine Menge Anwendungsbeispiele finden Sie in Ab-schnitt 10.13.

8.8.4 Abfrage- und Indexoptimierung

Grundsätzlich lassen sich realistische Performance-Abschätzungen erst dann durchführen, wenn die Datenbank mit ausreichend Testdaten gefüllt ist. Eine Testdatenbank mit einigen hundert Datensätzen befindet sich nach der ersten Abfrage üblicherweise vollständig im RAM, anschließend werden alle Abfragen mit oder ohne Index sehr schnell beantwortet. Interessant wird es, wenn Tabellen weit über 1000 Datensätze enthalten bzw. wenn die Gesamtgröße der Datenbank die des RAMs auf dem MySQL-Server überschreitet.

Bei der Entscheidung, welche Spalten mit Indizes versehen werden sollten, kann unter Umständen das Kommando EXPLAIN SELECT zu Hilfe genommen werden. Dazu wird einfach einem gewöhnlichen SELECT-Kommando das Schlüsselwort

8 Datenbankdesign


EXPLAIN vorangestellt. Anstatt die SELECT-Abfrage nun einfach auszuführen, stellt MySQL in einer Tabelle Informationen darüber dar, wie die Abfrage ausgeführt würde und welche Indizes dabei (soweit vorhanden) zum Einsatz kämen.

Kurz einige Hilfen zur Interpretation der von EXPLAIN erzeugten Tabelle: In der ersten Spalte werden die Namen der Tabellen in der Reihenfolge angegeben, wie sie aus der Datenbank gelesen werden. Die Spalte type gibt an, wie die Tabelle mit den anderen Tabellen verknüpft wird (JOIN). Am effizientesten (schnellsten) funktioniert das mit dem Typ system, zunehmend aufwändiger sind die Typen const, eq_ref, ref, range, index und ALL. (ALL bedeutet, dass für jeden Datensatz der übergeordneten Tabelle alle Datensätze dieser Tabelle eingelesen werden müssen. Das kann mit einem Index meist verhindert werden.)

Die Spalte possible_keys gibt an, auf welche Indizes MySQL bei der Suche nach Datensätzen zurückgreifen kann. Die Spalte key gibt an, für welchen Index sich MySQL tatsächlich entschieden hat. key_len gibt die Breite des Index in Byte an. Bei einem Index über eine INTEGER-Spalte beträgt die Byteanzahl beispielsweise 4. key_len kann auch Aufschluss darüber geben, wie viele Teile eines mehrteiligen Index verwendet werden. Grundsätzlich gilt: je kleiner key_len ist, desto besser (also schneller).

Die Spalte ref gibt an, über welche Spalte einer anderen Tabelle die Verknüpfung hergestellt wird. rows enthält eine Schätzung, wie viele Datensätze MySQL voraus-sichtlich lesen muss, um die gesamte Abfrage auszuführen. Das Produkt aller Zahlen der rows-Spalte erlaubt einen Rückschluss darauf, wie viele Kombinationen durch die Abfrage entstehen.

Die Spalte extra gibt schließlich Zusatzinformationen über die JOIN-Operation, bei-spielsweise using temporary, wenn MySQL eine temporäre Tabelle zur Ausführung der Abfrage erstellen muss.

Verweis

Wenngleich die von EXPLAIN gebotenen Informationen oft wertvoll sind, erfordert ihre Interpretation einiges an MySQL- und Daten-bankerfahrung. Weitere Interpretationshilfen finden Sie im MySQL-Handbuch:

http://dev.mysql.com/doc/mysql/en/query-speed.html http://dev.mysql.com/doc/mysql/en/explain.html

Eine lesenswerte Abhandlung zur MySQL-Geschwindigkeitsopti-mierung finden Sie hier im OpenOffice-Format:

http://dev.mysql.com/tech-resources/presentations/ http://dev.mysql.com/Downloads/Presentations/OSCON-2004.sxi

8.8 Indizes


Beispiel 1

Die Abfrage ermittelt eine ungeordnete Liste aller Bücher mit all ihren Autoren. Alle ID-Spalten sind mit primären Indizes ausgestattet.

USE mylibrary

EXPLAIN SELECT * FROM titles, rel_title_author, authors WHERE rel_title_author.authID = authors.authID AND rel_title_author.titleID = titles.titleID

table type key key_len ref rows Extra

titles ALL authName 60 NULL 53 Using index

rel_title_author ref authID 4 authors.authID 1 Using index

authors eq_ref PRIMARY 4 rel_title_author. titleID

1

Das obige EXPLAIN-Ergebnis besagt, dass zuerst alle Datensätze aus der titles-Tabelle gelesen werden, wobei der Index authName verwendet wird. (Das wäre eigentlich nicht notwendig, weil das SELECT-Kommando gar keine Sortierung der Ergebnisse verlangt.) Danach werden unter Zuhilfenahme des authID-Index von rel_title_author und des Primärindex von authors die Verbindungen zu den beiden anderen Tabellen hergestellt. Die Tabellen sind also optimal indiziert, für sämtliche Teile der Abfrage stehen Indizes zur Verfügung.

Aus Platzgründen wurden aus dem EXPLAIN-Ergebnis einige Spalten entfernt, darunter auch possible_keys. Diese Spalte enthält eine Aufzählung aller Indizes, die für den jeweiligen Teil der Abfrage in Frage kommen. Die Spalte key gibt an, für welchen dieser Indizes sich MySQL entschieden hat.

Beispiel 2

Die Abfrage ermittelt eine Liste aller Bücher (mit ihren Autoren), die in einem be-stimmten Verlag erschienen sind. Die Liste wird nach Buchtiteln geordnet. Aber-mals sind alle ID-Spalten mit primären Indizes ausgestattet. Außerdem sind in der titles-Tabelle title und publID indiziert.

EXPLAIN SELECT title, authName FROM titles, rel_title_author, authors WHERE titles.publID=2 AND titles.titleID = rel_title_author.titleID AND authors.authID = rel_title_author.authID ORDER BY title


titles ref publIDIndex 5 const 4 Using where; Using filesort

rel_title_author ref PRIMARY 4 titles.titleID 2 Using index

authors eq_ref PRIMARY 4 rel_title_author.authID 1

8 Datenbankdesign


Zur Interpretation: Die Tabellen sind optimal indiziert, d.h., für sämtliche Teile der Abfrage stehen Indizes zur Verfügung. Interessant ist der Umstand, dass die Titel-liste (ORDER BY title) offensichtlich extern sortiert wird, obwohl auch für die title-Spalte ein Index zur Verfügung steht. Der Grund besteht vermutlich darin, dass die title-Datensätze zuerst gemäß der Bedingung publID=2 ausgewählt werden und der title-Index dann nicht mehr angewandt werden kann.

Beispiel 3

Dieses Beispiel verwendet dieselbe SELECT-Abfrage wie Beispiel 2, geht aber da-von aus, dass es für titles.publID keinen Index gibt. Die Folge ist, dass nun alle 53 Datensätze der titles-Tabelle gelesen werden müssen. Dabei wird zwar der Index authName verwendet, dieser hilft aber nicht dabei, die Abfrage zu beschleu-nigen. Using temporary bedeutet, dass sogar eine temporäre Tabelle mit Zwischen-ergebnissen erzeugt wird.


titles index authName 60 NULL 53 Using index; Using temporary; Using filesort

rel_title_author ref PRIMARY 4 titles.titleID 4 Using index

authors eq_ref PRIMARY 4 rel_title_author.authID 4 Using where

8.9 Views

Views (wörtlich: Sichten oder Ansichten) ermöglichen es, eine spezielle Darstel-lung einer oder mehrerer Tabellen zu definieren. Die View verhält sich selbst weitestgehend wie eine Tabelle, d.h., Sie können daraus Daten mit SELECT abfragen und (je nach Definition der View) mit INSERT, UPDATE und DELETE auch ändern.

Views stehen ab MySQL 5.0 zur Verfügung. Der Abschnitt setzt voraus, dass Sie mit SELECT-Kommandos umgehen können (siehe das folgende Kapitel) und das Prinzip der MySQL-Zugriffsrechte kennen (siehe Kapitel 11). Beachten Sie, dass phpMyAdmin 2.6 noch nicht mit Views zurechtkommt.

Es gibt vor allem zwei Gründe für den Einsatz von Views:

■ Sicherheit: Es kann sein, dass Sie bestimmten Benutzern einer Datenbank keinen vollständigen Zugriff auf eine Tabelle gewähren möchten. Ein typisches Beispiel wäre eine Personaltabelle in einem Betrieb, in der diverse Daten zu allen Angestellten gespeichert sind. Sie möchten, dass alle Benutzer auf einige Daten zugreifen dürfen (z.B. Name und Telefonnummer), auf andere hingegen nicht (z.B. Geburtsdatum und Gehalt).

8.9 Views


Die Lösung ist eine View, die nur die für alle zugänglichen Spalten enthält. Gleichzeitig müssen Sie die MySQL-Zugriffsrechte so einstellen, dass der Be-nutzer zwar auf die View zugreifen darf, nicht aber auf die zugrunde liegende Personaltabelle.

■ Komfort: In vielen Anwendungen müssen immer wieder die gleichen Abfragen durchgeführt werden, um Daten aus einer oder mehreren Tabellen nach be-stimmten Anforderungen zusammenzufügen. Anstatt jeden Anwender bzw. Programmierer zu zwingen, immer wieder dieselben komplizierten SELECT-Kommandos zusammenzustellen, können Sie als Datenbankadministrator eine View definieren.

View definieren

Views verhalten sich wie virtuelle Tabellen mit dem Ergebnis einer SELECT-Ab-frage. Daher verwundert es nicht weiter, dass die Definition auf einem SELECT-Kommando basiert. Die beiden folgenden Beispiele definieren zwei Views für die mylibrary-Datenbank und zeigen jeweils fünf Datensätze dieser Views.

CREATE VIEW v1 AS SELECT titleID, title, subtitle FROM titles ORDER BY title, subtitle

SELECT * FROM v1 LIMIT 5

titleID title subtitle

11 A Guide to the SQL Standard NULL 52 A Programmer's Introduction ... NULL 19 Alltid den där Annette NULL 51 Anklage Vatermord Der Fall Philipp Halsmann 78 Apache Webserver 2.0 Installation, ...

CREATE VIEW v2 AS SELECT title, publname, catname FROM titles, publishers, categories WHERE titles.publid=publishers.publid AND titles.catID = categories.catID AND langID=2

SELECT * FROM v2 ORDER BY title LIMIT 5

title publname catname

Anklage Vatermord Zsolnay Literature and fiction Apache Webserver 2.0 Addison-Wesley Computer books CSS-Praxis Galileo Computer books Ein perfekter Freund Diogenes Verlag Literature and fiction Excel 2000 programmieren Addison-Wesley Programming

Damit Sie CREATE VIEW ausführen dürfen, müssen Sie über das Create-View-Privileg verfügen. Was Privilegien sind und wie sie verwaltet werden, lernen Sie in Kapitel 11.

8 Datenbankdesign


View-Datensätze ändern

Ob auf eine View die Kommandos INSERT, UPDATE und DELETE angewendet werden können (d.h., ob die View updateable ist, wie es im Datenbank-Jargon heißt), hängt vom zugrunde liegenden SELECT-Kommando ab. Für veränderliche Views gelten folgende Regeln:

■ Das SELECT-Kommando darf nicht GROUP BY, DISTINCT, LIMIT, UNION oder HAVING enthalten.

■ Views, die Daten aus mehreren Tabellen verarbeiten, sind fast immer unver-änderlich.

■ Die View sollte alle Spalten enthalten, für die Primär- oder Unique-Indizes oder Foreign-Key-Regeln definiert sind. Wenn derartige Spalten in der View fehlen, entscheidet die MySQL-Option updatable_views_with_limit, ob Änderungen mit einer Warnung ausgeführt werden (aktuelle Defaulteinstellung 1) oder ob ein Fehler ausgelöst wird (Einstellung 0).

View-Optionen

Die vollständige Syntax des CREATE-VIEW-Kommandos sieht so aus:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW name [(spaltenliste)] AS select-kommando [WITH [CASCADED | LOCAL] CHECK OPTION]

Die folgenden Punkte geben eine kurze Erklärung zu den verschiedenen Optionen:

■ OR REPLACE bedeutet, dass eine schon vorhandene View ohne Fehlermeldung durch die neue View ersetzt wird.

■ ALGORITHM gibt an, wie die View intern dargestellt wird. Die Option war leider noch nicht dokumentiert, als dieser Abschnitt verfasst wurde. Per Default verwendet MySQL auf jeden Fall UNDEFINED (kann durch SHOW CREATE TABLE viewname ermittelt werden).

■ WITH CHECK OPTION bedeutet, dass Änderungen an den View-Datensätzen nur zulässig sind, wenn dabei auch die WHERE-Bedingungen des SELECT-Kommandos erfüllt werden. WITH CHECK OPTION ist natürlich nur relevant, wenn die View überhaupt veränderbar ist.

Die Variante WITH LOCAL CHECK OPTION betrifft Views, die selbst von ande-ren Views abgeleitet sind (auch das ist erlaubt!). LOCAL bedeutet, dass nur die WHERE-Bedingungen des CREATE-VIEW-Kommandos berücksichtigt werden, nicht aber die WHERE-Bedingungen der übergeordneten Views.

8.10 Beispieldatenbank mylibrary (Bücherverwaltung)


Genau die gegenteilige Wirkung hat WITH CASCADED CHECK OPTION: Es werden die WHERE-Bedingungen aller übergeordneten Views berücksichtigt. Wenn Sie weder CASCADED noch LOCAL angeben, gilt per Default CAS-CADED.

View-Definitionen ansehen

So, wie Sie mit CREATE TABLE name den SQL-Code des Kommandos zum Erzeu-gen der Tabelle ermitteln können, ist dies mit CREATE VIEW name auch für Views möglich. Damit Sie CREATE VIEW ausführen dürfen, müssen Sie allerdings über das Create-View-Privileg verfügen.

SHOW CREATE VIEW v1

CREATE ALGORITHM=UNDEFINED VIEW `mylibrary`.`v1` AS SELECT `mylibrary`.`titles`.`titleID`, `mylibrary`.`titles`.`title`, `mylibrary`.`titles`.`subtitle` FROM `mylibrary`.`titles` ORDER BY `mylibrary`.`titles`.`title`, `mylibrary`.`titles`.`subtitle`

View löschen

SHOW TABLES liefert eine Liste aller Tabellen und Views. Zum Löschen von Views können Sie allerdings nicht DROP TABLE einsetzen, sondern müssen das Kom-mando DROP VIEW viewname aufrufen.

8.10 Beispieldatenbank mylibrary (Bücherverwaltung) Im Verlauf dieses Kapitels habe ich am Beispiel der mylibrary-Datenbank verschie-dene Aspekte des Datenbankdesigns präsentiert. Aus diesem Grund hat sich die Beschreibung der mylibrary-Datenbank so in die Länge gezogen, dass Sie mögli-cherweise den Überblick verloren haben, welche Tabellen, Felder und Indizes es nun tatsächlich gibt, welche Datentypen eingesetzt wurden etc. Daher gibt dieser Abschnitt eine abschließende Zusammenfassung aller Eigenschaften der mylibrary-Datenbank. Die Summe dieser Eigenschaften wird üblicherweise auch als Daten-bankschema bezeichnet.

Abbildung 8.15 zeigt eine grafische Kurzfassung des Schemas. Aus der Abbildung gehen zwar weder die Datentypen der Spalten noch alle Indizes hervor, aber im-merhin sieht man gut, welche Spalten als Primärindex verwendet werden und wie die Tabellen miteinander in Verbindung stehen. (Die Abbildung wurde übrigens wie alle anderen Schema-Abbildungen in diesem Buch mit dem Abfrage-Designer von OpenOffice erstellt.)

8 Datenbankdesign


Abbildung 8.15: Das Schema der Bücherdatenbank mylibrary

Hinweis

Eine *.sql-Datei mit der vollständigen Definition der mylibrary-Datenbank inklusive einiger Testdatensätze finden Sie auf der bei-liegenden CD-ROM. Um diese Datei einzulesen, erzeugen Sie mit phpMyAdmin eine leere Datenbank mit Latin1 als Defaultzeichen-satz, wechseln dann auf die Seite SQL und laden die *.sql-Datei. Alternativ können Sie die Datenbank durch die beiden folgenden Kommandos erzeugen:

> mysqladmin -u root -p create mylibrary > mysql -u root -p mylibrary < mylibrary.sql

Datenbankeigenschaften

Für die Datenbank mylibrary und alle darin enthaltenen Textfelder gelten per De-fault der Zeichensatz latin1 und die Sortierordnung latin1_german1_ci.

Tabelleneigenschaften

Alle Tabellen sind InnoDB-Tabellen. Die folgenden Tabellen geben zu jeder mylib-rary-Tabelle alle Felder, deren Datentypen, Attribute und Indizes an. Die Bedeu-tung der meisten Spalten bedarf keiner weiteren Erklärung. Bemerkenswert und bisher unerwähnt geblieben ist aber die ts-Spalte, die es in allen Tabellen gibt. In dieser Spalte wird automatisch der Zeitpunkt der letzten Änderung protokolliert.

8.10 Beispieldatenbank mylibrary (Bücherverwaltung)


Diese Spalte ist erforderlich, damit die Datenbank auch von ODBC/ADO-Pro-grammen genutzt werden kann. Feld Datentyp Attribute Index, Foreign-Key-Regeln

authID INT NOT NULL AUTO_INCREMENT

PRIMARY KEY

authName VARCHAR(60) KEY ts TIMESTAMP

Tabelle 8.13: Eigenschaften der authors-Tabelle

Feld Datentyp Attribute Index, Foreign-Key-Regeln

catID INT NOT NULL AUTO_INCREMENT

PRIMARY KEY

catName VARCHAR(60) NOT NULL KEY parentCatID INT KEY,

FOREIGN KEY categories.catID ts TIMESTAMP

Tabelle 8.14: Eigenschaften der categories-Tabelle


langID INT NOT NULL AUTO_INCREMENT

PRIMARY KEY

langName VARCHAR(60) NOT NULL KEY ts TIMESTAMP

Tabelle 8.15: Eigenschaften der languages-Tabelle


publID INT NOT NULL AUTO_INCREMENT

PRIMARY KEY

publName VARCHAR(60) NOT NULL KEY ts TIMESTAMP

Tabelle 8.16: Eigenschaften der publishers-Tabelle


authID INT NOT NULL PRIMARY KEY, FOREIGN KEY authors.authID

titleID INT NOT NULL PRIMARY KEY, KEY, FOREIGN KEY titles.titleID

ts TIMESTAMP

Tabelle 8.17: Eigenschaften der rel_title_author-Tabelle

8 Datenbankdesign



titleID INT NOT NULL AUTO_INCREMENT

PRIMARY KEY

title VARCHAR(100) NOT NULL KEY subtitle VARCHAR(100) edition TINYINT publID INT KEY,

FOREIGN KEY publishers.publID

catID INT KEY, FOREIGN KEY categories.catID

langID INT KEY, FOREIGN KEY languages.langID

year INT isbn VARCHAR(20) comment VARCHAR(255) ts TIMESTAMP

Tabelle 8.18: Eigenschaften der titles-Tabelle

8.11 Beispieldatenbank myforum (Diskussionsforum) Die meisten Beispiele dieses Buchs verwenden die im bisherigen Verlauf des Kapitels behandelte Datenbank mylibrary als Grundlage. Da sich aber nicht alle SQL-Konzepte an einer einzigen (noch dazu recht kleinen) Datenbank demonstrie-ren lassen, werden in diesem und dem nächsten Abschnitt zwei weitere Daten-banken kurz vorgestellt, myforum und exceptions:

myforum ist eine Datenbank für eine reale PHP-Anwendungen, deren Code aus Platzgründen aber nicht in diesem Buch erläutert wird. Vielmehr geht es hier nur um das Design (Schema) dieser Datenbank. Falls Sie sich auch für den PHP-Code interessieren, muss ich Sie auf das ebenfalls bei Addison-Wesley erschienene Buch PHP 5 & MySQL 5 verweisen. Die Datenbank enthält mehrere 1000 Datensätze und bildet damit eine gute Basis, um die Volltextsuche von MySQL auszuprobieren (siehe Abschnitt 10.13).

Hinter exceptions steht keine Anwendung. Die Datenbank dient ausschließlich da-zu, den Umgang mit besonderen MySQL-Datentypen auszuprobieren bzw. um verschiedene Sortierordnungen zu testen.

8.11 Beispieldatenbank myforum (Diskussionsforum)


Datenbank für ein Diskussionsforum

Zu den beliebtesten MySQL-Anwendungen zählen Gästebücher, Diskussionsforen oder andere Webseiten, die den Benutzern die Möglichkeit bieten, selbst einen Text zu verfassen und so die Webseite um eigene Informationen zu ergänzen oder zu kommentieren. Die Datenbank myforum zeigt, wie das Datenbankfundament für ein Diskussionsforum aussehen kann. Die Datenbank besteht aus drei Tabellen:

■ forums enthält eine Liste mit den Namen aller Diskussionsforen. Außerdem kann bei jedem Forum die gewünschte Diskussionssprache angegeben werden (Englisch oder Deutsch).

■ users enthält eine Liste aller in der Datenbank registrierten Benutzer, die selbst Beiträge verfassen dürfen. Zu jedem Benutzer werden der Loginname, das Passwort und die E-Mail-Adresse gespeichert. Die Spalte authent enthält eine Zufallszeichenkette, die beim Anlegen eines neuen Benutzers per E-Mail ver-sandt wird. Erst wenn der Benutzer den in dieser E-Mail enthaltenen Link an-klickt, wird der Account aktiviert (Spalte active).

■ messages enthält schließlich sämtliche gespeicherten Beiträge. Diese bestehen aus einer Subject-Zeile, dem eigentlichen Nachrichtentext, forumID- und userID-Nummern sowie einigen zusätzlichen Verwaltungsinformationen. Vom Stand-punkt des Datenbankdesigns ist diese Tabelle am interessantesten (siehe unten).

Als Tabellenformat wurde MyISAM gewählt, weil momentan nur dieses Format eine Volltextsuche unterstützt. Alle Spalten mit Zeichenketten verwenden den Latin1-Zeichensatz und die Sortierordnung latin1_german1_ci. Das Schema der Datenbank ist in Abbildung 8.16 zusammengefasst.

Abbildung 8.16: Das myforum-Datenbankschema

Die auf der beiliegenden CD befindliche myforum-Beispieldatenbank enthält mehr als 3000 Forumsbeiträge, die aus dem Forum meiner Website www.kofler.cc stam-men. (Die Beiträge wurden für das Buch anonymisiert.) Die Datenbank eignet sich damit sehr gut, um verschiedene Aspekte der Volltextsuche auszuprobieren (siehe auch Abschnitt 10.13).

8 Datenbankdesign


Hinweis

Die messages-Tabelle ist ein gutes Beispiel dafür, dass der Platzbedarf für einen Volltextindex erheblich sein kann: Bei der Testdatenbank enthält messages ca. 3000 Nachrichten mit insgesamt ca. 1,5 Millionen Zeichen. Zur Speicherung der messages-Daten sind ca. 1,7 MByte erforderlich. Der Platzbedarf für den Volltextindex beträgt allerdings nochmals 1,4 MByte, wodurch sich der Platzbedarf für die gesamte Tabelle beinahe verdoppelt!

Hierarchien zwischen Nachrichten

Wie schon bei der mylibrary-Datenbank gibt es auch in myforum einen Kampf mit den Hierarchien. Ein wesentliches Merkmal von Diskussionsforen besteht darin, dass der Diskussionsfaden (im Fachjargon: Thread) in einer hierarchischen Liste dargestellt wird. Die Hierarchie resultiert daraus, dass zu jedem Beitrag eine Reaktion möglich ist – eben im Sinne einer Diskussion. Eine Diskussion der fünf Benutzer A, B, C, D und E könnte wie in Abbildung 8.17 aussehen:

A: Wie kann ich mit MySQL Tabellen sortieren? (17.1.2005 12:00) ├─ B: erste Antwort (17.1.2005 18:30)

│ ├─ A: danke (17.1.2005 19:45) │ └─ C: besserer Vorschlag (19.1.2005 10:30)

├─ D: zweite Antwort (18.1.2005 3:45) │ └─ A: das verstehe ich nicht (18.1.2005 9:45) │ └─ D: die gleiche Erklärung, diesmal ausführlicher (18.1.2005 22:05)

└─ E: dritte Antwort (18.1.2005 19:00)

Abbildung 8.17: Diskussionsfaden

Hier wurde in die Titelzeile (subject) jedes Beitrags zur Illustration gleich eine Inhaltsbeschreibung eingefügt. In der Realität enthält die Titelzeile zumeist nur den Thread-Titel.

Eine datenbankmäßige Darstellung dieser Diskussion könnte wie in Abbildung 8.18 aussehen. Dabei wird angenommen, dass die fünf Benutzer A bis E die userID-Nummern 201 bis 205 aufweisen. Die Nachrichten des Threads haben msgID-Num-mern erhalten, die mit 301 beginnen. (In der Praxis ist natürlich nicht zu erwarten, dass ein Thread durchlaufende msgID-Nummern aufweist. Vielmehr werden ande-re Threads die Reihenfolge durchbrechen.) Die Tabelle ist chronologisch sortiert (also in der Reihenfolge, in der die Nachrichten geschrieben wurden).

8.12 Beispieldatenbank exceptions (Sonderfälle)


msgIDmsgIDmsgIDmsgID forumIDforumIDforumIDforumID rootIDrootIDrootIDrootID parentIDparentIDparentIDparentID userIDuserIDuserIDuserID subjectsubjectsubjectsubject tstststs

301 3 301 NULL 201 Wie kann ... 2005-01-17 12:00 302 3 301 301 202 erste Antwort 2005-01-17 18:30 303 3 301 302 201 danke 2005-01-17 19:45 304 3 301 301 204 zweite Antwort 2005-01-18 3:45 305 3 301 304 201 das verstehe ... 2005-01-18 9:45 306 3 301 301 205 dritte Antwort 2005-01-18 19:00 307 3 301 304 204 die gleiche ... 2005-01-18 22:05 308 3 301 302 203 besserer Vorschlag ... 2005-01-19 10:30

Abbildung 8.18: messages-Tabelle mit den Datensätzen eines Diskussionsfadens

Die Hierarchie wird durch die Spalte parentID ausgedrückt, die auf die jeweils übergeordnete Nachricht verweist. Wenn parentID den Wert NULL enthält, handelt es sich um einen Beitrag, der einen neuen Diskussionsfaden (Thread) begründet.

rootID verweist auf die erste Nachricht eines Threads. Die Spalte darf nicht NULL enthalten. Bei der ersten Nachricht eines Threads gilt msgID=rootID. rootID enthält eigentlich redundante Informationen, weil über parentID für jede Nachricht festge-stellt werden kann, welches die Startnachricht ist. rootID erleichtert diese Aufgabe aber wesentlich und erhöht die Effizienz des ganzen Forums ganz erheblich (etwa wenn es darum geht, die Anzahl der Nachrichten eines Threads zu ermitteln oder alle Nachrichten eines Threads mit einer einfachen SELECT-Abfrage zu erfassen).

8.12 Beispieldatenbank exceptions (Sonderfälle) Wenn Sie beginnen, eine Anwendung mit einer Ihnen noch unbekannten neuen Datenbank oder Programmiersprache oder API zu entwickeln, ist oft eine einfache Testdatenbank praktisch, mit der diverse Sonderfälle rasch ausgetestet werden können: Für die Arbeit an diesem Buch – sowohl zum Testen der diversen APIs als auch für Import- und Export-Tests – kam dazu die Datenbank exceptions zum Einsatz. Die Tabellen dieser Datenbank enthalten unter anderem:

■ Spalten mit beinahe allen von MySQL unterstützten Datentypen, inklusive xxx-TEXT, xxxBLOB, SET und ENUM

■ NULL-Werte

■ Texte und BLOBs mit allen möglichen Sonderzeichen

■ Alle 255 möglichen Textzeichen (Code 1 bis 255)

Die folgenden Absätze geben einen Überblick über die Tabellen und deren Inhalt. Die Spaltennamen geben gleich den Datentyp an (die Spalte a_blob hat also den Datentyp BLOB). Die id-Spalte ist eine AUTO_INCREMENT-Spalte (Typ INT). Bei allen Spalten außer der id-Spalte ist der Wert NULL zulässig.

8 Datenbankdesign


testall

Diese Tabelle enthält Spalten mit den wichtigsten MySQL-Datentypen (wenn auch nicht mit allen).

Spalten: id, a_char, a_text, a_blob, a_date, a_time, a_timestamp, a_float, a_decimal, a_enum, a_set

test_text

Mit dieser Tabelle können Sie den Umgang mit Texten testen.

Spalten: id, a_varchar (maximal 100 Zeichen), a_text, a_tinytext, a_longtext

test_blob

Mit dieser Tabelle können Sie den Umgang mit Binärdaten testen (Import, Export, in Client-Programm lesen und wieder speichern etc.).

Spalten: id, a_blob

Inhalt: Ein Datensatz (id=1) mit einem 512 Byte langen Binärblock. Die binären Daten weisen Byte für Byte die Codes 0, 1, 2, ..., 255, 0, 1, ..., 255 auf.

test_date

Mit dieser Tabelle können Sie den Umgang mit Daten und Zeiten testen.

Spalten: id, a_date, a_time, a_datetime, a_timestamp

Inhalt: Ein Datensatz (id=1) mit den Werten 2005-12-31, 23:59:59 sowie zweimal 2005-12-31 23:59:59.

test_enum

Mit dieser Tabelle können Sie den Umgang mit SETs und ENUMs testen.

Spalten: id, a_enum und a_set (jeweils mit den Zeichenketten 'a','b','c','d','e')

Inhalt: id a_enum a_set

1 a a 2 e b,c,d 3 4 NULL NULL

8.12 Beispieldatenbank exceptions (Sonderfälle)


test_null

Mit dieser Tabelle können Sie testen, ob Sie NULL (erster Datensatz) von einer leeren Zeichenkette (zweiter Datensatz) unterscheiden können.

Spalten: id, a_text

Inhalt: id a_text

1 NULL 2 3 'a text'

test_sort1

Mit dieser Tabelle können Sie verschiedene Sortierordnungen (ORDER BY) sowohl für den Latin1- als auch für den UTF-8-Zeichensatz (Unicode) ausprobieren. Die Tabelle besteht aus drei Spalten: id enthält durchlaufende Zahlen von 33 bis 126 sowie von 161 bis 255. latin1char enthält das Latin1-Zeichen für den aus id hervor-gehenden Code. utf8char enthält das dazugehörende Unicode-Zeichen. Die beiden CHAR-Spalten wurden bewusst ohne Index ausgestattet.

Inhalt: id latin1char ut8char

... 65 'A' 'A' 66 'B' 'B'

...

test_sort2

Auch diese Tabelle dient zum Ausprobieren von Sortierordnungen. Die Tabelle enthält wiederum drei Spalten, id, latin1text und utf8text (jeweils VARCHAR(100) im Zeichensatz Latin1 bzw. UTF8). Die Textspalten enthalten diesmal aber ganze Wörter, nämlich abc, Abc, ABC, Bar, Bär, Bären, Barenboim, bärtig und Ärger. Bei-spiele für die Anwendung der Tabellen test_sort1 und test_sort2 finden Sie in Ab-schnitt 9.3.

importtable1, importtable2, exporttable

Die drei Tabellen enthaltenen Testdaten für den Import bzw. Export von Text-dateien. Eine Beschreibung der Tabellen sowie der zahlreichen Import- und Ex-port-Möglichkeiten von MySQL finden Sie in Abschnitt 14.5.

Documents

MySQL 5 - *ISBN 3-8273-2253-7* - © 2005 by ... · 8 Datenbankdesign Am Beginn jeder Datenbankanwendung steht das Design der Datenbank. Es hat großen Einfluss darauf,

MySQL 5 - ISBN 3-8273-2253-7 - © 2005 by ... · 8 Datenbankdesign Am Beginn jeder Datenbankanwendung steht das Design der Datenbank. Es hat großen Einfluss darauf,