Join innerhalb einer Tabelle

[Joekox]

Hallo,
ich bin Gerade am Designen einer Datenbank, um automatisch einen Putzplan für unsere Jugendräume zu erstellen. dies bedeutet, eine Putzgruppe besteht aus zwei Personen, und wird für jeden Putzplan neu zufällig zusammengestellt. eine Gruppe hat immer eine Woche in der sie fürs Putzen verantwortlich ist. diese Personen sollten zusammen Putzen, aber es kann auch vorkommen das eine Person mal nicht putzt und die andere dann nur die Hälfte, dies sollte auch gespeichert werden.

Hier ist mal mein ansatz, aber jetzt meine frage gibt es hierfür auch eine andere Lösung?


Personen
- Personen_ID
- Vorname
- Nachname

Putzplanzwischentabelle
- Person_ID
- Putzgruppen_ID
- erledigt

Putzgruppen
- Putzgruppen_ID
- anfangsdatum
- enddatum

 

[Charly]

AW: Join innerhalb einer Tabelle

Hallo Joekox,

was hältst du hiervon:

CREATE TABLE Personen
(
 PersonID int PRIMARY KEY
, Vorname nvarchar(50)
, Nachname nvarchar(50)
)
 
CREATE TABLE Gruppe
(
 GruppeID int PRIMARY kEY
, Person1 int
, Person2 int
, Stunden1 int
, Stunden2 int
, CONSTRAINT UQ_Putzgruppe UNIQUE (Person1, Person2)
, CONSTRAINT CK_Putzgruppe CHECK (Person1 <> Person2)
, CONSTRAINT FK_Person1 FOREIGN KEY (Person1) REFERENCES Personen(PersonID)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, CONSTRAINT FK_Person2 FOREIGN KEY (Person2) REFERENCES Personen(PersonID)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
)
 
CREATE TABLE Putzplan
(
 Kalenderwoche int
, Jahr int
, GruppeID int
, Erledigt bit
, CONSTRAINT CK_Kalenderwoche CHECK(Kalenderwoche BETWEEN 1 AND 53)
, CONSTRAINT CK_Jahr CHECK (Jahr BETWEEN 2011 AND 2099)
, CONSTRAINT PK_Putzplan PRIMARY KEY (Kalenderwoche, Jahr)
, CONSTRAINT FK_GruppeID FOREIGN KEY (GruppeID) REFERENCES Gruppe(GruppeID)
)

Die Create-Anweisungen dürften unter den meisten DBMS laufen.

aber es kann auch vorkommen das eine Person mal nicht putzt und die andere dann nur die Hälfte, dies sollte auch gespeichert werden.

Dafür habe ich die Stunden-Spalten eingefügt.

Die Tabellen noch mit Daten füttern ...

INSERT INTO Personen VALUES (1,'Müller','A.')
INSERT INTO Personen VALUES (2,'Meier','B.')
INSERT INTO Personen VALUES (3,'Schulze','C.')
INSERT INTO Personen VALUES (4,'Krube','D.')
 
INSERT INTO Gruppe VALUES (1,1,2,10,10)
INSERT INTO Gruppe VALUES (2,3,4,10,10)
 
INSERT INTO Putzplan VALUES(1,2011,1,1)
INSERT INTO Putzplan VALUES(2,2011,2,1)
INSERT INTO Putzplan VALUES(3,2011,1,1)
INSERT INTO Putzplan VALUES(4,2011,2,0)

... und abfragen

SELECT 
P.Kalenderwoche AS KW
, P.Jahr
, P1.Vorname + ' ' + P1.Nachname AS Putzteufel1
, G.Stunden1
, P2.Vorname + ' ' + P2.Nachname AS Putzteufel2
, G.Stunden2
, P.Erledigt
FROM Putzplan AS P
JOIN Gruppe AS G ON G.GruppeID=P.GruppeID
JOIN Personen AS P1 ON G.Person1=P1.PersonID
JOIN Personen AS P2 ON G.Person2=P2.PersonID

Ergebins:

KW Jahr Putzteufel1 Stunden1 Putzteufel2 Stunden2 Erledigt
---------------------------------------------------------------
1 2011   Müller A.  10        Meier B.    10      1
2 2011   Schulze C. 10        Krube D.    10      1
3 2011   Müller A.  10        Meier B.    10      1
4 2011   Schulze C. 10        Krube D.    10      0

Die Einschränkungen verhindern z.B.:
- Das nur 1 Person oder keine Person einer Putzguppe zugeordnet werden kann.
- Das zweimal die gleiche Person eine Putzgrruppe zugewiesen wird.
- Das nur Kalenderwochen zwischen 1 und 53 geben kann.
- Das keine unsinnigen Jahreszahlen eingetragen werden.
- Das es nur jeweils eine Kombination aus KW und Jahr geben kann
- Das nicht nur eine Person zum Putzen eingeteilt wird
(Wenn eine Person nicht Putzt ist Stunden = 0)

Ich hoffe dir damit ein bisschen weitergeholfen zu haben.

Gruß Charly

 

[Joekox]

AW: Join innerhalb einer Tabelle

wow vielen dank, das ist ja sehr ausführlich und dadurch einfach erklärt.
vielen dank, werde mir das noch mal genauer anschauen und dann wahrscheinlich 1 zu 1 übernehmen . hast mir echt geholfen.