theuntouchable
Goto Top

Hilfe bei Datebankdesign

Hallo Leute,

da ich im Moment eine kleine Denkblockade habe, wende ich mich an euch face-smile

Ich möchte eine kleine Benutzerverwaltung für eine PHP Seite erstellen. Hierbei soll dann auch aufgezeichnet werden, wer wann wie was wo weshalb warum macht ;)

Dabei geht es um folgende Tabellen:

create table gruppen (
	GNr		int unsigned auto_increment not null primary key,
	GName		varchar(20)
);

create table benutzer (
	BNr		int unsigned auto_increment not null primary key,
	BName		varchar(20) not null,
	Passwort	varchar(256) not null,
	Gruppe		int unsigned not null,

	foreign key (Gruppe)
	references gruppen(GNr)
);

create table überwachung(
	UNr		int unsigned auto_increment not null primary key,
	LetzerLogin	datetime,
	LetzteAktio	datetime,
	Erstellt	datetime not null,
	ErstelltVon int unsigned not null,
	Geändert	datetime,
	GeändertVon	int unsigned,
	
	foreign key (ErstelltVon)
	references benutzer (BNr),

	foreign key (GeändertVon)
	references benutzer (BNr)
);

Ich möchte, das unter GeändertVon und ErstelltVon jeweils der User steht (also die BNr), der den anderen User erstellt oder bearbeitet hat. Aber ich hab gerade keinen Plan wie ich das hinbekomme.. Ich habs gefühl ich bräuchte noch eine Tabelle zwischen benutzer und überwachung..

Kann mir da wer unter die Arme greifen?

Danke face-smile

Content-Key: 243370

Url: https://administrator.de/contentid/243370

Ausgedruckt am: 19.03.2024 um 07:03 Uhr

Mitglied: SlainteMhath
SlainteMhath 11.07.2014 um 11:54:51 Uhr
Goto Top
Moin,

GeändertVon braucht noch ein "not null" sonst klappt das mit dem ForeignKey nicht.

Grundsätzlich würde ich das anders ausfbauen.
eher so
...
UNr		int unsigned auto_increment not null primary key, 
Zeitstempel  datetime,
Aktion  varchar(512),
DurchgefuehrtVon  int unsinged not null,
DurchgefuehrtAn    int unsinged not null,

foreign key (DurchgefuehrtVon) references benutzer (BNr),
foreign key (DurchgefuehrtAn) references benutzer (BNr)

lg,
Slainte
Mitglied: TheUntouchable
TheUntouchable 11.07.2014 aktualisiert um 12:08:38 Uhr
Goto Top
Hi Slainte und danke für deine Antwort!

Ich glaube das "not null" hab ich vorhin schon weggemacht, ich kanns jedenfalls nicht mehr finden face-smile

Bezüglich deines Vorschlags: Ansich nicht schlecht, aber ich möchte später in einer Liste alle Benutzer auflisten lassen, mit den Feldern die oben angegeben sind face-smile Die UNr sollte eigentlich dann die Nummer des jeweiligen Benutzers sein, aber wie ich die mit der BNr verknüpfe ist im Moment mein Problem.. Direkt als foreign key oder eine Tabelle dazwischen?

Die Tabelle, die ich ausgeben will, soll so aussehen:

BNr, Bname, Gruppe, LetzterLogin, LetzteAktion, Erstellt, ErstelltVon (BNr->BName), Geändert, GeändertVon (BNr->BName)
Mitglied: Biber
Biber 11.07.2014 aktualisiert um 16:31:18 Uhr
Goto Top
Moin SlaintheMath,


Zitat von @SlainteMhath:

GeändertVon braucht noch ein "not null" sonst klappt das mit dem ForeignKey nicht.

Das ist so nicht ganz richtig.
Das Feld GeändertVon kann durchaus NULLABLE sein - und der Inhalt darf dann auch bei gesetztem FK-Constraint entweder NULL oder ben ein gültiger Wert aus der referenzierten Parent-Tabelle sein.

Die Constraint sollte dann auch "ON DELETE SET NULL" beinhalten, d.h. wenn der "GeändertVon"-User 4711 aus der Parent-Tabelle gelöscht wird, dann soll in der Child-Tabelle überall der "GeändertVon"-User 4711 auf NULL geändert werden.
Dazu muss aber das Feld NULLABLE sein.

Grüße
Biber
Mitglied: SlainteMhath
SlainteMhath 11.07.2014 um 12:32:24 Uhr
Goto Top
@Biber
Ja du hast natürlich Recht. Ich arbeite bei FK's aber sehr ungern mit CASCADE/ON DELETE SET NULL. Lieber lass ich das DBS einen Fehler schmeissen als das es mir mit der NULL-Kettensäge durch die DB fährt face-smile

@TheUntouchable
Wenns Du's wirklich so haben willst, brauchst du keine zusätzliche Tabelle. Beachte aber den Post von Biber.
Mitglied: Biber
Biber 11.07.2014 aktualisiert um 13:14:43 Uhr
Goto Top
Moin Slainthe,,

na ja, bei CASCADE gebe ich dir recht.
Aber bei SET NULL...
Gerade hier in diesem Fall wären die Alternativen

- (bei einem Standard ON DELETE RESTRICT) - der User 4711, der vor 2 Jahren Admin war und 50 andere "geändert" hat (z.B. Passwort-Reset) darf niemals nich gelöscht werden, auch wenn er schon seit der letzten Sonnenwendfeier in Walhalla frühstückt.
- (bei ON DELETE CASCADE) - der User 4711 wird aus o.a. Gründen gelöscht und alle 50 User, denen er beim Passwort-Reset gehölfen hat, folgen ihm in die grosse Trinkhalle.

Da wäre meine Präferenz doch ein ON DELETE SET NULL.

Grüße
Biber:
Mitglied: TheUntouchable
TheUntouchable 15.07.2014 aktualisiert um 22:10:24 Uhr
Goto Top
Kleines/Großes Update:

Meine Tabellen sehen jetzt folgender maßen aus:

create table gruppen (
	GNr			int unsigned auto_increment not null primary key,
	GName		varchar(20)
);

create table benutzer (
	BNr			int unsigned auto_increment not null primary key,
	BName		varchar(20) not null,
	Passwort	varchar(256) not null,
	Gruppe		int unsigned,

	foreign key (Gruppe)
	references gruppen(GNr)
);

create table überwachung(
	BNr			int unsigned not null primary key,
	LetzerLogin	datetime,
	LetzteAktio	datetime,
	Erstellt	datetime not null,
	ErstelltVon int unsigned not null,
	Geändert	datetime,
	GeändertVon	int unsigned,

	foreign key(BNr)
	references benutzer(BNr)
	ON DELETE SET NULL,
	
	foreign key (ErstelltVon)
	references benutzer (BNr)
	ON DELETE SET NULL,

	foreign key (GeändertVon)
	references benutzer (BNr)
	ON DELETE SET NULL
);

Hier ein paar Daten, um mich dann auch am System anmelden zu können:

insert into benutzer (BName, Passwort) values
('Michael','-zensiert-');  

insert into überwachung (BNr,Erstellt, ErstelltVon) values
('1','0000-00-00 00:00:00','1')  

Mit dieser Abfrage
select * from benutzer inner join überwachung using (BNr)

Bekomme ich dann folgende Ausgabe:
# BNr, BName, Passwort, Gruppe, LetzerLogin, LetzteAktio, Erstellt, ErstelltVon, Geändert, GeändertVon
'1', 'Michael', '-zensiert-', NULL, NULL, NULL, '0000-00-00 00:00:00', '1', NULL, NULL  

Und jetzt meine Frage, auf die ich schon die ganze Zeit hinaus will:
Wie bekomme ich es hin, das bei Erstellt Von nicht die 1 sondern der Name des Benutzers steht? :D

PS: Vielen Dank für die schon gekommen Antworten und für die noch kommenden! :D
Mitglied: SlainteMhath
Lösung SlainteMhath 16.07.2014 aktualisiert um 11:33:16 Uhr
Goto Top
Versuchs mal so
select u.*, b1.BName as ErstelltVonBenutzer, b2.BName as GeaendertVonBenutzer
from uberwachung as u
left join Benutzer as b1 on u.ErstelltVon = b1.BNr
left join Benutzer as b2 on u.GeaendertVon = b2.BNr
Mitglied: TheUntouchable
TheUntouchable 16.07.2014 um 11:34:06 Uhr
Goto Top
Super, hat geklappt! Vielen vielen Dank face-smile Das mit den Alias für die Tabellen war mir neu face-smile
Mitglied: TheUntouchable
TheUntouchable 16.07.2014 um 13:52:31 Uhr
Goto Top
Das ist jetzt meine endgültige Lösung, falls es jemanden interessiert ;)

$anweisung =  	"select benutzer.BNr, benutzer.BName, gruppen.GName, überwachung.LetzerLogin, überwachung.LetzteAktio, überwachung.Erstellt, b1.BName as ErstelltVon, überwachung.Geändert, b2.BName as GeändertVon  
						from benutzer left join überwachung using (BNr)
						left join benutzer as b1 on b1.BNr = überwachung.ErstelltVon
						left join benutzer as b2 on b2.BNr = überwachung.GeändertVon
						left join gruppen on gruppen.GNr = benutzer.Gruppe";