FK mit CASCADE: Update unmöglich

[RSE]

Hallo,

ich habe ein Design entworfen, das dazu führt, dass die Institutions-ID nicht mehr verändert werden kann. Wie lässt sich das evtl. auflösen, ohne die Einschränkungen zu lockern? Oder wie würdet ihr an das Problem herangehen?

Wir haben in unserer DB Institutionen mit Mitarbeitern. Diese werden in projektübergreifenden Tabellen referenziert. Die Adress- und Namensdaten sind projektspezifisch, daher gibt es für jedes Projekt eigene Tabellen mit den Institutionen und mit den Mitarbeitern. Gleiche Institutionen verschiedener Projekte sollen über eine gleiche INSTID zuordenbar sein, gleiches gilt für die Mitarbeiter (MAID). Für diese Aufgabe habe ich folgende Tabellen entworfen:

SQL-Code:

/* Liste aller Institutionen */
CREATE TABLE INST (
  INSTID INTEGER PRIMARY KEY
);

/ * Liste aller Mitarbeiter, Zuordnung zur Institution */
CREATE TABLE MA (
  INSTID INTEGER REFERENCES INST ON UPDATE CASCADE,
  MAID   INTEGER,
  PRIMARY KEY (INSTID, MAID)
);

/* Projektspezifische Auswahl der Institutionen incl. Adressdaten */
CREATE TABLE PROJINST (
  INSTID  INTEGER REFERENCES INST ON UPDATE CASCADE, /* nur Institutionen aus INST */
  ADRESSE VARCHAR(100), /* Nutzdaten (mehrere Felder) */
  PRIMARY KEY (INSTID)
);

/* Projektspezifische Auswahl der Mitarbeiter der projektspezifischen Institutionen incl. Namensdaten */
CREATE TABLE PROJMA (
  INSTID INTEGER REFERENCES PROJINST ON UPDATE CASCADE, /* nur projektspezifische Institutionen */
  MAID   INTEGER,
  NAME   VARCHAR(100), /* Nutzdaten (mehrere Felder) */
  FOREIGN KEY (INSTID, MAID) REFERENCES MA (INSTID, MAID) ON UPDATE CASCADE, /* nur die in MA definierten Zuordnungen INSTID <-> MAID */
  PRIMARY KEY (INSTID, MAID)
);

Das Problem entsteht, wenn ich INST.INSTID ändern möchte. Dann stören sich die Update-Trigger (ON UPDATE CASCADE) für PROJINST.INSTID und die für PROJMA gegenseitig mit den FK-Restriktionen. Das wäre nur zu umgehen, wenn die Restriktionen erst wieder geprüft würden, wenn alle Updates erledigt sind.

Wie kann man dieses Problem lösen?

[shmia]

Das Feld INSTID in der Tabelle PROJMA ist redundant bzw. überbestimmt.

Jeder mit Mitarbeiter gehört zu einem best. Institut.
Primärschlüssel ist das Feld MAID.
INSTID gehört nicht zum Primärschlüssel!!

SQL-Code:

/* Liste aller Mitarbeiter, Zuordnung zur Institution */
CREATE TABLE MA (
  MAID  INTEGER,
  INSTID INTEGER REFERENCES INST ON UPDATE CASCADE,
  NAME  VARCHAR(100), /* Nutzdaten (Name, Anrede, Telefon, EMail, Gebdatum, Gehaltsstufe,...) */
  PRIMARY KEY (MAID)
);

Jedes Projekt gehört zu einem best. Institut. (sozusagen das Heimatinstitut des Projekts)
Primärschlüssel muss eine Projekt-ID sein!

SQL-Code:

/* Projektspezifische Auswahl der Institutionen incl. Adressdaten */
CREATE TABLE PROJINST (
  PROJID INTEGER,
  INSTID INTEGER REFERENCES INST ON UPDATE CASCADE, /* nur Institutionen aus INST */
  DESCRIPTION VARCHAR(100), /* Nutzdaten (mehrere Felder), nicht Adresse des Institut, sondern Beschreibung des Projekts */
  PRIMARY KEY (PROJID)
);

Mitarbeiter werden wohl dynamisch den Projekten zugewiesen.
Dabei kann ein Mitarbeiter durchaus Mitglied mehrerer Projekte sein.
Die INSTID hat in dieser Tabelle überhaupt nichts verloren.
Es könnte auch sein, dass ein Mitarbeiter einem Projekt eines anderen Instituts zugeteilt wird.
Man sollte das auf jeden Fall nicht durch die DB-Struktur verhindern.

SQL-Code:

/* Zuordnung der Mitarbeiter zu den Projekten */
CREATE TABLE PROJMA (
  MAID  INTEGER,  /* Primärschlüssel Feld 1 */
  PROJID INTEGER, /* Primärschlüssel Feld 2 */
  FOREIGN KEY ( MAID) REFERENCES MA (MAID) ON UPDATE CASCADE, 
  FOREIGN KEY ( PROJID) REFERENCES PROJINST (PROJID) ON UPDATE CASCADE, 

  STATUS INTEGER, /* z.B. 0=temporärer Projekt-MA, 1=Projektleiter,2=Projektberater*/
  PRIMARY KEY (INSTID, MAID)
);

[RSE]

Vielen Dank für deine Mühen, shmia, aber ich wollte etwas anderes abbilden, als du verstanden hast. Es gibt Institutionen (keine Institute, sondern Praxen, Apotheken etc. - Oberbegriff Institution), die Mitarbeiter haben. Deren IDs sind Projektübergreifend einheitlich (gleiche Institution = gleiche ID, gleicher MA der Institution = gleiche ID). Ein MA ist immer nur einer Institution zugeordnet (arbeitet dort).
In den Projekten geht es um diese Institutionen. Die Adressdaten haben aber projektspezifisch andere Qualität, sind daher erst in der projektspezifischen Tabelle vorhanden - die gleiche Institution kann in unterschiedlichen Projekten unterschiedlich heißen, hat aber die gleiche ID (z.B. "Richard-Von-Irgendwas-Apotheke" vs. "R. v. Irgendwas Apotheke"). Mit den Mitarbeitern verhält es sich genauso.

[shmia]

Zitat von RSE:

die gleiche Institution kann in unterschiedlichen Projekten unterschiedlich heißen, hat aber die gleiche ID (z.B. "Richard-Von-Irgendwas-Apotheke" vs. "R. v. Irgendwas Apotheke"). Mit den Mitarbeitern verhält es sich genauso.

Wenn das wirklich so ist, dann ist das ein bisschen (sehr) schizophren.
Ein Mitarbeiter hat doch immer den gleichen Namen und er wohnt am gleichen Ort.
Je nach Projekt mag er eine andere Rolle haben und vielleicht eine andere EMail, aber es bleibt doch immer der gleiche Mensch.
Die Daten, die pro Mitarbeiter unveränderlich sind (Name, Privatanschrift, Geburtsdatum, Versicherungsnr, ...)
gehören definitiv in die Mitarbeitertabelle.

Genau gleich verhält es sich auch mit den Arztpraxen und Apotheken.
Land, PLZ, Ort und Strasse sind fest gekoppelt an die Institution und gehören in die Tabelle INST.
Nehmen wir mal die Charitee in Berlin, die an mehreren Standorten arbeitet.
Rechtlich ist es ein Krankenhaus, das viele Abteilungen an versch. Standorten hat.
Somit fehlt deinem bisherigen Datenmodell die Abteilung.
Das ist der Grund weshalb es im deinem Datenmodell klemmt.

Eine Institution hat eine oder mehrere Abteilungen.
Jede Abteilung hat ein oder mehrere Mitarbeiter.
Jede Abteilung führt 0, 1 oder mehrere Projekte.
Ein Projekt hat ein oder mehrere Mitarbeiter.
Damit werden 5 Tabellen benötigt.

[RSE]

Es ist aus Datenschutzgründen nicht möglich den Institutionen und Mitarbeitern in allen Projekten die gleichen Daten zu geben, da die Daten aus unterschiedlichen Quellen kommen. Am Grundaufbau wird sich daher nichts ändern.

Folgender Aufbau scheint das Problem zu lösen:

SQL-Code:

/* Liste aller Institutionen */
CREATE TABLE INST (
  INSTID INTEGER PRIMARY KEY
);

/* Liste aller Mitarbeiter, Zuordnung zur Institution */
CREATE TABLE MA (
  INSTID INTEGER REFERENCES INST ON UPDATE CASCADE,
  MAID INTEGER,
  PRIMARY KEY (INSTID, MAID)
);

/* Projektspezifische Auswahl der Institutionen incl. Adressdaten */
CREATE TABLE PROJINST (
  INSTID INTEGER REFERENCES INST ON UPDATE CASCADE, /* nur Institutionen aus INST */
  ADRESSE VARCHAR(100), /* Nutzdaten (mehrere Felder) */
  PRIMARY KEY (INSTID)
);

/* Projektspezifische Auswahl der Mitarbeiter der projektspezifischen Institutionen incl. Namensdaten */
CREATE TABLE PROJMA (
  INSTID INTEGER, /* hier kein FK mehr <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< */
  MAID INTEGER,
  NAME VARCHAR(100), /* Nutzdaten (mehrere Felder) */
  FOREIGN KEY (INSTID, MAID) REFERENCES MA (INSTID, MAID) ON UPDATE CASCADE, /* nur die in MA definierten Zuordnungen INSTID <-> MAID */
  PRIMARY KEY (INSTID, MAID)
);

CREATE EXCEPTION BAD_INST 'Diese Institution gibt es in diesem Projekt nicht';

SET TERM ^ ;

CREATE TRIGGER PROJMA_BI0 FOR PROJMA
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if (not EXISTS(SELECT INSTID FROM PROJINST WHERE (PROJINST.INSTID = new.INSTID))) then
    exception BAD_INST;
end
^

SET TERM ; ^

Der Trigger schlägt beim Einfügen neuer Datensätze an und stellt so die Konsistenz sicher. Die einzige Möglichkeit eine Inkonsistenz herzustellen, wäre nun ein UPDATE auf PROJMA oder ein UPDATE oder DELETE auf PROJINST. Das müsste ich dann über die Rechte der DB-Benutzer ausschließen, was widerum zur Folge hat, dass ich noch eine Extra-Tabelle für die Daten bräuchte (mit FK auf die oben angegebenen Tabellen), da sonst keiner die Adress- und Namensdaten aktualisieren kann, wenn mit den Institutionen/Mitarbeitern gesprochen wird. Wie gesagt, eine Aktualisierung mit den Daten anderer Projekte ist aus datenschutzrechtlichen Gründen nicht möglich, sondern in jedem Projekt unabhängig nur bei Kontakt mit der Institution bzw. dem Mitarbeiter.

[RSE]

Die Lösung aus meinem letzten Post ist praktisch machbar, da man in FB 2.5 direkt Update-Rechte für einzelne Spalten setzen kann. Mein Benutzer hat also die Rechte in allen Tabellen INSERTs und SELECTs durchzuführen und in PROJINST und PROJMA hat er zudem ein UPDATE-Recht auf den Spalten mit den Inhalten (Namens- und Adressdaten). Ein DELETE-Recht hat nur der Admin, genauso wie ein UPDATE-Recht auf den Schlüsselfeldern von INST und MA. Damit ist die Konsistenz sichergestellt und es werden keine zusätzlichen Tabellen benötigt.