🖥️ Veni. Vidi. sudo rm -rf / vici.

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen: • bewerbungsnachweis
it-themen:projekt:dokumentation:bewerbungsnachweis_datenbankstruktur_sql

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

it-themen:projekt:dokumentation:bewerbungsnachweis_datenbankstruktur_sql [06.02.2026 07:58] – angelegt larsit-themen:projekt:dokumentation:bewerbungsnachweis_datenbankstruktur_sql [06.02.2026 08:11] (aktuell) – lars
Zeile 1: Zeile 1:
 [[..:start|zurück]] [[..:start|zurück]]
  
-<code sql> /* Bewerbungsnachweis – SQL-DDL Engine: InnoDB Charset: utf8mb4 Hinweis: Diese DDL entspricht der finalen Struktur (firma, stelle, bewerbung, status_verlauf, dokument + View). */+<code sql> /* ========================================================= 
 +   Bewerbungsdatenbank (MariaDB) 
 +   - deutsch benannte Tabellen und Spalten (ohne Umlaute) 
 +   - inkl. Status-Historie, Aktivitaeten, Dokumenten-Nachweisen 
 +   - Views fuer Nachweis-Export + Monatsreport 
 +   - Trigger: Statusaenderung -> Historie 
 +   ========================================================= */
  
-SET NAMES utf8mb4; +/* 1) Datenbank komplett neu */ 
-SET time_zone = '+00:00';+DROP DATABASE IF EXISTS bewerbungen;
  
--- ========================= +CREATE DATABASE bewerbungen 
--- Tabelle: firma +  CHARACTER SET utf8mb4 
--- ========================= +  COLLATE utf8mb4_unicode_ci;
-CREATE TABLE IF NOT EXISTS firma ( +
-firma_id INT UNSIGNED NOT NULL AUTO_INCREMENT, +
-name VARCHAR(200) NOT NULL, +
-ort VARCHAR(200) NULL, +
-webseite VARCHAR(300) NULL, +
-notizen TEXT NULL, +
-created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +
-PRIMARY KEY (firma_id), +
-KEY idx_firma_name (name) +
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;+
  
--- ========================= +USE bewerbungen;
--- Tabelle: stelle +
--- ========================= +
-CREATE TABLE IF NOT EXISTS stelle ( +
-stelle_id INT UNSIGNED NOT NULL AUTO_INCREMENT, +
-firma_id INT UNSIGNED NOT NULL, +
-titel VARCHAR(250) NOT NULL, +
-referenznummer VARCHAR(120) NULL, +
-stellen_url VARCHAR(500) NULL, +
-quelle_plattform VARCHAR(150) NULL, +
-ort_text VARCHAR(200) NULL, +
-arbeitsmodell VARCHAR(80) NULL, +
-beschaeftigung VARCHAR(80) NULL, +
-notizen TEXT NULL, +
-created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +
-PRIMARY KEY (stelle_id), +
-KEY idx_stelle_firma (firma_id), +
-KEY idx_stelle_titel (titel), +
-CONSTRAINT fk_stelle_firma +
-FOREIGN KEY (firma_id) REFERENCES firma(firma_id) +
-ON UPDATE RESTRICT +
-ON DELETE RESTRICT +
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;+
  
--- ========================= +/* ========================================================= 
--- Tabelle: bewerbung +   2) Tabellen 
--- ========================= +   ========================================================= */
-CREATE TABLE IF NOT EXISTS bewerbung ( +
-bewerbung_id INT UNSIGNED NOT NULL AUTO_INCREMENT, +
-stelle_id INT UNSIGNED NOT NULL, +
-bewerbungsdatum DATE NOT NULL, +
-kanal VARCHAR(40) NOT NULL, +
-aktueller_status VARCHAR(40) NOT NULL DEFAULT 'offen', +
-naechstes_nachfassen DATE NULL, +
-betreff VARCHAR(250) NULL, +
-notizen TEXT NULL, +
-created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +
-updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, +
-PRIMARY KEY (bewerbung_id), +
-KEY idx_bew_stelle (stelle_id), +
-KEY idx_bew_datum (bewerbungsdatum), +
-KEY idx_bew_status (aktueller_status), +
-KEY idx_bew_nachfassen (naechstes_nachfassen), +
-CONSTRAINT fk_bewerbung_stelle +
-FOREIGN KEY (stelle_id) REFERENCES stelle(stelle_id) +
-ON UPDATE RESTRICT +
-ON DELETE RESTRICT +
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;+
  
--- ========================= +CREATE TABLE firma ( 
--- Tabelle: status_verlauf +  firma_id      BIGINT PRIMARY KEY AUTO_INCREMENT, 
--- ========================= +  name          VARCHAR(255) NOT NULL, 
-CREATE TABLE IF NOT EXISTS status_verlauf ( +  ort           VARCHAR(120), 
-status_verlauf_id INT UNSIGNED NOT NULL AUTO_INCREMENT, +  webseite      VARCHAR(255), 
-bewerbung_id INT UNSIGNED NOT NULL, +  notizen       TEXT, 
-status VARCHAR(40) NOT NULL, +  erstellt_am   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
-status_datum DATE NOT NULL, +) ENGINE=InnoDB;
-quelle VARCHAR(80) NULL, +
-notizen TEXT NULL, +
-created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +
-PRIMARY KEY (status_verlauf_id), +
-KEY idx_sv_bew (bewerbung_id), +
-KEY idx_sv_datum (status_datum), +
-KEY idx_sv_status (status), +
-CONSTRAINT fk_statusverlauf_bewerbung +
-FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id) +
-ON UPDATE RESTRICT +
-ON DELETE RESTRICT +
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;+
  
--- ========================= +CREATE TABLE ansprechpartner ( 
--- Tabelle: dokument +  ansprechpartner_id BIGINT PRIMARY KEY AUTO_INCREMENT, 
--- ========================= +  firma_id           BIGINT NOT NULL, 
-CREATE TABLE IF NOT EXISTS dokument ( +  vollname           VARCHAR(255), 
-dokument_id INT UNSIGNED NOT NULL AUTO_INCREMENT, +  rolle              VARCHAR(255), 
-bewerbung_id INT UNSIGNED NOT NULL, +  email              VARCHAR(255), 
-typ VARCHAR(60) NOT NULL, +  telefon            VARCHAR(60), 
-dateipfad VARCHAR(600) NULL, +  notizen            TEXT, 
-url VARCHAR(600) NULL, +  erstellt_am        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
-erstellt_am_datum DATE NOT NULL, +  CONSTRAINT fk_ansprechpartner_firma 
-notizen TEXT NULL, +    FOREIGN KEY (firma_id) REFERENCES firma(firma_id) 
-created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +    ON DELETE CASCADE 
-PRIMARY KEY (dokument_id), +) ENGINE=InnoDB;
-KEY idx_doc_bew (bewerbung_id), +
-KEY idx_doc_typ (typ), +
-KEY idx_doc_datum (erstellt_am_datum), +
-CONSTRAINT fk_dokument_bewerbung +
-FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id) +
-ON UPDATE RESTRICT +
-ON DELETE RESTRICT +
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;+
  
--- ========================= +CREATE TABLE stelle ( 
--- View: v_nachweis_bewerbungen_mit_belegen +  stelle_id        BIGINT PRIMARY KEY AUTO_INCREMENT, 
--- ========================= +  firma_id         BIGINT NOT NULL, 
-DROP VIEW IF EXISTS v_nachweis_bewerbungen_mit_belegen;+  titel            VARCHAR(255) NOT NULL, 
 +  referenznummer   VARCHAR(120), 
 +  stellen_url      TEXT, 
 +  quelle_plattform VARCHAR(120),   -- z.B. StepStone, Indeed, LinkedIn, Firmenwebsite 
 +  ort_text         VARCHAR(255), 
 +  arbeitsmodell    VARCHAR(60),    -- vor Ort, hybrid, remote 
 +  beschaeftigung   VARCHAR(60),    -- Vollzeit, Teilzeit, ... 
 +  notizen          TEXT, 
 +  erstellt_am      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 +  CONSTRAINT fk_stelle_firma 
 +    FOREIGN KEY (firma_id) REFERENCES firma(firma_id) 
 +    ON DELETE CASCADE 
 +) ENGINE=InnoDB;
  
-CREATE VIEW v_nachweis_bewerbungen_mit_belegen AS+CREATE TABLE bewerbung ( 
 +  bewerbung_id        BIGINT PRIMARY KEY AUTO_INCREMENT, 
 +  stelle_id           BIGINT NOT NULL, 
 +  bewerbungsdatum     DATE NOT NULL, 
 +  kanal               VARCHAR(60) NOT NULL,      -- Email, Portal, Post, Recruiter, Telefon 
 +  aktueller_status    VARCHAR(60) NOT NULL DEFAULT 'offen', 
 +  naechstes_nachfassen DATE NULL, 
 +  betreff             VARCHAR(255), 
 +  notizen             TEXT, 
 +  erstellt_am         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 +  CONSTRAINT fk_bewerbung_stelle 
 +    FOREIGN KEY (stelle_id) REFERENCES stelle(stelle_id) 
 +    ON DELETE CASCADE, 
 +  INDEX idx_bewerbung_datum (bewerbungsdatum), 
 +  INDEX idx_bewerbung_status (aktueller_status), 
 +  INDEX idx_bewerbung_nachfassen (naechstes_nachfassen) 
 +) ENGINE=InnoDB; 
 + 
 +CREATE TABLE status_verlauf ( 
 +  status_verlauf_id BIGINT PRIMARY KEY AUTO_INCREMENT, 
 +  bewerbung_id      BIGINT NOT NULL, 
 +  status            VARCHAR(60) NOT NULL,  -- offen, absage, einladung, angebot, ... 
 +  status_datum      DATE NOT NULL, 
 +  quelle            VARCHAR(60),           -- Email, Portal, Telefon, Brief, system 
 +  notizen           TEXT, 
 +  erstellt_am       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 +  CONSTRAINT fk_statusverlauf_bewerbung 
 +    FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id) 
 +    ON DELETE CASCADE, 
 +  INDEX idx_statusverlauf_bewerbung_datum (bewerbung_id, status_datum) 
 +) ENGINE=InnoDB; 
 + 
 +CREATE TABLE aktivitaet ( 
 +  aktivitaet_id     BIGINT PRIMARY KEY AUTO_INCREMENT, 
 +  bewerbung_id      BIGINT NOT NULL, 
 +  typ               VARCHAR(60) NOT NULL,  -- Nachfassen, Telefonat, Interview, Mail, Test, ... 
 +  zeitpunkt         DATETIME NOT NULL, 
 +  ergebnis          VARCHAR(120), 
 +  notizen           TEXT, 
 +  erstellt_am       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 +  CONSTRAINT fk_aktivitaet_bewerbung 
 +    FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id) 
 +    ON DELETE CASCADE, 
 +  INDEX idx_aktivitaet_bewerbung_zeitpunkt (bewerbung_id, zeitpunkt) 
 +) ENGINE=InnoDB; 
 + 
 +CREATE TABLE dokument ( 
 +  dokument_id   BIGINT PRIMARY KEY AUTO_INCREMENT, 
 +  bewerbung_id  BIGINT NOT NULL, 
 +  typ           VARCHAR(60) NOT NULL,  -- Anschreiben, Lebenslauf, Stellenanzeige, Bestaetigung, Absage, ... 
 +  dateipfad     TEXT,                 -- z.B. /srv/bewerbungen/docs/.... 
 +  url           TEXT, 
 +  erstellt_am_datum DATE NOT NULL, 
 +  notizen       TEXT, 
 +  erstellt_am   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 +  CONSTRAINT fk_dokument_bewerbung 
 +    FOREIGN KEY (bewerbung_id) REFERENCES bewerbung(bewerbung_id) 
 +    ON DELETE CASCADE, 
 +  INDEX idx_dokument_bewerbung_typ (bewerbung_id, typ) 
 +) ENGINE=InnoDB; 
 + 
 +/* ========================================================= 
 +   3) Views (Export / Nachweis / Report) 
 +   ========================================================= */ 
 + 
 +CREATE OR REPLACE VIEW v_nachweis_bewerbungen AS
 SELECT SELECT
-b.bewerbung_id AS bewerbung_id, +  b.bewerbung_id, 
-b.bewerbungsdatum AS bewerbungsdatum, +  b.bewerbungsdatum, 
-f.name AS firma, +  f.name AS firma, 
-s.titel AS stelle, +  s.titel AS stelle, 
-b.kanal AS kanal, +  b.kanal, 
-b.aktueller_status AS status, +  b.aktueller_status AS status, 
-b.naechstes_nachfassen AS naechstes_nachfassen, +  b.naechstes_nachfassen, 
-s.referenznummer AS referenznummer, +  s.quelle_plattform AS quelle, 
-COUNT(d.dokument_id) AS anzahl_belege+  s.referenznummer, 
 +  s.stellen_url
 FROM bewerbung b FROM bewerbung b
 JOIN stelle s ON s.stelle_id = b.stelle_id JOIN stelle s ON s.stelle_id = b.stelle_id
-JOIN firma f ON f.firma_id = s.firma_id+JOIN firma f  ON f.firma_id  = s.firma_id; 
 + 
 +CREATE OR REPLACE VIEW v_nachweis_bewerbungen_mit_belegen AS 
 +SELECT 
 +  b.bewerbung_id, 
 +  b.bewerbungsdatum, 
 +  f.name AS firma, 
 +  s.titel AS stelle, 
 +  b.kanal, 
 +  b.aktueller_status AS status, 
 +  b.naechstes_nachfassen, 
 +  s.quelle_plattform AS quelle, 
 +  s.referenznummer, 
 +  COUNT(d.dokument_id) AS anzahl_belege 
 +FROM bewerbung b 
 +JOIN stelle s ON s.stelle_id = b.stelle_id 
 +JOIN firma f  ON f.firma_id  = s.firma_id
 LEFT JOIN dokument d ON d.bewerbung_id = b.bewerbung_id LEFT JOIN dokument d ON d.bewerbung_id = b.bewerbung_id
 GROUP BY GROUP BY
-b.bewerbung_id, +  b.bewerbung_id, b.bewerbungsdatum, f.name, s.titel, b.kanal, 
-b.bewerbungsdatum, +  b.aktueller_status, b.naechstes_nachfassen, s.quelle_plattform, s.referenznummer;
-f.name, +
-s.titel, +
-b.kanal, +
-b.aktueller_status, +
-b.naechstes_nachfassen, +
-s.referenznummer; +
-</code>+
  
-Hinweise zur DDL+CREATE OR REPLACE VIEW v_report_monat AS 
 +SELECT 
 +  DATE_FORMAT(bewerbungsdatum, '%Y-%m') AS monat, 
 +  COUNT(*) AS bewerbungen_total, 
 +  SUM(aktueller_status='offen') AS offen, 
 +  SUM(aktueller_status='nachfassen_geplant') AS nachfassen_geplant, 
 +  SUM(aktueller_status='rueckmeldung') AS rueckmeldung, 
 +  SUM(aktueller_status='einladung') AS einladung, 
 +  SUM(aktueller_status='absage') AS absage, 
 +  SUM(aktueller_status='angebot') AS angebot, 
 +  SUM(aktueller_status='zurueckgezogen') AS zurueckgezogen, 
 +  SUM(aktueller_status='keine_antwort') AS keine_antwort 
 +FROM bewerbung 
 +GROUP BY DATE_FORMAT(bewerbungsdatum, '%Y-%m') 
 +ORDER BY monat DESC;
  
-RESTRICT bei FKs: verhindert versehentliche Loeschungen (amtstauglich = kein Datenverlust).+</code>
  
-aktueller_status ist bewusst redundant (Performance + einfache Auswertungen); Historie liegt in status_verlauf.+<WRAP center round box 60%> 
 +**Hinweise zur DDL 
 +** 
 +  * RESTRICT bei FKs: verhindert versehentliche Loeschungen (amtstauglich = kein Datenverlust). 
 +  * aktueller_status ist bewusst redundant (Performance + einfache Auswertungen); Historie liegt in status_verlauf. 
 +  * dokument.dateipfad und dokument.url koennen optional sein; mindestens eines sollte befuellt sein (Validierung macht PHP). 
 +</WRAP>
  
-dokument.dateipfad und dokument.url koennen optional sein; mindestens eines sollte befuellt sein (Validierung macht PHP). 
it-themen/projekt/dokumentation/bewerbungsnachweis_datenbankstruktur_sql.1770361128.txt.gz · Zuletzt geändert: von lars