Dokumentation der Datenbank-Struktur des Musikschul-Managers

Hinweis: Erstellt mit Claude (KI-System) auf der Grundlage des Import-Skriptes für die Musikschule Grassau

Basierend auf der Analyse der Import-Skripts und unserer Konversation habe ich die Dokumentation der Tabellenstruktur und Beziehungen des Musikschul-Managers aktualisiert.

1. Quell-Tabellen Übersicht

Der Musikschul-Manager verwendet eine proprietäre Datenbankstruktur, die für den Import als CSV-Dateien exportiert wurde:

CSV-Datei	Beschreibung	Schlüsselfeld
teilein.csv	Teileinrichtungen/Mandanten	Satznummer
eltern.csv	Elterndaten	Satznummer
zahler.csv	Zahlungspflichtige	Satznummer
lehrer.csv	Lehrkräfte	Satznummer
mandat.csv	SEPA-Mandate	Satznummer
schueler.csv	Schülerdaten	Satznummer
fachber.csv	Fachbereiche	Satznummer
faecher.csv	Unterrichtsfächer	Satznummer
uort.csv	Unterrichtsorte	Satznummer
ustaetten.csv	Unterrichtsstätten	Satznummer
raum.csv	Unterrichtsräume	Satznummer
gebuehr1.csv	Gebührenordnungen (Zeiträume)	Satznummer
gebuehr2.csv	Gebührengruppen	Satznummer
gebuehr3.csv	Konkrete Gebührensätze	Satznummer, Gebuehrnummer
stundenplan.csv	Terminplanung	Satznummer
fachbelegung.csv	Fachbelegungen durch Schüler	Satznummer
schuelergebuehr.csv	Zuordnung von Gebühren zu Schülern	Satznummer
instrumente.csv	Leihinstrumente	Satznummer
instrumenteadr.csv	Instrumentenzuordnungen	Satznummer
instausleihe.csv	Instrumentenausleihe	Satznummer
krankmeldungen.csv	Lehrerkrankmeldungen	Satznummer
beurlaubung.csv	Schülerbeurlaubungen	Satznummer
buchungen.csv	Buchungen/Zahlungen	Satznummer
wliste.csv	Warteliste	Satznummer
wlistefach.csv	Fachwünsche der Warteliste	WListSatznummer
mitglied.csv	Vereinsmitglieder	Satznummer
mitgliedbeitrag.csv	Mitgliedsbeiträge	Satznummer

2. Wichtige Entitäten und ihre Beziehungen

2.1 Personen-Hierarchie

Die Personen-Daten sind auf mehrere Tabellen verteilt:

                 ┌─────────┐
                 │ eltern  │
                 └────▲────┘
                      │
┌─────────┐      ┌────┴─────┐      ┌────────┐
│ zahler  │◄─────┤ schueler │─────►│ lehrer │
└─────────┘      └────┬─────┘      └────────┘
                      │
                      ▼
               ┌────────────┐
               │ mitglied   │
               └────────────┘

Die Beziehungen sind:

Schüler haben optionale Verweise auf Eltern (eltern_satznr)
Schüler haben Verweise auf Zahler (zahler_satznr)
Mitglieder können Verweise auf Schüler oder Zahler haben

2.2 Unterrichtsstruktur

Die Organisation des Unterrichts:

┌──────────┐     ┌──────────┐     ┌─────────────┐
│ fachber  │◄────┤ faecher  │◄────┤ fachbelegung │
└──────────┘     └────┬─────┘     └──────┬──────┘
                      │                   │
                      ▼                   ▼
               ┌──────────────┐    ┌────────────┐
               │ stundenplan  │◄───┤ schueler   │
               └──────┬───────┘    └────────────┘
                      │
                      ▼
               ┌──────────────┐
               │ schuelergebuehr │
               └──────────────┘

Die Beziehungen sind:

Fächer gehören zu Fachbereichen (fachbereich)
Schüler belegen Fächer (fachbelegung.schueler → schueler.satznummer)
Fachbelegungen erscheinen im Stundenplan (stundenplan.belegung → fachbelegung.satznummer)
Gebühren werden den Schülern über schuelergebuehr zugeordnet

WICHTIG: Es gibt eine inkonsistente Namenskonvention in den Spalten, die auf fachbelegung.Satznummer verweisen:

In schuelergebuehr heißt die Spalte “Fach” (nicht zu verwechseln mit der Tabelle “faecher”!)
In stundenplan heißt die Spalte “Belegung”
In buchungen.csv wird ebenfalls “Fach” für den Verweis auf fachbelegung.Satznummer verwendet

Im Import-Skript wird diese Inkonsistenz in den temporären Tabellen bereinigt, indem die Spalte in “tmp_schuelergebuehr” in “fachbelegung” umbenannt wird.

2.3 Gebührenstruktur

Die hierarchische Organisation der Gebühren:

┌─────────┐     ┌─────────┐     ┌────────┐     ┌────────────────┐
│ gebuehr1 │◄────┤ gebuehr3 │◄───┤ gebuehr2│◄───┤ schuelergebuehr │
└─────────┘     └─────────┘     └────────┘     └────────┬───────┘
                                                        │
                                                        ▼
                                                 ┌──────────────┐
                                                 │ fachbelegung │
                                                 └──────────────┘
                                                        │
                                                        ▼
                                                 ┌──────────────┐
                                                 │ buchungen    │◄─── buchungen.Fach = fachbelegung.Satznummer
                                                 └──────────────┘

gebuehr1 definiert Zeiträume (GDatumvon, GDatumbis) verschiedener Gebührenordnungen. Es können mehrere Einträge für denselben Zeitraum mit unterschiedlichen Teileinrichtungen existieren. Jeder Eintrag repräsentiert eine bestimmte Version der Gebührenordnung, die durch ihre Satznummer identifiziert wird.
gebuehr2 dient als statische Referenztabelle für die Typen von Gebühren mit folgender fester Zuordnung:
1. Unterrichtsgebühren (reguläre Gebühren)
2. Ab- bzw. Zuschläge
3. Zusatzgebühren
4. Familienermäßigung
5. Mehrfächerermäßigung
6. Sonstige Ermäßigung
7. Einmalige Gebühren (z.B. MusiKinder, 10er-Karte, aber auch Jahresgrundgebühr)
8. Mietgebühren (für Instrumente)
gebuehr3 definiert konkrete Gebührensätze. Die Spalte “Zeitraum” verweist auf gebuehr1.Satznummer und definiert damit, zu welcher Version der Gebührenordnung der Satz gehört. Jede Gebuehrnummer kann in mehreren Zeiträumen vorkommen, wobei sich die Beschreibung oder der Betrag im Laufe der Zeit geändert haben kann.
schuelergebuehr verbindet Gebührensätze (über Gebuehrnummer) mit Fachbelegungen (über die irreführend benannte Spalte “Fach”, die auf fachbelegung.Satznummer zeigt) und Schülern (über Schueler). Die Spalte “Gebuehrgruppe” referenziert den Gebührentyp aus gebuehr2.

2.4 Räumliche Organisation

┌──────────┐     ┌──────────────┐
│ uort     │◄────┤ ustaetten    │
└────┬─────┘     └───────┬──────┘
     │                   │
     └───────────┬───────┘
                 │
                 ▼
           ┌───────────┐
           │ raum      │
           └───────────┘

Unterrichtsorte (uort) sind die übergeordneten Standorte
Unterrichtsstätten (ustaetten) sind spezifische Gebäude an diesen Orten
Räume sind konkreten Unterrichtsstätten zugeordnet

2.5 Instrumentenverleih

┌────────────┐     ┌────────────────┐     ┌─────────────┐
│ instrumente │◄────┤ instrumenteadr │────►│ instausleihe │
└─────┬──────┘     └────────────────┘     └──────┬──────┘
      │                     ▲                    │
      │                     │                    │
      │                     │                    ▼
      │                     │            ┌──────────────┐
      └──────────────────┐ │            │ schueler     │
                         │ │            └──────────────┘
                         ▼ │
                ┌──────────────────┐
                │ schuelergebuehr  │◄─── Gebuehrgruppe = 8 (Mietgebühren)
                └──────────────────┘
                         │
                         ▼
                ┌──────────────────┐
                │ buchungen        │◄─── buchungen.Fach = schuelergebuehr.Fach
                └──────────────────┘

instrumente enthält die Grunddaten der Instrumente
instrumenteadr verbindet Instrumente mit Ausleihvorgängen und verweist auf instausleihe.Satznummer
instausleihe enthält Informationen zum Ausleihenden (meist Schüler)
schuelergebuehr mit Gebuehrgruppe = 8 (Mietgebühren) verweist mit “Schueler” auf instrumenteadr.Satznummer (nicht auf schueler.Satznummer!)
In buchungen.csv wird das Feld “Fach” verwendet, um auf die fachbelegung.Satznummer zu verweisen

WICHTIG: Bei Instrumentenverleihungen gibt es eine komplexe Zuordnungskette, die in der Datenstruktur nicht eindeutig ist. Eine Fachbelegung kann mehrere Gebühren haben (z.B. reguläre Unterrichtsgebühr und Instrumentenmiete), wodurch eine eindeutige Zuordnung von Buchungen zu Verleihungen erschwert wird.

2.6 Buchungen und Zahlungen

┌─────────┐     ┌───────────────┐     ┌─────────────┐
│ zahler  │◄────┤ buchungen     │────►│ fachbelegung │
└─────────┘     └───────────────┘     └─────────────┘
                       │                     │
                       │                     │
                       ▼                     ▼
               ┌──────────────┐     ┌───────────────┐
               │ gebuehr2     │◄────┤ schuelergebuehr │
               └──────────────┘     └───────────────┘

Die Tabelle buchungen enthält alle finanziellen Transaktionen
buchungen.Zahler verweist auf zahler.Satznummer
buchungen.Fach verweist auf fachbelegung.Satznummer
Über die fachbelegung kann man mit schuelergebuehr die entsprechende Gebührenart ermitteln
Eine Fachbelegung kann mehrere verschiedene Gebührenarten haben (z.B. Unterricht, Instrumentenmiete, Jahresgrundgebühr)

Beim Import werden folgende Typen von Buchungen unterschieden:

Typ 1: Reguläre Unterrichtsgebühren (Erstabrechnung)
Typ 2: Nachberechnungen für Unterricht (enthält “Änderung” im Text)
Typ 10: Instrumentenmieten (Gebuehrgruppe = 8)
Typ 42: SEPA-Lastschriften und Datenträgeraustausch

Die korrekte Zuordnung von Buchungen erfolgt primär über das Feld buchungen.Fach, das auf fachbelegung.Satznummer verweist. Bei mehrdeutigen Zuordnungen (wenn eine Fachbelegung mehrere Gebührenarten hat) kann die buchungen.Text-Spalte zusätzliche Hinweise geben.

3. Besondere Felder und ihre Bedeutung

3.1 Gebührengruppen in schuelergebuehr

Das Feld ‘Gebuehrgruppe’ in “schuelergebuehr” verweist auf die Tabelle “gebuehr2” und hat folgende fest definierte Bedeutungen:

Unterrichtsgebühren (reguläre Gebühren)
Ab- bzw. Zuschläge
Zusatzgebühren
Familienermäßigung
Mehrfächerermäßigung
Sonstige Ermäßigung
Einmalige Gebühren (umfasst sowohl Unterrichtsangebote wie “MusiKinder” oder “10er-Karte” als auch administrative Gebühren wie “Jahresgrundgebühr”)
Mietgebühren (für Instrumente)

Diese Kategorisierung ist entscheidend für die korrekte Übernahme und Berechnung von Gebühren, Rabatten und Zuschlägen.

3.2 Gebuehrnummer in gebuehr3

Die Spalte “Gebuehrnummer” in “gebuehr3” dient als wichtiger Schlüssel für die Gebühren. Eine Gebuehrnummer kann in verschiedenen Zeiträumen unterschiedliche Werte (Beschreibung, Betrag) haben. Bei der Datenübernahme wird typischerweise die aktuellste Version (höchster Zeitraum) verwendet.

Bestimmte Gebuehrnummern haben besondere Bedeutungen:

232: Jährliche Kostenpauschale für Unterrichtsmaterialien
592: Jahresgrundgebühr
951: Rückerstattung
703: D3 Gebühr
506: D1/D2 Gebühr
588: 10er Karte
1035: MusiKinder 10x
1036: MusiKinder 8x
317: MusiKinder 12x

3.3 Satznummern als Primärschlüssel

Alle Tabellen verwenden “Satznummer” als Primärschlüssel. Bei der Datenübernahme werden diese oft durch interne IDs ersetzt, aber die Zuordnungen werden in Mapping-Arrays gespeichert (z.B. $this->schueler[$satznummer] = $schueler).

3.4 Datumsfelder

Die Daten in CSV-Dateien liegen oft in folgendem Format vor:

Datum: MM/DD/YYYY (amerikanisches Format)
Datumvon/Datumbis: verwendet für Gültigkeitszeiträume

3.5 Referenzen in schuelergebuehr

In der Tabelle “schuelergebuehr” gibt es eine wichtige Besonderheit:

Wenn Gebuehrgruppe = 1, 2, 3, 4, 5, 6, 7: Spalte “Schueler” verweist auf schueler.Satznummer
Wenn Gebuehrgruppe = 8 (Mietgebühren): Spalte “Schueler” verweist auf instrumenteadr.Satznummer

Diese unterschiedliche Bedeutung desselben Felds, abhängig vom Wert in einem anderen Feld, erschwert die eindeutige Zuordnung von Buchungen zu Verleihungen.

4. Wichtige Import-Transformationen

4.1 Personen-Konsolidierung

Im Import-Skript werden Personen (Eltern, Zahler, Lehrer, Schüler) in eine zentrale Personen-Tabelle importiert mit spezifischen Rollen.

4.2 Unterrichtsprogramme und -arten

Fächer werden zu Unterrichtsprogrammen
Kombinationen aus Fächern und Gebühren werden zu Unterrichtsarten
Stundenplan-Einträge werden zu Kursen
Fachbelegungen werden zu Kursbelegungen

4.3 Gebührentypen und Preisbezug

Die verschiedenen Gebührgruppen werden unterschiedlich behandelt:

Gebührgruppe 1 (Unterrichtsgebühren): PreisBezug = 1 (monatlich)
Gebührgruppe 7 (Einmalige Gebühren): PreisBezug = 8 (einmalig im Monat des Vertragsbeginns)

Bei der Übernahme müssen manche Gebuehrnummern ausgeschlossen werden, da sie keine echten Unterrichtsangebote darstellen:

232 (Jährliche Kostenpauschale für Unterrichtsmaterialien)
592 (Jahresgrundgebühr)
951 (Rückerstattung)

4.4 Zuordnung von Buchungen

Die Zuordnung von Buchungen erfolgt über fachbelegung.Satznummer:

Für jede Kursbelegung wird die ursprüngliche fachbelegung.Satznummer gespeichert
Bei der Übernahme von Buchungen wird buchungen.Fach verwendet, um die entsprechende Kursbelegung zu finden
Der Buchungstyp wird anhand der Gebuehrgruppe und des Textes ermittelt

4.5 Teileinrichtungen zu Mandanten

Teileinrichtungen aus dem Musikschul-Manager werden zu Mandanten im neuen System.

Wichtig: Im Musikschul-Manager (Quellsystem) sind SCHÜLER einer Teileinrichtung zugeordnet (schueler_teileinrichtung[$satznummer] = $teileinrichtung). Im MSVplus (Zielsystem) gibt es hingegen keine direkte Mandantenzuordnung der Schüler. Stattdessen werden die einzelnen KURSBELEGUNGEN einem Mandanten zugeordnet. Diese Zuordnung wird über die Tabelle mandant_kb abgebildet.

4.6 Temporäre Tabellen

Für komplexe Abfragen werden temporäre SQLite-Tabellen verwendet. Diese erleichtern die tabellenübergreifenden Abfragen, besonders für die Kursplanung. Folgende temporäre Tabellen werden angelegt:

tmp_gebuehr1 - Spiegelt Daten aus gebuehr1.csv
- Satznummer, Datum, GDatumvon, GDatumbis, Bemerkung, Teileinrichtung
tmp_gebuehr3 - Spiegelt Daten aus gebuehr3.csv
- Satznummer, Gruppe, Zeitraum, Gebuehrnummer, Beschreibung, Gebuehr, Prozent, Minuten, MinSchueler, MaxSchueler
tmp_schuelergebuehr - Spiegelt Daten aus schuelergebuehr.csv, mit Umbenennung der Spalte “Fach” zu “fachbelegung”
- Satznummer, Datum, Schueler, Gebuehrnummer, Datumvon, Datumbis, Gebuehrgruppe, fachbelegung, Automatik, Bemerkung, Betrag
tmp_fachbelegung - Spiegelt Daten aus fachbelegung.csv
- Satznummer, Datum, Schueler, Fach, Datumvon, Datumbis, Bemerkung
tmp_stundenplan - Spiegelt Daten aus stundenplan.csv
- Satznummer, Datum, Schueler, Datumvon, Datumbis, Lehrkraft, Raum, Wochentag, Zeitvon, Zeitbis, Belegung, Rhythmus, Woche, Gruppenstaerke, Beschreibung

Bei der Arbeit mit diesen Tabellen ist es wichtig, die unterschiedlichen Namenskonventionen zu beachten, insbesondere die Umbenennung der Spalte “Fach” zu “fachbelegung” in tmp_schuelergebuehr, um Verwirrung zu vermeiden.

5. Wichtige SQL-Abfragen für Datenextraktion

5.1 Stundenplan und Fachbelegungen

Eine zentrale Abfrage für die Übernahme von Kursen und Belegungen:

WITH zeitraeume AS (
    SELECT DISTINCT
        s.Satznummer as stundenplan_nr,
        s.Schueler as schueler_satznr,
        fb.Satznummer as belegung_nr,
        s.Lehrkraft as lehrer_satznr,
        s.Raum as raum_satznr,
        s.Wochentag,
        s.Zeitvon,
        s.Zeitbis,
        s.Beschreibung,
        s.Rhythmus,
        fb.Fach as fach_satznr,
        g3.Gebuehrnummer,
        g3.Minuten,
        g1.Teileinrichtung,
        -- Der effektive Zeitraum ist die Schnittmenge der Zeiträume
        CASE
            WHEN s.Datumvon > sg.Datumvon THEN s.Datumvon
            ELSE sg.Datumvon
        END as effektiv_von,
        CASE
            WHEN (s.Datumbis IS NULL OR s.Datumbis = '') AND (sg.Datumbis IS NULL OR sg.Datumbis = '') THEN NULL
            WHEN s.Datumbis IS NULL OR s.Datumbis = '' THEN sg.Datumbis
            WHEN sg.Datumbis IS NULL OR sg.Datumbis = '' THEN s.Datumbis
            WHEN s.Datumbis < sg.Datumbis THEN s.Datumbis
            ELSE sg.Datumbis
        END as effektiv_bis
    FROM
        tmp_stundenplan s
        JOIN tmp_fachbelegung fb ON fb.Satznummer = s.Belegung
        JOIN tmp_schuelergebuehr sg ON sg.fachbelegung = fb.Satznummer
        JOIN tmp_gebuehr3 g3 ON sg.Gebuehrnummer = g3.Gebuehrnummer
        JOIN tmp_gebuehr1 g1 ON g3.Zeitraum = g1.Satznummer
    WHERE
        sg.Gebuehrgruppe IN (1, 7)
        AND sg.Gebuehrnummer NOT IN (232, 592, 951)
)

5.2 Ermittlung der Gebührengruppen für eine Fachbelegung

SELECT DISTINCT Gebuehrgruppe 
FROM tmp_schuelergebuehr 
WHERE fachbelegung = ?

5.3 Rabatte und Zuschläge

Abfrage für die Ermittlung von Rabatten:

WITH aktuelle_gebuehren AS (
    -- Für jede Gebuehrnummer die aktuellste Beschreibung und den Betrag holen
    SELECT
        g3.Gebuehrnummer,
        g3.Beschreibung,
        g3.Gebuehr,
        g3.Prozent,
        g3.Zeitraum
    FROM tmp_gebuehr3 g3
    JOIN (
        SELECT
            Gebuehrnummer,
            MAX(Zeitraum) as max_zeitraum
        FROM tmp_gebuehr3
        WHERE Zeitraum IN (26)  -- nur aktuelle Zeiträume
        GROUP BY Gebuehrnummer
    ) aktuell ON g3.Gebuehrnummer = aktuell.Gebuehrnummer
        AND g3.Zeitraum = aktuell.max_zeitraum
)
SELECT
    sg.fachbelegung,
    sg.Gebuehrnummer,
    sg.Gebuehrgruppe,
    sg.Datumvon as von,
    sg.Datumbis as bis,
    sg.Bemerkung,
    ag.Beschreibung,
    ag.Gebuehr as betrag,
    ag.Prozent as ist_prozent
FROM tmp_schuelergebuehr sg
JOIN aktuelle_gebuehren ag ON ag.Gebuehrnummer = sg.Gebuehrnummer
WHERE
    sg.fachbelegung = ?  -- Parameter: Fachbelegungsnummer
    AND sg.Gebuehrgruppe IN (2, 4, 5, 6)

6. Ziel-Datenbankschema (auszugsweise)

Die Daten werden in folgende Zieltabellen überführt:

Person: Zentrale Personentabelle
Eltern, Zahler, Lehrer, Schueler: Rollen-spezifische Tabellen
Mandant: Teileinrichtungen
Standort: Unterrichtsorte
Raum: Unterrichtsräume
Unterrichtsprogramm: Fächer
Unterrichtsart: Kombination aus Programm und Gebühren
UnterrichtsartPreis: Preise für Unterrichtsarten
Kurs: Kurstermine
Kursbelegung: Teilnahme von Schülern an Kursen
Leihinstrument: Instrumente für Verleih
LeihinstVerleih: Verleihvorgänge
Pause: Krankmeldungen/Beurlaubungen
Interesse: Warteliste
zahlerkonto: Finanzielle Transaktionen (mit Typ-Zuordnung)

7. Buchungstypen im MSVplus

Im Zielsystem MSVplus werden Buchungen mit verschiedenen Typen kategorisiert:

Typ	Bedeutung	Herkunft
1	Unterricht (Erstabrechnung)	Gebührgruppe 1 oder 7
2	Nachberechnung für Unterricht	“Änderung” im Text
10	Instrumentenverleih	Gebührgruppe 8
42	SEPA-Lastschrift	“SEPA-Lastschrift” oder “Datenträgeraustausch” im Text

Für Buchungen vom Typ 1 oder 2 wird die Spalte “KbNr” mit der Kursbelegungs-ID gefüllt. Für Buchungen vom Typ 10 wird keine direkte Zuordnung zu einer Verleihung vorgenommen, da diese nicht eindeutig sein kann.

Dieses Dokument dient als Grundlage für das Verständnis der Datenbankschemata und kann bei zukünftigen Datenübernahmen oder Erweiterungen als Referenz verwendet werden.

Datenimport aus Musikschul-Manager