Discussion :

[CocoG]

Bonjour.
Je suis sur un petit projet de développement PHP, et je bute sur un problème au niveau d'une requête SQL. Le projet est une sorte de petit dictionnaire/traducteur, encore à ses balbutiements.

Voici ma base de données.

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
28
29
30
31
32
33
34
create table users (
	user_id int auto_increment primary key,
	user_account varchar(40) not null,
	user_password varchar(40) not null
);
 
create table languages (
	language_id int auto_increment primary key,
	language_name varchar(50) not null,
	language_description varchar(1000) not null
);
 
create table words (
	word_id int auto_increment primary key,
	word_word varchar(100) character set utf8 collate utf8_bin not null,
	word_userid int not null,
	foreign key (word_userid) references users(user_id)
);
 
create table translations (
	translation1 int not null,
	translation2 int not null,
	translation_userid int not null,
	foreign key (translation1) references words(word_id),
	foreign key (translation2) references words(word_id),
	foreign key (translation_userid) references users(user_id)
);
 
create table belongs (
	belongs_language int not null,
	belongs_word int not null,
	foreign key (belongs_language) references languages(language_id),
	foreign key (belongs_word) references words(word_id)
);

J'ai des words, qui peuvent être traduits via la table translations à d'autre words. Chaque word appartient à un language. On oubliera les users, qui ne sont pas concernés par la requête.

Dans mon code PHP, j'ai deux variables, le mot connu que l'on cherche à traduire, et la langue de traduction. Mais cette association entre deux mots me fait m'arracher les cheveux. Auriez vous une piste ? Faut-il utiliser les alias, ou les requêtes imbriquées ?

[lola06]

Les jointures te permettront de faire ce que tu recherches.

[StringBuilder]

J'ai des words, qui peuvent être traduits via la table translations à d'autre words.

=> Ok pour ça, pas de problème.

Chaque word appartient à un language.

=> C'est pas ce que dit ton modèle. Ta table "belong" permet d'avoir un même mot pour X langues, ce qui est une erreur à mon avis, d'autant que le même mot d'une langue à l'autre n'a pas forcément la même signification. Par exemple "do", en français c'est une note de musique, et en anglais c'est un verbe.

Dans mon code PHP, j'ai deux variables, le mot connu que l'on cherche à traduire, et la langue de traduction. Mais cette association entre deux mots me fait m'arracher les cheveux. Auriez vous une piste ? Faut-il utiliser les alias, ou les requêtes imbriquées ?

Poste ta requête, ça ira mieux.

Cependant, pour moi la table "belong" devrait disparaître, remplacée par un champ "language_id" dans la table "word".

Ensuite, ta requête devrait être du genre :

Code sql :

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

1
2
3
4
5
6
7
8
 
select w_tra.word_word
from words w_ori
inner join translations t on t.translation1 = w.id
inner join words w_tra on w_tra.id = t.translation2
inner join languages l on l.id = w_tra.language_id
where w_ori.word_word = 'mot_à_traduire'
and w_tra.language_name = 'nom_de_la_langue';

A noter aussi qu'on préfère généralement utiliser le singulier pour nommer les tables, c'est juste une convention de nommage.

A mon avis, il manque aussi un champ "sens" dans la table "words".
En effet, il faut pouvoir différencier les homonymes dans le résultat...

Par exemple "to" en anglais pourra se traduire par "pour" (I need this to do something), "à" (I'm going to Paris), "vers" (Idem que précédent) et même par "<null>" lorsque c'est la marque de l'infinitif ou d'une forme de futur (to sleep ; I'm going to fall), Mise à part "à" et "vers" qui sont une même notion, les autres ne sont absolument pas synonymes => Il y a donc un unique mots avec quatre ou cinq id différents "to", qu'il faut pouvoir différencier. On pourraît d'ailleurs aller plus loin et créer une table "notion", avec pour clé "word_id" et "language_id" afin de proposer dans la langue maternelle de l'utilisateur le sens du mot traduit, mais aussi de pouvoir regrouper les synonymes sous une même notion/sens.

[CocoG]

=> C'est pas ce que dit ton modèle. Ta table "belong" permet d'avoir un même mot pour X langues, ce qui est une erreur à mon avis, d'autant que le même mot d'une langue à l'autre n'a pas forcément la même signification. Par exemple "do", en français c'est une note de musique, et en anglais c'est un verbe.

Cependant, pour moi la table "belong" devrait disparaître, remplacée par un champ "language_id" dans la table "word".

J'ai pensé faire comme ça au début, mais dans certaines familles de langues, les mots peuvent être les mêmes d'un idiome à l'autre. Je pense notamment aux langues romanes. Il me semble qu'il en va de même pour les langues arabes et chinoises, mais sans aucune certitude.

A mon avis, il manque aussi un champ "sens" dans la table "words".
En effet, il faut pouvoir différencier les homonymes dans le résultat...

C'était bien sûr prévu, mais je préfère mettre à jour ma base petit à petit, je m'embrouille moins comme ça.

[...] On pourraît d'ailleurs aller plus loin et créer une table "notion", avec pour clé "word_id" et "language_id" afin de proposer dans la langue maternelle de l'utilisateur le sens du mot traduit, mais aussi de pouvoir regrouper les synonymes sous une même notion/sens.

Il était prévu, si j'ai le temps de continuer, de créer des thèmes lexicaux, qui pourront regrouper également ces notions. J'en prend bonne note.

A noter aussi qu'on préfère généralement utiliser le singulier pour nommer les tables, c'est juste une convention de nommage.

Bien d'accord, mais les tables users et translations devenaient bleues dans Notepad++, j'ai pensé, sûrement à tord, que ça pourrait être mal compris par le SGBD. Je vais corriger ça.

Sinon, j'ai tenté d'arranger ta requête, mais je n'ai pas appris à utiliser INNER JOIN. Je suis donc reparti à zéro, à tête reposée, et j'ai réussi à former une requête, un peu dégueu, mais au moins ça a l'air de fonctionner.

SELECT w_tran.word_word
FROM words w_ori, words w_tran, translations, languages, belongs
WHERE (
w_ori.word_id = translation1
OR w_ori.word_id = translation2
)
AND (
w_tran.word_id = translation1
OR w_tran.word_id = translation2
)
AND w_ori.word_word = 'onion'
AND w_tran.word_id = belongs_word
AND belongs_language = language_id
AND language_id = '2'

Est-il propre de faire la recherche directement sur belongs_language plutôt que sur la table languages ?

En tout cas, merci de votre aide, le cours et tes explications m'ont guidé, et rafraîchi la mémoire.

[StringBuilder]

Si tu as le code langue, il vaut mieux passer directement par belong. Tout du moins, ça ne me choque pas.

En revanche, ne pas utiliser la syntaxe SQL92 pour faire les jointures me choque bien plus. Essaie d'utiliser INNER JOIN