Beziehungen geschickt abbilden um Integrität von vornherein zu wahren?

[sticky bit]

Habe da gerade ein designtechnisches Problem...

folgende Entitäten sind gegeben:

• Kunde
• Kontaktperson
• Termin

folgende Anforderungen sollen erfüllt werden:

• zu einem Kunden können mehrere Termin gehören aber ein Termin gehört immer zu genau einem Kunden
• zu einem Kunden können mehrere Ansprechpartner gehören aber ein Ansprechpartner gehört immer zu genau einem Kunden
• zu einem Termin kann ein Kunde gehören
• zu einem Termin können mehre Ansprechpartner gehört (und jetzt kommt das Problem) diese müssen aber zu dem Kunden gehören der zu dem Termin gehört

OK, also im Anhang ist ein ER-Diagramm wie ich drauf gekommen bin das man das machen könnte, andere Ideen hat ich bis jetzt keine...

Das ganze in Tabellen umgesetzt sieht dann etwa so aus (Attribute (ausser Schlüssel), Datentypen, etc. spar ich mir):

    ._______________.
    | Kontaktperson |      ._________.
   1+---------------+      | Kunde   |
.---| id (PK)       |n    1+---------+1
|   | kunde (FK)    |------| id (PK) |----------------.
|   '---------------'      '---------'                |
|                                                     |
|   ._________________________.      .____________.   |
|   | TerminKontaktperson     |      | Termin     |   |
|   +-------------------------+n    1+------------+   |
|  n| termin (PK) (FK)        |------| id (PK)    |n  |
'---| kontaktperson (PK) (FK) |      | kunde (FK) |---'
    '-------------------------'      '------------'

Schön und gut, so umgesetzt ist es aber möglich, dass wärend in der Tabelle `Termin` auf einen Eintrag in der Tabelle `Kunden` gezeigt wird, in der Tabelle `TerminKontaktperson` jedoch auf diesen Eintrag in `Termin` wird während auf einen Eintrag in `Kontaktperson` verwiesen wird der nicht auf den selben Eintrag in `Kunden` verweist, also Beispiel:

 ________.
/ Termin |
+------------.
| id | kunde |
+----+-------+
|  1 |     1 |
'----+-------'
 _______.
/ Kunde |
+-------+
| id |
+----+
|  1 |
+----+
|  2 |
'----'
 _______________.
/ Kontaktperson |
+---------------+
| id | kunde |
+----+-------+
|  1 |     2 |
'----+-------'
 _____________________.
/ TerminKontaktperson |
+------------------------.
| termin | kontaktperson |
+--------+---------------+
|      1 |             1 |
'--------+---------------'

Hier ist der Termin 1 dem Kunden 1 zugehörig, gleichzeitig jedoch ist die Kontaktperson 1 dem Termin 1 zugewiesen obwohl diese Kontaktperson nicht dem Kunden 1 angehört...


Kann man das irgendwie vom Design her schon auflösen ohne dass die Abbildungsmöglichkeiten dahinter (siehe Anforderungen am Anfang) verloren gehen?

Und bitte, ich weiss, was Check-Constraints sind, auf sowas will ich nicht hinaus, so wirds zur Not gelöst wenns nicht anders geht.
Mir ginge das darum, dass das ganze so umgeformt werden kann, dass es nur durch Foreign-Key-Constraints abgefangen werden kann...


Danke für jeden Hinweis!

[mwanaheri]

Unter der Voraussetzung, dass zu einem Termin jeweils alle Ansprechpartner eines Kunden gehören, sollte das so gehen:

--Kunden--
kundenid (pk)
weitere Daten

--Mitarbeiter--
mitarbid (pk)
weitere Daten

--Betreuung--
mitarbeiterid(pk)(fk --Mitarbeiter--)
kundenid (fk --Kunden--)
weitere Daten

--Termin--
id (pk)
datum
kundenid(fk --Kunden--)

Kunden und Mitarbeiter sind unabhängige Entitäten. Die Betreuungstabelle nimmt die Mitarbeiter_id als Primärschlüssel und als Fremdschlüssel, so dass sichergestellt ist, dass ein Mitarbeiter nur einem Kunden zugeordnet ist.
Die zu einem Termin gehörenden Mitarbeiter ergeben sich dann aus dem Kundeneintrag zum Termin.

termin join betreuung on termin.kundenid = betreuung.kundenid

Wenn nicht alle Ansprechpartner automatisch zu einem Termin gehören, wird es natürlich komplizierter.

[sticky bit]

Das Ding ist gerade eben, dass zu einem Termin 0 bis 1 Kunde gehören kann. Und, wenn ein Kunde zu einem Termin gehört, können 0 bis alle Kontaktpersonen des Kunden zum Termin gehören. Also es gehöhren nicht automatisch alle Kontaktpersonen des Kunden zu einem Termin wenn der Kunde zum Termin gehört.

Sonst würde es in der Tat reichen in der Relation `Termin` einen Fremdschlüssel auf die `Kunde` zu haben.

P.S.: Weiteres Missverständnis, Kontaktperson meint einen Mitarbeiter des Kunden (Kunden sind Firmen keine Einzelpersonen). Es reicht also aus, wenn eine Kontaktperson zu nur einem Kunden gehören kann, da es äussert unüblich ist, dass einer für zwei Firmen arbeitet (und selbst wenn so was mal kommt wird er höchstwahrscheinlich trotzdem für jede Firma eine eigene Adresse, Telefondurchwahl, etc. haben es sei denn es sei ein externer, der auf eigene Rechnung von seinem eigenen Büro aus arbeitet, aber das muss nicht berücksichtigt werden). Daraus folgt, das es genügt in der Relation `Kontaktperson` einen Fremdschlüssel auf `Kunden` zu haben um abbilden zu können zu welchem Kunde die Kontaktperson gehört. Eine Tabele zwischen `Kunde` und `Kontaktperson` ist also eigentlich nicht nötig, es sei denn sie hilft irgendwie das Problem zu lösen, was ich gerade aber nicht erkennen könte?

[elrond]

ich löse so etwas wie folgt:

  termin                   kunde                    person
  -------                  ---------                --------------
  terminid (PK)      /=> n kundeid (PK)      /==> n personid (PK)
  kundeid (FK) 1 ===/      personid (FK)1 ==/            |
     | 1                                                 | 1
     |                                                   |
     |         terminpersonass                           |
     |          ---------------                          |
      =====> n terminid (FK)|                            |
               personid(FK) |(PK) n <====================

Die Zuordnung zwischen termin und person kann dann noch qualifiziert werden...

[sticky bit]

ich löse so etwas wie folgt:

  termin                   kunde                    person
  -------                  ---------                --------------
  terminid (PK)      /=> n kundeid (PK)      /==> n personid (PK)
  kundeid (FK) 1 ===/      personid (FK)1 ==/            |
     | 1                                                 | 1
     |                                                   |
     |         terminpersonass                           |
     |          ---------------                          |
      =====> n terminid (FK)|                            |
               personid(FK) |(PK) n <====================

Die Zuordnung zwischen termin und person kann dann noch qualifiziert werden...

Danke, scheitert aber an zwei Dingen:
1. Kann jetzt zu einem Kunden nur eine Person gehören, es sollen aber 0 bis unendlich gehen.
2. In `termin` kann immer noch ein Verweis auf einen Kunden gespeichert sein, während die Person in `terminpersonass` gar nicht zu diesem Kunden gehört. Altes Problem bleibt also bestehen.

[elrond]

nö,

1. zum Kunden können 0..n personen gespeichert werden, allerdings muss jede person genau einem Kunden zugeordnet sein

2. ein termin ist einem kunden zugeordnet. die teilnehemenden personen sind dem termin allerdings gesondert zugeordnet. damit können beliebig viele personen auch unterschiedlicher kunden dem termin zugeordnet sein.

das problem, dass personen unterschiedlicher Kunden dem termin zugeordnet werden kriegst du so einfach (zumindest in der mysql) nicht in der db abgefangen. das bedarf ein wenig programmlogik...

[sticky bit]

1. zum Kunden können 0..n personen gespeichert werden, allerdings muss jede person genau einem Kunden zugeordnet sein

Vielleicht verstehe ich Deine Zeichnung auch nicht richtig.
Aber wenn ich in der Kundentabelle ein Fremdschlüsselattribut auf Person habe und so sieht das aus, dann kann ich genau pro Zeile in der Kunden Tabelle eine Zeile der Personen Tabelle referenzieren. Und zwar nur eine, ausser ich verteil den Kunden über mehrere Zeilen, was aber bekanntlichweise Schwachsinn ist. Also geht nur eine Person pro Kunde!
Der Fremdschlüssel müsste von Person auf Kunde zeigen, aber dann ists genau das was ich habe...

2. ein termin ist einem kunden zugeordnet. die teilnehemenden personen sind dem termin allerdings gesondert zugeordnet. damit können beliebig viele personen auch unterschiedlicher kunden dem termin zugeordnet sein.

Ja, das war in meinem Modell ja genau so.
Und es sollen zwar beliebig viele Personen zu einem Termin gehören dürfen, diese dürfen aber nicht, wie du gerade selbst schreibst dass bei Dir möglich ist (und bei mir auch), von unterschiedlichen Kunden stammen dürfen, sondern nur von diesem einem Kunden dem der Termin zugeordnet ist.
Und genau da liegt das Problem, das auszuschliessen

das problem, dass personen unterschiedlicher Kunden dem termin zugeordnet werden kriegst du so einfach (zumindest in der mysql) nicht in der db abgefangen. das bedarf ein wenig programmlogik...

Hmm, wenn dann ist erst mal Oracle und später evtl. Microsoft relevant, aber eigentlich soll es DBMS unabhängig betrachtet werden.

Aber trotzdem im DBMS implementiert werden.
Nur scheints, nach ein paar gescheiterten Versuchen so, als obs auch da mau aussieht. Zumindest weigern sich die sch...nöden Dinger meine Check-Constraints zu akzeptieren weil ich ohne SELECTs darin wohl kaum auskomme.
Vielleicht kann man mit Triggern was richten, mal schaun.

[ClausVB]

Hmm, wenn dann ist erst mal Oracle und später evtl. Microsoft relevant, aber eigentlich soll es DBMS unabhängig betrachtet werden. (..) Aber trotzdem im DBMS implementiert werden.
Nur scheints, nach ein paar gescheiterten Versuchen so, als obs auch da mau aussieht. Zumindest weigern sich die sch...nöden Dinger meine Check-Constraints zu akzeptieren weil ich ohne SELECTs darin wohl kaum auskomme. (..) Vielleicht kann man mit Triggern was richten, mal schaun.

Mal eine Frage von meiner Seite: Solche Strukturen sollten doch mit referentiellen Integritäten abbildbar sein, aber das ist mit MySQL (Version 4) eh nicht machbar. In PostgreSQL und Oracle aber schon ...

Wenn Du es also möglichst datenbankabhängig machen willst, darfst Du keine Funktionen wie CONCAT(), LIMIT und LEFT() von MySQL benutzten und die Logik außerhalb von SQL implementieren. ADODB soll das umsetzen können, aber mir ist noch nicht klar, wie ADODB herausbekommt, ob Microsoft SQL-Server über ein LIMIT verfügt oder nicht ... oder sie lösen es über PHP Programmlogik, was nach meinem Empfinden nicht immer performanter sein wird, als die MySQL-Funktionen selbst ...

Gruß
Claus

[sticky bit]

Mal eine Frage von meiner Seite: Solche Strukturen sollten doch mit referentiellen Integritäten abbildbar sein, aber das ist mit MySQL (Version 4) eh nicht machbar. In PostgreSQL und Oracle aber schon ...

Wenn ja, wie? Das ist ja die Frage...

Wenn Du es also möglichst datenbankabhängig machen willst, darfst Du keine Funktionen wie CONCAT(), LIMIT und LEFT() von MySQL benutzten und die Logik außerhalb von SQL implementieren.

Es ist ja wurst ob ich das für jedes DBMS neu programmiere die Trigger oder Constraints oder wies dann auch immer geht. Am liebsten wärs mir halt es lässt schon durch Fremdschlüssel-Einschränkungen abdecken, die wohl jedes halbwegs brauchbares DBMS mitbringen sollte. Aber es soll auf keinen Fall in die Logik von aufsetztenden Programmen, da gehörts nicht hin!

ADODB soll das umsetzen können, aber mir ist noch nicht klar, wie ADODB herausbekommt, ob Microsoft SQL-Server über ein LIMIT verfügt oder nicht ... oder sie lösen es über PHP Programmlogik, was nach meinem Empfinden nicht immer performanter sein wird, als die MySQL-Funktionen selbst ...

Jetzt komm ich nicht mehr ganz mit. Also ADODB ist AFAIK so ne Zugriffsschicht wie ODBC, JDBC etc? Aber da kann ich mich täuschen, also nicht hauen... Was hat das jetzt mit dem Thema zu tun?
Bei Microsoft gibts kein LIMIT da muss man ein TOP benutzen... Aber auch hier frag ich mich wo da jetzt der bezug zum Thema ist?
Es geht nicht um Performance sondern um "Sauberkeit" ausserdem ist $Programm in $Programmiersprache (PHP kommt leider vsl. nicht zum Einsatz, aber es ist eigenltich auch egal was da drauf aufsetzt und wie und mit was es geschrieben wurde) sehr wahrscheinlich langsamer wenn es die Daten erst übertragen muss um zu prüfen...

Nicht böse nehmen aber irgendwie scheint mir als lesen die meissten an dem was ich will schnurstraks vorbei? Oder ich habs so undeutlich geschrieben?

[mwanaheri]

Ich hab es mir noch mal durch den Kopf gehen lassen und mache einen neuen Versuch (bin aber müde, also Gnade, wenns Schwachsinn ist):
-----------------
Kunden:
id serial not null
name

id ist primary key
-----------------
Kontakt:
id serial not null
kunde
name

kunde referenziert tabelle Kunden
(id,kunde) als gemeinsamer primary key
-------------------
Termin:
datum
kunde
thema

kunde referenziert tabelle Kunden
(datum,kunde) als gemeinsamer primary key
-------------------
Kommen:
datum
kunde
kontaktid

(datum,kunde,kontaktid) als primary key
(datum,kunde) referenziert Tabelle Termin
(kunde,kontaktid) referenziert Tabelle Kontakt
-------------------

Die kombinierten Primärschlüssel können sozusagen über Kreuz als Fremdschlüssel verwendet werden. Blöd an dieser Lösung ist bislang, dass ein Kunde nur einen Termin pro Tag haben kann, aber so sind die Tabellen einfacher.

Ein Termin kann genau einen Kunden haben (allerdings geht nur ein Termin ohne Kunde/Tag)
Ein Kunde kann jeden Tag einen neuen Termin kriegen (sonst halt Zeitstempel statt Datum oder eine Kombination mit Ort hinzufügen)
Ein Kunde kann beliebig viele Ansprechpartner haben.
jeder Ansprechpartner ist genau einem Kunden zugeordnet
in der Tabelle kommen können genau die Ansprechpartner eingetragen werden, die zu dem Termin gehören, da der Kunde sowohl beim Termin als auch beim Ansprechpartner vermerkt ist. Jeder Ansprechpartner kann für einen Termin genau ein mal eingetragen werden.

Einem kurzen Test nach scheint das so zu gehen,was meinst du?

[elrond]

@sticky

sorry, du hast latürnich recht! ich habe die beziehungen falschrum gemalt... *schäm* Ich war so drauf bedacht, dass es hübsch aussieht...

  termin                   kunde                    person
  -------                  ---------                --------------
  terminid (PK)      /== 1 kundeid (PK)      /=== 1 personid (PK)
  kundeid (FK) n >==/      personid (FK)n >=/            |
     | 1                                                 | 1
     |                                                   |
     |         terminpersonass                           |
     |          ---------------                          |
      =====> n terminid (FK)|                            |
               personid(FK) |(PK) n <====================

so ist's besser. Damit können zu einem Kunden auch mehrere Personen gehören... *peinlichpeinlich*

Damit bin ich genau da, wo du angefangen hast

Aber wie schon gesagt, würde ich das Problem in der Programmlogik lösen. alternativ, muss die tabelle terminpersonass einen insert/update trigger erhalten, der sicherstellt, dass die zugeordnete person auch wirklich zu dem kunden gehört, zu den der termin erstellt wurde

[sticky bit]

@mwanaheri:
Von der Idee her ist die Lösung eigentlich n super Ding.
Allerdings muss ich wohl an einigen Ecken und Enden das noch ein bisschen anders machen (z. B. das Datum vom Termin im Primärschlüssel, das geht nicht, weil es auch möglich sein muss mehrere Termine zu einem Zeitpunkt mit einem Kunden zu haben) und ob sich das auch umsetzten lässt, teilweise denke ich mir könnte das ein oder andere DBMS da streiken bei dem ein oder anderen Konstrukt, aber das muss ich ausprobieren.
mal sehen was sich letztendlich draus machen lässt, auf jeden Fall, super, Danke!

[mwanaheri]

Wie wär's z.B. mit 'ner Raum/Ortsangabe im Schlüssel? Zwei Termine im gleichen Raum sind ja eher unwahrscheinlich. Oder Zeit statt datum?