[mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

[afr0kalypse]

hi zusammen
ich habe mal eine verständnisfrage:

ich habe 2 tabellen:
tbl_user:
- user_id
- username
- passwort
- abteilungs_id

tbl_abteilung:
- abteilungs_id
- abteilungs_name
- abteilungs_chef

in der tabelle user steht nun der fremdschlüssel der abteilung, zu der der user gehört.
in der tabelle abteilung steht der fremdschlüssel des users, der chef ist.

wie man sieht, zeigen hier 2 fremdschlüssel auf gegenüberliegende tabellen.

ich bin mir sicher, dass ich mal gelesen habe, dass man so nicht designed oder so nicht designen soll...
aber ich finde die passende erklärung nicht, warum man das so nicht machen soll...
kann mir da wer weiter helfen und die erklärung liefern?

mfg

 

[aliasltb]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

1. Sag nicht designen, das klingt schlimm und ist es nicht.

2. Es wäre gut zu wissen in welchem Zusammenhang du das benötigst.

3. Ich versuche mal nen Ansatz mit einem Beispiel aus dem Hotelgewerbe:

Die Küche/Abteilung hat ID 1 in der Tabelle abteilung (abteilungs_id = 1).
Demnach erhalten alle Beschäftigten der Tabelle User den Wert 1 unter abteilungs_id.

Als nächstes kommt bspw die Wäscherei mit der ID 2 (abteilungs_id = 2).
Auch hier erhalten wieder alle Beschäftigten der Tabelle User die ID der Abteilung, demnach abteilungs_id = 2.

Das ganze geht so weiter bis man alle seine Abteilungen eingetragen hat.


Was aber wenn aus irgendeinem Grund bspw die abteilungs_id der Wäscherei zu 50 wird und die der Küche zu 2? Dann werden auf einmal alle Beschäftigten der Wäscherei der Küche zugeordnet.

Da musst du aufpassen. Wenn man ein Feld in einer Tabelle ändert wird nicht automatisch woanders das gleiche getan, wo derselbe Wert stehen müsste.

 

[rest5]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Also ich würde mal sagen, das Konstrukt wäre dann sinnlos, wenn jede Abteilung nur aus einem Mitarbeiter bestünde, der zugleich Abteilungsleiter ist.
Aber in der Form wie Du es darstellst, sehe ich da eigentlich kein Problem.
Ich bin aber gespannt, ob hier noch jemand einen Haken findet.

 

[rest5]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

1. Sag nicht designen, das klingt schlimm und ist es nicht.

2. Es wäre gut zu wissen in welchem Zusammenhang du das benötigst.

3. Ich versuche mal nen Ansatz mit einem Beispiel aus dem Hotelgewerbe:

Die Küche/Abteilung hat ID 1 in der Tabelle abteilung (abteilungs_id = 1).
Demnach erhalten alle Beschäftigten der Tabelle User den Wert 1 unter abteilungs_id.

Als nächstes kommt bspw die Wäscherei mit der ID 2 (abteilungs_id = 2).
Auch hier erhalten wieder alle Beschäftigten der Tabelle User die ID der Abteilung, demnach abteilungs_id = 2.

Das ganze geht so weiter bis man alle seine Abteilungen eingetragen hat.


Was aber wenn aus irgendeinem Grund bspw die abteilungs_id der Wäscherei zu 50 wird und die der Küche zu 2? Dann werden auf einmal alle Beschäftigten der Wäscherei der Küche zugeordnet.

Da musst du aufpassen. Wenn man ein Feld in einer Tabelle ändert wird nicht automatisch woanders das gleiche getan, wo derselbe Wert stehen müsste.

Ich verstehe den Zusammenhang zur Frage nicht.

 

[Duddle]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Es kommt darauf an, wie die Beziehung nun tatsächlich ist:

Fall 1: Eine Abteilung hat exakt einen Chef, ein Mitarbeiter kann nur von exakt einer Abteilung der Chef sein.
Fall 2: Eine Abteilung kann mehrere Chefs haben, ein Mitarbeiter kann nur von exakt einer Abteilung der Chef sein.
Fall 3: Eine Abteilung hat exakt einen Chef, ein Mitarbeiter kann von mehreren Abteilungen der Chef sein.
Fall 4: Eine Abteilung kann mehrere Chefs haben, ein Mitarbeiter kann von mehreren Abteilungen der Chef sein.

Im ersten Fall hast du eine 1:1-Beziehung. Ich zitiere Wikipedia für die Auflösung dieses Falls:

Hier wird der Primärschlüssel einer der beiden Tabellen als Fremdschlüssel der anderen Tabelle in eine zusätzliche Spalte aufgenommen. Bei welcher der Tabellen das geschieht, ist technisch irrelevant. Praktisch versucht man die reale Abhängigkeit darzustellen, indem man den Primärschlüssel der Master-Tabelle in eine zusätzliche Spalte der Detail-Tabelle aufnimmt. Zusätzlich muss sichergestellt werden, dass die Werte in der Spalte mit dem Fremdschlüssel nur einmal vorkommen. (Z. Bsp. durch Trigger,UNIQE-Constraints o.ä.)

Im zweiten und dritten Fall hast du eine 1:n-Beziehung, die anders aufgelöst wird:

Die Detail-Tabelle erhält eine zusätzliche Spalte, die als Fremdschlüssel den Primärschlüssel der Master-Tabelle aufnimmt. Bei einer 1:n-Beziehung nennt man das Attribut, das mehrere „Instanzen“ haben kann, also jenes der n-Seite 'Mehrwertiges Attribut'.

Im letzten Fall hast du eine n:m-Bezeiehung, für die du eine Hilfstabelle benötigst:

m:n-Beziehungen können in den meisten relationalen Datenbanken nicht direkt umgesetzt werden. Zur Realisierung wird eine zusätzliche Tabelle erstellt, die die Primärschlüssel beider Tabellen als Fremdschlüssel enthält. Die m:n-Beziehung wird also aufgelöst, und man erhält eine weitere Datenbanktabelle, die zwei 1:n-Beziehungen realisiert.

Oft werden für die Bezeichnung der die m:n-Beziehung realisierenden Tabelle die Bezeichnungen der zwei daran beteiligten Tabellen verknüpft. Gibt es zwei Tabellen "Student" und "Professor", kann die weitere Tabelle "StudentProfessor" heißen.

Es gibt keinen Fall, in dem eine Beziehung über zwei Fremdschlüssel - wie in deinem Beispiel - aufgelöst wird. Du hast also einen Entwurfsfehler gemacht. Das führt automatisch zu Fehlern in der Implementation und erschwert schließlich die Weiterentwicklung/Wartung. Wenn meine Datenbankausbildung nicht schon so weit zurückliegen würde, könnte ich dir auch genau sagen, warum du es "nicht so machen" solltest. So wie es jetzt ist, verletzt es sehr wahrscheinlich eine der drei Normalformen


Duddle

 

[rest5]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Wenn ich es richtig verstehe, stimmt Deine Konstruktion des ersten Falls nicht so ganz, da ja alle Mitarbeiter einer Abteilung erfasst werden sollen und nicht nur die Chefs.
Oder habe ich was nicht kapiert?

 

[Duddle]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Ich habe die möglichen Fälle für die Beziehung "Chef von einer Abteilung" aufgelistet.

Eine andere Beziehung wäre "Mitarbeiter in einer Abteilung". Für diese wären die Fälle:
1) Ein Mitarbeiter ist in exakt einer Abteilung, eine Abteilung hat exakt einen Mitarbeiter.
2) Ein Mitarbeiter kann in mehreren Abteilungen sein, eine Abteilung hat exakt einen Mitarbeiter.
3) Ein Mitarbeiter ist in exakt einer Abteilung, eine Abteilung kann mehrere Mitarbeiter haben.
4) Ein Mitarbeiter kann in mehreren Abteilungen sein, eine Abteilung kann mehrere Mitarbeiter haben.

Offensichtlich sind 3) und 4) eher in der realen Welt anzutreffen als 1) oder 2). Aber diese Fälle sagen überhaupt nichts darüber aus, wer nun Chef der jeweiligen Abteilung ist. Die zwei oben genannten Beziehungen sind voneinander getrennt zu betrachten.


Duddle

 

[afr0kalypse]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

also das konstrukt sollte so aussehen:
ein mitarbeiter gehört zu genau einer abteilung
eine abteilung besteht aus n mitarbeitern

1:n

1 mitarbeiter ist chef von genau einer abteilung
eine abteilung hat immer einen chef gleichzeitig. der chef kann (soll) aber (täglich) wechseln.

1:1

wenn ich das richtig verstehe, würde dieses konstrukt erlauben, dass ein chef einer fremden abteilung zugeordnet werden kann und somit die referentielle integrität nicht mehr gewährleistet ist.

wie kann ich aber nun gewährleisten, dass 1 user eindeutig einer abteilung zugeordnet ist und gleichzeitig ein user chef der gleichen abteilung ist, der er angehört?
es darf also datenbankseitig nicht erlaubt werden, einen mitarbeiter als chef einer fremden abteilung zuzuordnen und es darf nicht erlaubt werden, mehr als einen chef einer abteilung zuzuordnen....

ich suche etwas wie einen zusammengesetzten primärschlüssel (user_id und abteilungs_id) aus der tabelle user, der diese zusammensetzung in einer zusätzlichen tabelle abteilungs_chef steht und dort eindeutig einen abteilungschef stehen hat

(ich hoffe man versteht was ich meine)
in mssql gibts wohl sowas, was vielleicht der richtige ansatz ist:

t1: (col1, col2)
primary key (col1,col2)

t2: (col1 null, col2 null)
foreign key (col1,col2) references t1

If t1 contains one row:

col1 col2
---- ----
A 2
----------------------------

 

[pluspiano]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Richtig unlogisch erscheint mir das dargestellte Problem nur bei den ICH-AGs, wo tbl_user-username gleich tbl_abteilung-abteilungs_name ist. Username und Abteilungsname sollten vllt. noch alphabethisch aufgeschlüsselt werden. Meistens reichen die ersten drei Buchstaben. :uhm:

 

[afr0kalypse]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Richtig unlogisch erscheint mir das dargestellte Problem nur bei den ICH-AGs, wo tbl_user-username gleich tbl_abteilung-abteilungs_name ist. Username und Abteilungsname sollten vllt. noch alphabethisch aufgeschlüsselt werden. Meistens reichen die ersten drei Buchstaben. :uhm:

Also das versteh ich mal gar nicht

ich denke die lösung ist nur durch erreichbar....
mal schauen wie man foreign key constraints so benutzt...

 

[Duddle]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Ich habe mal die Unterlagen rausgekramt, die sehr detailiert auf die Übersetzung von ERM in das relationale Modell eingehen. Wenn ich es methodisch durchführe, komme ich auf eine ähnliche Lösung wie deine, nur mit Constraints (die du ja selbst schon entdeckt hast). Das heißt, meine initiale Argumentation (von wegen Entwurfsfehler) war wohl falsch.

Mein Ergebnis ist folgendes:
Abteilung(abteilungs_id, abteilungs_name, abteilungs_chef NOT NULL & UNIQUE -> Mitarbeiter.user_id)
Mitarbeiter(user_id, username, passwort, abteilungs_id NOT NULL -> Abteilung.abteilungs_id)

Dabei sind die unterstrichenen Attribute Primärschlüssel. Die kursiven Attribute sind jeweils Fremdschlüssel mit den genannten Constraints. Die Begründung ist wie folgt:
- abteilungs_chef ist NOT NULL, damit jede Abteilung gesichert einen Chef hat (also mindestens einen) und UNIQUE, damit nie ein Mitarbeiter Chef von mehr als einer Abteilung ist (sonst wären hier mehrere gleiche Werte, das verletzt UNIQUE)
- abteilungs_id ist NOT NULL, damit jeder Mitarbeiter gesichert in einer Abteilung ist, also in mindestens einer. UNIQUE darf hier offensichtlich nicht gesetzt werden, sonst könnten nicht mehrere Mitarbeiter in der gleichen Abteilung sein.

Meiner Meinung nach sollte das die korrekte Lösung sein, du solltest jedenfalls keinen fehlerhaften Eintrag machen können, ohne dass die DB meckert.


Duddle

 

[pluspiano]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

Hallo Duddle,

die von Dir skizzierte Beziehungs-Logik ist im allgemeinen äusserst hilfreich, um sich einem konkreten Problem zu nähern. Dank dafür, selten so kompakt dargestellt gelesen.

Im hier vorliegenden Fall mag durch ein vorangestelltes Präfix "A_" für Arbeitgeber sowie "U_" für Mitarbeiter geltend für tbl_abteilung-abteilungs_id bzw. tbl_user-user_id ausreichen.

Conditions wie

Abteilung(abteilungs_id, abteilungs_name, abteilungs_chef NOT  NULL & UNIQUE -> Mitarbeiter.user_id)
Mitarbeiter(user_id, username, passwort, abteilungs_id NOT  NULL -> Abteilung.abteilungs_id)

sichern dabei die DB ab.

 

[afr0kalypse]

AW: [mysql] Fremdschlüssel zeigen auf gegenüberliegende tabellen

hallo duddle und danke für die antwort
aber das funktioniert so leider nicht.

beispiel:
abteilung1: mitarbeiter_a, mitarbeiter_b
abteilung2: mitarbeiter_c, mitarbeiter_d

es hindert mich nichts daran, in die tabelle abteilung für abteilung1 mitarbeiter_c als chef einzutragen. das ist genau das problem was durch diese konstellation mit den gegenüberliegenden fremdschlüsseln entsteht.

die passende lösung habe ich aber nun gefunden

tbl_mitarbeiter(user_id (PK), name, passwort, abteilungs_id (PK + FK))
tbl_abteilungen(abteilungs_id (PK), abteilungs_name, ...)
tbl_abteilungs_chef(fk_mitarbeiter_id (zusammengesetzter FK aus user_id und abteilungs_id von tbl_mitarbeiter), fk_abteilungs_id(FK aus tbl_mitarbeiter unique not null))

die hilfstabelle tbl_abteilungs_chef ist so aufgebaut, dass das feld fk_mitarbeiter_id die abteilungs id enthält und somit auch nur im feld fk_abteilungs_id die richtige abteilungs id zugewiesen werden kann. andernfalls gibts nen constraint error.
durch dies unique not null attribute kann so immer nur ein mitarbeiter gleichzeitig in einer abteilung chef sein

vg
blunt