Konsistenz in komplexer relationaler DB erhalten - Trigger oder Alternativen?

[smirk_mirkin]

Hallo, ich sitze momentan an meinem ersten richtigen Web-Projekt. Entwickelt wird ein Prüfungsverwaltungssystem mit einer (relativ) komplizierten Struktur.
Es gibt unter anderem die Entities:

• Student
• Exam
• ExamQuestion
• ExamQuestionResult (Antwort eines Studenten also fk auf Student)

Die DB ist gut gefüllt. Es sollen oft Studenten angezeigt werden, die fertig sind (also bestimmte Exams absolviert haben). Diese Information ist bereits in den Daten enthalten, jedoch wäre eine Anfrage performancetechnisch nicht realisierbar. Meine aktuelle Lösung ist, dass Student ein Attribut 'complete' (0 oder 1) bekommt und in der Application bei jeder Erstellung/Änderung/Löschung von Prüfungen oder Schemata erneut geprüft wird, ob die betroffenen Studenten fertig sind. Das ist aber auch sehr unsicher was Konsistenzerhaltung angeht. Wie löst man sowas optimal, evt. mit Triggern? Ich habe das Problem schon in dieser Anwendung mehrfach.

Grüße smirk

 

[akretschmer]

Wie löst man sowas optimal, evt. mit Triggern?

Ja.

Du hast ungefähr wohl das:

test=# create table student(id int primary key, name text, fertig bool default false);
CREATE TABLE
test=*# create table fragen (id int primary key, frage text);
CREATE TABLE
test=*# create table antworten (frage int references fragen, student int references student);
CREATE TABLE
test=*# create or replace function check_student() returns trigger as $$begin if (select count(1) from antworten where student = new.student) = (select count(1) from fragen) then update student set fertig = true where id=new.student; end if; return new; end; $$language plpgsql;
CREATE FUNCTION
test=*# create trigger trg_check_student after insert on antworten for each row execute procedure check_student();
CREATE TRIGGER
test=*# insert into student (id, name) values (1, 'student 1');
INSERT 0 1
test=*# insert into student (id, name) values (2, 'student 2');
INSERT 0 1
test=*# insert into fragen values (1, 'frage 1');
INSERT 0 1
test=*# insert into fragen values (2, 'frage 2');
INSERT 0 1
test=*# insert into fragen values (3, 'frage 3');
INSERT 0 1

Damit auch:

test=*# select * from student ;
 id |  name  | fertig
----+-----------+--------
  1 | student 1 | f
  2 | student 2 | f
(2 rows)

Nach 2 Antworten:

test=*# insert into antworten values (1,1);
INSERT 0 1
test=*# insert into antworten values (2,1);
INSERT 0 1
test=*# select * from student ;
 id |  name  | fertig
----+-----------+--------
  1 | student 1 | f
  2 | student 2 | f
(2 rows)

Unser fleißiger Student macht nun die dritte Frage:

test=*# insert into antworten values (3,1);
INSERT 0 1
test=*# select * from student ;
 id |  name  | fertig
----+-----------+--------
  2 | student 2 | f
  1 | student 1 | t
(2 rows)

Das ist natürlich ein Verstöß gegen die Normalisierung, ich würde es vermeiden, solange es geht. Alternativen wäre z.B. ein materialized View.

test=*# create materialized view student_fertig as select id, name, case when (select count(1) from fragen) = (select count(1) from antworten where student=id) then true else false end as schon_fertig from student ;
SELECT 2
test=*# select * from student_fertig ;
 id |  name  | schon_fertig
----+-----------+--------------
  2 | student 2 | f
  1 | student 1 | t
(2 rows)

und diesen bei Bedarf oder regelmäßig (zeitgesteuert) aktualisieren.

 

[smirk_mirkin]

Ja.
Das ist natürlich ein Verstöß gegen die Normalisierung, ich würde es vermeiden, solange es geht.

Triggernde Einfüge-/Änderungsoperationen sind selten, während die lesenden Zugriffe so schnell wie möglich sein sollen - das Problem sollte doch sehr häufig in dieser Form auftreten und man kann das ja auch schlecht vermeiden. Ist das Triggern also quasi so eine Art pragmatische Kompromiss-Standardlösung?

Ein "überflüssiges" Attribut mit Trigger scheint mir noch immer die beste Lösung zu sein. Ich nehme mal an, dass ein DB-Trigger immer konsistenzerhaltenden Mechanismen in der Anwendung (wie ich es momentan mache) vorzuziehen sind. Ein Materialized View würde wiederum ähnlich einem Trigger funktionieren, nur auf den ersten Blick etwas umständlicher. Zeitgesteuerte Aktualisierung fällt bei mir raus und man müsste den View auch wieder genau bei den Ereignissen aktualisieren, bei welchen man auch triggern würde.

 

[akretschmer]

Triggernde Einfüge-/Änderungsoperationen sind selten, während die lesenden Zugriffe so schnell wie möglich sein sollen - das Problem sollte doch sehr häufig in dieser Form auftreten und man kann das ja auch schlecht vermeiden. Ist das Triggern also quasi so eine Art pragmatische Kompromiss-Standardlösung?

Ohne das Problem richtig zu kennen schwer zu sagen. Dauert das SELECT denn wirklich zu lange? Explain?

 

[Distrilec]

Meine Frage ist... Braucht man überhaupt ein zusätzliches Attribut dafür ?
Die Anfrage hört sich ja erstmal ziemlich simpel an...
Solange du nicht geraden Millionen von Studenten hast, sollte das hier ziemlich fix die Antwort lieferen...

Select studenten
From   deine_examen_studenten_referenz_tabelle
Where  examen In ('Deutsch', 'Mathe', 'Englisch', 'Biologie')
Group  By studenten Having count(examen) = 4

 

[smirk_mirkin]

Explanation:

Die Daten zu einer Prüfung werden in 3 Ebenen gespeichert:

CREATE TABLE `pruef`.`exam` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`date` DATE NOT NULL,
`focus_id` INT UNSIGNED NOT NULL,
`ratingprofile_id` INT UNSIGNED,
`description` VARCHAR(50) COLLATE utf8_unicode_ci,
`released` TINYINT(1) DEFAULT 0 NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`focus_id`) REFERENCES `focus`(`id`),
FOREIGN KEY (`ratingprofile_id`) REFERENCES `ratingprofile`(`id`)
);

CREATE TABLE `pruef`.`exam_question` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`number` TINYINT(4) UNSIGNED NOT NULL,
`exam_id` INT UNSIGNED NOT NULL,
`subject_id` INT UNSIGNED NOT NULL,
`difficulty` TINYINT(4),
`selectivity` TINYINT(4),
`maxscore` TINYINT(4) UNSIGNED NOT NULL DEFAULT 1,
`question` TEXT COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
FOREIGN KEY (`exam_id`) REFERENCES `exam`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`subject_id`) REFERENCES `subject`(`id`)
);

CREATE TABLE `pruef`.`exam_question_result` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`exam_question_id` INT UNSIGNED NOT NULL,
`student_id` INT UNSIGNED NOT NULL,
`result` TINYINT(4) NOT NULL,
`devalued` TINYINT(1) DEFAULT 0 NOT NULL,
`cancelled` TINYINT(1) DEFAULT 0 NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`exam_question_id`) REFERENCES `exam_question`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`student_id`) REFERENCES `student`(`id`)
);

Welche Studenten an einer Prüfung teilgenommen haben, ist nur durch Laden der entsprechenden ExamQuestionResults möglich.
Der Prüfung ist jeweils ein Fokus zugeordnet.
Dem Student ist jeweils ein Ratingprofile (Jahrgang) zugeordnet.
Das Ratingprofile legt außerdem fest, welche Fokusse ein Student absolvieren muss, um fertig zu sein.

CREATE TABLE `pruef`.`student` (
`id` INT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
`matrikel` INT UNSIGNED NOT NULL UNIQUE,
`name` VARCHAR(50) COLLATE utf8_unicode_ci,
`prename` VARCHAR(50) COLLATE utf8_unicode_ci,
`semester` TINYINT(4) UNSIGNED,
`ratingprofile_id` INT UNSIGNED,
`complete` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
FOREIGN KEY (`user_id`) REFERENCES `user`(`id`),
FOREIGN KEY (`ratingprofile_id`) REFERENCES `ratingprofile`(`id`)
);

CREATE TABLE `pruef`.`focus` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `pruef`.`ratingprofile` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(8) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
PRIMARY KEY (`id`)
);

CREATE TABLE `pruef`.`ratingprofile_focus_map` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ratingprofile_id` INT UNSIGNED NOT NULL,
`focus_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`ratingprofile_id`) REFERENCES `ratingprofile`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`focus_id`) REFERENCES `focus`(`id`) ON DELETE CASCADE
);

Auf einer der Hauptseiten soll dann eine Tabelle mit den Studenten angezeigt werden, wobei auch alle fertigen Studenten markiert sein sollen.
Der Request mit dieser Übersicht sollte keine Ladepause > 3s generieren.
Dabei müssen dann auch noch Anrechnungen beachtet werden, deren Entities ich mir hier mal spare. Mit SELECT ist das zumindest bei dem Datenmodell einfach unmöglich. Zudem gibt es in der Application mehrere derartige Probleme, da die Strukturen eben sehr komplex und mit vielen Ausnahmen gespickt sind. Es sind zum Beispiel auch Prüfungen mit mehreren Fokussen möglich...

 

[Distrilec]

Dabei müssen dann auch noch Anrechnungen beachtet werden, deren Entities ich mir hier mal spare. Mit SELECT ist das zumindest bei dem Datenmodell einfach unmöglich. Zudem gibt es in der Application mehrere derartige Probleme, da die Strukturen eben sehr komplex und mit vielen Ausnahmen gespickt sind. Es sind zum Beispiel auch Prüfungen mit mehreren Fokussen möglich...

Wenn etwas in einem SQL-Datenmodell nicht mehr mit SQL lösbar ist... Ist dein Datenmodell einfach murks
Wenn ich auf so ein Problem stoßen würde, wäre mein erster Gedanke "naja... Zurück ans Klemmbrett".
Allerdings schreibst du ja schon das es kein Einzelfall ist, sondern häufiger vorkommt... Das heißt ich empfehle es dir umso dringlicher

 

[smirk_mirkin]

Wenn etwas in einem SQL-Datenmodell nicht mehr mit SQL lösbar ist...

Es ist doch mit SQL lösbar. Es dauert aber mehr als die angedachten 3s, wenn man das über ein SELECT-Stmt aus den Daten ermittelt.

Zudem wirkt es auf mich auch unlogisch, für eine Information die sich fast nie ändert, jedes mal eine aufwendige Anfrage anzustoßen.

Wie könnte man das Schema denn verbessern? Soll ich mal ein ER-Diagramm oder sowas hochladen?

 

[Distrilec]

Für mich ist es eher unlogisch eine Information, die sich aus den bestehenden Daten berechnen lässt, zusätzlich nocheinmal zu speichern.
Aber natürlich ist hier nichts in Stein gemeißelt und im Endeffekt auch deine Entscheidung

 

[smirk_mirkin]

Genau dieser Konflikt ist doch der Grund für diesen Thread:

Zudem wirkt es auf mich auch unlogisch, für eine Information die sich fast nie ändert, jedes mal eine aufwendige Anfrage anzustoßen.

Für mich ist es eher unlogisch eine Information, die sich aus den bestehenden Daten berechnen lässt, zusätzlich nocheinmal zu speichern.

Ich stimme Dir da vollkommen zu.
Nur was macht man, wenn die Anfrage auf den bestehenden Daten zu lange dauert? Da ist die Lösung über ein zusätzliches Attribut mit Trigger doch die beste Lösung oder?

 

[Distrilec]

Schonmal überprüft warum dein Statement so "lange" dauert?
- Index vergessen?
- Schlechte Where-/Join-Clause die keinen Index benutzt?
- Zwanzig unnötige Tabellen dazugejoint?
- Teure Berechnungen dabei laufen?

Das kann tausende Gründe haben... Einfach quick&dirty (bewusst Fehler einbauen, weil man nicht weiß wie man andere behebt) kann jeder... Wenn es nur dass wäre... Man, warum bin ich noch kein Professor der Astrophysik oder Quantenmechanik?

Ich gehe derzeit einfach davon aus, dass es an deinem Modell liegt. Basierend auf dem ganz einfachen Fakt das sich deine Anforderung erst einmal ziemlich simpel anhört.
Wenn du jetzt tausend Zeilen transponieren wolltest und dabei jede dritte mit jeder 18ten multiplizieren und durch jede 32te teilen wolltest... Dann hätte ich gesagt "JA, da lohnt sich eine zeitgesteuerte Berechnung (In Form einer gesonderten Tabelle oder Materialized View, etc), einfach weil es ad hoc zu lange dauert."... Aber das ist mMn einfach nur ein schlecht geplantes Modell.... Und das bleibt ersteinmal so, bis man mich vom gegenteil überzeugen kann 5 Tabellen joinen ist im Normalfall keine wirkliche Kunst... Solange man wenigstens die Grundlagen beachtet.

Genau dieser Konflikt ist doch der Grund für diesen Thread: [...]
Nur was macht man, wenn die Anfrage auf den bestehenden Daten zu lange dauert? Da ist die Lösung über ein zusätzliches Attribut mit Trigger doch die beste Lösung oder?

Da du aber anscheinend immer noch nicht so ganz verstanden hast worauf ich hinaus will... Um deine anfängliche Frage wirklich zu beantworten -

Wie löst man sowas optimal, evt. mit Triggern? Ich habe das Problem schon in dieser Anwendung mehrfach.

Optimal liegt hier wirklich im Auge des Betrachters.
Aus Performance-Sicht? - Sehr wahrscheinlich ja.
Aus Redundanz-Sicht? - Definitiv Nein.
In Betracht auf Normalisierung? - Definitiv Nein.
Aus meiner Sicht? - Daten die sich ad hoc berechnen lassen in einer eigenen Spalte zu verewigen, nur weil man sein Modell nicht erneuern will? Nein. Allerdings Daten die aus Gründen (von denen du bisher noch keine genannt hast) wie z.B. komplexe Berechnungen oder ähnliches die Performance einstürzen lassen... kann man durchaus so hinterlegen. Allerdings nur unter dem Umstand das man nocheinmal sämtliche möglichen Fehlerquellen durchgegangen ist und wirklich keine Möglichkeit gefunden hat diesen zu beheben. (Bei Berechnungen hilft sehr oft Mathe weiter... Terme lassen sich sehr gerne vereinfachen)

Aber wie immer... Es ist nichts in Stein gemeißelt und andere in diesem Forum sind wahrscheinlich nicht so zimperlich mit solchen "Kleinigkeiten" wie ich (Und aktive Leser haben vllt. gemerkt das der Teil "eigene Meinung" den restlichen Teil stark überwiegt...)

Aber wer beim Auto fahren den Blinker vergisst... Ne ?

 

[Tom.S]

Ich komprimiere die Antworten mal:
Welche Größenordnung hast Du? Hast Du weniger als eine Millionen Studenten dürfte Deine Abfrage kein Problem darstellen. Tut es das doch, dann dürfte es entweder an einer gravierenden Design-Schwäche oder, wahrscheinlicher, an einem fehlenden oder nicht genutzten Index liegen. Führe Dein Select-Statement mit einem vorgesetzten EXPLAIN aus, dann wird Dir angezeigt, wo ein Index fehlt. Danach dürfte sich Deine Frage erledigt haben.
Besteht das Problem jedoch weiterhin, dann ist ein Materialized View im Grunde genau das, was Du suchst.