Datenbanken mit mehreren Tabellen
Werbung
Datei: Teil2_Kap04.doc, Seite51 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen woran Sie redundante Daten in Tabellen erkennen wie Sie diese in Nachschlagetabellen auslagern warum Referentielle Integrität so wichtig ist und wie sie aktiviert wird wie Sie Werte aus Nachschlagetabellen in Kombinationsfeldern anzeigen wie Sie die Fehlermeldung bei ungültigen Nachschlagewerten verbessern wie Unterdatenblätter bei verknüpften Tabellen die Übersicht erleichtern wie m:n-Beziehungen in zwei 1:n-Beziehungen verwirklicht werden wie ein Index über mehrere Felder doppelte Daten-Kombinationen verhindert II – Datenbank-Design In diesem Kapitel lernen Sie 4.1 Nachschlagetabellen Natürlich zeigt eine relationale Datenbank ihre Stärken erst in den Beziehungen (=Relationen) zwischen mehreren Tabellen. Bis hierhin hätten Sie alles eigentlich auch noch in Excel machen können. Warum sollten Sie aber mehrere Tabellen benutzen? Betrachten Sie dazu einmal das Feld perPrioritaet. Es ist ein Textfeld mit maximal 10 Zeichen, von denen von einem Eintrag wie »manchmal« schon 8 Zeichen belegt werden. Gleichzeitig wiederholen sich in diesem Feld wenige Einträge ganz oft. Das ist ein Hinweis darauf, dass es ein Nachschlagefeld sein sollte. Anstatt immer wieder das gleiche lange Wort zu schreiben, wird dort eine kurze Zahl gespeichert. Zu dieser Zahl lässt sich in einer anderen Tabelle dieses Wort bei Bedarf nachschlagen. Wenn Sie beispielsweise weniger als 255 verschiedene Prioritäten haben – wovon wir hier sicher ausgehen können –, dann könnte die Referenzzahl vom Datentyp Byte sein. In Tabelle 3.4 auf Seite 35 können Sie nachlesen, dass dieser einen Platzbedarf von 1 Byte hat. Eine Priorität mit einer durchschnittlichen Textlänge von 6-8 Zeichen verbraucht im Gegensatz dazu bis zu 8 Bytes, nämlich 1 Byte je Buchstabe. Durch die Umstellung vom Datentyp Text zu Byte reduziert sich also je nach Textlänge der Platzbedarf pro Datensatz erheblich. Es gibt noch einen zweiten, nicht zu unterschätzenden Vorteil: solange Texte gespeichert werden, kann es Schreibfehler geben. Sobald ein Datensatz aber statt der Priorität »gelegentlich« eine solche wie 51 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite52 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen »gelgentlich« erhält, wird diese falsch geschriebene Priorität in allen gefilterten Abfragen vergessen. Es gibt auch keine Warnung, dass im Ergebnis vielleicht Daten fehlen, denn falsch geschriebene Worte werden einfach wie neue Prioritäten behandelt. Dieses Risiko ist für eine Datenbank eigentlich untragbar. Es kann nicht sein, dass unfreiwillig neue Prioritäten erfunden werden, wenn Sie als Datenbank-Designer davon ausgehen, dass es eine vorhersehbare Liste von Werten gibt. Auch dieses Problem ist mit der Nachschlagetabelle lösbar, denn Access lässt sich so einstellen, dass nur die Zahlen der Nachschlagetabelle akzeptiert werden. Wichtig: Möglichst keine späten Änderungen am Konzept Für diese Änderung muss das Konzept der Datenbank erweitert werden. Deswegen möchte ich hier noch einmal ganz deutlich darauf hinweisen, dass das bei einer echten Datenbank jetzt eigentlich zu spät wäre. Diese Entscheidung hätten wir besser noch auf dem Papier gefällt, denn es werden sich Feldnamen ändern. Bei großen Datenbanken müssen Sie nach einer solchen Erweiterung wirklich alle Abfragen, Formulare, Berichte Makros und VBA-Prozeduren einzeln daraufhin prüfen, ob sie noch funktionieren. Die neue Nachschlagetabelle mit dem Namen tblPrioritaeten wird also zwei Felder benötigen: priNr für die eindeutige Zahl und priName für den Text. Sie mögen sich an dieser Stelle wundern, dass ich die eindeutige Zahl in tblPersonen als »ID« und hier in tblPrioritaeten als »Nr« benannt habe. Während aber bei den Personen ein AutoWert mit einer automatisch erzeugten Zahl eingesetzt wird, können hier die Werte für die Priorität manuell vergeben werden. Zum einen sind es nur wenige Datensätze, zum anderen sind AutoWerte mindestens Long-Zahlen und damit viel zu groß. Diese Unterscheidung in automatischen AutoWert und manuell zu vergebende Zahl halte ich auch im Feldnamen fest, damit ich beispielsweise in Aktionsabfragen später weiß, dass ich in ein Feld perID keinen Wert hineinschreiben dürfte, in priNr hingegen schon. Nutzen Sie jetzt bitte die Erstellen-Registerkarte und gehen von dort in den Tabellenentwurf, um die Tabelle perPrioritaeten gemäß Abbildung 4.1 anzulegen. Abbildung 4.1: Entwurf der Tabelle tblPrioritaeten. 52 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite53 3. Juli 2007 4.1 Nachschlagetabellen Der Felddatentyp für priNr steht auf Zahl und die Feldgröße auf Byte. Sobald Sie für diese Zeile den Primärschlüssel in der Registerkarte anklicken, wechselt auch hier Indiziert auf Ja (Ohne Duplikate). Damit ist es trotz manueller Eingabe nicht mehr möglich, mehrfach dieselbe Nummer zu vergeben. II – Datenbank-Design Die Feldgröße für das Text-Feld priName können Sie gerne großzügig auf 20 Zeichen stellen. Dadurch, dass jedes Wort genau einmal vorkommt, ist seine tatsächliche Länge wegen des Platzbedarfs völlig unerheblich. 500 Adressen mit durchschnittlich 7 Buchstaben für die Priorität verbrauchen 3.500 Bytes, aber diese Adressen mit einer Byte-großen Nachschlagezahl plus vielleicht drei Prioritäten zu je 20 Buchstaben verbrauchen 560 Bytes (500 * 1 Byte + 3 * 20 Byte). Das ist knapp ein Siebtel des bisher notwendigen Platzbedarfs. Damit die Tabelle gleich sinnvoll benutzt werden kann, sollten Sie schon ein paar Daten eintragen wie in Abbildung 4.2. Abbildung 4.2: Daten für die Tabelle tblPrioritaeten. Probieren Sie ruhig auch einmal aus, was passiert, wenn Sie versehentlich eine schon vergebene priNrZahl ein zweites Mal eintragen wollen. Beim Versuch, diesen Datensatz zu verlassen und damit zu speichern, erhalten Sie die Warnmeldung aus Abbildung 4.3. Sie können diesen fehlerhaften Datensatz nur mit der Esc-Taste verlassen, damit er nicht gespeichert wird. Abbildung 4.3: Warnung bei doppelten Inhalten in indizierten Feldern. Damit ist auf dieser Seite alles in Ordnung. Passend dazu muss in der Tabelle tblPersonen jetzt das bisherige Text-Feld perPrioritaeten gelöscht und durch ein Zahl-Feld ersetzt werden. Tatsächlich könnten Sie mit Aktionsabfragen auch dafür sorgen, dass zu den bisherigen Texten die passende priNr gefunden und dann direkt eingetragen wird. Der Aufwand steht aber für die wenigen Testdatensätze in gar keinem Verhältnis zum Erfolg. Öffnen Sie also bitte die Tabelle tblPersonen in der Entwurfsansicht. Das geht am schnellsten mit einem Rechtsklick auf den Namen im Navigationsbereich am linken Rand und der Auswahl Entwurfsansicht wie in Abbildung 4.4. 53 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite54 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen Abbildung 4.4: Vorhandene Tabelle in der Entwurfsansicht öffnen. Natürlich können Sie die Tabelle auch erst per Doppelklick in der Datenblattansicht öffnen und dann von dort aus in die Entwurfsansicht zurückwechseln. Bei den wenigen Daten in einer lokalen Tabelle ist der Unterschied unwesentlich. Wenn aber mal wirklich viele Datensätze enthalten sind und diese vielleicht sogar aus einer verknüpften Tabelle im Netzwerk kommen, warten Sie völlig unnötig auf die Daten, um dann doch nur in den Entwurf zu wechseln. Klicken Sie in der Zeile perPrioritaet auf den Zeilenkopf und löschen die Zeile mit der Entf-Taste. Da mit dem Feld auch die Daten gelöscht werden, warnt Access Sie mit folgender Fehlermeldung. Abbildung 4.5: Warnung beim Löschen eines Feldes im Tabellenentwurf. Sie können diese Warnung bestätigen und anschließend den neuen Feldnamen eingeben. Üblicherweise würde jetzt auch dieses Feld prioNr genannt, weil es zu dem entsprechenden Feld in der Tabelle tblPrioritaeten gehört. Als zusätzlicher Grund für die Namenswahl wird dann oft noch erwähnt, dass Access zwei gleichnamige Felder in Abfragen mit mehreren Tabellen automatisch miteinander verbinden kann. Tipp: Deaktivieren Sie die Autoverknüpfung Wenn in einer Abfrage zwei Tabellen aufgenommen werden, die gleichnamige Felder enthalten, kann Access diese automatisch miteinander verknüpfen. Damit dies tatsächlich geschieht, müssen beide Tabellen ein Feld mit gleichem Namen und Datentyp enthalten. Außerdem muss eines der beiden Felder ein Primärschlüssel sein. Um dieses Verhalten auszuschalten, müssen Sie im Menü der Office-Schaltfläche ganz unten die Access-Optionen-Schaltfläche anklicken. Dort wählen Sie dann links die Kategorie ObjektDesigner aus und entfernen das Häkchen vor AutoVerknüpfung aktivieren. 54 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite55 3. Juli 2007 4.1 Nachschlagetabellen Das sind aber im Grunde schon zwei Design-Fehler: gleichnamige Felder darf es nach unseren bisherigen Überlegungen innerhalb dieser Datenbank schon gar nicht mehr geben, weil jedes Feld als Präfix seinen Tabellennamen enthält. Der zweite Design-Fehler wiegt viel schwerer, wie Sie gleich noch ausführlich sehen werden: Feldverknüpfungen werden lange vor dem Abfrageentwurf zentral bestimmt. Die Abfrage übernimmt diese sinnvollerweise und muss sie nicht lokal immer wieder neu erraten. Da mir meine Erfahrung aus vielen Schulungen zeigt, dass oftmals unklar ist, welches Feld welche Aufgabe hat, hänge ich immer noch ein Ref an, so dass der tatsächliche Name perpriNrRef ist. Es ist in der per...-Tabelle eine Referenz auf das priNr-Feld. Der Feldname ist vielleicht nicht besonders schön, aber er zeigt klar an, dass das Feld nur Zahlen referenzieren darf, die in der Nachschlagetabelle tblPrioritaeten vorhanden sind. Auf keinen Fall darf hier eine Zahl neu vergeben werden. Also: priNr führt, perpriNrRef folgt. Damit das technisch funktioniert, muss perpriNrRef auch den gleichen Datentyp besitzen wie priNr, also eine Zahl mit der Feldgröße Byte. Speichern Sie anschließend diesen Entwurf und geben in der Datenblattansicht testweise Zahlen ein. Derzeit müssen Sie auf Seite 53 nachblättern, welche Zahlen welche Bedeutung als Priorität haben. Das ist mehr als umständlich und wird natürlich bei Gelegenheit noch verbessert. Wenn Sie aber mal ein paar Beispielwerte für perpriNrRef in Abbildung 4.6 ansehen, werden Sie hoffentlich überrascht sein. Abbildung 4.6: Mögliche und unmögliche Werte in perpriNrRef. Schon der Wert 5, aber noch viel deutlicher der ebenfalls in der Nachschlagetabelle nicht vorhandene Wert 99 als perpriNrRef wird von Access offensichtlich akzeptiert. Sie können also Werte »nachschlagen«, die in der Nachschlagetabelle gar nicht vorkommen. Eine Datenbank, die in diesem Zustand bleibt, können Sie getrost löschen. Sie können nicht nur vermuten, dass solche »Datensatz-Leichen« eventuell vorkommen, sondern nach meiner Erfahrung geradezu sicher sein, dass sie existieren. Das wäre eine Katastrophe, denn Sie wüssten nie, ob Ihre Benutzer möglicherweise ungültige Daten eintragen. Das mag hier für die Prioritäten noch als lästige Kleinigkeit abgetan werden, aber stellen Sie sich einmal eine Datenbank vor, bei der für die Verkäufe die Referenz-ID des Kunden und des Artikels notiert wird. Das ist im Sinne der Normalformen völlig in Ordnung. Wenn es diese Kunden-ID oder die Artikel-ID nun aber gar nicht gibt? Was haben Sie dann wem eigentlich verkauft? 55 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) II – Datenbank-Design Entsprechend unseren Namenskonventionen (siehe Seite 29) muss dieser Feldname mit per beginnen, weil er in der Tabelle tblPersonen enthalten ist. Dann folgt der Name des Feldes aus der Nachschlagetabelle, also priNr. Datei: Teil2_Kap04.doc, Seite56 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen Daher müssen wir sicherstellen, dass nie und nimmer falsche perpriNrRef-Werte eingetragen werden können. Damit das funktioniert, müssen Sie zuerst diese bereits enthaltenen falschen Werte korrigieren. Machen Sie also bitte aus 5 und 99 jeweils eine 1. Inhaltlich ist der Wert im Moment ohne Bedeutung, aber er muss gültig sein. Andernfalls wäre Access nicht bereit, zukünftige Werte auf Gültigkeit zu überprüfen. 4.2 Referentielle Integrität Das Stichwort für diese Überprüfung heißt Referentielle Integrität, das heißt, die Referenzen zu den Nachschlagetabellen werden auf ihre Integrität hin geprüft. Das ist eine Prüfung, die mehrere Tabellen betrifft. Es ist keine Einstellung in nur einer Tabelle. Schließen Sie jetzt also alle Tabellen. 1. In der Registerkarte Datenbanktools finden Sie in der Gruppe Einblenden/Ausblenden das Symbol Beziehungen. 2. Beim Öffnen erscheint direkt der Dialog zur Anzeige der neu aufzunehmenden Tabellen, die Sie bitte beide per Doppelklick hinzufügen. Dann können Sie den Dialog schließen. 3. Bei beiden Tabellen können Sie nun durch Ziehen am Fensterrand die Größe ändern, um alle Felder lesen zu können. Ich achte zudem darauf, dass Nachschlagetabellen wie die Tabelle tblPrioritaeten höher als die abhängigen Tabellen wie tblPersonen stehen. Je weiter eine Tabelle unten steht, desto mehr Daten enthält sie und desto mehr muss man sich um Optimierung hinsichtlich Geschwindigkeit und Größe kümmern. Die Tabelle tblPrioritaeten beispielsweise enthält nur vier Datensätze und ist daher zu vernachlässigen, fast egal, wie viele Personen in tblPersonen aufgenommen werden. Abbildung 4.7: Beide Tabellen wurden in das Beziehungen-Fenster aufgenommen. 4. Wenn die Tabellen wie in Abbildung 4.7 in das Beziehungen-Fenster aufgenommen sind, müssen Sie die eigentliche Referentielle Integrität einrichten. Dazu ziehen Sie den Primärschlüssel, hier also priNr, mit gedrückter linker Maustaste auf den so genannten Fremdschlüssel, hier perpriNrRef. 56 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite57 3. Juli 2007 4.2 Referentielle Integrität 5. Wenn der Mauszeiger mit seiner Spitze exakt auf den Feldnamen zeigt, lassen Sie los. Damit er- Abbildung 4.8: Dialog für Referentielle Integrität. 6. Wenn Sie diesen Dialog einfach so mit Erstellen bestätigten, wäre das unverbindlich. Erst durch das Ankreuzen der Option Mit referentieller Integrität wird eine sichere Überprüfung eingeschaltet. Der Beziehungstyp zwischen dem Primärschlüssel priNr und dem Fremdschlüssel perpriNrRef ist 1:n (sprich »eins zu n«), das heißt, ein Wert von priNr kann n-mal in perpriNrRef benutzt werden. Sobald Sie die referentielle Integrität aktivieren, werden auch zwei weitere Kontrollkästchen freigeschaltet: Aktualisierungsweitergabe an verwandte Felder bedeutet, dass Sie in priNr eine Zahl ändern dürfen, beispielsweise, wenn eine Priorität in der Reihenfolge dazwischensortiert werden soll. Solange die Option nicht eingeschaltet ist, dürften Sie bereits von perpriNrRef benutzte Feldwerte nicht ändern, denn dann hätten diese perpriNrRef-Werte (wenigstens vorübergehend) keine gültigen Daten. Mit aktivierter Aktualisierungsweitergabe passt Access die zugehörigen perpriNrRef-Daten sofort und automatisch an die priNr-Änderung an. Die Option Löschweitergabe an verwandte Datensätze folgt einem ähnlichen Prinzip: Um eine Priorität zu löschen, müssen zuerst alle Datensätze aus der Tabelle tblPersonen gelöscht werden, die diese Priorität benutzen. Das kann schon ein wenig lästig sein. Sobald die Löschweitergabe aktiviert ist, kümmert sich Access darum, alle abhängigen Daten zu löschen. Das ist natürlich mit ahnungslosen Benutzern ziemlich riskant, denn wenn sie eine Priorität löschen, erhalten sie nur die unauffällige Warnung aus Abbildung 4.9. Abbildung 4.9: Warnung, falls mit aktivierter Löschweitergabe Daten der Master-Tabelle gelöscht werden. Es gibt keinen Hinweis darauf, wie viele Datensätze oder gar wie viele weitere Tabellen betroffen sind, die ebenfalls mitgelöscht werden! Mit dieser Warnung im Hinterkopf können Sie die Option aktivieren. 57 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) II – Datenbank-Design scheint automatisch der Dialog aus Abbildung 4.8. Datei: Teil2_Kap04.doc, Seite58 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen 7. Wenn Sie diesen Dialog nun mit Erstellen bestätigen, präsentiert sich die referentielle Integrität durch eine Verbindungslinie zwischen den Tabellen sowie die Kennzeichnungen 1 und ∞ wie in Abbildung 4.10. Das meint das Gleiche wie 1:n im Dialog, auch wenn Mathematiker bei der Gleichsetzung von n und ∞ erstaunt sein mögen. Abbildung 4.10: Die referentielle Integrität ist aktiviert. 8. Jetzt können Sie das Beziehungen-Fenster schließen. Die Rückfrage »Sollen die am Layout von 'Beziehungen' vorgenommenen Änderungen gespeichert werden?« bezieht sich nur auf die Anordnung der Tabellen, die referentielle Integrität selber ist mit Erscheinen der Verbindungslinie bereits gesichert. Zu diesem Zeitpunkt hat Access auch bereits geprüft, dass weder Datensatzleichen (also perpriNrRef-Werte, die kein Gegenstück in priNr besitzen) noch Datentyp-Verletzungen (perpriNrRef ist Byte, aber priNr ist Long) vorliegen. Erst wenn alles korrekt ist, wird die Linie angezeigt. 9. Sicherheitshalber sollten Sie nun nochmals versuchen, eine ungültige perpriNrRef einzugeben. Öffnen Sie also bitte per Doppelklick die Tabelle tblPersonen und ändern einen perpriNrRef-Wert auf 99. Sobald Sie versuchen, diesen Datensatz zu verlassen und damit zu speichern, erscheint folgende Meldung. Abbildung 4.11: Warnung bei ungültigen perpriNrRef-Werten. Diese Meldung ist zwar inhaltlich korrekt, aber für einen »normalen« Benutzer sicherlich keine große Hilfe. Das wird später noch verbessert. 10. Um weiterarbeiten zu können, müssen Sie die fehlerhafte Eingabe mit der Esc-Taste abbrechen. 4.3 Kombinationsfelder für Nachschlagetabellen Nachdem wir gewährleistet haben, dass die Datenbank nur gültige Daten akzeptiert, wollen wir uns etwas um die Bequemlichkeit der Benutzung kümmern. 58 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite59 3. Juli 2007 4.3 Kombinationsfelder für Nachschlagetabellen 1. Wechseln Sie bitte nochmals in den Entwurf von tblPersonen und klicken dort das Feld perpriNrRef an. Die Feldeigenschaften unten bieten eine zweite Registerkarte Nachschlagen, welche auf den ersten Blick eher langweilig wirkt. 2. Sie zeigt ihr Potential erst, wenn Sie die Eigenschaft Steuerelement anzeigen auf Kombinationsfeld einstellen. Damit eröffnet sich die Möglichkeit, bei der Dateneingabe direkt eine Liste gültiger priNr-Werte anzuzeigen. 3. Der Herkunftstyp steht bereits auf Tabelle/Abfrage, so dass Sie bei Datensatzherkunft lediglich aus II – Datenbank-Design der angebotenen Liste die passende Datenquelle tblPrioritaeten angeben müssen. Abbildung 4.12: Erste Eigenschaften für die Nachschlagetabelle. 4. Nachdem Sie die Tabelle mit den Einstellungen wie in Abbildung 4.12 gespeichert haben, können Sie in der Datenblattansicht probieren, was sich geändert hat. Auf den ersten Blick nichts. Erst, wenn Sie in eine perpriNrRef-Zelle klicken, erscheint der DropDown-Pfeil für die Kombinationsfeldliste. Abbildung 4.13: DropDown-Liste im Datenfeld. Das ist schon ganz nett, wie Sie in Abbildung 4.13 sehen, denn nun haben Sie – bzw. die späteren Benutzer – eine Auswahl aller gültigen priNr-Werte. Es gibt aber noch einiges zu verbessern: Die Fehlermeldung, die beim Eingeben eines nicht in der Liste enthaltenen Eintrags erscheint, lässt sich benutzerfreundlicher gestalten. Die Bezeichnungen der Prioritäten fehlen. Sobald diese erscheinen, werden Sie feststellen, dass die Listenbreite nicht mehr besonders brauchbar ist. 59 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite60 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen Fangen wir mit der besseren Fehlermeldung an, denn bisher sehen Sie bei einer ungültigen Nummer die Meldung aus Abbildung 4.11. 5. Wechseln Sie wieder in den Tabellenentwurf zum Feld perpriNrRef und stellen den Wert für Nur Listeneinträge auf Ja. Tipp: So ändern Sie Eigenschaften am bequemsten Viele Eigenschaften (nicht nur der Felder hier, sondern später vor allem in Formularen und Berichten) bieten eine Auswahl möglicher Werte in einer Liste. In diesen Fällen können Sie per Doppelklick auf den Eigenschaftsnamen zum jeweils nächsten Eintrag der Liste weiterschalten. Wenn Sie jetzt einen ungültigen Wert für perpriNrRef eingeben, wird nicht erst beim Verlassen der ganzen Zeile (und damit beim Speichern des Datensatzes) die unschöne Fehlermeldung angezeigt. Stattdessen erscheint direkt beim Verlassen des Feldes folgende Fehlermeldung. Da weiß ein Benutzer doch gleich, was zu tun ist! Abbildung 4.14: Bessere Fehlermeldung für ungültige Nachschlagewerte. Zufällig ist dieses Feld auch das letzte in der Zeile, so dass das Verlassen des Datensatzes und das Verlassen des Feldes meistens identisch sind. Sie können den Unterschied testen, indem Sie in der gleichen Zeile auf ein anderes Feld klicken, dann ist der Datensatz noch nicht gespeichert, aber die neue Meldung trotzdem schon ausgelöst. Damit kommen wir zum zweiten Änderungswunsch, der Anzeige der Prioritäten-Bezeichnungen. Sie sehen in der ausgeklappten Liste nur die erste Spalte der Datenquelle, weil das in den Feldeigenschaften so eingestellt ist. 6. Ändern Sie dort die Spaltenanzahl von 1 auf 2 und die Namen sind sichtbar! Abbildung 4.15: Verbesserte DropDown-Liste im Datenfeld. Die Abbildung 4.15 zeigt aber das schon angekündigte nächste Problem: die Listenspalten sind sichtbar, aber schlecht zu lesen. Sie müssten jetzt die perpriNrRef-Spalte so breit machen, dass die komplette DropDown-Liste hineinpasst. Nein, das müssen Sie nicht. Für die Spaltenbreiten der ausgeklappten Liste gibt es eine Eigenschaft, die derzeit noch leer ist. Daher erhält jede der Listenspalten die Standardbreite von 1 Zoll (=1 Inch). Sie können passend zur Schriftgröße eigene Daten eingeben, im deutschen Access in der Einheit cm und durch Semikolon getrennt. 60 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite61 3. Juli 2007 4.3 Kombinationsfelder für Nachschlagetabellen 7. Wenn Sie beispielsweise 1;4 eintragen, wird dies beim Verlassen in 1cm;4cm korrigiert. Das dürfte aber noch zu breit sein, vor allem für die erste Spalte. 8. Ändern Sie den ersten Wert nun auf 0,5cm. Er wird beim Verlassen (offensichtlich durch einen Rundungsfehler beim internen Speichern als Inch-Zahl) als 0,501cm angezeigt. Bei der nächsten Änderung an irgendeiner Zahl dieses Feldes wird er übrigens auf 0,503cm verschlimmbessert. Das ist schon seit den Anfängen von Access so und im Wesentlichen nur lästig. 9. Das ändert sich, sobald Sie die Listenbreite von Automatisch auf die Summe der Spaltenbreiten stellen. In diesem Fall ergibt 0,5 cm + 4 cm etwa 4,5cm, aber wegen der Rundungsprobleme sollten Sie direkt 5cm eintragen. Jetzt sieht die DropDown-Liste so benutzerfreundlich aus wie in Abbildung 4.16. Abbildung 4.16: Optimale DropDown-Liste für Nachschlagewerte. Tipp: So machen Sie DropDown-Listen noch schöner Bei DropDown-Listen wird immer der Wert der ersten sichtbaren Spalte angezeigt, wenn sie wieder einklappt, in diesem Beispiel also die priNr. Die Betonung liegt dabei auf sichtbar. Probieren Sie mal, was passiert, wenn die Spaltenbreiten auf 0cm;4cm geändert werden. Die Liste zeigt auch im eingeklappten Zustand die Bezeichnungen der Prioritäten: Das ist sehr praktisch für Formulare, dort können Sie sogar noch die Ausrichtung linksbündig einstellen. Hier in Tabellen sollten Sie darauf noch verzichten, weil Sie ansonsten wenig Chancen haben, die nun versteckte priNr tatsächlich mal zu sehen und überprüfen zu können. Bei der Einrichtung der referentiellen Integrität hat sich übrigens noch mehr geändert, nämlich die Ansicht der Tabelle tblPrioritaeten. Vor allen Datenzeilen wird jetzt eine Spalte mit +-Zeichen angezeigt. Wenn Sie diese wie in Abbildung 4.17 durch Anklicken expandieren, zeigt Access die jeweils zugehörigen Daten in einer eingeschobenen Personen-Tabelle an. 61 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) II – Datenbank-Design Dafür wird die Liste ausgeklappt ein bisschen lesefreundlicher, denn die erste Spalte benötigt nicht so viel Platz. Allerdings ist die ausgeklappte Liste insgesamt nur so breit wie die Spalte. Obwohl im Ergebnis nur eine schmale Zahl angezeigt wird, müsste die Spalte nun die Breite der Liste haben. Datei: Teil2_Kap04.doc, Seite62 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen Abbildung 4.17: Darstellung der Tabelle mit verknüpften Daten. Wie Sie sehen, gilt das sogar für mehrere Datenzeilen gleichzeitig. Solange eine Tabelle lediglich eine einzige 1:n-Beziehung zu einer untergeordneten anderen Tabelle hat, reicht im Entwurf die Tabellen(!)Eigenschaft Unterdatenblattname mit dem Wert Automatisch wie in Abbildung 4.18. Abbildung 4.18: Unterdatenblattname-Eigenschaft der Tabelle. 62 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite63 3. Juli 2007 4.4 Viele Inhalte in einem Feld 4.4 Viele Inhalte in einem Feld Im Laufe der Arbeit mit dieser Datenbank stellen Sie möglicherweise fest, dass es ganz praktisch wäre, die Adressen in Gruppen zusammenzufassen. Da gibt es etwa alle Adressen von Schul- oder Kindergarten-Eltern, berufliche Adressen oder solche aus Sportverein und Kegelclub. Also könnten Sie wiederum eine Nachschlagetabelle anlegen, wo jeder Gruppenname mit einer Nummer codiert wird, die dann im Referenz-Feld benutzt wird. Genauso haben wir es ja für die Prioritäten gemacht. m:n-Beziehungen Bei einer 1:n-Beziehung können Sie zwangsläufig nur einen einzigen Wert auswählen. Hier liegt hingegen eine so genannte m:n- oder many-to-many-Beziehung vor, da viele Adressen in vielen Gruppen einsortiert werden sollen. Die nahe liegende Lösung wäre diejenige, die auch Outlook für seine Kategorien verwendet: Sie legen ein großes Textfeld an und schreiben – durch ein Trennzeichen auseinander gehalten – alle Gruppennamen hintereinander. Diese Lösung wäre nicht nur eine Schande für ein anständiges Datenbank-Design, sondern hat auch ganz banale technische Probleme, denn bestimmte Abfragen wie Gruppierungsabfragen lassen sich auf solche Sammeltextfelder nicht anwenden! Im Übrigen verstößt so ein Feld bereits gegen die erste Normalform (siehe Seite 28), denn die Inhalte sind nicht »atomisiert«. Also muss es eine andere Lösung geben. Dazu wird die m:n-Beziehung in zwei 1:n-Beziehungen aufgelöst. Es gibt eine dritte Tabelle als Verbindung zwischen Personen und Gruppen wie in Abbildung 4.19. Abbildung 4.19: Die Konstruktion für eine m:n-Beziehung über eine Hilfstabelle (PK = Primary Key bzw. Primärschlüssel, FK = Foreign Key bzw. Fremdschlüssel). 1. Erstellen Sie also bitte die Tabelle tblGruppen mit den in Abbildung 4.19 sichtbaren Feldern mit dem Datentyp Byte für den Primärschlüssel grpNr und Text für grpName. Geben Sie dann ein paar Beispieldaten ein wie in Abbildung 4.20. 63 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) II – Datenbank-Design Hier dürfte das aber ein schwerer Design-Fehler werden, denn das geht nur so lange gut, wie jede Adresse einer einzigen Gruppe zuzuordnen ist. Sobald jemand im Sportverein und im Kegelclub und vielleicht sogar noch Ihr beruflicher Kollege ist, haben Sie datenbanktechnisch verloren. Datei: Teil2_Kap04.doc, Seite64 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen Abbildung 4.20: Die Beispieldaten für die Tabelle tblGruppen. 2. Außerdem benötigen Sie die Tabelle tblGrp2Per (Gruppen-zu-Personen) mit den zwei Long- Feldern g2pID als Primärschlüssel und AutoWert sowie g2pperIDRef. Letzteres muss ein Long-Feld sein, weil es auf den AutoWert perID Bezug nimmt, der ja auch ein Long-Datentyp ist. 3. Schließlich erhält die Tabelle das Feld g2pgrpNrRef, welches wie grpNr ein Byte-Datentyp ist. In der nächsten Abbildung sehen Sie seine Einstellungen auf der Registerkarte Nachschlagen. Mit Ausnahme der abweichenden Datenquelle tblPersonen sind die Feldeigenschaften mit denen des Feldes g2pperIDRef identisch. Abbildung 4.21: Die Feldeigenschaften für g2pgrpNrRef. 4. Sie können jetzt in tblGrp2Per alle Kombinationen aus Personen und Gruppen eintragen, die Ihnen sinnvoll erscheinen, wie es in Abbildung 4.22 zu sehen ist. Abbildung 4.22: Die Beispieldaten für die Tabelle tblGrp2Per. Die Person mit der ID 2 ist beispielsweise Mitglied in den Gruppen mit der Nummer 1 und 2. Umgekehrt hat die Gruppe 2 mehrere Mitglieder, nämlich die Personen mit der ID 2, 3 und 7. Das ist vielleicht noch nicht besonders lesefreundlich, erfüllt aber alle Anforderungen an eine m:nBeziehung. 64 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite65 3. Juli 2007 4.4 Viele Inhalte in einem Feld Mehrfelder-Index Ist Ihnen das Problem in Abbildung 4.22 aufgefallen? In der Tabelle taucht die Kombination g2pgrpNrRef = 1 und g2pperIDRef = 2 zweimal auf, was natürlich vermieden werden sollte. Sonst könnte es passieren, dass ein Mitglied vielleicht zweimal einen Mitgliedsbeitrag zahlen muss. Es ist dazu nicht möglich, einfach auf eines der beiden Felder einen Index Ja (keine Duplikate) zu setzen, denn selbstverständlich muss jedes der Felder mehrfach die gleichen Inhalte haben dürfen. Nur eben nicht beide zusammen. 1. Klicken Sie in der Entwurf-Registerkarte bitte auf Indizes, so dass ein Hilfsfenster wie in Abbildung 4.23 mit den schon vorhandenen Indizes erscheint. Abbildung 4.23: Die vorhandenen Indizes für die Tabelle tblGrp2Per. Eigentlich ist dessen erste Zeile überflüssig, denn der PrimaryKey (= Primärschlüssel) hat die gleiche Sortierreihenfolge und außerdem noch die Eigenschaft Eindeutig auf Ja stehen. 2. Unabhängig davon können Sie in diesem Fenster aber neue Indizes eintragen. Dazu erfinden Sie einen neuen Indexnamen beliebigen Inhalts, der hier NixDoppelt heißen wird. Dahinter wählen Sie den ersten beteiligten Feldnamen g2pperIDRef aus. 3. In der zweiten Zeile – aber ohne erneuten Indexnamen in der ersten Spalte – wählen Sie das zweite beteiligte Feld g2pgrpNrRef aus. An dem fehlenden Eintrag in der ersten Spalte erkennt Access, dass dieses Feld ebenfalls zum Index NixDoppelt gehört. 4. Allerdings verlangt der Index noch keine Eindeutigkeit. Dazu müssen Sie wieder seine erste Zeile mit dem Namen NixDoppelt markieren und dafür wie in der folgenden Abbildung die Eigenschaft Eindeutig auf Ja stellen. Abbildung 4.24: Der neue Mehrfach-Index für die Tabelle tblGrp2Per. 65 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) II – Datenbank-Design Also benötigen Sie einen so genannten Mehrfelder-Index. Dieser ist im Tabellen-Entwurf eher unauffällig einzurichten. Datei: Teil2_Kap04.doc, Seite66 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen 5. Wenn Sie jetzt versuchen, den Tabellen-Entwurf zu speichern, wird Access das verweigern, weil der letzte Datensatz ein Duplikat des ersten war. Sie müssen also die Eindeutigkeit des Index vorübergehend ausschalten, damit Sie die Tabelle speichern können. 6. Dann ändern Sie den letzten Datensatz, so dass er kein Duplikat eines anderen ist, und stellen die Eindeutig-Eigenschaft für diesen Index erst jetzt endgültig auf Ja. 7. Das Indizes-Fenster können Sie nun schließen. Ab jetzt ist es wie gewünscht nicht mehr möglich, die gleiche Kombination aus Person und Gruppe mehrfach aufzunehmen. Sobald Sie das versuchen, erscheint beim Speichern des Datensatzes die Meldung aus Abbildung 4.25. Abbildung 4.25: Neue Datensätze dürfen kein Duplikat eines vorhandenen sein. Referentielle Integrität nicht vergessen! Das mag so im Moment schon fertig erscheinen, weil nur Listeneinträge erlaubt und keine Duplikate mehr möglich sind. Die Listeneinträge werden allerdings nur geprüft, wenn Sie Daten manuell eingeben. Per VBA-Programmierung ist es hingegen ohne Probleme möglich, ungültige Werte einzugeben. Allerdings greift trotzdem der Mehrfach-Index, d.h. Duplikate werden auch hier nicht zugelassen. Um diese Schwachstelle noch zu beseitigen, dürfen Sie nie vergessen, auch die Referentielle Integrität für alle Nachschlagetabellen einzurichten. Und aus Sicht der Tabelle tblGrp2Per gibt es zwei Nachschlagetabellen: tblPersonen und tblGruppen! 1. Klicken Sie daher bitte in der Registerkarte Datenbanktools auf das Symbol Beziehungen, um diese beiden Einstellungen nachzuholen. 2. Im Beziehungen-Fenster können Sie in der Registerkarte Entwurf mit Tabelle anzeigen die noch fehlenden Tabellen tblGruppen und tblGrp2Per hinzufügen. 3. Ziehen Sie dann das Primärschlüssel-Feld perID mit gedrückter Maustaste auf g2pperIDRef, um dort eine 1:n-Beziehung mit Löschweitergabe einzurichten. Die Löschweitergabe ist hier eher unbedenklich, weil die Tabelle tblGrp2Per sozusagen nur Mitgliedschaften enthält. Da ist es nur konsequent, dass mit dem Löschen einer Adresse auch deren Mitgliedschaft in einer der Gruppen endet. Es wird ja nicht die Gruppe selber gelöscht. 4. Auch von grpNr zu g2pperNrRef muss eine 1:n-Beziehung eingerichtet werden, hier ist außer der Löschweitergabe auch die Aktualisierungsweitergabe sinnvoll, denn die grpNr könnte im Gegensatz zu einem AutoWert ja manuell geändert werden. Abbildung 4.26 zeigt den aktuellen Stand der Beziehungen mit den neu hinzugefügten Tabellen. Jetzt wäre es auch per VBA-Programmierung nicht mehr möglich, ungültige Daten einzugeben. 66 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite67 3. Juli 2007 II – Datenbank-Design 4.5 Übungen zu diesem Kapitel Abbildung 4.26: Erweiterte Beziehungen mit allen Tabellen. 4.5 Übungen zu diesem Kapitel In diesem Abschnitt finden Sie einige Übungen zu diesem Kapitel. Die richtigen Antworten finden Sie wie immer auf der Website www.richtig-einsteigen.de. Übung 4.1 Verbessern Sie die Bücherei-Datenbank aus den Übungen des vorangegangenen Kapitels so, dass die Referentielle Integrität für tblBenutzer:tblLeihen und tblBuecher:tblLeihen sichergestellt ist. Übung 4.2 Was passiert mit den Datensätzen in tbLleihen, wenn Sie in der Bücherei-Datenbank einen BenutzerDatensatz löschen, der Bücher ausgeliehen hat? Übung 4.3 Was passiert mit dem Datensatz in tblBuecher, wenn Sie in tblLeihen alle Datensätze löschen, die sich auf dieses Buch beziehen? 67 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9) Datei: Teil2_Kap04.doc, Seite68 3. Juli 2007 Kapitel 4 Datenbanken mit mehreren Tabellen 4.6 Zusammenfassung In diesem Kapitel haben Sie die Details für die Verknüpfung mehrerer Tabellen miteinander kennen gelernt. Dabei unterstützt Access Sie mit verschiedenen Techniken, die sicherstellen, dass es keine ungültigen Daten geben kann: Treten in mehreren Datensätzen einer Tabelle gleichartige Einträge in einem Feld auf, spricht vieles dafür, dass dessen Daten in eine so genannte Nachschlagetabelle ausgelagert werden sollten. Dann kann sowohl die Größe des Feldes (meistens von Text-Datentyp auf Long-Zahl) deutlich verkleinert werden als auch die Auswahl auf nur noch vorgegebene Werte reduziert werden. Die Aktivierung der Referentiellen Integrität stellt sicher, dass für die Detailtabelle auch nur Werte akzeptiert werden, die tatsächlich in der Master-/Nachschlagetabelle enthalten sind. Ebenso können in der Mastertabelle keine Datensätze gelöscht werden, solange noch davon abhängige Datensätze in einer Detailtabelle enthalten sind. Damit der Benutzer die nachzuschlagenden Werte nicht auswendig wissen muss, stellen Kombinationsfelder diese übersichtlich dar. Dabei kann sogar die eindeutige Referenz-Zahl so ausgeblendet werden, dass wieder die ursprünglichen Texte sichtbar werden und der Benutzer davon nichts merkt, aber einen deutlichen Komfort-Gewinn hat. Durch einen Index über mehrere Felder hinweg können Sie dafür sorgen, dass auch Kombinationen mehrerer Datenfelder untereinander nicht doppelt gespeichert werden dürfen. 68 Lorenz Hölscher: Richtig einsteigen: Datenbanken entwickeln mit Access 2007. Microsoft Press 2007 (ISBN 978-3-86645-203-9)
