Auf dem Weg von Projekt zu Projekt sehen wir uns leider mit dem Mangel an einheitlichen Standards für das Datenbankdesign konfrontiert, obwohl es SQL schon seit mehreren Jahrzehnten gibt. Ich vermute, der Grund liegt zum Teil daran, dass die meisten Entwickler die Architektur der Datenbank nicht verstehen. Während meiner jahrelangen Arbeit bei der Einstellung von Entwicklern habe ich nur einige Male getroffen, die die Datenbank korrekt normalisieren konnten. Ehrlich gesagt kann dies eine schwierige Aufgabe sein, aber viele der von mir befragten Entwickler, die sogar SQL fließend beherrschen, verfügten nicht über Kenntnisse im Datenbankdesign.

In diesem Artikel geht es nicht um die DB-Normalisierung. Wenn du das lernen willst, habe ich dir hier kurz die Grundlagen erklärt.

Wenn Sie über eine funktionierende Datenbank verfügen, müssen Sie die Frage beantworten: „Welche Standards können angewendet werden, um die Verwendung dieser Datenbank zu erleichtern?“. Wenn diese Standards weit verbreitet sind, können Sie die Datenbank problemlos verwenden, da Sie nicht jedes Mal, wenn Sie mit einer neuen Datenbank arbeiten, neue Standardsätze studieren und sich diese merken müssen.

CamelCase-Benennung oder Unterstreichung?

Ich stoße ständig auf Datenbanken, in denen Tabellen im Stil von CustomerOrders oder customer_orders . Was ist besser zu benutzen? Vielleicht möchten Sie einen bereits etablierten Standard anwenden, aber wenn Sie eine neue Datenbank erstellen, empfehle ich die Verwendung von Unterstrichen, um die Barrierefreiheit zu erhöhen. Der Ausdruck "unter Wert" hat eine andere Bedeutung als "unter Wert", aber mit einem Unterstrich ist der erste immer unter under_value und der zweite unter undervalue . Bei Verwendung von CamelCase erhalten wir UnderValue und UnderValue , die in Bezug auf SQL, bei dem die UnderValue und UnderValue , identisch sind. Wenn Sie außerdem Sehprobleme haben und ständig mit Kopfhörern und Stiften experimentieren, um Wörter hervorzuheben, ist das Unterstreichen viel einfacher zu lesen.

Schließlich ist CamelCase schwer zu lesen für diejenigen, für die Englisch nicht muttersprachlich ist.
Zusammenfassend ist dies keine strenge Empfehlung, sondern eine persönliche Präferenz.

Plural oder Singular in Tabellennamen?

Experten der Datenbanktheorie haben lange darüber gestritten, ob Tabellen Singular (Kunde) oder Plural (Kunde) sein sollten. Lassen Sie mich diesen gordischen Knoten durchtrennen, ohne näher auf die Theorie einzugehen, und zwar einfach mit Hilfe des Pragmatismus: Pluralisierte Tabellennamen widersprechen weniger häufig reservierten Schlüsselwörtern.

Haben Sie Benutzer - users ? SQL hat das user . Benötigen Sie eine Einschränkungstabelle? constraint ist ein reserviertes Wort. Das Wort audit
reserviert, aber brauchen Sie eine audit Tabelle? Verwenden Sie einfach die Pluralform von Nomen, und die meisten reservierten Wörter werden Sie in SQL nicht stören. Sogar PostgreSQL, das einen ausgezeichneten SQL-Parser hat, ist auf dem user gestolpert.

Verwenden Sie einfach den Plural, und die Wahrscheinlichkeit von Konflikten wird viel geringer sein.

Benennen Sie die Spalte mit der ID nicht als "id"

Ich selbst habe im Laufe der Jahre gesündigt. Einmal habe ich mit einem Kunden in Paris gearbeitet, und der DBA hat sich über mich beschwert, als ich der Spalte id den Namen id . Ich dachte, er sei nur ein Pedant. Der Spaltenname customers.id ist eindeutig und customers.customer_id ist eine Wiederholung von Informationen.

Und später musste ich das debuggen:

 SELECT thread.* FROM email thread JOIN email selected ON selected.id = thread.id JOIN character recipient ON recipient.id = thread.recipient_id JOIN station_area sa ON sa.id = recipient.id JOIN station st ON st.id = sa.id JOIN star origin ON origin.id = thread.id JOIN star destination ON destination.id = st.id LEFT JOIN route ON ( route.from_id = origin.id AND route.to_id = destination.id ) WHERE selected.id = ? AND ( thread.sender_id = ? OR ( thread.recipient_id = ? AND ( origin.id = destination.id OR ( route.distance IS NOT NULL AND now() >= thread.datesent + ( route.distance * interval '30 seconds' ) )))) ORDER BY datesent ASC, thread.parent_id ASC 

Beachten Sie das Problem? Wenn SQL vollständige ID-Namen wie email_id , star_id oder station_id , werden die Fehler sofort station_id , wenn ich diesen Code station_id , und nicht später, wenn ich zu verstehen versuchte, was ich falsch gemacht habe.

Tun Sie sich selbst einen Gefallen und verwenden Sie die vollständigen Namen für die ID. Danke später

Spaltennamen

Geben Sie den Spalten so aussagekräftige Namen wie möglich. Nehmen wir an, die temperature hat nichts damit zu tun:

 SELECT name, 'too cold' FROM areas WHERE temperature < 32; 

Ich lebe in Frankreich und für uns wird eine Temperatur von 32 Grad „zu kalt“ sein. Daher ist es besser, die Spalte fahrenheit zu benennen.

 SELECT name, 'too cold' FROM areas WHERE fahrenheit < 32; 

Jetzt ist alles völlig klar.

Wenn Sie Einschränkungen für Fremdschlüssel haben, geben Sie den Spalten auf beiden Seiten der Einschränkung nach Möglichkeit den gleichen Namen. Hier ist ein perfekt durchdachtes, vernünftiges SQL:

 SELECT * FROM some_table s JOIN some_other_table o ON o.owner = s.person_id; 

Dieser Code ist wirklich in Ordnung. Wenn Sie sich jedoch die Tabellendefinition ansehen, werden Sie some_other_table.owner dass some_other_table.owner eine Fremdschlüsseleinschränkung für companies.company_id . Im Grunde genommen ist diese SQL also falsch. Es mussten identische Namen verwendet werden:

 SELECT * FROM some_table s JOIN some_other_table o ON o.company_id = s.person_id; 

Jetzt ist sofort klar, dass wir einen Fehler haben. Sie müssen nur eine Codezeile überprüfen und müssen nicht auf die Tabellendefinition verweisen.

Ich möchte jedoch darauf hinweisen, dass dies nicht immer möglich ist. Wenn Sie eine Tabelle mit einem Quell-Warehouse und einem Ziel haben, möchten Sie möglicherweise source_id mit destination_id source_id mit warehouse_id source_id vergleichen. In diesem Fall ist es besser, die Namen source_warehouse_id und destination_warehouse_id source_warehouse_id .

Beachten Sie auch, dass der owner im obigen Beispiel den Zweck besser beschreibt als company_id . Wenn Ihnen dies verwirrend erscheint, können Sie die Spalte owning_company_id . Der Name gibt dann den Zweck der Spalte an.

Vermeiden Sie Nullwerte

Dieser Rat ist vielen erfahrenen Datenbankentwicklern bekannt, aber sie sprechen leider nicht oft genug darüber: Lassen Sie aus keinem guten Grund NULL-Werte in der Datenbank zu.
Dies ist ein wichtiges, aber ziemlich kompliziertes Thema. Wir diskutieren zuerst die Theorie und dann ihre Auswirkungen auf die Architektur der Datenbank und analysieren abschließend ein praktisches Beispiel für schwerwiegende Probleme, die durch das Vorhandensein von NULL-Werten verursacht werden.

Arten von Datenbanken

Die Datenbank kann Daten verschiedener Typen enthalten : INTEGER, JSON, DATETIME usw. Der Typ ist der Spalte zugeordnet, und jeder Wert, der hinzugefügt wird, muss diesem Typ entsprechen.

Aber was ist ein Typ? Dies ist ein Name, eine Reihe gültiger Werte und eine Reihe gültiger Operationen. Sie helfen uns, unerwünschtes Verhalten zu vermeiden. Was passiert beispielsweise in Java, wenn Sie versuchen, eine Zeichenfolge und eine Zahl zu vergleichen?

 CustomerAccount.java:5: error: bad operand types for binary operator '>' if ( current > threshold ) { ^ first type: String second type: int 

Auch wenn Sie nicht bemerken, dass current > threshold unvergleichbare Typen vergleicht, wird der Compiler dies für Sie abfangen.

Ironischerweise funktionieren die Datenbanken, in denen Ihre Daten gespeichert sind - und die Ihre letzte Verteidigungslinie gegen Datenkorruption sind - fürchterlich mit Typen! Einfach nur ekelhaft. Wenn Ihre customers beispielsweise einen Ersatzschlüssel enthält, können Sie Folgendes tun:

 SELECT name, birthdate FROM customers WHERE customer_id > weight; 

Das macht natürlich keinen Sinn und in Wirklichkeit werden Sie einen Kompilierungsfehler bekommen. Viele Programmiersprachen erleichtern das Abfangen solcher Typfehler, bei Datenbanken ist das Gegenteil der Fall.

Dies ist eine normale Situation in der Datenbankwelt, wahrscheinlich weil der erste SQL-Standard 1992 veröffentlicht wurde . Computer waren in jenen Jahren langsam, und alles, was die Implementierung komplizierte, verlangsamte zweifellos die Datenbanken.

Und dann erscheinen NULL-Werte in der Szene. Der SQL-Standard hat sie nur an einer Stelle korrekt implementiert, in den IS NOT NULL IS NULL und IS NOT NULL . Da der NULL-Wert per Definition unbekannt ist, können keine Operatoren dafür entworfen werden. Und so gibt es IS NULL und IS NOT NULL anstelle von = NULL und != NULL . Und jeder Vergleich von NULL-Werten führt dazu, dass ein neuer NULL-Wert angezeigt wird.

Wenn dies für Sie seltsam klingt, ist es viel einfacher, wenn Sie "unknown" anstelle von NULL schreiben:

Der Vergleich von NULL unbekannten Werten führt zu NULL unbekannten Werten.

Ja, jetzt verstehe ich!

Was bedeutet ein Nullwert?

Ausgerüstet mit den Krümeln der Theorie betrachten wir ihre praktischen Konsequenzen.

Sie müssen einen Bonus von 500 US-Dollar an alle Mitarbeiter zahlen, deren Jahresgehalt mehr als 50.000 US-Dollar betrug.

 SELECT employee_number, name FROM employees WHERE salary > 50000; 

Und Sie wurden gerade entlassen, weil Ihr Chef mehr als 50.000 US-Dollar verdient hat, sein Gehalt jedoch nicht in der Datenbank (in der Spalte employees.salary ist NULL) vorhanden ist und der Vergleichsoperator NULL nicht mit 50.000 vergleichen kann.

Warum ist in dieser Spalte NULL? Vielleicht ist das Gehalt vertraulich. Vielleicht ist die Information noch nicht angekommen. Vielleicht ist dies ein Berater und wird nicht bezahlt. Vielleicht hat er einen Stundenlohn, kein Gehalt. Es gibt viele Gründe, warum Daten fehlen können.

Das Vorhandensein oder Fehlen von Informationen in der Spalte lässt darauf schließen, dass dies von etwas anderem abhängt und nicht von der Denormalisierung des Primärschlüssels und der Datenbank. Daher sind Spalten, in denen möglicherweise NULL-Werte vorhanden sind, gute Kandidaten für die Erstellung neuer Tabellen. In diesem Fall haben Sie möglicherweise , _ , __ usw. Sie werden immer noch entlassen, weil Sie die Gehälter blind kombiniert haben und Ihr Chef keine hat. Aber dann fängt Ihre Basis an, Sie mit genügend Informationen zu versorgen, um darauf hinzuweisen, dass das Problem mehr als eine Gehaltsfrage ist.

Und ja, es war ein dummes Beispiel, aber es war der letzte Strohhalm.

NULL-Werte führen zu logisch unmöglichen Situationen

Es mag Ihnen so vorkommen, als ob ich in Bezug auf NULL-Werte pedantisch bin. Schauen wir uns jedoch ein anderes Beispiel an, das der Realität viel näher kommt.

Vor einigen Jahren arbeitete ich in London für einen Domain-Registrar und versuchte zu verstehen, warum eine 80-zeilige SQL-Abfrage falsche Daten zurückgibt. In dieser Situation hätten Informationen definitiv zurückgegeben werden müssen, aber dies ist nicht geschehen. Ich schäme mich zuzugeben, aber ich brauchte einen Tag, um zu verstehen, dass der Grund eine solche Kombination von Bedingungen war:

• Ich habe OUTER JOIN verwendet.
  
• Sie könnten leicht NULL-Werte erzeugen.
  
• NULL-Werte können dazu führen, dass SQL eine falsche Antwort gibt.
  

Viele Entwickler kennen den letzteren Aspekt nicht. Schauen wir uns ein Beispiel aus dem Buch Database In Depth an . Ein einfaches Diagramm zweier Tabellen:

suppliers

parts


Es ist schwierig, ein einfacheres Beispiel zu finden.

Dieser Code gibt p1 .

 SELECT part_id FROM parts; 

Was wird dieser Code tun?

 SELECT part_id FROM parts WHERE city = city; 

Es wird nichts zurückgegeben, da Sie einen NULL-Wert auch nicht mit einem anderen NULL-Wert oder demselben NULL-Wert vergleichen können. Es sieht komisch aus, weil die Stadt in jeder Zeile gleich sein sollte, auch wenn wir es nicht wissen, oder? Was gibt dann den folgenden Code zurück? Versuchen Sie dies zu verstehen, bevor Sie weiterlesen.

 SELECT s.supplier_id, p.part_id FROM suppliers s, parts p WHERE p.city <> s.city OR p.city <> 'Paris'; 

Wir haben keine Zeichenfolge als Antwort erhalten, da wir die Stadt NULL ( p.city ) nicht vergleichen können und daher keiner der Zweige der WHERE zu true .

Wir wissen jedoch, dass die unbekannte Stadt entweder Paris oder nicht Paris ist. Wenn es Paris ist, ist die erste Bedingung erfüllt ( <> 'London' ). Wenn es nicht Paris ist, ist die zweite Bedingung erfüllt ( <> 'Paris' ). Daher muss die WHERE true , dies ist jedoch nicht der true Infolgedessen generiert SQL ein logisch unmögliches Ergebnis.

Es war ein Fehler, auf den ich in London gestoßen bin. Jedes Mal, wenn Sie SQL schreiben, das NULL-Werte generieren oder enthalten kann, besteht die Gefahr, dass Sie ein falsches Ergebnis erhalten. Dies kommt selten vor, ist aber sehr schwer zu identifizieren.

Zusammenfassung

• Verwenden Sie __ anstelle von CamelCase .
  
• Tabellennamen müssen im Plural sein.
  
• Geben Sie erweiterte Namen für Felder mit Bezeichnern an ( item_id anstelle von id ).
  
• Vermeiden Sie mehrdeutige Spaltennamen.
  
• Benennen Sie die Spalten mit Fremdschlüsseln nach Möglichkeit genauso wie die Spalten, auf die sie sich beziehen.
  
• Fügen Sie nach Möglichkeit allen Spaltendefinitionen NOT NULL hinzu.
  
• Vermeiden Sie nach Möglichkeit das Schreiben von SQL, das NULL-Werte generieren kann.
  

Obwohl nicht perfekt, wird dieser Datenbank-Designleitfaden Ihr Leben einfacher machen.