Datenbankdesign

1. Normalisierung

1.1 Primary Key

Um einen Datensatz in einer Tabelle eindeutig identifizieren zu können, ist ein eindeutiger Bezeichner notwendig, der in der ganzen Tabelle eindeutig ist. Hierfür werden so genannte Primary Keys oder auch Primärschlüssel genannt, eingesetzt. Diese bestehen zumeist aus dem Datentyp integer oder im SQL-Server auch durch das synonym int gekennzeichnet. Um die Einzigartigkeit des Wertes in dieser Spalte zu gewährleisten, sollte diese Spalte eine Identity Spalte sein und möglichst ein Schrittwert von 1 haben d.h. das bei jedem hinzufügen eines neuen Datensatzes automatisch der Wert für diese Spalte generiert wird und dabei jeweils immer um 1 erhöht wird.

Daraus ergeben sich folgende Vorteile:
Durch einen Bezeichner, der in keiner Beziehung zu den Daten steht, können diese Daten mit beliebigen, in anderen Tabellen vorhandenen Daten verknüpft werden und stellen so die Datenintegrität mit z.B. Detaildatensätzen in anderen Tabellen sicher. Ein Beispiel hierfür finden Sie am Ende des Normalisierungsartikels.
Fehler, wie z.B. dass 2 User mit dem gleichem Vor- und Zunamen nicht von einander unterschieden werden können, fällt ebenfalls weg da jedem Datensatz automatisch durch die Identity Eigenschaft eine eindeutiger Bezeichner verliehen wurde.

1.2 Datenbankdesign Normalisierung

(!) Bitte beachten, zum Verständnis für diesen Absatz ist der Punkt 1.1 Vorraussetzung.

Vorteile:
Änderungen an Detaildaten sind leichter durchzuführen. 
Geringere Datenbankgröße durch Ausschluß von redundanten Daten.
Datenstruktur ist übersichtlicher.
Abfragen sind bei überlegter, also nicht übertriebener Normalisierung schneller.

Normalisierung bedeutet soviel wie, redundante Daten als Detailtabelle auszulagern. Haben Sie z.B. eine Benutzerdatenbank, in der Benutzerdaten und Informationen wie Name der Abteilung und dessen Vorgesetzter, so wären für eine Änderung des Vorgesetzten in dieser Abteilung eine sehr große und vielleicht Fehlerbehaftete Änderung notwendig.

Es müßte jede Spalte geändert werden, worin die entsprechende Abteilung vorkommt in unserem Beispiel der 1. und 3. Datensatz. Außerdem werden Daten unnötig redundant, wenn mehrere Benutzer der gleichen Abteilung angehören (wie hier die ID 1 und 3) und mehrere Personen den gleichen Vorgesetzten haben.

Hier kommt die Normalisierung ins Spiel. Aus diesem Beispiel könnten z.B. 3 Tabellen gemacht werden wie 1: Benutzer / 2: Abteilungen / 3: Vorgesetzte. Ideal wären aber 2 (Benutzer und Abteilungen), da der Vorgesetzte immerhin auch ein Benutzer sein sollte und die "Abteilungen" Tabelle wiederum den vorgesetzten in der Benutzer Tabelle referenzieren sollte.

Das folgende Beispiel zeigt das Beispiel einer erfolgreichen Normalisierung.

Benutzer Tabelle:

Abteilungen Tabelle:

Verknüpft werden diese Daten über den Primary Key. Im obigen Beispiel wäre dies die Spalte AbteilungsId. Diese Spalte in der Benutzer Tabelle Identifiziert den entsprechenden Datensatz in der Abteilungen Tabelle. Abrufen können Sie diese Daten z.B. mit folgender Syntax:

SELECT b1.Vorname, b1.Nachname, a1.Abteilung,b2.Vorname,b2.Nachname 
FROM Benutzer b1
LEFT JOIN Abteilungen a1 ON a1.AbteilungsId = b1.AbteilungsId
LEFT JOIN Benutzer b2 ON b2.VorgesetzterId = b1.BenutzerId

Mit dieser Abfrage würde das gleiche Ergebnis erscheinen, wie vor der Normalisierung der Benutzer Tabelle und einer "SELECT * FROM Benutzer" Abfrage, aber mit erheblich effizienteren Datenbankdesign im Hintergrund. Um eine Abwärtskompatibilität zu erreichen, wäre auch das erstellen eines View´s bzw. Sicht im SQL-Server möglich. Dabei verhält sich diese Abfrage wie eine richtige Tabelle und sie können auch entsprechend damit arbeiten.

Immer über PKs referenzieren
Hier kommt auch der Vorteil eines PK´s zum Vorschein. Angenommen, ich hätte nur eine separate "Vorgesetzten" Tabelle angelegt und die Vorgesetzten Datensätze über die Spalte "Abteilung" vom Datentyp "nvarchar" in der alten "Benutzer" Tabelle referenziert, so würde eine Namensänderung der Abteilung dafür sorgen, das die Detaildatensätze nicht mehr gefunden werden können. Daher bei der Normalisierung möglichst immer über ein eindeutigen PK referenzieren. Nur so bleibt die Datenintegrität erhalten.

# Die Namen entstammen der PUBS Datenbank.

2. Sonstige Tuning/Optimierungsmöglichkeiten

2.1 Indexes

Was ist ein Index?
Stellen Sie sich Ihre Datenbank wie ein Buch ohne Inhaltsangabe oder besser, ohne Stichwortverzeichnis vor. Nun suchen Sie Informationen nur zu einem kleinem Themengebiet. In diesem Fall müssen Sie ggf. das ganze Buch lesen, um an die gewünschte Teilinformation zu kommen was sehr zeit aufwendig wäre. Ein Index ist, wie die deutsche Übersetzung schon sagt, ein Inhaltsverzeichnis oder Stichwortverzeichnis Ihrer Datenbank und beschleunigt die Suche nach Informationen z.b. bei einem SELECT erheblich, vorrausgesetzt der Index ist wohlüberlegt angelegt. Es ist so, als wenn Sie nach dem aufschlagen des Buches das Inhaltsverzeichnis nach dem gewünschten Thema durchsuchen, und zur entsprechenden Seite gehen, so ähnlich funktioniert ein Index. Ein Negativbeispiel wäre das anlegen eines Indexes auf jede Spalte Ihrer Tabelle. Wenn das Inhaltsverzeichnis eines Buches genauso groß wäre, wie das Buch selbst, wäre der Geschwindigkeitsvorteil gering bis gar nicht vorhanden. Durch den Speicherplatzverbrauch, der durch den großen Index in Anspruch genommen wird, hätte man eher ein Problem, falls die verfügbare Kapazität begrenzt ist, wie im Sharedhosting üblich. Sollten Sie eine MSDE verwenden, ist hier der Platz der Datenbank auf 2 GB beschränkt, daher sollte die Überlegung, einen Index zu verwenden auch auf einem dedizierten Server vorgenommen werden.

Anlegen können Sie einen Index mit folgender SQL-Syntax:
CREATE [UNIQUE] [CLUSTERED | UNCLUSTERED] INDEX Indexname
ON Tabellenname (Spaltenname [ASC | DESC])

Weitere Informationen zu den Parametern und Syntax finden sie im MSDN unter http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp 

Vorteil:
Bei effizienter Bestimmung der Indexspalten erhöhte Geschwindigkeit bei SELECT Abfragen.
Nachteil:
INSERT und DELETE Querys können geringfügig langsamer sein, da bei jedem dieser Vorgänge ein Indexeintrag angelegt werden muß. Es empfiehlt sich daher, ein Index dort zu verwenden, wo viele SELECT Abfragen stattfinden. 
Wenn auf jeder Spalte ein Index liegt, bringt ein Index kein Geschwindigkeitsvorteil.


2.2. effiziente Datentypenwahl

Der richtige Datentyp hängt in erster Linie von den enthaltenden Daten ab. Hier wird oft nach dem Muster vorgegangen, je universeller, desto besser. Dies ist absolut falsch! Man sollte den Datentyp so gut wie möglich einschränken d.h. für ein Feld wie z.B. die Höhe eines Warenbestandes eines Onlineshops, wäre ein ntext oder varchar Feldtyp absolut unpassend. Da hier im Normalfall nur Zahlen im 4stelligen Bereich zu erwarten sind, wäre der Datentyp smallint die beste Wahl. Der Speicherverbrauch wäre hier nur 2 Byte groß im Vergleich zu varchar mit 4 Byte. Bei einem Datenbestand von z.B. 500.000 Datensätze würde der Speicherplatzgewinn fast einen Megabyte betragen, nur bei diesem Feld im Vergleich zu varchar wohl gemerkt. Bei dem LOB Feld ntext wäre der Gewinn natürlich noch höher. 

Aber die Datentypenwahl hat nicht nur Auswirkung auf die Größe der Datenbank, sondern hat auch Auswirkungen auf die Geschwindigkeit. Logisch kann der Server mit einer kleineren Datenmange besser und schneller arbeiten und es müssen nicht ständig Daten konvertiert werden, wenn Berechnungen durchgeführt werden müssen. 

In folgender Tabelle sind noch mal alle Sql-Server Zahlen Datentypen und deren maximum Dimensionen sowie Speicherbelegung aufgelistet. Die Größe der Textdatentypen wie nvarchar zum Beispiel entspricht immer der Anzahl Ihrer Zeichen. Ein Feld mit 4 Zeichen ist im Speicher auch 4 Byte groß.

2.3 Vorteile von Stored Procedures
Einführende Infos: StoredProcedure Grundlagen und Syntax

Angenommen, sie haben einen Onlineshop welcher Bestellungen entgegen nimmt. Bei der Bestellung soll auch der Lagerbestand überprüft werden. In der klassischen Methode würde am Anfang ein SQL Query an die Datenbank geschickt werden, der den Lagerbestand überprüft und ggf. die Anzahl der im Lager befindlichen Artikel abfragt. Anschließend müßte, wenn der Lagerbestand sich mit der Bestellten Menge decken sollte, die Bestellung per INSERT in der Datenbank eingefügt werden. Möglich währen hier noch weitere Schritte wie z.B. Änderung per UPDATE des Lagerbestandes usw...

In diesem Beispiel würden also min. 2 SQL Querys an die Datenbank geschickt werden und min. einem Resultset wäre notwendig oder Sie packen die Abläufe der Bestellung in eine Stored Procedure. Es wird also nur ein Query an die Datenbank geschickt. Der Lagerbestand wird von der Stored Procedure selbst abgefragt und verarbeitet und nicht unnötig noch mal zum aufrufenden Script zurückgeschickt und dort von Ihrem ASP oder ASP.net Skript verarbeitet. Der dadurch reduzierte Datentraffic zwischen dem Datenbankserver und Ihrem Webspace würde erheblich sinken und die Geschwindigkeit steigen. Dazu kommt noch, das Operation ausgelagert werden, die vorher durch Skripte auf Ihrem Webspace ausgeführt werden müßten, was wiederum zu einem schnelleren Bearbeitung und übersichtlicheren Code führen kann.

Weitere Informationen zu den Parametern und Syntax rund um Stored Procedures finden sie im MSDN unter http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_4hk5.asp