Aller au contenu principal

Table profils / classes

Création de la table de relation entre utilisateurs et classes

Notions théoriques​

Comprendre les relations many-to-many​

Dans une base de données relationnelle, une relation many-to-many (n:m) signifie qu’un enregistrement dans une table peut être lié à plusieurs enregistrements dans une autre table, et inversement.

info

Dans notre projet, un utilisateur peut appartenir à plusieurs classes, et une classe peut contenir plusieurs utilisateurs.

Un SGBDR comme PostgreSQL (et Supabase) ne permet pas de créer une relation many-to-many directement entre deux tables. Il faut créer une table intermédiaire appelée table de jointure.

La table de jointure class_members​

Cette table permet de relier les utilisateurs (auth.users) aux classes (public.classes). Elle contient :

ColonneTypeDescription
iduuidIdentifiant unique de la relation
class_iduuidRéférence à la classe (clé étrangère)
user_iduuidRéférence à l’utilisateur (clé étrangère)
joined_attimestamp with time zoneDate d’inscription à la classe

Clés étrangères et ON DELETE CASCADE​

Les colonnes class_id et user_id sont des clés étrangères. Elles permettent de garantir que les valeurs insérées existent bien dans les tables référencées.

class_id uuid REFERENCES public.classes(id) ON DELETE CASCADE
user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE
info

La clause ON DELETE CASCADE signifie que si une classe ou un utilisateur est supprimé, tous les enregistrements associés dans class_members seront aussi supprimés automatiquement.

attention

Il est important d’utiliser CASCADE uniquement si la suppression automatique est bien souhaitée. Cela peut entraîner la perte de données si mal utilisé.

Contrainte UNIQUE sur deux colonnes​

Il faut éviter qu’un même utilisateur soit inscrit deux fois à la même classe. Pour cela, on ajoute une contrainte :

UNIQUE(class_id, user_id)

Cette contrainte empêche l’insertion de doublons pour un même couple (classe, utilisateur).

Valeurs par défaut​

Comme dans les autres tables :

  • id est généré automatiquement avec uuid_generate_v4()
  • joined_at est rempli automatiquement avec l’heure actuelle (en UTC)
id uuid default uuid_generate_v4()
joined_at timestamp with time zone default timezone('utc'::text, now())

Résumé de la structure​

Voici le script complet pour créer la table :

create table public.class_members (
  id uuid default uuid_generate_v4() primary key,
  class_id uuid references public.classes(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  joined_at timestamp with time zone default timezone('utc'::text, now()) not null,
  unique(class_id, user_id)
);

Bonne pratique cyber​

  • Validation des références : toujours s’assurer que les UUID utilisés existent réellement dans les tables classes et auth.users.
  • Usage prudent de CASCADE : ne jamais utiliser ON DELETE CASCADE sans comprendre ses conséquences.
  • Auditabilité : le champ joined_at permet de tracer l’historique des inscriptions.

Exemple pratique​

Créer la table class_members dans Supabase​

Il est possible de créer la table via le SQL Editor de Supabase avec le script suivant :

create table public.class_members (
  id uuid default uuid_generate_v4() primary key,
  class_id uuid references public.classes(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  joined_at timestamp with time zone default timezone('utc'::text, now()) not null,
  unique(class_id, user_id)
);

Ce script :

  • crée une clé primaire sur id,
  • ajoute deux clés étrangères avec suppression en cascade,
  • empêche les doublons (même utilisateur dans la même classe),
  • ajoute un champ joined_at renseigné automatiquement.

Insérer un membre dans une classe​

Il est possible d’ajouter un utilisateur existant à une classe existante avec la requête suivante :

insert into public.class_members (class_id, user_id)
values ('<UUID_de_la_classe>', '<UUID_de_l_utilisateur>');
remarque

Il faut remplacer les deux UUID par des identifiants valides présents dans les tables classes et auth.users.

Vérifier les inscriptions​

Aller dans Table Editor > class_members pour vérifier que :

  • Une ligne a bien été insérée,
  • joined_at est rempli automatiquement,
  • id est un UUID généré automatiquement.

Tester la contrainte UNIQUE​

Si on essaie d’insérer deux fois le même couple class_id / user_id, une erreur SQL est levée :

insert into public.class_members (class_id, user_id)
values ('<UUID>', '<UUID>'); -- même couple qu'avant

Erreur attendue : duplicate key value violates unique constraint.

Supprimer une classe et observer les effets​

Si on supprime une classe :

delete from public.classes where id = '<UUID>';

Alors toutes les lignes associées dans class_members sont automatiquement supprimées.

Test de mémorisation/compréhension​


Quel type de relation est représenté par la table class_members ?


Quel champ permet de savoir quand un utilisateur a rejoint une classe ?


Quelle clause empêche un utilisateur d'être inscrit deux fois à la même classe ?


Que fait ON DELETE CASCADE sur une clé étrangère ?


Pourquoi utilise-t-on uuid_generate_v4() pour la colonne id ?


Quel est le type de données utilisé pour class_id et user_id ?


Que se passe-t-il si on insère un user_id qui n'existe pas ?


Dans quel cas la suppression d'une classe supprime aussi des lignes de class_members ?


Pourquoi est-il recommandé de valider les UUID avant insertion ?


Quel champ de class_members est automatiquement rempli avec la date actuelle ?


TP pour réfléchir et résoudre des problèmes​

Objectif du TP​

Mettre en place la table de relation class_members entre les utilisateurs et les classes, en respectant les bonnes pratiques de modélisation relationnelle et de cybersécurité.

Étape 1 — Créer la table class_members via l’éditeur SQL de Supabase​

  1. Se connecter à https://supabase.com et accéder à son projet.
  2. Ouvrir l’onglet SQL Editor.
  3. Coller et exécuter le script suivant :
create table public.class_members (
  id uuid default uuid_generate_v4() primary key,
  class_id uuid references public.classes(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  joined_at timestamp with time zone default timezone('utc'::text, now()) not null,
  unique(class_id, user_id)
);
Une solution

Étape 2 — Vérifier que la table a été créée correctement​

  1. Aller dans l’onglet Table Editor.
  2. Vérifier que la table class_members existe.
  3. Vérifier que les colonnes suivantes sont présentes : id, class_id, user_id, joined_at.
  4. Vérifier les contraintes : clés étrangères et contrainte UNIQUE.
Une solution

Étape 3 — Insérer une classe et un utilisateur si besoin​

Si vous n’avez pas encore de classe ou d’utilisateur, créez-les :

insert into public.classes (id, name, description)
values (uuid_generate_v4(), 'Classe Alpha', 'Introduction à PostgreSQL');

Pour un utilisateur, vous pouvez en créer un via l’interface Auth > Users de Supabase.

Une solution

Étape 4 — Insérer une ligne dans class_members​

  1. Récupérer l’id d’un utilisateur existant dans auth.users.
  2. Récupérer l’id d’une classe existante dans public.classes.
  3. Exécuter la requête suivante :
insert into public.class_members (class_id, user_id)
values ('<id_classe>', '<id_utilisateur>');

Remplacer <id_classe> et <id_utilisateur> par les UUID réels.

Une solution

Étape 5 — Vérifier que l’insertion a fonctionné​

  1. Aller dans Table Editor > class_members.
  2. Vérifier que la ligne est bien présente.
  3. Vérifier que la colonne joined_at est remplie automatiquement.
  4. Vérifier que l’UUID généré pour id est bien présent.
Une solution

Étape 6 — Tester la contrainte UNIQUE​

  1. Réexécuter la même requête d’insertion qu’à l’étape 4.
  2. Observer l’erreur retournée par Supabase.
Une solution

Étape 7 — Tester la suppression en cascade​

  1. Supprimer la classe utilisée avec la commande suivante :
delete from public.classes where id = '<id_classe>';
  1. Vérifier que l’entrée correspondante dans class_members a bien été supprimée.
Une solution

Étape 8 — Refaire un test avec un nouvel utilisateur et une nouvelle classe​

  1. Créer une nouvelle classe et un nouvel utilisateur.
  2. Insérer une nouvelle ligne dans class_members.
  3. Supprimer l’utilisateur.
  4. Vérifier que la ligne dans class_members est bien supprimée.
Une solution

Étape 9 — Écrire une requête SQL pour afficher tous les membres d’une classe​

Écrire une requête SQL qui affiche tous les user_id associés à une classe donnée.

select user_id from public.class_members
where class_id = '<id_classe>';
Une solution

Étape 10 — Vérifier l’intégrité des données​

  1. Tenter d’insérer un class_id ou user_id qui n’existe pas.
  2. Observer le message d’erreur.
Une solution