„Let’s Meet-Datenbank“ Beispielumsetzung  Teil 1: Modellierung

Modellierung des “Let’s Meet”-Datenbankprojekts (LS Beispiellösung Teil 1)

https://oer-informatik.de/lets_meet-beispielloesung_modellierung

https://bildung.social/@oerinformatik/110207682728246503

tl/dr; Dies ist der erste Teil (Modellierung) einer Beispiellösung für die “Let’s Meet” Datenbank-Lernsituation. Es führen viele Wege zum Ziel - der einzig richtige ist der Weg, den man selbstständig gegangen ist. Daher ist diese Variante bestenfalls als eine Ideenskizze zu verstehen, die genutzt werden kann, wenn man nicht mehr weiterkommt. Einige Schritte habe ich bewusst etwas umständlicher realisiert, um so bestimmte Techniken des Datenbankentwurfs bzw. Abfragetechniken darstellen zu können. (Zuletzt geändert am 23.05.2023)

Dieser Artikel ist Teil einer mehrteiligen Serie, in der

• die eigentliche Aufgabestellung das Ausgangsszenario vorstellt und den Datenbank-Export als Tabelle verlinkt,

• im ersten Teil der Beispiellösung das Zielsystem modelliert wird (ER-Modell, Relationenmodell, physisches Modell / DDL),

• im zweiten Teil der Beispiellösung SQL-Befehle für den Import aus der Tabellenkalkulation erstellt werden und

• im dritten Teil der Beispiellösung die Ausgangsdaten über Zeichenkettenfunktionen, Datumsfunktionen, JOINS und Vereinigung von Abfragen schließlich Daten in das neue Zielsystem übernommen werden.

Allen Artikel enden mit einem SQL-Skript, mit dem Daten als Ausgangspunkt für die nächste Etappe genutzt werden können, falls es zu Problemen bei der Eigenentwicklung kam.

Ausgangslage

Die eigentliche Aufgabestellung der “Let’s Meet” Datenbank-Lernsituation kann hier nachgelesen werden. Ein Datenbankdump liegt als Excel-Datei (hier) vor ist wirklich von unerträglicher Qualität - offensichtlich entspringt er einer Exportabfrage, die zwar alle Daten enthält, jedoch keine sinnvolle Struktur aufweist. Zudem sind die Inhalte redundant gespeichert. Wir müssen hier wohl auf vielen Ebenen Probleme lösen (siehe Beispielauszug):

Problemanalyse

Offensichtlich gibt es eine Reihe von Schritten, die erforderlich sind:

• Es muss eine sinnvolle Zieldatenbank entworfen werden,

• die vorliegenden Daten müssen in die Struktur der Zieldatenbank gebracht werden

• und die Daten müssen in diese Zieldatenbank importiert werden.

Während der Entwurf des Zielsystems noch recht einfach zu realisieren ist, gibt es für den Import der Daten in diese Struktur sehr viele Varianten, die sich in zwei grundlegende Strategien zusammenfassen lassen:

• Einlesen und Anpassen der Daten per Script. Die Logik, die die Daten aufteilt, findet im Script statt.

• Importieren der Ursprungsdaten in eine Tabelle und Normalisierung / Umformung der Daten per SQL-Befehl.

Für beide Varianten gibt es Vor- und Nachteile. Die Aufgabenstellung sieht jedoch vor, dass die Ausgangs-Datenstruktur zunächst importiert werden sollen und daraufhin mit weiteren SQL-Statements in die Zielstruktur überführt werden sollen.

Planung Teil 1: Datenbankentwurf des Zielsystems

• Konzeptuelles Datenmodell: Entity-Relationship-Modell aus bestehender Excel-Tabelle extrahieren

• Logisches Datenmodell auswählen (Spoiler: relationale DB und Relationenmodell erstellen

• Relationenmodell normalisieren

• Datenbankmanagementsystem auswählen

• Physisches Datenmodell: SQL-DDL-Befehle für Ursprungsdaten und Ziel-Datenbank erstellen

Planung Teil 2: Die Ursprungsdaten importieren (Details in einem zweiten Schritt, später)

Planung Teil 3: Die Ursprungsdaten in das Zielsystem überführen (Details in einem dritten Schritt, später)

Konzeptuelles Datenmodell: Welche Daten befinden sich in der Excel-Datei

Aus der Ursprungstabelle lassen sich direkt zwei Entitätstypen ableiten: Hobby und User. Eine Reihe von Attributen geben die Spaltenüberschriften unmittelbar her, davon sind einige mehrwertig (Name, Address):

Die Beziehung zwischen User und Hobby hat selbst ein Attribut (Priorität), es handelt sich bei der Relationship also um einen assoziativen Entitätstypen (Rechteck mit Raute). Da dieser Entitätstyp ohne User und ohne Hobby nicht existiert, habe ich ihn als schwachen Entitätstypen notiert (doppeltes Rechteck).

Außerdem müssen die mehrwertigen Attribute noch aufgelöst werden. Gender wird zudem ausgelagert, da es auf zwei verschiedene Arten berücksichtigt werden muss: Das Geschlecht, als das ein User gelesen werden will, sowie das Geschlecht der anderen User, an dem ein User Interesse hat. Mit den nötigen Kardinalitäten versehen sieht das Ganze etwa so aus:

Bleiben noch die Erweiterungen, die im Datenmodell berücksichtigt werden sollen:

• das Profilbild wird als neuer Entitätstyp Picture mit einer 1:1 Relationship zu Users notiert

• die Fotos werden über denselben Entitätstyp modelliert, jedoch mit einer 1:n Relationship

• Freundesbeziehungen werden als m:n Relationship umgesetzt

• Hobbies der anderen und deren Priorität analog zu den eigenen Hobbies mit einem schwachen assoziativen Relationshiptypen.

Die Erweiterung des Modells sieht somit so aus:

Um die einzelnen Kardinalitäten und Optionalitäten festzulegen, werden die einzelnen Beziehungen mit Hilfe von ERDish formuliert. ERDish formuliert je Relationship zwei Sätze nach dem Muster:

EACH Entity A MUST | MAY Relationship ONE AND ONLY ONE | ONE OR MORE Entity B.

Im Einzelnen ergeben sich also für jede Relationship zwischen zwei Entitätstypen zwei Sätze:

User - Hobby

EACH User MAY like ONE OR MORE Hobby.

EACH Hobby MAY be liked by ONE OR MORE User.

EACH User MAY like people having ONE OR MORE Hobby.

EACH Hobby MAY of persons may liked by ONE OR MORE User.

User - Gender

EACH User MAY be seen as ONE AND ONLY ONE Gender.

EACH Gender A MAY be the gender of ONE OR MORE User.

Ich gehe also bei diesem Modell davon aus, dass die Geschlechtszuordnung eindeutig ist und sich in Schubladen einteilen lässt (z.B. maskulin / feminin / non-binary, aber diese Liste ist offen für Erweiterungen).

Zeitgemäßer ist es, anstelle des Geschlechts einfach die Pronomen oder die Anrede zu erfassen. Da für viele auf einer Dating-Plattform das Geschlecht eine Rolle spielt, habe ich das zunächst so umgesetzt. Ich bin nicht ganz zufrieden damit und bin für Hinweise dankbar.

Die zweite Beziehung wird etwa so umgesetzt:

EACH User MAY be interested in ONE OR MORE Gender.

EACH Gender A MAY be the interest of ONE OR MORE User.

User-Picture

EACH User MAY have as a profile picture ONE AND ONLY ONE Picture.

EACH Picture MAY be the profile picture of ONE AND ONLY ONE User.

EACH User MAY own ONE OR MORE Picture.

EACH Picture MUST be owned by ONE AND ONLY ONE User.

User-User

EACH User MAY be a friend of ONE OR MORE User.

EACH User MAY be a friend of ONE OR MORE User.

Aus den ERDish-Sätzen ergeben sich v.a. Optionalitäten, die im ER-Diagramm so nicht deutlich waren. Ein angepasstes ER-Diagramm mit Optionalität und ergänzt um die Ausgangsdaten (Ursprung) sieht etwa so aus:

Logisches Datenmodell: Welche Daten befinden sich in der Excel-Datei

Zur Umwandlung in das logische Modell muss zunächst das Datenbankmodell ausgewählt werden. In unserem Fall soll es das Relationenmodell sein, daher müssen die Transformationsregeln des Relationenmodells angewendet werden:

• Aus allen Entitätstypen werden Relationen gebildet.

• In jeder Relation wird geprüft, ob es natürliche Schlüsselattribute (oder Attributgruppen) gibt, die einen Datensatz eindeutig beschreiben. Wo dies nicht der Fall ist werden künstliche Primärschlüssel (artificial primary keys) vergeben. Diese werden durch den Suffix _id gekennzeichnet.

• Die Relationen werden entgegen der Konvention nicht kleingeschrieben und im Plural beschrieben, sondern nach ungarischer Notation mit vorangestelltem tbl (für table) und englischen Bezeichnungen benannt.

• 1:n bzw. 1:nc Beziehungen werden über Schlüsselattribute auf der “n”-Seite aufgelöst. Wenn keine Optionalität vorliegt (also bei 1:n) wird zudem ein NOT NULL-Constraint notiert.

• m:n wird über eine Zwischentabelle realisiert. Im Fall des assoziativen Relationshiptypen mit einem Attribut in der Zwischentabelle. Mit Optionalität wird analog zu oben mit NOT NULL verfahren.

Erstellung des Physisches Modells: Die SQL-DDL-Befehle

Auswahl der Datentypen

Durch die Modellierung wurden bereits eine Reihe von Entscheidungen getroffen, die jetzt nur in SQL-DDL-Code umgesetzt werden müssen. Noch geklärt werden müssen die Fragen hinsichtlich der Datentypen und Constraints in der Datenbank:

Zeichenketten:

• Müssen die Daten performanceoptimiert sein (CHAR) oder speicherplatzoptimiert (VARCHAR)? Speicherplatz ist schon länger kein Problem mehr, ich wähle daher CHAR.

• Bis zu welcher Länge sollen Zeichenketten gespeichert werden? _

• Welche Zeichencodierung soll genutzt werden? Hier bietet sich UTF8 an (wir haben internationale Namensschreibweisen). UTF8 sollte ohnehin bei den meisten DBMS der Standard sein.

Indizes, Schlüssel, Constraints:

• Sollen diese als fortlaufende Nummer oder UUID realisiert werden? UUID sind zwar der Weg der Wahl, in vielen DBMS aber ab Werk noch nicht möglich. Daher zunächst laufende Nr.

• Sollen die Constraints und Trigger direkt mit umgesetzt werden?

Datumswerte:

• Welcher Datentyp soll gewählt werden?

• Welche Lokalisierung soll gewählt werden?

Constraints:

• Welche Zusicherungen müssen für einzelne Felder oder für Kombinationen aus Feldern gegeben werden? Wird im Einzelnen je Tabelle entschieden.

• In welchen Fällen ist beim Löschen eine Löschweitergabe sinnvoll und wo nicht? Wird ein User oder ein Hobby gelöscht, so sollen alle verweisenden Daten gelöscht werden. Wird ein Geschlecht gelöscht, so sollen alle Daten erhalten bleiben.

Startpunkt ist eine Datenbank. In einigen Datenbankmanangementsystemen muss diese zunächst erzeugt werden, dann muss eine neue Verbindung zu der Datenbank aufgenommen werden, da alle connections jeweils datenbankabhängig sind.

Vorbereitung der Datenbank

Die Datenbankverbindung und Vorbereitung des Frontends wird im zweiten Teil dieser Beispiellösung dargestellt. Hier soll es zunächst nur um die reine DDL-Struktur gehen. Zum Nachstellen der Befehle kann es trotzdem sinnvoll sein, bereits jetzt ein gestartetes DBMS zu haben.

CREATE DATABASE letsMeetMigrationScheme;

CREATE DATABASE letsmeetdb;
USE letsmeetdb;

CREATE SCHEMA letsMeetMigrationScheme;

CREATE DATABASE letsmeetdb;

CREATE SCHEMA letsMeetMigrationScheme;

Erstellen der Tabellen

Die Tabellen müssen in der richtigen Reihenfolge erstellt und gelöscht werden: Alle Tabellen, die keinerlei Fremdschlüssel enthalten können unabhängig erstellt werden (tblGender, tblUrsprung, tblHobby). tblUser muss nach tblGender erstellt werden, tblProfilePicture zuletzt, alle übrigen nach tblUser. Alternativ können die Tabellen zunächst erstellt werden und später die entsprechenden Foreign Key Constraints per ALTER TABLE nachgereicht werden.

Wenn mal etwas schiefgeht, möchte ich die Tabellen schnell löschen und habe die entsprechenden Befehle immer direkt mit verzeichnet. Aber beim Löschen gilt genau das Gegenteil: Ich kann tblGender, tblUser, tblHobby und tblPicture erst löschen, nachdem es keine Fremdschlüsselbeziehungen mehr gibt. Die Lösch-Befehle muss ich also in umgekehrter Reihenfolge ausführen.

Die Ursprungstabelle ist noch am einfachsten: es wird zunächst alles als Text eingelesen - ich wähle den performanceoptimierten CHAR als Datentyp, die Länge wähle ich zunächst per Bauchgefühl und muss ggf. später noch anpassen. Mit der UserID wird ein Schlüssel mit laufender Nummer erzeugt.

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUrsprung;
CREATE TABLE letsMeetMigrationScheme.tblUrsprung (
    user_id  INT PRIMARY KEY AUTO_INCREMENT,
    namen CHAR(64),      /* Vorname und Nachname nicht über 32 */
    adresse CHAR(128),   /* Ort und Straße nicht über 64 */
    telefon CHAR(16),    /* bei einer Nr. nicht über 16 */
    hobbytext CHAR(255), /* 255 better be safe than sorry... */
    email CHAR(64),      /* realistisch nicht über 64 */
    geschlecht CHAR(4),  /* m,w,nb => max 4 + Puffer */
    interesse CHAR(8),   /* m,w,nb => max 4 + Puffer */
    geburtstag CHAR(10)  /* DD.MM.YYYY => max 10 Zeichen */
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblHobby;
CREATE TABLE letsMeetMigrationScheme.tblHobby (
   hobby_id INT PRIMARY KEY AUTO_INCREMENT,
   hobbyname CHAR(127) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUrsprung;
CREATE TABLE letsMeetMigrationScheme.tblUrsprung (
    user_id  int IDENTITY(1,1) PRIMARY KEY,
    namen char(64),      /* Vorname und Nachname nicht über 32 */
    adresse char(128),   /* Ort und Straße nicht über 64 */
    telefon char(16),    /* bei einer Nr. nicht über 16 */
    hobbytext char(255), /* 255 better be safe than sorry... */
    email char(64),      /* realistisch nicht über 64 */
    geschlecht char(4),  /* m,w,nb => max 4 + Puffer */
    interesse char(8),   /* m,w,nb => max 4 + Puffer */
    geburtstag char(10)  /* DD.MM.YYYY => max 10 Zeichen */
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblHobby;
CREATE TABLE letsMeetMigrationScheme.tblHobby (
   hobby_id int IDENTITY(1,1) PRIMARY KEY,
   hobbyname CHAR(127) NOT NULL UNIQUE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUrsprung;
CREATE TABLE letsMeetMigrationScheme.tblUrsprung (
    user_id SERIAL PRIMARY KEY,
    namen CHAR(64),      /* Vorname und Nachname nicht über 32 */
    adresse CHAR(128),   /* Ort und Straße nicht über 64 */
    telefon CHAR(16),    /* bei einer Nr. nicht über 16 */
    hobbytext CHAR(255), /* 255 better be safe than sorry... */
    email CHAR(64),      /* realistisch nicht über 64 */
    geschlecht CHAR(4),  /* m,w,nb => max 4 + Puffer */
    interesse CHAR(8),   /* m,w,nb => max 4 + Puffer */
    geburtstag CHAR(10)  /* DD.MM.YYYY => max 10 Zeichen */
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblHobby;
CREATE TABLE letsMeetMigrationScheme.tblHobby (
   hobby_id SERIAL PRIMARY KEY,
   hobbyname CHAR(127) NOT NULL UNIQUE
);

tblGender umfasst lediglich die Werte m, w, nb, da ich noch nicht 100% überzeugt bin, dass das dem Anwendungsfall gerecht wird, gönne ich mir hier 8 Zeichen.

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblGender;
CREATE TABLE letsMeetMigrationScheme.tblGender (
      gender CHAR(8) PRIMARY KEY
);

tblUser nutzt im Wesentlichen die getroffenen Datentypenentscheidungen von Ursprung - die Größen der vormals zusammengefassten Attribute wurden halbiert. Birthday wird als reines Datumsfeld gespeichert. Mit gender ist es erstmals nötig, einen Fremdschlüssel zu verzeichnen (Constraint), entsprechend hat das Attribut den gleichen Datentyp wie oben.

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUser;
CREATE TABLE letsMeetMigrationScheme.tblUser (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    firstname CHAR(32) NOT NULL,
    lastname CHAR(32) NOT NULL,
    telephone CHAR(16),
    street CHAR(64),
      street_no CHAR(16),
    postcode CHAR(16),
    city CHAR(64),
    country CHAR(64),
    email CHAR(64),
    gender CHAR(8),
    birthday DATE,
    FOREIGN KEY (gender) REFERENCES letsMeetMigrationScheme.tblGender(gender)
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUser;
CREATE TABLE letsMeetMigrationScheme.tblUser (
    user_id int IDENTITY(1,1) PRIMARY KEY,
    firstname CHAR(32) NOT NULL,
    lastname CHAR(32) NOT NULL,
    telephone CHAR(16),
    street CHAR(64),
      street_no CHAR(16),
    postcode CHAR(16),
    city CHAR(64),
    country CHAR(64),
    email CHAR(64),
    gender CHAR(8),
    birthday DATE,
    FOREIGN KEY (gender) REFERENCES letsMeetMigrationScheme.tblGender(gender)
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUser;
CREATE TABLE letsMeetMigrationScheme.tblUser (
    user_id SERIAL PRIMARY KEY,
    firstname CHAR(32) NOT NULL,
    lastname CHAR(32) NOT NULL,
    telephone CHAR(16),
    street CHAR(64),
    street_no CHAR(16),
    postcode CHAR(16),
    city CHAR(64),
    country CHAR(64),
    email CHAR(64),
    gender CHAR(8),
    birthday DATE,
    FOREIGN KEY (gender) REFERENCES letsMeetMigrationScheme.tblGender(gender)
);

Die Einträge in der Tabelle tblGenderInterest sind nur für bestehende Userkonten relevant. Daher wurde beim Foreign Key Constraint für die user_id eine Löschweitergabe vorgesehen (ON DELETE CASCADE). Selbiges wäre auch beim Löschen des Geschlechts denkbar, diese Information würde ich aber sicherheitshalber behalten. Der Primärschlüssel ist hier ein zusammengesetzter, der gesondert benannt werden muss:

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblGenderInterest;
CREATE TABLE letsMeetMigrationScheme.tblGenderInterest(
    gender CHAR(8),
    user_id INT,
    PRIMARY KEY (gender, user_id),
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
    FOREIGN KEY (gender) REFERENCES letsMeetMigrationScheme.tblGender(gender)
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblGenderInterest;
CREATE TABLE letsMeetMigrationScheme.tblGenderInterest(
    gender char(8),
    user_id int,
    PRIMARY KEY (gender, user_id),
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
    FOREIGN KEY (gender) REFERENCES letsMeetMigrationScheme.tblGender(gender)
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblGenderInterest;
CREATE TABLE letsMeetMigrationScheme.tblGenderInterest(
    gender CHAR(8),
    user_id INTEGER,
    PRIMARY KEY (gender, user_id),
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
    FOREIGN KEY (gender) REFERENCES letsMeetMigrationScheme.tblGender(gender)
);

Bei tblHobby2User und tblUserLikesPeopleWithHobby gelten dieselben Überlegungen: Löschweitergabe, zusammengesetzter Primärschlüssel:

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblHobby2User;
CREATE TABLE letsMeetMigrationScheme.tblHobby2User (
    user_id INT,
    hobby_id INT,
    priority INT,
    PRIMARY KEY (hobby_id, user_id),
    FOREIGN KEY (hobby_id) REFERENCES letsMeetMigrationScheme.tblHobby(hobby_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUserLikesPeopleWithHobby;
CREATE TABLE letsMeetMigrationScheme.tblUserLikesPeopleWithHobby (
    user_id INT,
    hobby_id INT,
    priority INT,
    PRIMARY KEY (hobby_id, user_id),
    FOREIGN KEY (hobby_id) REFERENCES letsMeetMigrationScheme.tblHobby(hobby_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblHobby2User;
CREATE TABLE letsMeetMigrationScheme.tblHobby2User (
    user_id int,
    hobby_id int,
    priority int,
    PRIMARY KEY (hobby_id, user_id),
    FOREIGN KEY (hobby_id) REFERENCES letsMeetMigrationScheme.tblHobby(hobby_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUserLikesPeopleWithHobby;
CREATE TABLE letsMeetMigrationScheme.tblUserLikesPeopleWithHobby (
    user_id int,
    hobby_id int,
    priority int,
    PRIMARY KEY (hobby_id, user_id),
    FOREIGN KEY (hobby_id) REFERENCES letsMeetMigrationScheme.tblHobby(hobby_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblHobby2User;
CREATE TABLE letsMeetMigrationScheme.tblHobby2User (
    user_id INTEGER,
    hobby_id INTEGER,
    priority INTEGER,
    PRIMARY KEY (hobby_id, user_id),
    FOREIGN KEY (hobby_id) REFERENCES letsMeetMigrationScheme.tblHobby(hobby_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblUserLikesPeopleWithHobby;
CREATE TABLE letsMeetMigrationScheme.tblUserLikesPeopleWithHobby (
    user_id INTEGER,
    hobby_id INTEGER,
    priority INTEGER,
    PRIMARY KEY (hobby_id, user_id),
    FOREIGN KEY (hobby_id) REFERENCES letsMeetMigrationScheme.tblHobby(hobby_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

Bleiben noch die Tabellen der erweiterten Aufgabenstellung: die Tabelle tblFriendship modelliert die selbstreferenzierende Beziehung, entsprechend verweisen zwei Foreign Keys auf user_id. Wie bei allen Userkontenbezügen wird auch hier eine Löschweitergabe vorgesehen:

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblFriendship;
CREATE TABLE letsMeetMigrationScheme.tblFriendship(
    user_id INT,
    friend_user_id INT,
    PRIMARY KEY (friend_user_id, user_id),
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
    FOREIGN KEY (friend_user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblFriendship;
CREATE TABLE letsMeetMigrationScheme.tblFriendship(
    user_id int,
    friend_user_id int,
    PRIMARY KEY (friend_user_id, user_id),
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE NO ACTION,
    FOREIGN KEY (friend_user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE NO ACTION
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblFriendship;
CREATE TABLE letsMeetMigrationScheme.tblFriendship(
    user_id INTEGER,
    friend_user_id INTEGER,
    PRIMARY KEY (friend_user_id, user_id),
    FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
    FOREIGN KEY (friend_user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE
);

Bei tblPicture ist die Besonderheit, dass mit einem Check-Constraint sichergestellt wird, dass entweder eine URL oder ein Bild als Datei vorhanden ist. Der Dateityp von pictureData ist zum Speichern von Binärdateien vorgesehen.

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblPicture;
CREATE TABLE letsMeetMigrationScheme.tblPicture (
  picture_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  picture_url text,
  pictureData BLOB,
  FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
  CHECK ((picture_url IS NOT NULL) OR (pictureData IS NOT NULL))
 );

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblPicture;
CREATE TABLE letsMeetMigrationScheme.tblPicture (
  picture_id int IDENTITY(1,1) PRIMARY KEY,
  user_id int,
  picture_url nvarchar,
  pictureData binary,
  FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
  CHECK ((picture_url IS NOT NULL) OR (pictureData IS NOT NULL))
 );

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblPicture;
CREATE TABLE letsMeetMigrationScheme.tblPicture (
  picture_id SERIAL PRIMARY KEY,
  user_id INTEGER,
  picture_url text,
  pictureData bytea,
  FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
  CHECK ((picture_url IS NOT NULL) OR (pictureData IS NOT NULL))
 );

Bleibt schließlich noch die Tabelle tblProfilePicture. Damit diese eine 1:1 Beziehung zwischen tblUser und tblPicture abbildet, muss ein UNIQUE-Constraint gesetzt werden.

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblProfilePicture;
CREATE TABLE letsMeetMigrationScheme.tblProfilePicture(
  user_id INT PRIMARY KEY,
  picture_id INT NOT NULL UNIQUE,
  FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
  FOREIGN KEY (picture_id) REFERENCES letsMeetMigrationScheme.tblPicture(picture_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblProfilePicture;
CREATE TABLE letsMeetMigrationScheme.tblProfilePicture(
  user_id int PRIMARY KEY,
  picture_id int NOT NULL UNIQUE,
  FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE NO ACTION,
  FOREIGN KEY (picture_id) REFERENCES letsMeetMigrationScheme.tblPicture(picture_id) ON DELETE CASCADE
);

DROP TABLE IF EXISTS letsMeetMigrationScheme.tblProfilePicture;
CREATE TABLE letsMeetMigrationScheme.tblProfilePicture(
  user_id INTEGER PRIMARY KEY,
  picture_id INTEGER NOT NULL UNIQUE,
  FOREIGN KEY (user_id) REFERENCES letsMeetMigrationScheme.tblUser(user_id) ON DELETE CASCADE,
  FOREIGN KEY (picture_id) REFERENCES letsMeetMigrationScheme.tblPicture(picture_id) ON DELETE CASCADE
);

Testen der SQL-DDL-Befehle

Aus der Modellierung ergeben sich bereits eine Reihe von Testfällen, die erst mit laufendem Datenbanksystem ausgeführt werden können. Damit sie nicht in Vergessenheit geraten, sollten sie bereits notiert werden:

Fazit der Modellierung

Damit wäre die Struktur für die Ausgangstabelle (von Excel nach tblUrsprung) und das Zielsystem modelliert und per SQL-Befehl definiert. Ein fertiges SQL-Skript findet sich hier für MariaDB, hier für MS SQL Server und hier für postgres.

Beispiele für den Import (Teil 2) und die Normalisierung im Bestand werden in zwei weiteren Artikeln beschrieben.

Links und weitere Informationen

• oer-Informatik Repositories auf GitLab

Hinweis zur Nachnutzung als Open Educational Resource (OER)

Dieser Artikel und seine Texte, Bilder, Grafiken, Code und sonstiger Inhalt sind - sofern nicht anders angegeben - lizenziert unter CC BY 4.0. Nennung gemäß TULLU-Regel bitte wie folgt: “Modellierung des "Let’s Meet"-Datenbankprojekts (LS Beispiellösung Teil 1)” von oer-informatik.de (H. Stein), Lizenz: CC BY 4.0. Der Artikel wurde unter https://oer-informatik.de/lets_meet-beispielloesung_modellierung veröffentlicht, die Quelltexte sind in weiterverarbeitbarer Form verfügbar im Repository unter https://gitlab.com/oer-informatik/db-sql/lets-meet-db. Stand: 23.05.2023.

[Kommentare zum Artikel lesen, schreiben] / [Artikel teilen] / [gitlab-Issue zum Artikel schreiben]