Discussion :

[mtisset]

Bonjour à tous,

je vous présente rapidement mon problème puis je vous pose ma question.

J'ai trois tables :

• Utilisateurs avec l'uid et les pseudos des utilisateurs
• Roles avec un identifiant (rid) et les noms de mes rôles
• Utilisateurs_Roles une table de jonction entre les deux précédentes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE TABLE IF NOT EXISTS Utilisateurs (
	uid SERIAL,
	username VARCHAR(45) NOT NULL,
	...
	PRIMARY KEY (uid),
	UNIQUE (username)
);
 
CREATE TABLE IF NOT EXISTS Roles (
	rid SERIAL,
	nom VARCHAR(60) NOT NULL,
	PRIMARY KEY (rid),
	UNIQUE (nom)
);
 
CREATE TABLE IF NOT EXISTS Utilisateurs_Roles (
	uid INT NOT NULL,
	rid INT NOT NULL,
	PRIMARY KEY (uid, rid),
	FOREIGN KEY (uid)
		REFERENCES Utilisateurs(uid)
		ON DELETE CASCADE,
	FOREIGN KEY (rid)
		REFERENCES Roles(rid)
		ON DELETE CASCADE		
);

Je souhaite créer une table 'classe' avec une contrainte sur la colonne uid

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE IF NOT EXISTS Classes (
	id SERIAL,
	nom VARCHAR(45),
	profPrinc_id INT, 
	PRIMARY KEY (id),
	UNIQUE (nom),
	FOREIGN KEY (profPrinc_id)
		REFERENCES Utilisateurs(id)
		ON DELETE SET NULL
)

J'aimerais que sur la clé profPrinc_id il y ait une contrainte pour qu'on ne puisse mettre que des uid d'utilisateurs ayant un certain rôle (exemple rid = 3).
Ma première idée était d'introduire une sous-requête dans un CHECK sur profPrinc_id mais ce n'est apparemment pas possible (d'après mes recherches).

Quelqu'un aurait-il une idée pour cela ? Ou faut-il que je déplace le problème dans mon php ?

Merci par avance

[punkoff]

Bonjour,

utilisez des triggers : before insert / before update


Regardez la doc pour des exemples

[SQLpro]

C'est votre modélisation qui est foireuse. Si vous avez besoin de distinguer certains utilisateurs d'autres, alors il vous faut faire une spécialisation via l'héritage de données.

A +

[mtisset]

Bonjour,

merci pour vos réponses.
Je ne connaissais par les triggers (pas encore), je vais aller y jeter un œil.

@SQLPro : effectivement, je suis en train de re-réfléchir à ma modélisation, car elle ne me satisfait pas pleinement (c'est ma 1ère modélisation et je crois qu'il faut que je m'y repenche).

Je vais me renseigner sur les deux notions dont vous me parlez (trigger et héritage) et peut-être reprendre ma modélisation.
Ne vous sauvez pas, je reviens quand j'aurai terminé mes recherches et mes réflexions

[alassanediakite]

Salut
Je propose cette fonction...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
create or replace function fncheckrole(id int)returns boolean
as
$$
begin
return exists (select utilisateurs_roles.rid FROM 
  utilisateurs inner join   utilisateurs_roles on utilisateurs.uid = utilisateurs_roles.uid
  where utilisateurs_roles.rid=1 and utilisateurs.uid=$1 );
end;$$ language 'plpgsql'

et la contrainte...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE classes
  ADD CONSTRAINT classes_profprinc_id_check CHECK (fncheckrole(profprinc_id) = true);

Je vois que cette solution n'est pas très viable si le contenu de la table "role" est fréquemment changé.
Si maitre SQLpro pouvait détailler un peu son propos nous en tirerons peut être plus de bénéfice.
@+

[mtisset]

Re-bonjour,

bon, j'ai un peu potassé sur le sujet et notamment sur les héritages dont tu parles SQLPro (très bonne doc au passage).
Du coup, je partirais plutôt vers une modélisation du type :

Table générale des utilisateurs :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE IF NOT EXISTS Utilisateurs (
	uid SERIAL,
	username VARCHAR(45) NOT NULL,
	nom VARCHER(45) NOT NULL,
	prenom VARCHER(45) NOT NULL,
	...
	PRIMARY KEY (uid),
	UNIQUE (username)
);

Puis une table par "rôle" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE IF NOT EXISTS Profs (
	uid SERIAL,
	PRIMARY KEY (uid),
	FOREIGN KEY (uid)
		REFERENCES Utilisateurs (uid)
);
 
CREATE TABLE IF NOT EXISTS Eleves (
	uid SERIAL,
	classe INT,
	...
	PRIMARY KEY (uid),
	FOREIGN KEY (uid)
		REFERENCES Utilisateurs (uid)
);
 
idem Parents

Il faut donc que je mette en place les différents triggers pour vérifier l'existence de la clé Utilisateurs(uid) lors de l'ajout/modification d'un prof ou élève ou parent.
Ainsi que le trigger permettant de ne pas supprimer un Utilisateurs(uid) si l'uid en question est utilisé dans une table fille.

Du coup, ma table Classe peut pointer par une clé étrangère sur la table Profs, ce qui m'assure que la colonne ProfPrinc_id contient bien un uid prof :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE IF NOT EXISTS Classes (
	id SERIAL,
	nom VARCHAR(45),
	profPrinc_id INT, 
	PRIMARY KEY (id),
	UNIQUE (nom),
	FOREIGN KEY (profPrinc_id)
		REFERENCES Profs (uid)
		ON DELETE SET NULL
);

Deux questions :
- @SQLPro : c'est moins foireux comme ça ? ;-)
- Petite question, lors de l'ajout d'un prof ou d'un élève, est-il possible de créer automatiquement l'insertion dans la table Utilisateurs et dans la table Profs
ou est-ce que je suis obligé de faire deux requêtes différentes (insert dans Utilisateurs puis insert dans Profs et mettre le tout dans une transaction pour éviter tout soucis) ?

Merci encore

[punkoff]

bonjour,

Regardez du côté des RULE (spécifique à postgresql de mémoire), applicable sur une vue par exemple.


Sinon au niveau de la modélisation, je ne pense pas que la classe doive être spécifiée au niveau de l'entité élève.

On est dans une relation de type n/n logiquement, vu qu'un élève pourra faire différentes classes lors de sa scolarité et qu'une classe est composée de plusieurs élèves.

[alassanediakite]

Salut
Je pense qu'il faut simplifier la vie. La grosse artillerie n'est vraiment pas la peine.
Elève et prof dans la même table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE IF NOT EXISTS Utilisateurs (
uid SERIAL,
username VARCHAR(45) NOT NULL,
nom VARCHER(45) NOT NULL,
prenom VARCHER(45) NOT NULL,
typepersonne smallint not null check(typepersonne in(1,2)), --1=élève 2=prof
...
PRIMARY KEY (uid),
UNIQUE (username)
);

et utiliser la fonction et la contrainte que j'ai donnée plus haut.
La fonction est modifiée comme suite

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION fncheckrole(id int)returns BOOLEAN
AS
$$
begin
RETURN EXISTS (SELECT utilisateurs.uid FROM 
  utilisateurs   WHERE typepersonne =2 AND utilisateurs.uid=$1 );
end;$$ LANGUAGE 'plpgsql'

@+

[mtisset]

Sinon au niveau de la modélisation, je ne penses pas que la classe doit être spécifiée au niveau de l'entité élève.

On est dans une relation de type n/n logiquement, vu qu'un élève pourra faire différente classe lors de sa scolaritée et qu'une classe est composée de plusieurs élèves

Dans la modélisation choisie, je me concentre sur une année, donc on est en relation 1/n (1 élève a une classe et une classe a plusieurs élèves). L'historique des classes d'un élève est gérée dans une table à part (je perds tout le tintouin, mais ce qui m'intéresse, c'est juste ses précédentes classes).

bonjour, Elève et prof dans la même table

J'ai trop d'utilisateurs aux caractéristiques différentes pour les mettre dans la même table (prof, parent élève, élève, des parents d'élèves pouvant aussi être prof, les CPE, les assistants d'éduc, ...)
Mes sous-tables me paraissent donc importantes. En revanche, ton idée de fonction pour tester les rôles peut être appropriée tout-de-même en mettant un codage binaire :
1er bit : 0 ou 1 : prof
2e bit : 0 ou 1 : élève
3e bit : 0 ou 1 : parent
...