Clé étrangère et primaire

[developpCathy]

Bonjour,
Mon problème ne porte pas vraiment sur le langage SQL mais la logique des bases de données.
Voilà j'ai deux tables :
Message (où je stocke les infos de l'email) et Attachement où je stocke les infos sur les pièces jointes de l'email).
J'ai deux possibilités :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TABLE Message(
	codeMessage Integer NOT NULL AUTO_INCREMENT,
	Objet Varchar(50),
	Message Text,
	DateMessage Date NOT NULL,
	TimeMessage Time NOT NULL,
	PRIMARY KEY (codeMessage)
);
 
CREATE TABLE Attachement (
	codeAtt Integer NOT NULL AUTO_INCREMENT,
	Chemin Varchar(50) NOT NULL,
	codeMessage Integer NOT NULL,
	PRIMARY KEY (codeAtt),
	FOREIGN KEY (codeMessage) REFERENCES Message (codeMessage)
							  ON UPDATE Cascade
							  ON DELETE Cascade
);

Ici, je crée une clé primaire "codeAtt" pour les pièces jointes, mais qui ne veut rien dire sémantiquement, car la même pièce jointe peut être stockée à chaque fois avec une nouvelle clé, chaque fois qu'elle est utilisée.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE TABLE Message(
	codeMessage Integer NOT NULL AUTO_INCREMENT,
	Objet Varchar(50),
	Message Text,
	DateMessage Date NOT NULL,
	TimeMessage Time NOT NULL,
	PRIMARY KEY (codeMessage)
);
 
CREATE TABLE Attachement (
 
	Chemin Varchar(50) NOT NULL,
	codeMessage Integer NOT NULL,
 
	FOREIGN KEY (codeMessage) REFERENCES Message (codeMessage)
							  ON UPDATE Cascade
							  ON DELETE Cascade,
	PRIMARY KEY (codeMessage)
);

Ici, j'utilise codeMessage dans la table Attachement, non seulement comme une clé étrangère mais aussi pour une clé primaire de la table, je trouve ça plus significatif dans la réalité, mais ça ne me permettra d'avoir qu'une seule pièce jointe par message !!

Où pourrais-je utiliser une autre clé primaire pour la table attachement ??

[tatayo]

Bonjour,
Avec ta deuxième méthode, comment fais-tu pour avoir 2 pièces jointes dans un mail ?

Tatayo.

[dehorter olivier]

Pourquoi le fait d'avoir une clé primaire qui ne sert jamais te pose des soucis ?


Clairement la première solution est plus pertinente (dans la réalité on a souvent plusieurs fichiers attachés, non ?)

[developpCathy]

Justement tatayo, c'est pour cette raison que je suis perdue, dans le deuxième exemple, je ne pourrais avoir qu'une seule pièce jointe par message !!

[developpCathy]

Envoyé par dehorter olivier

Pourquoi le fait d'avoir une clé primaire qui ne sert jamais te pose des soucis ?


Clairement la première solution est plus pertinente (dans la réalité on a souvent plusieurs fichiers attachés, non ?)

Et ben si j'utilise le premier exemple, à chaque fois que je veux utiliser la même pièce jointe, elle va être ajoutée avec une nouvelle clé, alors que si tu essayes avec ta boite email, d'envoyer un message, avec 2 fois la même pièce, ça ne marche pas !!

[developpCathy]

Je crois que j'ai trouvé
Si la clé primaire de la table Attachement est a la fois codeMessage et Chemin, ca va permettre d'avoir plusieurs pièces jointes par message, et aussi de ne pas choisir la même pièce jointe pour le même message!! voilà

(parfois quand tu essaye d'expliquer le problème tu le comprend mieux !!)

[developpCathy]

Voici le nouveaux code

Code :

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
 
CREATE TABLE Message(
	codeMessage Integer NOT NULL AUTO_INCREMENT,
	Objet Varchar(50),
	Message Text,
	DateMessage Date NOT NULL,
	TimeMessage Time NOT NULL,
	PRIMARY KEY (codeMessage)
);
 
CREATE TABLE Attachement (
	Chemin Varchar(200) PRIMARY KEY,
);
 
CREATE TABLE Att_Message(
	Chemin Varchar(200) NOT NULL,
	codeMessage Integer NOT NULL,
	FOREIGN KEY Chemin REFERENCES Attachement (Chemin)
					   ON UPDATE Cascade
					   ON DELETE Cascade,
	FOREIGN KEY codeMessage REFERENCES Message (codeMessage)
							ON UPDATE Cascade
							ON DELETE Cascade,
	PRIMARY KEY (Chemin, codeMessage)
);

Des remarques..??!!

[CinePhil]

Envoyé par developpCathy

Des remarques..??!!

Oh oui !
Un VARCHAR(200) comme clé primaire, c'est contre performant !

Si je comprends bien ta structure, tu as voulu implémenter l'association suivante :
message -0,n----joindre----1,n- attachement

Met un identifiant auto-incrémenté à ton attachement et utilise le dans la clé primaire de la table associative :
message (msg_id, msg_objet...)
attachement (att_id, att_chemin)
att_message (am_id_message, am_id_attachement)

Au passage, pourquoi séparer la date et l'heure du message au lieu d'utiliser le type DATETIME ?

[developpCathy]

CinePhil je vous remercie infiniment pour vos précieuses remarques

Envoyé par CinePhil

Mets un identifiant auto-incrémenté à ton attachement et utilise le dans la clé primaire de la table associative :
message (msg_id, msg_objet...)
attachement (att_id, att_chemin)
att_message (am_id_message, am_id_attachement)

Pas mal comme idée, mais ça revient à ce que j'ai mentionné au début, un id qui s'auto-incrémente donc à chaque fois que je veux ajouter le même attachement au même message l'id s'incrémente et ça passe, alors que c'est pas là ce que je veux!!
et si je mets "text" pour le chemin au lieu de varchar(200)..?

[developpCathy]

Est-ce que je pourrais par exemple ajouter l'id qui s'incrémente, mais pas dans la base, j’incrémenterai à partir de l'application à chaque fois que l'utilisateur choisit un nouveau chemin...??!!

[CinePhil]

Envoyé par developpCathy

Pas mal comme idée, mais ca reviens à ce que j'ai mentioné au début, un id qui s'auto-incrémente donc à chaque fois que je veux ajouter le même attachement au même message l'id s'incrémente et sa passe, alors que c'est pas la ce que je veux!!

Là ça ne dépend plus des données mais du processus de ton programme !

Scénario :
1) J'envoie un message avec la pièce jointe stockée en /home/philippe/documents/mapomme.jpg.
2) Le programme interroge la BDD pour savoir si la pièce jointe est déjà référencée.

Code :

1
2
3
SELECT att_id
FROM attachement
WHERE att_chemin = '/home/philippe/documents/mapomme.jpg'

3) Si la requête ne retourne pas de résultat, j'ajoute la pièce jointe à la table attachement et je récupère son identifiant créé par auto-incrémentation.

Code :

1
2
INSERT INTO attachement (chemin)
VALUES ('/home/philippe/documents/mapomme.jpg')

4) Je crée le message et je récupère son identifiant.
5) J'associe les deux et l'enregistre dans la table associative att_message.

et si je met "text" pour le chemin au lieu de varchar(200)..?

Surtout pas ! C'est encore pire pour les performances !

[developpCathy]

Je vous remercie

J’attendrais d'autres remarques..!!

[estofilo]

A quoi sert d'avoir la table attachement avec sa colonne chemin alors que le chemin est déjà dans la table Att_Message?
Pour interdire d'avoir plusieurs fois le même chemin dans un message, un index unique sur le couple (codemessage,chemin) est suffisant, et au passage, il n'est pas nécessaire que cet index unique soit la clef primaire.
Il peut très bien y avoir en plus une clef primaire en ID auto-incrémenté, s'il se trouve que cet ID a une utilité.