Entités et relations

Ce que vous saurez faire

Décidez de quoi le service de soutien garde une trace et comment chaque enregistrement est identifié. Puis décidez combien d’enregistrements d’un type appartiennent à un enregistrement d’un autre type. Ces choix déterminent chaque table, règle et requête dans le reste du cours.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 50 minutes

Ce que vous devez savoir

• Les clés primaires, les clés étrangères, les relations un-à-plusieurs et la lecture d’un diagramme entité-association, vus dans Les concepts relationnels du cours SQL et préparation des données pour l’IA.
• Les tables du service de soutien et comment ouvrir psql, vus dans De SQLite à PostgreSQL.
• Ce que le service de soutien stocke et comment il l’utilise, vus dans Inventaire des modes d’accès.

Ce qu’il vous faut sur votre ordinateur

• Le projet ~/projects/ticket-api après le module 1 (l’instantané end-of-m01), avec son environnement virtuel activé.
• PostgreSQL en marche (docker compose up -d db), avec le petit jeu de données chargé. python -m pytest affiche 34 passed (34 tests réussis).

Cette leçon ajoute docs/schema.md au projet.

Les résultats de ce module proviennent d’un Apple M4 avec 16 Go, sous macOS 26.6. La base de données utilisait PostgreSQL 18.6 avec pgvector 0.8.7, dans Docker. Vos temps seront différents; les nombres seront les mêmes.

Nous avons testé les étapes sur macOS seulement. Les commandes Windows sont fournies, mais elles n’ont pas été testées.

Une carte de bibliothèque

Pensez à une bibliothèque. Elle garde des enregistrements de trois types de choses : les membres, les livres et les prêts. Quand vous vous inscrivez, vous recevez une carte avec un numéro. Ce numéro reste le même quand vous déménagez ou changez de nom. La bibliothèque ne l’attribue jamais à un autre membre, même après votre départ.

La bibliothèque compte deux membres qui s’appellent Emma Petrov. Personne ne les confond, car le bibliothécaire regarde le numéro de carte, pas le nom. Une fiche de prêt ne répète pas non plus votre adresse. Elle contient votre numéro de carte, et votre adresse se trouve à un seul endroit : votre fiche de membre.

Comptez maintenant les liens. Un membre peut avoir zéro prêt, un prêt ou vingt prêts. Chaque prêt appartient à exactement un membre et à un exemplaire d’un livre. Une bibliothèque qui écrirait « le livre emprunté » sur la carte du membre serait bloquée quand il emprunte un deuxième livre.

Cette leçon présente trois idées : les types de choses, un identifiant qui ne change jamais et combien d’éléments d’un type appartiennent à un autre.

Le problème : une ligne qui ne peut pas contenir une liste

Regardez le billet de soutien T-30034, « Colis arrivé endommagé ». Ouvrez psql dans le dossier du projet :

docker compose exec db psql -U tickets -d tickets

Puis demandez les fichiers du billet :

SELECT ticket_id, file_name, content_type, size_bytes
FROM attachments WHERE ticket_id = 'T-30034';

 ticket_id |  file_name  | content_type | size_bytes
-----------+-------------+--------------+------------
 T-30034   | photo-1.png | image/png    |       3613
 T-30034   | photo-2.png | image/png    |       3767
(2 rows)

Le client a envoyé deux photos. Supposons que l’ancien outil ait conservé le fichier dans une colonne du billet, photo. La première photo y entre, mais la deuxième n’a pas de place. Une deuxième colonne, photo_2, ne fait que déplacer le problème : une troisième photo n’aurait alors pas de place.

Une colonne contient une valeur. Une table de fichiers, où chaque ligne indique son billet, contient autant de fichiers par billet que nécessaire.

Description textuelle du diagramme

À gauche, la ligne du billet a une seule colonne photo, qui contient photo-1.png. La deuxième photo, photo-2.png, n’a pas de place. Une deuxième colonne ne ferait que déplacer la limite à deux photos. À droite, les fichiers sont des lignes dans leur propre table, attachments. Chaque ligne de fichier contient l’identifiant du billet T-30034. Le billet peut donc avoir zéro, un, deux ou plusieurs fichiers.

Il y a un deuxième problème, qui concerne les noms. Cherchez un client par son nom :

SELECT customer_id, name, email, segment, region, joined_on
FROM customers WHERE name = 'Emma Petrov';

 customer_id |    name     |          email           | segment | region | joined_on
-------------+-------------+--------------------------+---------+--------+------------
 C-0004      | Emma Petrov | emma.petrov@example.com  | home    | west   | 2020-04-05
 C-0012      | Emma Petrov | emma.petrov2@example.com | trade   | west   | 2025-11-22
(2 rows)

Deux clients différents portent le même nom, comme les deux membres de la bibliothèque. Si un billet indiquait « de la part d’Emma Petrov », personne ne pourrait savoir quel client l’a écrit.

L’idée : entités, identifiants et cardinalité

Une entité est un type de chose dont le service de soutien garde une trace : un client, un billet, un message. Chaque entité a sa propre table, et chaque ligne représente une chose. Le cours de SQL appelait cela la granularité d’une table.

Un identifiant désigne une seule chose. Dans une table, c’est la clé primaire. Un bon identifiant est stable : il ne change jamais et n’est jamais attribué à une autre chose. Le numéro de carte de bibliothèque est stable. Un nom n’est pas unique, et une adresse courriel peut changer.

Une relation indique comment deux entités sont liées, par exemple « un client écrit des billets ». Sa cardinalité indique combien il y en a de chaque côté : au minimum zéro ou un, au maximum un ou plusieurs. Ce mot vient des mathématiques, où la cardinalité d’un ensemble est le nombre de ses éléments.

Il existe trois formes courantes :

• Un-à-plusieurs : un client a zéro ou plusieurs billets, et chaque billet a exactement un client. La clé étrangère va du côté « plusieurs » : tickets.customer_id.
• Un-à-un, généralement « un à zéro ou un » : un billet a au plus une ligne dans une autre table. La clé primaire de cette table peut aussi être sa clé étrangère.
• Plusieurs-à-plusieurs : un billet peut citer plusieurs documents de politique, et plusieurs billets peuvent citer un même document. Aucun côté ne peut contenir une liste, alors une troisième table contient une ligne par paire. Vous en concevrez une à la fin de cette leçon.

Voici un petit exemple détaillé où chaque valeur est visible. Le client C-0022 est Hugo Garcia, et son billet T-30002 sert d’exemple tout au long du cours :

Enregistrement	Combien	Les lignes
Billets d’Hugo Garcia	6	T-30002, T-30111, T-30119, T-30130, T-30140, T-30171
Messages de T-30002	1	message 2, d’un agent : « Pourriez-vous envoyer le reçu ou une capture d’écran du… »
Exécutions d’IA de T-30002	1	exécution 3 : classify (classifier) avec keywords-1.0, coût 0, état ok (réussi)
Fichiers de T-30002	0	aucun
Vecteurs de T-30002	1	384 nombres

Chaque ligne enfant indique son parent : un billet contient C-0022, et un message contient T-30002. Aucun parent ne contient une liste de ses enfants. C’est ainsi qu’une table stocke « plusieurs » : plusieurs lignes qui pointent vers une seule ligne.

Le service de soutien en une image

Un diagramme entité-association (ERD) représente les entités, leurs clés et leurs relations. Voici le schéma du service de soutien à la fin de ce module :

Les entités et les relations du service de soutien à la fin du module 2. Les marques à chaque extrémité d'une ligne indiquent combien : une barre signifie un, un cercle signifie zéro, une fourche signifie plusieurs.

Description textuelle du diagramme

Sept tables. customers a la clé primaire customer_id (texte, par exemple C-0001). Elle a aussi un nom, un courriel unique sans distinction de majuscules et de minuscules, un segment et une région. Une ligne de customers vers tickets indique : chaque billet a exactement un client, et un client a zéro ou plusieurs billets. tickets a la clé primaire ticket_id (texte, par exemple T-30001) et la clé étrangère customer_id. Elle a aussi le sujet et le corps, l’équipe, la priorité de 1 à 3, l’état, les heures et message_count, une copie volontaire. Quatre lignes partent de tickets. Vers messages (message_id, un nombre) : chaque message a exactement un billet, et un billet a zéro ou plusieurs messages. Vers attachments (attachment_id, un UUID; object_key, unique) : chaque fichier a exactement un billet, et un billet a zéro ou plusieurs fichiers. Vers ai_runs (run_id, un nombre) : une exécution a zéro ou un billet, et un billet a zéro ou plusieurs exécutions. Vers ticket_embeddings (la clé est ticket_id) : un vecteur a exactement un billet, et un billet a zéro ou un vecteur. documents (clé : doc_id et version) n’a aucune ligne de relation.

Lisez chaque ligne dans les deux sens, comme dans le cours de SQL. Puis demandez pourquoi elle a cette forme, car chaque marque représente une décision :

• customers → tickets : exactement un client. L’équipe de Grace doit toujours savoir à qui répondre. Un billet sans client est une erreur, donc customer_id est obligatoire.
• tickets → messages et attachments : exactement un billet. Un message ou un fichier sans son billet n’a de sens pour personne.
• tickets → ai_runs : zéro ou un billet. Priya compare les modèles selon leur coût mensuel. Si un client demande à Larkfield de supprimer ses données, les billets disparaissent. Mais le coût de leurs exécutions d’IA doit rester dans les totaux. Une exécution peut donc perdre son billet, et son ticket_id peut être vide. Restauration et conservation utilise cette règle.
• tickets → ticket_embeddings : zéro ou un vecteur. Aujourd’hui, la clé de la table des vecteurs est l’identifiant du billet, donc un billet a au plus un vecteur. Dans Stockage des vecteurs, chaque billet reçoit un vecteur par version de plongement (embedding). Cette extrémité devient alors « zéro ou plusieurs ».
• documents : aucune ligne de relation. Les assistants lisent les documents de politique, mais aucun billet n’en possède un. La clé d’un document a deux colonnes, doc_id et version. Par exemple, returns-policy a une version 3, valide jusqu’au 31 octobre 2026, et une version 4, valide à partir du 1er novembre 2026.

Sam demande : « Pourquoi un billet ne garde pas une liste des identifiants de ses messages? Ça éviterait une jointure. » Mei répond avec le problème des photos. Une liste dans une colonne n’a pas de longueur fixe, et la base de données ne peut pas vérifier que chaque élément existe. Elle peut vérifier une clé étrangère du côté « plusieurs ». La prochaine leçon lui fera faire cette vérification.

Choisir les identifiants

Le projet utilise trois types de clés primaires. Chacun est un choix, avec une raison et un coût.

Identifiants textuels de l’ancien outil, pour les billets et les clients (T-30001, C-0001). Ces identifiants sont publics. Ils apparaissent dans les courriels aux clients, et les agents les disent au téléphone. Les identifiants des clients sont aussi les mêmes que dans l’exportation du cours de SQL. Si le projet les garde, chaque ancien courriel et rapport pointe encore vers le bon enregistrement. La base de données ne doit donc jamais inventer un identifiant de billet différent, ni réutiliser un identifiant.

Ils ont un petit coût. Une clé textuelle se compare caractère par caractère, ce qui est plus lent que de comparer deux nombres. Son format peut être incorrect, par exemple 30003 sans le T-. La prochaine leçon ajoute donc une règle de format. Ici, un identifiant de billet stocké occupe 8 octets, comme un nombre de 64 bits. Sa taille n’est donc pas un problème. Une longue clé textuelle en serait un : olga.olsen@example.com occupe 23 octets, et chaque index sur une telle clé devient plus volumineux.

Nombres d’identité, pour les messages et les exécutions d’IA. Personne ne dit « message 332 » au téléphone, donc un nombre fourni par la base de données suffit. message_id bigint GENERATED BY DEFAULT AS IDENTITY signifie que PostgreSQL attribue le prochain nombre quand le programme n’en envoie pas.

Un UUID, pour les pièces jointes. Un UUID est une valeur aléatoire de 128 bits, comme fcf3867b-6929-4624-9e25-c526ce2f3e67, et occupe 16 octets. Le programme le crée avant de téléverser le fichier. Il devient une partie de l’adresse du fichier dans le stockage objet : tickets/T-30034/fcf3867b-…/photo-1.png. Personne ne peut s’en servir pour deviner l’adresse du fichier d’un autre client. Modèles de stockage objet téléverse les fichiers de cette façon.

Jamais un nom ni une adresse courriel. Les noms se répètent, comme le montrent les deux Emma Petrov. Les courriels changent, et un courriel est une donnée personnelle. Comme clé, il serait copié dans chaque ligne qui pointe vers le client. Une suppression des données du client devrait alors retrouver chaque copie.

Entité	Clé	Exemple	Pourquoi
customers	texte de l’ancien outil	C-0022	public, stable, utilisé dans les anciens rapports
tickets	texte de l’ancien outil	T-30002	public : dans les courriels et au téléphone
messages, ai_runs	nombre d’identité	2, 3	interne; la base de données l’attribue
attachments	UUID	fcf3867b-…	créé avant le téléversement; partie de l’adresse du fichier
documents	doc_id + version	returns-policy, 4	un document a plusieurs versions

Exercice guidé : vérifier les relations dans les données

Le diagramme entité-association fait des promesses, par exemple « un billet a zéro ou plusieurs messages » et « chaque message a exactement un billet ». Vérifiez-les dans les données.

1. Comptez les billets selon leur nombre de messages. Exécutez cette requête dans psql :

   SELECT n AS messages, count(*) AS tickets
   FROM (SELECT t.ticket_id, count(m.message_id) AS n
         FROM tickets t LEFT JOIN messages m ON m.ticket_id = t.ticket_id
         GROUP BY t.ticket_id) per_ticket
   GROUP BY n ORDER BY n;

    messages | tickets
   ----------+---------
           0 |      26
           1 |      71
           2 |      64
           3 |      24
           4 |      15
   (5 rows)

   26 billets n’ont aucun message après le premier, et aucun billet n’en a plus de 4. « Zéro ou plusieurs » est correct. Le LEFT JOIN (jointure externe gauche) garde les billets sans messages. Une jointure interne masquerait la ligne pour zéro message.

2. Vérifiez maintenant l’autre extrémité. Chaque message a-t-il son billet?

   SELECT m.message_id, m.ticket_id, m.author, m.body
   FROM messages m LEFT JOIN tickets t ON t.ticket_id = m.ticket_id
   WHERE t.ticket_id IS NULL;

    message_id | ticket_id | author |             body
   ------------+-----------+--------+------------------------------
           332 | T-29994   | agent  | Test message, please ignore.
           333 | T-29997   | agent  | Test message, please ignore.
           334 | T-29999   | agent  | Test message, please ignore.
   (3 rows)

   Ce sont trois orphelins : des messages dont le billet n’existe pas. Ce sont des messages de test, et quelqu’un a supprimé leurs billets à la main dans l’ancien outil. Le diagramme indique « exactement un billet », mais la base de données ne connaît pas encore le diagramme. Les données du cours contiennent ces trois lignes volontairement, et la prochaine leçon s’en occupe.

3. Quittez psql avec \q. Copiez le document du schéma depuis l’instantané vers votre projet. Dans ~/projects/ticket-api, exécutez :

   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/docs/schema.md docs/

   Sur Windows, dans PowerShell : Copy-Item ..\nextia-learning-labs\C20\snapshots\end-of-m02\docs\schema.md docs\.

4. Ouvrez docs/schema.md dans VS Code, puis ouvrez son aperçu (Ctrl+Shift+V, ou Cmd+Shift+V sur macOS). Le diagramme est écrit en Mermaid, un format textuel pour les diagrammes, et GitHub le dessine aussi. Si l’aperçu affiche seulement du texte, lisez-le : chaque ligne comme tickets ||--o{ messages : "has" représente une relation.

docs/schema.md représente « zéro ou plusieurs » vecteurs par billet, car il montre déjà où le cours mène. Le fichier nomme les migrations ultérieures qui modifient le schéma.

Variante autonome : des billets qui citent des documents

Les assistants du cours RAG : créer une IA qui utilise vos données répondent en citant les documents de politique qu’ils ont utilisés. Grace veut conserver ces citations : quelles versions des documents la réponse à chaque billet a citées.

Dessinez la relation entre les billets et les documents sur papier, puis répondez à trois questions :

1. Quelle est la cardinalité de chaque côté?
2. Où vont les clés? Rappelez-vous que la clé d’un document a deux colonnes.
3. Quelle est la clé primaire de la nouvelle table, et qu’empêche-t-elle?

Afficher un exemple de réponse

C’est une relation plusieurs-à-plusieurs. Un billet peut citer zéro ou plusieurs versions de documents. Zéro ou plusieurs billets peuvent citer une version de document. Aucun côté ne peut contenir une liste, alors une nouvelle table contient une ligne par citation :

CREATE TABLE ticket_citations (
    ticket_id   text    NOT NULL REFERENCES tickets (ticket_id) ON DELETE CASCADE,
    doc_id      text    NOT NULL,
    doc_version integer NOT NULL,
    PRIMARY KEY (ticket_id, doc_id, doc_version),
    FOREIGN KEY (doc_id, doc_version) REFERENCES documents (doc_id, version)
);

Elle a deux clés étrangères : une vers le billet, et une avec deux colonnes vers la version du document. La clé primaire réunit les trois colonnes, donc un billet ne peut pas citer deux fois la même version. Le diagramme reçoit deux lignes un-à-plusieurs : de tickets vers ticket_citations, et de documents vers ticket_citations. Vous créerez et testerez cette table dans l’exercice du module à la fin de Transactions. Elle ne fera pas partie du projet.

Erreur courante

« Trouver le client par son nom »

Symptôme : une requête ou un programme cherche un client par son nom et obtient deux lignes. C-0004 et C-0012 s’appellent tous deux Emma Petrov. Un programme qui prend la première ligne rattache le billet au mauvais client, et rien ne signale d’erreur.

Cause : un nom n’est pas un identifiant. Il n’est pas unique et peut changer. Une adresse courriel est unique ici, mais elle peut aussi changer, et c’est une donnée personnelle.

Diagnostic : pour une colonne utilisée pour trouver « la seule » ligne, cherchez les valeurs répétées : SELECT name, count(*) FROM customers GROUP BY name HAVING count(*) > 1; renvoie Emma Petrov | 2.

Correction : pointez vers les lignes par leur clé primaire stable (C-0004). Utilisez le nom seulement pour l’afficher aux gens. Si les gens cherchent par nom, affichez chaque résultat avec son identifiant et laissez-les choisir.

Quand utiliser ce modèle et ce qu’il coûte

Utilisez une table par entité, avec la clé étrangère du côté « plusieurs », pour les données que l’application possède et modifie : billets, messages, exécutions. Chaque fait se trouve alors à un seul endroit, et la base de données peut vérifier chaque lien.

Cela a un coût :

• Jointures. Pour afficher un billet avec le nom du client et ses messages, une requête joint trois tables. Avec les bons index, c’est rapide. Lire un plan de requête permet de le mesurer.
• Décisions à prendre dès le départ. Chaque marque sur une ligne représente une décision d’affaires. Par exemple, une exécution conserve-t-elle son coût sans son billet? Consultez les personnes responsables des données. Pour le service de soutien, Grace et Priya ont répondu.
• Certaines données n’ont pas besoin de leur propre table. Une liste que le programme stocke et affiche seulement peut rester dans une colonne, par exemple la sortie brute d’un modèle. Le test est simple. Devez-vous trouver des lignes selon un élément, compter les éléments ou vérifier que chaque élément existe? Donnez alors aux éléments leur propre table.

Vérification des connaissances

Vérifiez votre compréhension

Le service de soutien ajoute une table, refunds : une ligne par remboursement qu'un agent effectue pour un billet. Un billet peut avoir zéro, un ou plusieurs remboursements. Tracez la ligne entre tickets et refunds. Quelle table reçoit la clé étrangère, et est-elle obligatoire?

Afficher la réponse

C’est une relation un-à-plusieurs : chaque remboursement a exactement un billet, et un billet a zéro ou plusieurs remboursements. La clé étrangère va du côté « plusieurs » : refunds.ticket_id. Elle est obligatoire, car personne ne peut expliquer ou vérifier un remboursement sans son billet. Le billet n’a pas de colonne de remboursement : un deuxième remboursement n’aurait pas de place. De même, photo-2.png n’avait pas de place dans une colonne photo (photo).

Vérifiez votre compréhension

Sam propose l'adresse courriel du client comme clé primaire de customers : « Elle est unique de toute façon, et on n'aurait pas besoin de C-0001. » Donnez deux raisons de refuser, avec un exemple du service de soutien.

Afficher la réponse

Premièrement, une adresse courriel peut changer. Un client qui change d’adresse aurait besoin d’une nouvelle clé. Tous les billets qui pointent vers l’ancienne clé devraient aussi changer. Un bon identifiant est stable. Deuxièmement, une adresse courriel est une donnée personnelle. Comme clé, elle est copiée dans chaque billet du client. La suppression des données d’un client doit donc trouver chaque copie. De plus, une clé de 23 octets ou plus rend chaque index associé plus large que les 8 octets de C-0001.

Vérifiez votre compréhension

Pourquoi une exécution d'IA peut-elle avoir zéro ou un billet, alors qu'un message doit en avoir exactement un?

Afficher la réponse

Parce que le service de soutien a besoin de l’exécution après la suppression de son billet. Les totaux mensuels des coûts de Priya incluent chaque exécution. Si les données d’un client sont supprimées, ses billets et leurs messages disparaissent. Les exécutions restent avec un identifiant de billet vide, pour que les totaux restent exacts. Un message sans son billet ne sert à rien. Il doit donc en avoir exactement un.

Récapitulatif

• Une entité est un type de chose sur lequel le service de soutien conserve des données. Chacune a sa table, avec une ligne par chose.
• Un identifiant doit être stable. Les billets et les clients gardent les identifiants publics de l’ancien outil. Les messages et les exécutions reçoivent un numéro de la base de données, et les pièces jointes reçoivent un UUID. Les noms et les adresses courriel ne sont jamais des clés : rappelez-vous les deux Emma Petrov.
• La cardinalité indique combien d’éléments il y a de chaque côté. Une ligne ne peut pas contenir une liste (la deuxième photo de T-30034). La clé étrangère va donc du côté « plusieurs ».
• Le diagramme entité-relation du service de soutien montre une décision à chaque extrémité de chaque ligne. Par exemple, une exécution d’IA conserve son coût sans son billet. Le fichier docs/schema.md contient ce diagramme dans le projet.
• Les données ne respectent déjà pas une promesse : trois messages pointent vers des billets qui n’existent pas. Ensuite, la base de données apprend les règles et refuse de telles lignes.

Leçon suivante : Contraintes d'intégrité