[SQL] Dienstplan

[unXtremo]

Hallo zusammen,
ich entwerfe gerade einen Online Dienstplan.
Ich habe jedoch ein Problem bei der Datenbank Entwicklung.

meine Bisherige Datenbank Struktur:

Spoiler

/**
 * Mitarbeiter
 */
CREATE TABLE user (
 u_id INT NOT NULL UNIQUE AUTO_INCREMENT,
 vorname VARCHAR(50) NOT NULL,
 nachname VARCHAR(50) NOT NULL,
 mail VARCHAR(50) NOT NULL,
 username VARCHAR(50) NOT NULL,
 password VARCHAR(50) NOT NULL,
 u_status BOOLEAN NOT NULL DEFAULT 1,
 statistik_mini INT NOT NULL DEFAULT 0,
 PRIMARY KEY (u_id)
);

/**
 * Kategorien
 */
CREATE TABLE kategorie (
 kategorie_nr INT NOT NULL UNIQUE AUTO_INCREMENT,
 kategorie_name VARCHAR(50) NOT NULL,
 kategorie_tag VARCHAR(50) NOT NULL,
 kategorie_ort VARCHAR(50) NOT NULL,
 kategorie_uhrzeit TIME NOT NULL,
 anzahl_mitarbeiter INT NOT NULL,
 PRIMARY KEY (kategorie_nr)
);

/**
 * Aufträge
 */
CREATE TABLE auftrag (
 a_id INT NOT NULL UNIQUE AUTO_INCREMENT,
 datum DATE NOT NULL,
 kategorie INT NOT NULL,
 PRIMARY KEY (a_id),
 FOREIGN KEY (kategorie)
 REFERENCES kategorie(kategorie_nr)
 ON DELETE CASCADE
);

/**
 * Dienstplan
 */
CREATE TABLE plan (
 p_id INT NOT NULL UNIQUE AUTO_INCREMENT,
 auftrag INT NOT NULL,
 mitarbeiter VARCHAR(50) NOT NULL DEFAULT '',
 a_status BOOLEAN NOT NULL DEFAULT 0,
 PRIMARY KEY (p_id),
 FOREIGN KEY (auftrag)
 REFERENCES auftrag(a_id)
 ON DELETE CASCADE
);

Ich habe vor, dass die Aufträge mit der "a_id" aus der 2. Tabelle automatisch in den Dienstplan übernommen werden.
Bisher habe ich das nur manuell über folgenden Code hinbekommen:

// Datenbank einbinden
include('dbconnect.php');

// Aufträge in plan übernehmen		
$sql_insert = "INSERT INTO plan(auftrag) SELECT a_id FROM auftrag";
$result_insert = mysql_query($sql_insert) OR die(mysql_error());

...

Ich bin mir nicht sicher ob man dies auch über ein Verweis wie FOREIGN KEY machen kann.
Ich habe mich auch schon über JOIN Abfragen belesen, komme allerdings nicht auf den richtigen Ansatz.

Ich würde mich freuen wenn mir jemand einen Tipp geben könnte.

 

[cx01]

Du könntest einen Trigger anlegen, der automatisch die entsprechende Zeile in 'plan' einfügt.

 

[AlbertLast]

Hi,

erstens sieht es mit Fremdschlüssel schlecht aus bei dir,
weil du keine db engine angibts und weil du eine mysql verison < 5.5 benutzt
wirst du als db engine myisam? haben, die fk nicht unterstützt.

zweitens versuchst du was falsches nun im nachhinein zu verbessern,
der fehler liegt nach meiner ansicht im Dienstplan selbst, dieser sollte wohl keine
Tabelle direkt sein sondern eine SQL select welches sich aus den beteiligten Tabellen
die information besorgt, damit ersparst du dir das Kopieren und as Doubletten anlegen.

Als Kompromisslösung wäre hier dann die Dienstplan tabelle mit ein View zu ersetzen und dahinter dann das select abfrage zu verstecken die im zweiten punkt erwähnt wurde.

 

[unXtremo]

achso, ja ich benutze innoDB, da gibt es FOREIGN KEY die habe ich im nach hinein geändert.
Die Datenbank Version weiß ich gerade nicht auswendig, ich denke es ist 5.4.

Danke für die Tipps,
ich werde mich dann mal über Trigger und View belesen.

@AlbertLast:
Ich habe auch mit dem reinen SELECT aus den beteiligten Tabellen überlegt, wenn ich jedoch einen Mitarbeiter einen bestimmten Auftrag geben möchte, dann muss ich das ja irgendwo vermerken.
Gut man könnte eine Spalte in der 'user' Tabelle hinzufügen, ...

Ich werde erst mal die genannten Vorschläge studieren und dann schauen ob ich weiterkomme.

Danke an alle!
Ich melde mich wenn ich weitergekommen bin.

Ergänzung (27. Juli 2011)

Wenn ich das Problem mit einem Trigger lösen würde, müsste ich ja mehrere anlegen, wenn ich das richtig verstanden habe:

Spoiler

Bei INSERT:

CREATE TRIGGER plan_ins
AFTER INSERT
ON plan
BEGIN
    INSERT INTO plan(auftrag) SELECT a_id FROM auftrag
END;

Bei UPDATE:

CREATE TRIGGER plan_upd
AFTER UPDATE
ON plan
BEGIN
    INSERT INTO plan(auftrag) SELECT a_id FROM auftrag
END;

Bei DELETE:

CREATE TRIGGER plan_del
AFTER DELETE
ON plan
BEGIN
    INSERT INTO plan(auftrag) SELECT a_id FROM auftrag
END;

Ich bin mir nicht sicher, aber das mit den Dubletten in der Datenbank stimmt schon, es gefällt mir nicht so gut, und ich denke das hier eher Fehler auftreten.

Möglichkeit mit VIEW:

Spoiler

Zunächst würde ich die Tabelle 'plan' löschen und stattdessen in der Tabelle 'auftrag' die Spalte 'mitarbeiter' hinzufügen.

/* Tabelle plan löschen */
DROP TABLE plan

/* Tabelle auftrag erweitern */
ALTER TABLE auftrag
ADD 
 mitarbeiter VARCHAR(50) NOT NULL DEFAULT '',
 a_status BOOLEAN NOT NULL DEFAULT 0,

Dann müsste ich einen VIEW erstellen:

CREATE VIEW plan
AS
SELECT a_id, mitarbeiter, a_status
FROM auftrag;

Dann könnte ich ohne Probleme auf diesen VIEW per SELECT, UPDATE und INSERT zugreifen.

Aber wenn ich nochmal auf die Anmerkung von AlbertLast eingehe ist die verwendung des VIEWs lediglich eine Abkürzung des SELECT Befehls.
Ich könnte genauso auch einfach nur eine SELECT Abfrage durchführen:

SELECT a_id, mitarbeiter, a_status FROM auftrag;

Ich hoffe, das ich das ganze jetzt richtig verstanden habe und werde es demnächst ausprobieren!
Vielen Dank an alle!