Doppelte Datensätze verhindern in 1:N-Verknüpfung

[BlueStarHH]

Hallo,

ich habe diese Tabelle in der Gegenstänge (z.B. Krug, Würfel) enthalten sind.

Code:

Gegenstand
----------
PK  GegenstandID integer
UNQ Groesse varchar(50)
UNQ Farbe varchar(50)
...

Nun soll es zu jedem Gegenstand beliebig viele vom Anwender definierbare Eigenschaften (wie z.B. "rund", "zerbrechlich") geben. Also noch 2 Tabellen dazu:

Code:

Eigenschaft
-----------
PK EigenschaftID integer
   Name varchar(50)

Code:

GegenstandEigenschaft
---------------------
PK FK GegenstandID integer 
PK FK EigenschaftID integer

Wie kann ich nun verhindern, dass gleiche Gegenstände mit gleichen Eigenschaften mehrfach gespeichert werden UND zulassen dass es Gegenstände gibt wo Groesse+Farbe GLEICH sind, aber die Eigenschaften jeweils unterschiedlich sind?

[mkinzler]

Über constraint oder besser (bi) Trigger

[Delphi.Narium]

Eindeutigen Index auf GegenstandEigenschaft mit GegenstandID und EigenschaftID?

SQL-Code:

create unique index ix_uni_GegenstandEigenschaft on GegenstandEigenschaft (GegenstandID, EigenschaftIDI;

[mkinzler]

Es kommt darauf an, was er gemeint hat. Dieser Index verhindert, dass ein Gegenstand eine Eigenschaft mehrfach zugeordnet wird, aber nicht mehrere gleichartige Gegenstände.

[EgonHugeist]

Schau dir mal https://firebirdsql.org/refdocs/langrefupd21-merge.html an. Ein roundtrip..

[mkinzler]

Oder

SQL-Code:

update or insert

Aber auch dies wirkt nur auf die Detailtabelle

[jobo]

Zunächst:
Das Unique auf Größe und Farbe ergibt keinen Sinn oder soll wirklich jede Farbe und Größe für sich nur einmalig in der ganzen Tabelle auftauchen?
Dann sind Größe und Farbe auch Eigenschaften und können/sollten dort mit aufgenommen werden.

Am Ende machst Du auf Deine M:N Tabelle einen PK auf die beiden FK zu Gegenstand und Eigenschaft und das sollte dann okay sein.

[himitsu]

Es gibt DBMS die erlauben in den Indizes auch Funktionen und Formeln. (bzw. k.A. ob Einige sowas nicht erlauben/können)

SQL-Code:

create unique index ix_uni_GegenstandEigenschaft on GegenstandEigenschaft (GegenstandID, EigenschaftID <> '');

-- bzw. (wobei, da GegenstandEigenschaft.EigenschaftID NOT NULL ist, kürzt sich das raus)
create unique index ix_uni_GegenstandEigenschaft on GegenstandEigenschaft (GegenstandID, EigenschaftID IS NOT NULL); 
create unique index ix_uni_GegenstandEigenschaft on GegenstandEigenschaft (GegenstandID, true);
create unique index ix_uni_GegenstandEigenschaft on GegenstandEigenschaft (GegenstandID);

also jeder Gegenstand kann in GegenstandEigenschaft mit nur jeweils einer Eigenschaft vorkommen,
quasi so als wenn man man in der Tabelle Gegenstand ein Feld EigenschaftID hätte, wo man nur einwas rein bekommt.

[mkinzler]

Nennt man "Expression Index"

https://firebirdsql.org/rlsnotesh/in...xpression.html

[TigerLilly]

Trigger und Constraints wurden ja schon genannt.

Variante:
Du kannst die einzelnen EigenschaftIDs eines Gegenstands in formatierte Strings umwandeln (zB "001", "004", "099", etc) und zu einem String zusammenbauen: "001002099".
Haben zwei Gegenstände idente Strings, haben sie gleiche Eigenschaften.

[Jumpy]

Ich finde das Konzept der Eigenschaften schon zu schwammig und würde eher mit Merkmalen und Merkmalsausprägungen arbeiten.

Sonst erstelle ich einen roten Krug mit Eigenschaft zerbrechlich.
Der nächste erstellt einen roten Krug mit Eigenschaft unzerbrechlich und rund.
Kommt der dritte rote Krug und der User wählt aus den ja jetzt angelegten Eigenschaften zerbrechlich und unzerbrechlich aus und du hast ein fragwürdiges Objekt.

[jobo]

Zitat:

Trigger und Constraints wurden ja schon genannt..

Lustig, es wurde ja nun fast alles genannt, was eine Lösung für irgendein Datenbank Problem sein könnte.
Bin gespannt, was noch so dazu kommt bis der TE sich meldet.
(falls er sich meldet)

[BlueStarHH]

Zitat:

Zitat von mkinzler (Beitrag 1470125)

Über constraint oder besser (bi) Trigger

Danke an alle für die Antworten. Wie würden den eine Constraint oder Trigger aussehen? Stehe da gerade etwas auf dem Schlauch...

[BlueStarHH]

Zitat:

Zitat von jobo (Beitrag 1470140)

Zunächst:
Das Unique auf Größe und Farbe ergibt keinen Sinn

Jup, eine Altlast. Kann und muss weg.

Zitat:

Zitat von jobo (Beitrag 1470140)

Am Ende machst Du auf Deine M:N Tabelle einen PK auf die beiden FK zu Gegenstand und Eigenschaft und das sollte dann okay sein.

Hab mir den Satz jetzt mehrfach durchgelesen: Habe ich das nicht schon gemacht oder verstehe ich den Satz falsch?

[BlueStarHH]

Zitat:

Zitat von mkinzler (Beitrag 1470135)

Es kommt darauf an, was er gemeint hat. Dieser Index verhindert, dass ein Gegenstand eine Eigenschaft mehrfach zugeordnet wird, aber nicht mehrere gleichartige Gegenstände.

Zu erst möchte ich gleichartige Gegenstände verhindern. Wenn man dann auch noch verhindern kann, dass eine Eigenschaft dem selben Gegenstand mehrfach zugeordet wird, ist das noch besser.

[jobo]

Zitat:

Zitat von BlueStarHH (Beitrag 1470288)

Zitat:

Zitat von jobo (Beitrag 1470140)

Zitat:

Zitat von jobo (Beitrag 1470140)

Am Ende machst Du auf Deine M:N Tabelle einen PK auf die beiden FK zu Gegenstand und Eigenschaft und das sollte dann okay sein.

Hab mir den Satz jetzt mehrfach durchgelesen: Habe ich das nicht schon gemacht oder verstehe ich den Satz falsch?

Hier eine detailiertere Formulierung:
Du legst auf Deine M:N Verbindungstabelle "GegenstandEigenschaft" einen Primärschlüssel auf die beiden Fremdschlüssel, die auf Gegenstand und Eigenschaft zeigen.
Das sollte alles sein.

Als SQL:

Code:

  
create table GegenstandEigenschaft(
  GegenstandID bigint ,
  EigenschaftID bigint );

alter table GegenstandEigenschaft ADD PRIMARY KEY (GegenstandID,EigenschaftID );

alter table GegenstandEigenschaft ADD foreign KEY (GegenstandID) references Gegenstand(GegenstandID);
alter table GegenstandEigenschaft ADD foreign KEY (EigenschaftID ) references Eigenschaft(EigenschaftID );

[BlueStarHH]

Zitat:

Zitat von jobo (Beitrag 1470333)

Als SQL:

Code:

  
create table GegenstandEigenschaft(
  GegenstandID bigint ,
  EigenschaftID bigint );

alter table GegenstandEigenschaft ADD PRIMARY KEY (GegenstandID,EigenschaftID );

alter table GegenstandEigenschaft ADD foreign KEY (GegenstandID) references Gegenstand(GegenstandID);
alter table GegenstandEigenschaft ADD foreign KEY (EigenschaftID ) references Eigenschaft(EigenschaftID );

Genau das habe ich schon gemacht. Siehe mein Eingangspost, da ist das sehr verkürzt mit PK (PRIMARY KEY) und FK (Foreign KEY) dargestellt:

Code:

GegenstandEigenschaft
---------------------
PK FK GegenstandID integer
PK FK EigenschaftID integer

Hilft mir aber nicht mein Problem vollständig zu lösen. Trigger oder Contraints sind wohl doch noch zusätzlich nötig... Hat jemand ein Beispeil wie ein Trigger oder Contraints für meinen Anwendungsfall aussehen könnte? Danke!

[jobo]

Oh sorry, dann verstehe ich Dein Problem nicht.
Meiner Meinung nach löst mein Vorschlag Deine Probleme.

[BlueStarHH]

Zitat:

Zitat von jobo (Beitrag 1470486)

Oh sorry, dann verstehe ich Dein Problem nicht.
Meiner Meinung nach löst mein Vorschlag Deine Probleme.

Dann gebe ich mal ein Beispiel um das zu verdeutlichen:

Code:

Gegenstand
----------
GegenstandID  Groesse  Farbe
1             50x90    weiß
2             XL       blau
3             XL       blau             



Eigenschaft
-----------
EigenschaftID  Name
1              brennbar
2              zerbrechlich


GegenstandEigenschaft
---------------------
GegenstandID  EigenschaftID
1             2
2             1
3             1

Das zusammengejoint ergibt dann:

Code:

GegenstandID  Groesse  Farbe  Eigenschaft
1             50x90    weiß   zerbrechlich
2             XL       blau   brennbar
3             XL       blau   brennbar

Einfach zu sehen, dass die letzten beiden Datensätze doppelt sind. Kein PK und FK haben das verhindert. Das darf nicht sein.

In Tabelle "Gegenstand" muss es möglich sein, bis auf die ID gleiche Datensätze anzulegen, da sie ja unterschiedlich Eigenschaften haben könnten. Also sowas sollte möglich sein:

Code:

GegenstandID  Groesse  Farbe  Eigenschaft
1             50x90    weiß   zerbrechlich
2             XL       blau   leicht, ...
3             XL       blau   lang, ...

[Delphi.Narium]

Wären diese Daten aus einer Tabelle,

Code:

GegenstandID Groesse Farbe Eigenschaft
1             50x90    weiß   zerbrechlich
2             XL      blau  brennbar
3             XL      blau  brennbar

würdest Du also einen Index der Form

SQL-Code:

create unique index ix_Groesse_Farbe_Eigenschaft on tabelle (Groesse, Farbe, Eigenschaft);

benötigen.

Da es sich aber um zwei Tabellen handelt, brauchst Du eine entsprechende "tabellenübergreifende" Lösung.

Hab' ich das so (in etwa) richtig verstanden?

[BlueStarHH]

Zitat:

Zitat von Delphi.Narium (Beitrag 1470490)

Wären diese Daten aus einer Tabelle,

Code:

GegenstandID Groesse Farbe Eigenschaft
1             50x90    weiß   zerbrechlich
2             XL      blau  brennbar
3             XL      blau  brennbar

würdest Du also einen Index der Form

SQL-Code:

create unique index ix_Groesse_Farbe_Eigenschaft on tabelle (Groesse, Farbe, Eigenschaft);

benötigen.

Da es sich aber um zwei Tabellen handelt, brauchst Du eine entsprechende "tabellenübergreifende" Lösung.

Hab' ich das so (in etwa) richtig verstanden?

Ja, richtig! Wobei noch wichtig ist, dass es durch die Drei-Tabellen-Lösung mehr als eine Eigenschaft pro Gegenstand geben könnte und können soll.

[jobo]

Wie geht das mit dem bisher geforderten übernander?

Würden die Beispieldaten aus #20 mit dem genannten unique Index aus #20 überhaupt existieren?

Wie wär es, wenn BluestarHH mal positive und negative Beispiel aufführt, um sein Problem plastischer zu machen?

[BlueStarHH]

Zitat:

Zitat von jobo (Beitrag 1470502)

Wie geht das mit dem bisher geforderten übernander?

Wo siehst Du einen Wiederspruch? Evtl. Post #20 falsch verstanden?

Zitat:

Zitat von jobo (Beitrag 1470502)

Würden die Beispieldaten aus #20 mit dem genannten unique Index aus #20 überhaupt existieren?

Nein, würden sie nicht. Ich hab den Ersteller von #20 auch nicht so verstanden. (Könnte man aber leicht). Die Verwirrung war nur seinem Copy&Paste meiner Beispieldaten geschuldet. Streiche einfach das Beispiel aus Post #20 dann stimmt die Aussage mit dem Index trotzdem. Also nochmal deutlich : In einer Tabelle würde ich es mit einem Index wie diesem lösen:
create unique index ix_Groesse_Farbe_Eigenschaft on tabelle (Groesse, Farbe, Eigenschaft)
Das geht bei mir aber nicht, da ich 3 Tabellen habe und haben muss.

Ein positiv und negativ Beispiel findest Du in #19.

[Delphi.Narium]

Bei dem von mir vorgeschlagenen Index würde die dritte Zeile im Beispiel zu einer Indexverletzung führen und wäre somit nicht möglich.

[jobo]

Zitat:

Zitat von BlueStarHH (Beitrag 1470505)

Evtl. Post #20 falsch verstanden?

Zitat:

Zitat von jobo (Beitrag 1470502)

Würden die Beispieldaten aus #20 mit dem genannten unique Index aus #20 überhaupt existieren?

Nein, würden sie nicht.
..
Ein positiv und negativ Beispiel findest Du in #19.

Ja, anders verstanden. In Post 20 steht nirgends, dass die dargestellte Kombination nicht funktionieren soll. Sondern dass hier unerwünschte Daten stehen, die mittels des Index verhindert werden sollen.

Aber wie auch immer, ich hab's endlich kapiert, sorry.
Wenn unter keinen Umständen das bestehende System verändert, sondern nur erweitert werden soll, ja dann muss man häßliche Dinge machen mit Triggern.

Ich würde wie eingangs vorgeschlagen die Farbe und Größe mit in die Eigenschaften übernehmen. Falls der Erhalt als "Originalspalte" unabdingbar ist, würde ich das per View einblenden.