Discussion :

[Sylvercis]

Bonjour,

Après avoir revus certaines requêtes, je me suis rendu comptes que j'ai besoin de rajouter des index sur certaines tables et j'ai donc décider d'en profiter pour faire un peu de réorganisation.

voici 2 tables pour stocker des articles pour un site internet multi langues.

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
CREATE TABLE IF NOT EXISTS `articles` (
  `article_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id de l''article',
  `user_id` int(10) UNSIGNED NOT NULL COMMENT 'auteur de l''article',
  `parution` datetime NOT NULL COMMENT 'date et heure de publication sur le site',
   CONSTRAINT `pk_articles` PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	");

CREATE TABLE IF NOT EXISTS `articles_items` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `article_id` int(10) UNSIGNED NOT NULL COMMENT 'id de l''article',
  `user_id` int(10) UNSIGNED NOT NULL COMMENT 'id du traducteur',
  `language_id` tinyint(3) UNSIGNED NOT NULL COMMENT 'id du langage (table langues)',
  `titre` varchar(200) NOT NULL COMMENT 'titre de l''article dans le langage',
  `inventaire` text DEFAULT NULL COMMENT 'inventaire du contenu de la boite du produit testé',
  `texte` text NOT NULL COMMENT 'texte de l''article dans le langage indiqué dans cette clé',
  `extra` text DEFAULT NULL COMMENT 'texte supplémentaire optionnel',
  `affiche` varchar(200) DEFAULT NULL COMMENT 'nom de fichier de l\'image de présentation de l\'article',
  `status` tinyint(1) UNSIGNED NOT NULL COMMENT '0 = actuelle, 1 = attente validation, 2 = archivée',
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

la 1ere table permet d'identifier chaque article
la 2eme contient chaque version de l'article


J'ai déjà corrigé la 1ere table et je n'ai pas besoin d'index supplémentaire.

Sur la 2eme c'est un peu plus compliqué.
Je sais déjà que je peux supprimer la colonne 'id' car elle est finalement inutilisé.
j'utilise principalement 'article_id' et 'language_id' pour définir l'article.

En mode affichage (pour le site), seul les versions avec 'status' = 0 sont utilisées

En mode édition (pour l'admin)
des versions supplémentaires sont définis grâce au colonnes suivantes:
'user_id' + 'status' qui marche de paires

Et enfin j'utilise aussi 'modified' qui me permet de trier par date ou de limiter le nombre de mes résultats dans sur le site ou dans l'admin.


Après une première réflexion, je me demande si je ne devrait pas séparer cette table en 3 en séparant les versions selon le status
1 table avec les articles validés et affichables
1 table avec les articles brouillons a valider
1 table pour archiver les anciennes version de chaque articles.


On m'avait dis dans d'autres post que dans une base bien conçu, on ne devait pas avoir plus de 1 ou 2 clés primaires.
Ici j'ai 2 colonnes essentielles qui sont candidates:
'article_id'
'language_id'

mais ensuite j'ai ces autres colonnes qui me sont utiles pour cibler plus précisément et dont je ne sais pas quel traitement leur appliquer:
`user_id`
`status`
`modified`


Est ce qu'il vaudrai mieux définir une ou des tables de liaisons supplémentaires, et si oui lesquels?


Merci de vos conseils

[escartefigue]

Bonjour Sylvercis

la 1ere table permet d'identifier chaque article
la 2eme contient chaque version de l'article

C'est quoi une version ? Un même article traduit dans une nouvelle langue ou bien une mise à jour de l'article dans sa langue d'origine ?

J'ai déjà corrigé la 1ere table et je n'ai pas besoin d'index supplémentaire.

C'est très prématuré de se préoccuper des index tant que le modèle n'est pas à peu près stabilisé

Je sais déjà que je peux supprimer la colonne 'id' car elle est finalement inutilisé.
j'utilise principalement 'article_id' et 'language_id' pour définir l'article.

Ca semble répondre à ma première question : il ne s'agit pas d'une version, mais d'une traduction d'article puisque l'identifiant est le couple article+langue
Par ailleurs, l'ajout d'une colonne identifiante inutile (ici 'id') est symptomatique d'un modèle tabulaire créé directement sans commencer par le modèle conceptuel.
Erreur souvent lourde de conséquences.


Vos questions suivantes sont du même acabit. Toutes sont dues au fait que vous réfléchissez sur les tables alors qu'il faut réfléchir aux règles de gestion.
Les tables ne doivent être qu'une conséquence des règles de gestion.
Dans l'ordre
- rédigez ces règles de gestion (par exemple : un article a un et un seul auteur ou bien peut il en avoir plusieurs ?)
- téléchargez un logiciel de modélisation, il en existe des gratuits
- constituez une ébauche de modèle conceptuel (ici, pas de tables, uniquement des types d'entités et d'associations) en utilisant ce logiciel de modélisation, ainsi, vous n'aurez plus d'identifiants inutiles et autres erreurs de débutants
- quand ce MCD sera abouti, le MLD et donc les tables seront automatiquement créées (avec son script automatiquement décliné selon le choix du SGBD)


Le forum consacré à la modélisation se trouve ici : https://www.developpez.net/forums/f6...sation/schema/
Consultez les autres sujets de ce forum, vous comprendrez la démarche à adopter


EDIT : j'ai retrouvé cet autre sujet dans lequel vous aviez commis la même erreur de commencer par les tables, ce qui vous avait mis en difficulté pour trouver la requête correspondant à votre besoin :
https://www.developpez.net/forums/d2.../#post11636371
La aussi, j'avais proposé un modèle conceptuel (MCD) duquel j'avais dérivé le modèle tabulaire et à partir de là, la requête qui va bien.
MCD bâclé ou absent ==> tables mal conçues, intégrité des données non garantie, requêtes complexes et peu performantes...

[Sylvercis]

Cette table contient toutes les versions confondu de tous les articles.

C'est a dire:
- la version française "definitive" (article_id + language_id + status=0)
- la version anglaise "definitive" (article_id + language_id + status=0)
- l'ancienne version anglaise "archivé" (article_id + language_id + status=2)
- la version anglaise "brouillon" (article_id + language_id + status=1)
- la version anglaise "brouillon" mais d'un autre utilisateur (article_id + language_id + status=1 + user_id)

On voit que selon le type de recherche que j'effectue, le nombre de clés primaire peut évoluer a la hausse et donc oui il doit y avoir une erreur de conception.

Euh sinon les règles de gestion sont défini et bien rangé dans ma tête

J'ai créer les tables en prenant soin de séparer les données qui doivent l'être puis en ajoutant des clés qui me permettent de trier ou exclure les lignes selon mes recherche.
C'est vrai qu'ensuite au moment d’écrire les requêtes, certaines sélections peuvent être lourdes, notamment sur cette table qui contient l’intégralité des articles.

Je pensais qu'utiliser un logiciel de modélisation permettait juste de mettre tout ça sur papier pour mieux voir les liaisons entre les différentes tables mais que de toute façon la décision de créer tel ou tel tables était a la charge de celui qui modélise la base de donnée.

J'ai été faire un tour sur le forum dédié à la modélisation (merci pour le lien, je le note) et en regardant les schémas j'ai l'impression que tout est régie par les requêtes plutôt que par le classement des données.
Du coup je me demande si en se limitant aux requêtes que l'on pense avoir besoin au moment de la conception, est ce que plus tard on ne va pas de toute façon être obliger de redécouper les tables si l'on désire ajouter un nouveau type de recherche.

J'avais commencé a créer le MCD en essayant d'être le plus propre selon mes compétences et j'avais du mal a m'en sortir avec le logiciel, je me suis aussi vu ajouter beaucoup de tables supplémentaires.
Bref j'étais parti pour passer beaucoup de temps juste pour le MCD + ensuite je devrai tout modifier tables, requêtes et écrire du code pour importer les données etc...
Ce n'était et cela n'est toujours pas ma priorité du moment. (tant que je trouve une solution pour me dépanner en attendant de faire ça dans les règles).

Je peux prendre un peu de temps pour faire quelques améliorations sur un petit groupe de tables comme celles ci dessus quand je constate que ça engendre un problème mais pas plus.

Du coup je pense que je vais me contenter de virer la colonne "id", ajouter des index là où j'en ai besoin et je reprendrai le travaille sur le MCD quand j'aurai terminé les parties essentielles du site.

[Paprick]

Bonjour,

Je pensais qu'utiliser un logiciel de modélisation permettait juste de mettre tout ça sur papier pour mieux voir les liaisons entre les différentes tables mais que de toute façon la décision de créer tel ou tel tables était a la charge de celui qui modélise la base de donnée.

Un logiciel de modélisation permet de construire un modèle (MCD) et génère ensuite le schéma relationnel de la base de données.
C'est donc la première étape que "celui qui modélise la base de donnée" doit mettre en œuvre.

J'ai été faire un tour sur le forum dédié à la modélisation (merci pour le lien, je le note) et en regardant les schémas j'ai l'impression que tout est régie par les requêtes plutôt que par le classement des données.
Du coup je me demande si en se limitant aux requêtes que l'on pense avoir besoin au moment de la conception, est ce que plus tard on ne va pas de toute façon être obliger de redécouper les tables si l'on désire ajouter un nouveau type de recherche.

Absolument pas ! Le MCD n'a rien à voir avec les requêtes : le schéma relationnel qui en découle sert de socle aux futures requêtes.

J'avais commencé a créer le MCD en essayant d'être le plus propre selon mes compétences et j'avais du mal a m'en sortir avec le logiciel, je me suis aussi vu ajouter beaucoup de tables supplémentaires.
Bref j'étais parti pour passer beaucoup de temps juste pour le MCD + ensuite je devrai tout modifier tables, requêtes et écrire du code pour importer les données etc...
Ce n'était et cela n'est toujours pas ma priorité du moment. (tant que je trouve une solution pour me dépanner en attendant de faire ça dans les règles).
Je peux prendre un peu de temps pour faire quelques améliorations sur un petit groupe de tables comme celles ci dessus quand je constate que ça engendre un problème mais pas plus.
Du coup je pense que je vais me contenter de virer la colonne "id", ajouter des index là où j'en ai besoin et je reprendrai le travaille sur le MCD quand j'aurai terminé les parties essentielles du site.

Je crains que ce ne soit pas la bonne démarche... Mais bon...

[Sylvercis]

Absolument pas ! Le MCD n'a rien à voir avec les requêtes : le schéma relationnel qui en découle sert de socle aux futures requêtes.

C'est vrai qu'après y avoir réfléchi, je me suis rendu comme que j'avais dis une bêtise. Les relations que l'on défini dans le MCD ne sont pas l'équivalent de requêtes mais bien juste des relations entre les données.

[escartefigue]

Les relations que l'on défini dans le MCD ne sont pas l'équivalent de requêtes mais bien juste des relations entre les données.

Non plus .Le MCD représente les types d'entités (ce sont vos "objets de gestion" ou "acteurs" ou encore "individus") et les associations entre ces types d'entité (comment elles interagissent)

Dans votre contexte, on devine un certain nombre de types d'entité, par exemple les auteurs, les articles, les langues etc.
On suppose certaines associations, par exemple qu'un auteur peut écrire un ou plusieurs articles, qu'un article a au moins un auteur, etc.

Comme je le disais plus haut, rédigez les règles de gestion avant de vous intéresser au tables et plus encore aux requêtes

[Sylvercis]

Voilà, j'ai refait un essai avec Looping:


et ça me donne cela:

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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51



CREATE TABLE Article(
   article_id INT,
   date_parution DATETIME,
   PRIMARY KEY(article_id)
);

CREATE TABLE langues(
   language_id INT,
   suffixe VARCHAR(2),
   PRIMARY KEY(language_id)
);

CREATE TABLE utilisateurs(
   user_id INT,
   nom VARCHAR(50),
   prenom VARCHAR(50),
   pseudo VARCHAR(50),
   language_id INT NOT NULL,
   PRIMARY KEY(user_id),
   FOREIGN KEY(language_id) REFERENCES langues(language_id)
);

CREATE TABLE article_items(
   id INT,
   titre VARCHAR(50),
   texte TEXT,
   affiche VARCHAR(50),
   date_modification VARCHAR(50),
   status INT,
   user_id INT NOT NULL,
   language_id INT NOT NULL,
   article_id INT NOT NULL,
   PRIMARY KEY(id),
   FOREIGN KEY(user_id) REFERENCES utilisateurs(user_id),
   FOREIGN KEY(language_id) REFERENCES langues(language_id),
   FOREIGN KEY(article_id) REFERENCES Article(article_id)
);



CREATE TABLE langage(
   article_id INT,
   language_id INT,
   PRIMARY KEY(article_id, language_id),
   FOREIGN KEY(article_id) REFERENCES Article(article_id),
   FOREIGN KEY(language_id) REFERENCES langues(language_id)
);

alors c'est vrai que c'est pratique dans le sens ou ça me donne directement les FOREIGN KEY et cela sécurise les données.
Et surtout ça permet de se rappeler en un coup d'oeil les différentes liaisons entre les données.

Mais bizarrement je ne me sens pas plus avancé qu'avant et je n'ai pas l'impression que cela va me permettre d’écrire mes requêtes plus facilement ni de les rendre plus efficace.

J'ai essayé de découper la table article_items mais comme ça compliquait la base de données sans que je n'y vois un intérêt, je suis revenu a la structure initiale.
Par contre je constate que le logiciel m'a forcé a créer une clé primaire 'id' dans la table article_items alors que dans la pratique cette colonne je viens de la supprimer car j'ai constaté qu'elle m'est inutile.

J'ai aussi un problème pour indiquer un niveau de priorité sur chaque langue qu'un utilisateur PARLE.
ex pour moi:
Français = 1
Anglais = 2

J'explique pourquoi:
Dans ma base, j'ai une table dédié avec les préférences de langages qui me permet de présenter la liste complète des titres des articles a un utilisateur en prenant soin d'afficher ces titres dans le langage préféré de l'utilisateur.
C'est a dire que pour moi, quand le titre français est disponible, c'est celui ci qui s'affichera et sinon il m'affichera le titre en anglais (si il existe bien sur).
Un autre utilisateur peut préférer n'avoir que les titres en anglais si il ne parle pas français (dans ce cas un titre qui n'existe qu'en français ne s'affichera pas).

[Paprick]

Bonsoir,

J'ai essayé de découper la table article_items mais comme ça compliquait la base de données sans que je n'y vois un intérêt, je suis revenu a la structure initiale.
Par contre je constate que le logiciel m'a forcé a créer une clé primaire 'id' dans la table article_items alors que dans la pratique cette colonne je viens de la supprimer car j'ai constaté qu'elle m'est inutile.

Définir une classe d'entités (qui deviendra une table dans le schéma relationnel de la BD) sans identifiant (donc sans clé primaire pour la table de la BD), il y en a qui ont essayé... ils ont eu des problèmes !

[escartefigue]

De plus [article_item] étant une déclinaison de [article] devrait être identifié relativement à l'article
Il est préférable de renommer [article_item] en [version], car un "item" est une sous-partie de l'article, un paragraphe de l'article par exemple


Ce qui donne

[article] 1,n --- decliner --- 1,1(R) [version]

Pour obtenir cette identification relative, il faut cliquer sur la patte de l'association coté 1,1 puis cocher la case "identifiant relatif"

[Sylvercis]

Je suis d'accord qu'il faut forcement une clé primaire à la table.
J'avais donc le choix entre ajouter 'id' ou bien définir les clés qui me semblent les plus cohérentes et donc 'article_id'+'language_id' mais je me suis dis que si je commence a tout décider sans laisser le logiciel ou le MCD me guider ça n'a plus trop d’intérêt. J'ai donc rajouter 'id' quand le logiciel m'a demandé de le faire.

Je viens de faire des modifications pour voir ce que ça donne au niveau du code:



Le fait d'ajouter 1,1R ne me fait aucune différence sur le code de créations des tables. Ou alors j'ai mal regardé?

Et puis comme dis précédemment je ne me sens pas plus avancé car je me retrouve toujours avec les mêmes tables et le choix des clés primaires me revient toujours finalement.
Je viens d'ailleurs de remarquer que je n'ai pas inclu 'user_id' dans les clé primaire et je n'ai pas de message d'erreur qui m'indique qu'il y a un problème.

Le MCD (et le programme) est très utile pour avoir une vue graphique de la base et pour ajouter les FOREIGN KEY automatiquement. Est ce que ses FOREIGN KEY sont juste des sécurités ou bien est ce qu'en interne cela va modifier la structure de la base et optimiser les performances?

Parce que pour moi au niveau de l'écriture des requêtes ça ne va avoir aucune influence.

[Sylvercis]

@Paprick
Je viens de commander votre livre car maintenant que j'arrive un peu a utiliser le logiciel, j'ai bien envie de dessiner ma base tel qu'elle est en ce moment pour mieux voir les relations entre les tables.
Pour cela j'ai besoin de comprendre ce que sont les héritage, CIF, etc..

Ma base de données au niveaux des articles ce n'est pas compliqué mais j'ai d'autres données dans lesquels je peux facilement sortir de faux résultats si j'omets certaines jointures.
Un schéma graphique est très utile pour se rappeler les relations entre chaque données.

Lorsque j'avais revue la structure de l'ancienne base de données (elle n'était pas de moi et était pire, genre 1 table excel pour chaque section du site), j'avais un peu cherché un logiciel pour créer des diagrammes sans trouver mon bonheur.
Avec Looping, on n'est pas submergé par une multitude d'options a cocher et on est libre dans un premier temps de créer une structure incorrecte.

Parfait pour mon usage immédiat, je pourrai corriger ma base par petites touches et mieux voir se qu'il me reste a faire.

[escartefigue]

Concernant les articles et les langues :

Je n'y avais pas prêté attention, mais votre type d'entité [Article] est une coquille vide. La date de parution, sans texte dans l'article ni même de titre n'a pas de sens. Et associer cette coquille vide à une langue n'a pas de sens non plus.

Ce que je ne comprends pas non plus, c'est ce que vous expliquez dans votre premier post :

Cette table contient toutes les versions confondues de tous les articles
- la version française "definitive" (article_id + language_id + status=0)
- la version anglaise "definitive" (article_id + language_id + status=0)
- l'ancienne version anglaise "archivé" (article_id + language_id + status=2)
- la version anglaise "brouillon" (article_id + language_id + status=1)
- la version anglaise "brouillon" mais d'un autre utilisateur (article_id + language_id + status=1 + user_id)

Ici, on comprend qu'un autre utilisateur U2 peut créer une nouvelle version d'un article écrit auparavant par un utilisateur U1.
Si c'est bien le cas, l'identifiant utilisateur doit contribuer à identifier la version de l'article

Reste à savoir ce qui caractérise un article du coup quels sont ses attributs ? pour l'instant, ça reste une coquille vide. Et rien n'empêche que deux versions attribuées à un même article parlent de choses complètement différentes (une version concerne une recette de cuisine, l'autre une thèse sur la physique quantique par exemple...).

Et au passage, quand on utilise l'identification relative notée (R), il ne faut pas ajouter dans le MCD les identifiants issus des entité-types fortes, c'est au niveau tabulaire que ces identifiants seront automatiquement ajoutés dans la PK de la table issue de l'entité-type faible
Exemple ci-dessous classique de MCD pour une commande avec ses lignes de commandes et son MLD correspondant : on voit que l'identifiant de la commande est inclut automatiquement dans la table LC_ligne_cde sans pour autant l'avoir ajoutée dans le MCD
Pièce jointe 583993

[Sylvercis]

La table 'article' est une table de liaison qui sert a regrouper plusieurs version d'un même article. C'est grâce à elle et a l'auto-increment de sa clé primaire que je défini un identifiant commun entre toutes les versions. (une dans chaque langue + d'autres utilisées pour les ébauches, l'archivage et uniquement consultable dans l'admin)
Le champ 'auteur' de cette table 'article' sert a identifier l'auteur original de l'article. La personne qui se charge de traduire ou apporter une correction à l'article n'est pas auteur mais soit traducteur ou contributeur.
Cette personne est enregistré dans le champ 'traducteur' de la table version.
Le champs 'parution' détermine la date de parution de l'article quelque soit la version. C'est discutable mais mettre une date de parution pour chaque version de l'article n'était pas forcement une solution idéale.

Les personnes autorisées à modifier les articles sont limités et pour le moment je n'ai pas donné la possibilité de pouvoir modifier l'auteur, une fois l'article crée, l'auteur est enregistré définitivement.
Je n'ai pas non plus trouvé utile de pouvoir définir plusieurs auteurs, comme je l'ai dis plus haut les autres personnes sont soit des contributeurs, soit des traducteurs.

Les différentes versions d'un article sont censés contenir la même chose à l'exception du langage employé qui change.
L’utilisateur bien sur écrire n'importe quoi dans sa version, seul un humain sera capable de vérifier qu'il s'agit bien d'une traduction ou d'une simple correction de l'article original.
Sur le site au final, il n'y a qu'une version par langage d'accessible.

Ah oui et pour les attributs d'un article, effectivement ils ne se trouvent ni dans la table article, ni dans la table version.
J'ai d'autres tables a coté dont je n'ai pas mentionné l'existence qui recensent les différents attributs d'un article.
Enfin en fait c'est plutôt la table article qui est une table enfant d'un produit et c'est ce produit qui donne des attributs a l'article.

Pour l'identification relative je pense comprendre ce que vous voulez dire et du coup je devrait alors revenir au schéma précédent et remettre la colonne 'id' comme clé primaire dans la table 'version'.

Quand j'aurai lu le livre de Paprick je comprendrai mieux (j'espère) l'utilité du (R).
Je l'ai mis sans trop savoir pourquoi, pour vous faire plaisir mais surtout pour voir ce que cela pouvais changer au niveau des requêtes de création des tables.