template

Index :: PHP/MySQL :: DB-Struktur/Tabellenplanung

Der Entwurf einer Datenbank ist selten trivial, auch wenn es oft auf den ersten Blick so scheint. Zwar mögen die anfänglichen Entwürfe zufriedenstellend sein, aber in der Praxisphase tauchen dann schnell unerwartete Schwierigkeiten auf; sei es, weil das Grundkonzept einen Denkfehler enthält, sei es, weil sich die Aufgabenstellung ändert und Teile der Datenbank umgeschrieben werden müssen, oder sei es, weil die im Laufe der Zeit gewünschten Erweiterungen mangels flexibler Strukturen nur schwer zu implementieren sind.
Natürlich sind Modifikationen (inklusive Datenkonvertierung) im Nachhinein jederzeit möglich - und faktisch oft gar nicht vorherzusehen bzw. zu vermeiden - aber bei geschickter Planung und der Befolgung gewisser Spielregeln lässt sich dieser Aufwand in Grenzen halten.

Philosophie
Objekte
Verbindungen

Philosophie

Im Grunde besteht die Aufgabe darin, ein reales System auf ein Tabellensystem abzubilden. Wie in der Realität, so existieren auch in der Philosophie der Datenbank zwei essentielle Termini: Objekt und Verbindung.

Während frühe Konzepte wie dbase ausschließlich auf Objekte bezogen waren (Elemente in isolierten Listen - historisch bedingt durch die 1:1-Übernahme von auf Papier geschriebenen Listen), erfahren bei modernen Datenbanken die Begriffe Objekt und Verbindung gleichrangige Behandlung (relationale Tabellen mithilfe von IDs/Verweisen - Bereichskapselung und Minimierung von Redundanzen).

Rein intuitiv betrachtet sind Objekte so etwas wie (be)greifbare Dinge, wie sie uns im Alltag begegnen. Und sie tauchen in nahezu jedem Projekt auf, z.B. Shop-Artikel, Kunden, Fahrzeuge, CDs oder Log-Einträge. Jedes Objekt besitzt zudem eine Reihe von meist kompakten Eigenschaften, die in entsprechenden Feldern gespeichert werden.

Dieses traditionelle Listenprinzip ist in folgenden Fällen suboptimal, bzw. stößt gar an seine Grenzen:

Situation	Beispiel	Folge
Redundanz Eine Eigenschaft enthält in vielen Datensätzen den selben Wert.	In einer Dokument-Liste steht im Feld Pfad bei allen Datensätzen '/public/applications/abc/release/documents/'.	Sollte sich der Pfad irgendwann ändern, müssen alle betroffenen Datensätze aktualisiert werden. Im schlimmsten Fall steht kein Automatismus zur Verfügung, und die Bearbeitung erfolgt im Fließbandverfahren manuell: neben Tippfehlern besteht die Gefahr, den ein oder anderen Datensatz zu übersehen. Selbst bei einer Automatisierung (UPDATE ... SET Pfad=... WHERE Pfad=...) werden manche Datensätze evtl. nicht erfasst; nämlich jene, die bereits manuell bearbeitet wurden und dadurch nicht mehr dem Muster entsprechen.
Aggregation Eine einzelne Eigenschaft ist nicht kompakt, sondern besteht ihrerseits aus einer Gruppe von Eigenschaften (technisch gesehen einem Unterobjekt).	In einem Grundstück-Datensatz sind die Kontaktdaten von Makler, Eigentümer und Vorbesitzer enthalten.	Es werden unverhältnismäßig viele Felder benötigt, die das Ganze unübersichtlich und schwer pflegbar machen.
Dynamik Objekte eines Typs sind nicht konform, d.h. die Anzahl der Felder unterscheidet sich, oder die Feldanzahl ist grundsätzlich dynamisch.	In einem CD-Datensatz werden Felder für die einzelnen Track-Titel angelegt. Oder: In einem Adress-Datensatz werden zwei Felder für Telefonnummern angelegt.	Wenn die Feldanzahl nicht genau definierbar ist, werden i.d.R. Reservefelder angelegt, deren Anzahl sich an der geschätzten zu erwartenden Wertemenge orientiert - meist ein Erfahrungswert. Erweist sich diese Anzahl irgendwann als zu niedrig, muss die Tabelle erweitert werden. Abgesehen davon, dass diese Reservefelder häufig ungenutzt bleiben, werden Queries umständlicher, wenn z.B. die belegten Felder ermittelt oder gar sortiert werden sollen.

Selbstverständlich können diese Situationen auch ohne den Einsatz von Verbindungen/Verweisen geregelt werden, aber allein schon das Auftreten von Redundanzen (identische Informationen, die mehrfach an verschiedenen Stellen gespeichert sind) erfordert oft zusätzliche Prüfungen und Kontrollen - sofern dies überhaupt machbar ist, damit die Konsistenz der Datenbank gewahrt bleibt.
Aufgrund mangelnder Flexibilität der starren Listenstrukturen müssen Tabellen und der jeweilige Skriptcode evtl. immer wieder einmal angepasst/bearbeitet werden. Hier zählt dann nicht, ob eine Sache funktioniert, sondern vielmehr, ob sie gut pflegbar und erweiterbar ist.

Objekte

Der erste Schritt ist wohl immer die Klärung der Frage: Welches sind die Objekte?

Denn erst, wenn ihre Definitionen vorliegen, kann man beginnen, pro Objekttyp eine Tabelle mit entsprechenden Feldern anzulegen. Diese Tabellen werden dann meist mit Dummy-Daten oder importierten Daten gefüllt, um in einer Testphase grundlegende Skripte und Abfragen zu entwerfen. Verständlicherweise streben es viele Entwickler an, so schnell wie möglich etwas auf dem Bildschirm zu sehen: ein optisches Feedback, welches ihnen bestätigt, dass es mit der Arbeit vorangeht. Meist wird parallel auch schon am Design gefeilt, damit auch dem Kunden etwas präsentiert werden kann.

Tatsache ist, dass diese oft nur zu Testzwecken konzipierte Planung dann den Unterbau für nachfolgende Entwicklungen darstellt. Und solange keine tiefgreifenden Anpassungen notwendig sind, mag das ursprüngliche Konzept den Projektanforderungen genügen. Meist stellt sich erst später - evtl. in einer frühen Release-Version, die vom Kunden benutzt wird - heraus, dass die ein oder andere Funktionalität noch einmal überdacht werden muss. I.d.R. handelt es sich um die Optimierung von Arbeitsabläufen oder um ein Feature, an das der Kunde bisher nicht gedacht hatte.

Manchmal können solche Probleme unter Zuhilfenahme zusätzlicher Felder (oder auch Tabellen) gelöst werden, manchmal jedoch verändern Objekte ihre Rolle im Gesamtzusammenhang. Vielleicht hat der Entwickler dies sogar vorausgesehen, konnte aber aufgrund strikter Kundenvorgaben oder Termindruck keine diesbezüglichen Vorkehrungen treffen.

Auch wenn (gemäß traditioneller Richtlinien bzw. Projektvorgaben seitens Auftraggeber) eine direkte Adaption realer Objekte naheliegend ist - datenbanktechnisch kann eine weitere Gliederung sinnvoll und notwendig sein.

Beispiel: Administratoren lassen sich zunächst in einer Tabelle admin verwalten: mit Name, Abteilung, Berechtigungsflags u.s.w.:

admin
	Feldname	Typ
0	AdminID	int	[AUTOINCR] ID
1	Vname	varchar[24]	Vorname
2	Nname	varchar[24]	Nachname
3	Abtlg	varchar[32]	Arbeitsbereich
4	Tel	varchar[16]	Telefon
5	Mail	varchar[48]	EMail-Adresse
6	Rights	int	Rechte-Flags

Jetzt kommen noch Tabellen für normale Mitarbeiter (employee) und Kunden (customer) dazu:

employee
	Feldname	Typ
0	EmployID	int	[AUTOINCR] ID
1	Vname	varchar[24]	Vorname
2	Nname	varchar[24]	Nachname
3	Abtlg	varchar[32]	Arbeitsbereich
4	Tel	varchar[16]	Telefon
5	Mail	varchar[48]	EMail-Adresse

customer
	Feldname	Typ
0	CustomID	int	[AUTOINCR] ID
1	CustNr	varchar[12]	Kundennummer
2	Vname	varchar[24]	Vorname
3	Nname	varchar[24]	Nachname
4	PAdr	varchar[80]	Postadresse
5	Tel	varchar[16]	Telefon
6	Mail	varchar[48]	EMail-Adresse

Dieses augenscheinlich klare und übersichtliche Konzept birgt aber schon jetzt einige Nachteile:

Da die Administratoren gleichzeitig Mitarbeiter (der Firma) sind, müssten deren Felder eigentlich doppelt gepflegt werden: sowohl in admin als auch in employee - eine unnötige Redundanz.
Wenn allerdings Admins weiterhin separat verwaltet werden, ist es schwierig, eine sortierte Liste aller Mitarbeiter (incl. Admins) zu generieren.
In beiden Fällen bedeutet die Ernennung eines normalen Mitarbeiters zum Administrator (oder auch der umgekehrte Weg, die Degradierung), dass Daten kopiert/verschoben werden müssen. Optimal wäre es, einfach nur ein Admin-Häkchen zu setzen/entfernen.
Falls irgendwann ein zusätzliches Feld, z.B. für Faxnummer, erwünscht ist, müssen 3 Tabellen (und natürlich die jeweils dazugehörigen Skripte, Formulare und Queries) erweitert werden.

Nach kurzer Überlegung wird klar, dass wir unsere Objekte neu definieren müssen: unter dem Gesichtspunkt, dass es sich um Menschen (der gemeinsame Nenner) handelt, liegt es nahe, als Basisobjekt so etwas wie eine Visitenkarte zu verwenden. Damit könnten dann alle Personen zentral erfasst und verwaltet werden.

Verbindungen

Beziehungen zwischen Objekten werden mithilfe von Verweisen realisiert. Damit solch ein Verweis eindeutig ist, braucht das Zielobjekt eine ID, normalerweise ein

AUTOINCREMENT-Primärschlüssel.

Prinzipiell unterscheidet man 3 Typen von Verweissystemen:

1:1 (Auslagerung)

Diese einfache Art des Verweises dient der besseren - meist thematischen - Strukturierung von Datenblöcken.
Dadurch lassen sich Teilinformationen individueller verwalten, und der Umfang diesbezüglicher Skripte/Queries wird minimiert.

1:N (Hierarchie)

Die Hierarchie oder Baumstruktur ist eine erweiterte Form der Auslagerung, bei der mehrere Kindobjekte auf ein und dasselbe Elternobjekt verweisen können.
Je nachdem, welches Objekt den Primärschlüssel besitzt, kann alternativ auch gelten: mehrere Elternobjekte verweisen auf ein und dasselbe Kindobjekt (N:1).

N:N (Vernetzung)

Bei der Vernetzung können zwischen den Elementen zweier Gruppen beliebige Verbindungen auftreten.
Diese Zuordnungen werden dann in einer separaten Tabelle verwaltet, in der jeweils die IDs der Partner vermerkt sind. Deshalb ist es nötig, dass hier beide Objekttypen einen Primärschlüssel besitzen.

Verbindungen :: 1:1 (Auslagerung)

Zur besseren Organisation der obigen drei Gruppen (Admins, Mitarbeiter und Kunden) brauchen wir zuerst ein allgemeines Objekt zwecks Speicherung persönlicher Daten (im Feld Flags könnte u.A. vermerkt werden, ob es sich um einen Firmenangehörigen handelt, was auch die Erzeugung einer Angestellten-Liste erleichtert):

person
	Feldname	Typ
0	PersonID	int	[AUTOINCR] ID
1	Flags	int
2	Vname	varchar[24]	Vorname
3	Nname	varchar[24]	Nachname
4	Tel	varchar[16]	Telefon
5	Mail	varchar[48]	EMail-Adresse

Schließlich benötigen wir noch die Tabellen für Mitarbeiter und Kunden, jeweils mit typspezifischen Feldern:

employee
	Feldname	Typ
0	PersonID	int	[INDEX] Verweis
1	Abtlg	varchar[32]	Arbeitsbereich
2	Rights	int	Rechte-Flags

customer
	Feldname	Typ
0	PersonID	int	[INDEX] Verweis
1	CustNr	varchar[12]	Kundennummer
2	PAdr	varchar[80]	Postadresse

Das Feld Rights in

employee gibt implizit Auskunft darüber, ob es sich um einen Administrator handelt.

Die Verbindung zwischen Person und Funktion erfolgt über den blau gekennzeichneten Verweis PersonID.

Als Ergebnis der Objekt-Aufsplittung und Neukombination (hier 1:1-Verbindung) ist es uns gelungen, alle oben aufgeführten Nachteile zu beseitigen:

Administratoren müssen nicht mehr doppelt gepflegt werden.
Es ist einfach, eine sortierte Liste aller Mitarbeiter (incl. Admins) zu generieren.
```
SELECT * FROM person WHERE Flags&1 ORDER BY NName,VName
```
Die Wandlung vom normalen Mitarbeiter zum Administrator (oder auch umgekehrt) reduziert sich auf das Setzen/Entfernen von Rechte-Flags.
Falls irgendwann ein zusätzliches Feld, z.B. für Faxnummer, erwünscht ist, muss lediglich die Personen-Tabelle erweitert werden.

Verbindungen :: 1:N (Hierarchie)

Die 1:N-Beziehung ist das Standardhilfsmittel zur Gruppierung gleichartiger Elemente; beispielweise CD-Tracks, die sich auf einer CD befinden und daher auf deren ID verweisen:

cd
	Feldname	Typ
0	CDID	int	[AUTOINCR] ID
1	Titel	varchar[64]	Titel des Albums
2	Jahr	smallint	Erscheinungsjahr
3	Artist	varchar[48]	Künstler

track
	Feldname	Typ
0	TrackID	int	[AUTOINCR] ID
1	CDID	int	Verweis
2	Titel	varchar[64]	Titel des Stücks
3	Secs	smallint	Länge in Sekunden

Auch in einer Firmendatenbank könnten solche Verbindungen auftreten, z.B. Mitarbeiter, die zur selben Filiale gehören (Feld FilialID in Tabelle employee), oder Artikel, die von einem bestimmten Kunden bestellt wurden (Feld PersonID in Tabelle order).

Verbindungen :: N:N (Vernetzung)

Eine freie Verknüpfung taucht z.B. auf, wenn Personen an Projekten beteiligt sind, etwa die Kursbelegung an einer Universität, oder in unserem Fall die Zuteilung von Administratoren für die Abteilungen. Hier muss sowohl jede Person mit mehreren Objekten, als auch jedes Objekt mit mehreren Personen verknüpfbar sein.
Hier ein Abteilungsobjekt:

abtlg
	Feldname	Typ
0	AbtlgID	int	[AUTOINCR] ID
1	Titel	varchar[64]	Bezeichnung der Abteilung

Die Verbindung zwischen Abteilungen und Administratoren (die über ihre PersonID identifiziert werden) hält man in einer separaten Tabelle (hier: xadmin) fest:

xadmin
	Feldname	Typ
0	AbtlgID	int	Verweis
1	AdminID	int	Verweis (PersonID)

Solche Verknüpfungstabellen erlauben ein Höchstmaß an Flexibilität; allerdings muss man bei manchen Abfragen ein JOIN-Konstrukt verwenden (oder innerhalb der Schleife eine SubQuery absetzen).
Hier ein paar typische Queries (mit JOIN-Variante):

Wievielen Abteilungen ist ein bestimmter Admin (PersonID 7) zugeordnet?

SELECT COUNT(*) FROM xadmin WHERE AdminID=7

Welche Abteilungen sind dies im einzelnen?

SELECT Titel
  FROM abtlg
  LEFT JOIN xadmin ON xadmin.AbtlgID=abtlg.AbtlgID
  WHERE AdminID=7

Wieviele Admins sind einer bestimmten Abteilung (AbtlgID 3) zugeordnet?

SELECT COUNT(*) FROM xadmin WHERE AbtlgID=3

Welche Admins sind dies im einzelnen?

SELECT NName,Vname
  FROM person
  LEFT JOIN xadmin ON xadmin.AdminID=person.PersonID
  WHERE AbtlgID=3

Index :: PHP/MySQL

template