Inkonsistentes Mitglieder-Datenschema verbessern

Ludwigmller

SQL-Guru
Beiträge
168
Hallo,
ich habe eine Tabelle mitglied (id_mitglied, name_mitglied, anzahl) in der jedes Mitglied gespeichert ist. Dann gibt es noch eine Tabelle gruppe (id_gruppe, name_gruppe) und eine Tabelle gruppenzuordnung (id_zuordnung, id_gruppe, id_mitglied) in der eine Zuordnung von Mitgliedern zu einer Gruppe erfolgen kann.
Es sollen dann in anderen Tabellen z.B. erfassung1 Daten erfasst werden, die entweder einem einzelnen Mitglied zugewiesen werden können, oder mehreren Mitgliedern in Form einer Gruppe.
Bis jetzt habe ich es so gelöst, dass in der Tabelle guppenzuordnung jedes Mitglied gespeichert ist, also eine Gruppe mit nur einem Mitglied, es gilt id_gruppe = id_mitglied. Zusätzlich gibt es dann Gruppen aus mehreren Mitgliedern, die als id_gruppe eine fortlaufende Nummer mit Präfix 'G' für Gruppe haben. Das ganze ist auf jeden Fall keine "schöne" Lösung. Vor allem weil ich dann in erfassung1 keinen Fremdschlüssel setzen kann, der auf die Tabelle gruppe verweist. Dann ist es ein Konsistenz-Problem, wenn Mitglieder hinzugefügt werden. Dafür könnte man ja noch einen Trigger ergänzen...

Hat jemand eine bessere Idee?
 
Werbung:
wenn ich Dich richtig verstehe, kann in gruppenzuordnung zu jeder Gruppe 1 oder mehr Mitglieder zugeordnet werden, der PK dieser Tabelle wäre dann wohl Gruppen_ID und Mitglieder_ID. Das kannst Du dann auch als FK in Deiner Erfassungstabelle nehmen.

Oder hab ich was flasch verstanden?
 
der PK dieser Tabelle wäre dann wohl Gruppen_ID und Mitglieder_ID
Das wäre eine bessere Alternative als derzeit id_zuordnung als PK, was überflüssig ist
Das kannst Du dann auch als FK in Deiner Erfassungstabelle nehmen.
Dann würde ich ja in der Erfassungstabelle nicht die gesamte Gruppe referenzieren, sondern nur ein Mitglied einer Gruppe.
Bsp.:
Code:
INSERT INTO zuordung (id_gruppe, id_mitglied)
VALUES
(1,1),
(1,2),
(1,3)
Ordnet die Mitglieder 1,2 und 3 der Gruppe 1 zu. Wenn ich diese Gruppe 1 in der Erfassung referenzieren möchte, muss ich auf die Tabelle gruppen verweisen
 
Also du hast folgende Tabellen:
Mitglieder (PK)
Gruppen (PK)
Mitglieder_zu_Gruppen (FK_Mitglied,FK_Gruppe)
Datenerfassung(FK_Gruppe)

a) Damit das jetzt normalisiert wäre fehlt eigentlich nur Datenerfassung FK_Mitglied für Datenerfassung zu einem einzelnen Mitglied. Man könnte dann noch einen Constraint machen der nur einen der beiden FK erlaubt. Das wäre erstmal sauber.

Denkbar sind auch noch andere Lösungen:

b) Eine Spalte Datenerfassung FK die sich auf die ID aus Gruppen oder aus Mitglieder beziehen kann. Dazu eine Definition wann was der Fall ist oder ein Präfix vor den IDs, etc., etc.

c) Nur folgende Tabellen:
Entität (PK)
Beziehungen (FK Enttität N, FK Entität M)
Datenerfassung (FK Entität)
Gruppen und Mitglieder sind dann Entitäten und können verschachtelt werden. Erfasste Daten können auf jede Entität bezogen sein.

d) ...

Die Frage ist womit du am Ende in der Anwendung am besten klar kommst. Kommt es auf Geschwindigkeit an, etc.
 
So wie ich das Problem erfasst habe, sollte mein Weg zielführend sein:
Code:
test=# create sequence ids;
CREATE SEQUENCE
Ich erstelle eine Sequence, für eine fortlaufende ID über mehrere Spalten
Code:
test=# create table grps(id integer default nextval('ids') primary key);
CREATE TABLE
Ich erstelle eine Tabelle für die möglichen Gruppen, und vergebe hier die fortlaufende ID über mehrere Tabellen.
Code:
test=# create table member(id integer default nextval('ids') primary key);
CREATE TABLE
Ich erstelle eine Tabelle für de Mitglieder, die in einer Gruppe sein können.
Code:
test=# create table relations(grp integer references grps(id), member integer references member(id));
CREATE TABLE
Ich erstelle eien Tabelle für die möglichen Beziehungen.
Code:
test=# insert into grps(id) values (default);
INSERT 0 1
test=# insert into member(id) values (default);
INSERT 0 1
test=# insert into member(id) values (default);
INSERT 0 1
test=# insert into member(id) values (default);
INSERT 0 1
test=# insert into grps(id) values (default);
INSERT 0 1
test=# insert into member(id) values (default);
INSERT 0 1
test=# insert into member(id) values (default);
INSERT 0 1
test=# insert into member(id) values (default);
INSERT 0 1

Ich füge ein paar Werte ein (ja, jetzt fallt mir ein, es wäre auch schöner möglich mit einem generate_series)

Code:
test=# select * from member;
 id
----
  2
  3
  4
  6
  7
  8
(6 Zeilen)

test=# select * from grps;
 id
----
  1
  5
(2 Zeilen)
Ich lasse mir die bisherigen Werte anzeigen, ob sie drinnen sind

Code:
test=# insert into relations(grp, member) values (1, 8), (1, 3), (1, 2);
INSERT 0 3
test=# insert into relations(grp, member) values (5, 4), (5, 6);
INSERT 0 2
test=# select * from relations;
 grp | member
-----+--------
   1 |      8
   1 |      3
   1 |      2
   5 |      4
   5 |      6
(5 Zeilen)
Ich füge den Gruppen diverse Mitglieder hinzu.

Ich hoffe, die kleine Arbeit wird hilfreich sein ;)
 
und eine Tabelle gruppenzuordnung (id_zuordnung, id_gruppe, id_mitglied) in der eine Zuordnung von Mitgliedern zu einer Gruppe erfolgen kann.
Nur als kleine Randbemerkung: id_zuordnung ist eigentlich überflüssig in so einer Tabelle. Die Kombination gruppe/mitglied muss sowieso eindeutig und kann gleich als Primärschlüssel definiert werden. Die zusätzliche Spalte id_zuordnung und der damit verbundene Unique Index sind letztendlich nur Platzverschwendung.
 
Nur als kleine Randbemerkung: id_zuordnung ist eigentlich überflüssig in so einer Tabelle. Die Kombination gruppe/mitglied muss sowieso eindeutig und kann gleich als Primärschlüssel definiert werden. Die zusätzliche Spalte id_zuordnung und der damit verbundene Unique Index sind letztendlich nur Platzverschwendung.
Vorausgesetzt das sind alle Spalten die wir kennen, nicht wenn du z.B. auch historische Werte in der Tabelle führst und ein Eintritts- oder Austrittsdatum existieren kann. Dann machen auch mehrfach Eintragungen Sinn.
 
a) Damit das jetzt normalisiert wäre fehlt eigentlich nur Datenerfassung FK_Mitglied für Datenerfassung zu einem einzelnen Mitglied. Man könnte dann noch einen Constraint machen der nur einen der beiden FK erlaubt. Das wäre erstmal sauber.
Das heißt, ich verweise mit einem FK in einem Fall auf den PK der Tabelle Mitglieder und in einem anderen auf den PK der Tabelle Gruppen? Wie definiert man das in der Praxis?
Die Frage ist womit du am Ende in der Anwendung am besten klar kommst. Kommt es auf Geschwindigkeit an, etc.
Geschwindigkeit ist zweitrangig. An der Tabelle Mitglieder sollte sich möglichst wenig ändern, da diese bereits existiert, und Grundlage für vieles anderes ist. Andererseits soll es jetzt schlussendlich sauber werden.

Was ist denn von meiner jetzigen Lösung zu halten? Wäre diese mit einer Redundanz-Pflege per Trigger auch in Ordnung?
Ich hoffe, die kleine Arbeit wird hilfreich sein ;)
Auf was verweise ich dann in der Erfassungstabelle, wenn es nur für ein Mitglied erfassst werden soll?
 
zu a)
Code:
CREATE TABLE mitglied(
    pk UNIQUEIDENTIFIER PRIMARY KEY NOT NULL
    );

CREATE TABLE gruppe(
    pk UNIQUEIDENTIFIER PRIMARY KEY NOT NULL
    );

--mitglied_zu_gruppe sollte klar sein

CREATE TABLE datenerfassung(
    fk_mitglied UNIQUEIDENTIFIER NULL,
    fk_gruppe UNIQUEIDENTIFIER NULL
    );

ALTER TABLE datenerfassung ADD CONSTRAINT datenerfassung_fk_mitglied FOREIGN KEY(fk_mitglied) REFERENCES mitglied(pk);
ALTER TABLE datenerfassung ADD CONSTRAINT datenerfassung_fk_gruppe FOREIGN KEY(fk_gruppe) REFERENCES gruppe(pk);
ALTER TABLE datenerfassung ADD CONSTRAINT datenerfassung_check_fk CHECK((fk_mitglied IS NOT NULL OR fk_gruppe IS NOT NULL) AND (fk_mitglied IS NULL OR fk_gruppe IS NULL));
Das wäre aus meiner Sicht ein sauberes Datenmodell. In wie Fern die Anwendung damit gut klar kommt ist dann die andere Frage, die Datenbank schmeißt halt einen Fehler wenn z.B. beide FKs gesetzt werden.

Deine jetzige Lösung ist auch okay, mit Triggern aber etwas mehr Auwand. Auch hast du natürlich durch die Dummy-Einträge mehr Daten (die eigentlich überflüssig sind). Der große Vorteil kann in deinem Frontend liegen: Der Benutzer muss an einer Stelle eine Referenz setzen und sieht ggf. Gruppen und Personen (in Form der Dummy-Gruppe) in einer Liste. Im Frontend ist das ggf. nicht so kompliziert umzusetzen.
 
mit Triggern aber etwas mehr Auwand
ich müsste dann ja bei jedem INSERT, UPDATE und DELETE der Mitglieder die Dummy-Gruppen aktualisieren. Wenn ich deine Lösung implementieren würde, müsste ich halt ziemlich viel in Folge dessen ändern. Das wäre wahrscheinlich jetzt im Nachhinein noch mehr Aufwand als mit Triggern.... Das kommt davon wenn man es nicht gleich ordentlich macht ;)
 
Verstehe mich nicht falsch ich hab nichts (nicht viel) gegen Trigger aber ich weiß wie gruselig meine ersten Trigger waren :)

Hier mal ein Trigger für mein Beispiel Code:
Code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER    [dbo].[mitglied_trigger]
    ON            [dbo].[mitglied]
    FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    --INSERT
    INSERT INTO gruppe(pk,IstMitglied)
    SELECT    i.pk,
            1
    FROM    INSERTED i
    LEFT JOIN DELETED d
    ON        i.pk = d.pk
    WHERE    d.pk IS NULL

    INSERT INTO mitglied_zu_gruppe(fk_mitglied,fk_gruppe)
    SELECT    i.pk,
            i.pk
    FROM    INSERTED i
    LEFT JOIN DELETED d
    ON        i.pk = d.pk
    WHERE    d.pk IS NULL

    --UPDATE
    --ist nur für den pk nicht nötig, der ändert sich ja nicht

    --DELETE
    DELETE
    FROM    mitglied_zu_gruppe
    WHERE    fk_mitglied = fk_gruppe
    AND        fk_mitglied IN (    SELECT    d.pk
                                FROM    DELETED d
                                LEFT JOIN INSERTED i
                                ON        d.pk = i.pk
                                WHERE    i.pk IS NULL )
END
- Der Trigger ist jetzt MSSQL spezifisch.
- MSSQL Trigger feuern für jedes Query gegen eine Tabelle genau einmal, müssen also mehrere Datensätze beherschen (das kann dieser). Bei MySQL oder anderen gibt es teilweise per row Trigger.
- DELETE könnte man auch mit ON CASCADE oder sowas abfrühstücken.
- Da ich mit UNIQUEIDENTIFIER angefangen habe bin ich jetzt mal nur von einer Markierung IstMitglied in der Gruppen-Tabelle ausgegangen, es könnte aber auch einfach die selbe GUID in beiden Tabellen verwendet werden, oder eine Zeichenkette oder sonstwas.
- Der Trigger prüft nicht auf bereits vorhandene Datensätze, das sollte aber bei GUID auch nicht der Fall sein. Bei einer Zeichenkette könnte natürlich schon jemand die Gruppe vor der Person angelegt haben.
 
Werbung:
Genau das meine ich mit "per row"-Trigger. Fand das am Anfang doof bei MSSQL aber ehrlich gesagt ist es eigentlich eleganter alle Datensätze gleichzeitig mit einem Statement zu "verarbeiten" als erst irgendwas in irgendwelche Variablen zu schreiben um es dann wieder zu verwenden und das dann nochmal pro Datensatz auszuführen. Das ist in meinen Augen einfach schlechter Code, selbst wenn es nicht langsammer läuft.
 
Zurück
Oben