[sql/mysql] gérer les mots-clés d'une base de données bibliographique

[ibliss11]

Salut à tous !

Comme d'habitude, merci à tous ceux qui font vivre ce forum

Je débute en gestion de base de données, et vais créer une base de données bibliographiques.
Je pense que mon problème est un cas d'école dans la gestion de base données pour ceux qui s'y connaissent.

J'ai fait mon modèle entité-relation, et suis plutôt content car je retombe, dans les grandes lignes, sur ce modèle disponible sur le net :
http://www.vn.refer.org/vietnam/theses/mjbauer/MCD.gif
avec notamment des tables document et mot-clé séparées.

Voici grosso merdo le code que j'utilise pour ces deux tables :

CREATE TABLE IF NOT EXISTS article ( id INT(10) NOT NULL, pubyear INT(4), issn INT(8), FOREIGN KEY (issn) REFERENCES journal(issn), PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS keyword ( value CHAR(20) NOT NULL, PRIMARY KEY (value) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

J'ai cependant un problème pour créer la table relationnelle (dites moi svp si ce terme n'est pas adapté), et notamment dans la gestion de la cardinalité : (1,n) article peut avoir (0,n) mots-clé.
Quel code utiliseriez-vous pour créer cette table ?
D'instinct je partirai sur ça :

CREATE TABLE has_keyword ( id INT(10), value CHAR(20), FOREIGN KEY (id) REFERENCES article(id), FOREIGN KEY (value) REFERENCES keyword(value), PRIMARY KEY (id, value) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

En fait j'ai du mal à me représenter la forme de la cardinalité des données.
Je suis habitué à la combinaison csv + R pour les données, et pour moi, si un article a plusieurs mots-clé ce sera du format long :

id,value
article1,biologie moléculaire
article1,immunologie
article1,lupus
article2,informatique thérique
article2,théorie des langages
article2,théorie des graphes
article2,théorie de la complexité
...

Pourriez-vous m'éclairer s'il vous plaît ?

Merci beaucoup !
-----

[bisou10]

Quelle est ta question ?

Tes tables sont presque correctes:
- manque toutefois un ID int dans ta table keyword, mais ce n'est que pour les performances
- en FK dans ta table has_keyword tu ne devrais avoir que ID article et ID keyword

Ainsi, has_keyword peut avoir la cardinalité souhaitée (0,n).
C'est tes requetes qui te presenteront les données sous la forme que tu souhaites.

[ibliss11]

Merci bisou10.

En fait je ne sais pas si ma table relationnelle est correcte.
Je me retrouve avec une ligne par couple article/mot-clé, du coup si un article a 3 mots-clé il y a 3 lignes, et donc de la redondance...
Une solution serait une table avec un champ article et un champ mot-clé contenant les mots-clé séparés par une virgule...
Cette solution ne me semble pas correcte : je ne sais pas s'il faut faire l'un ou l'autre.

Redis moi si je ne suis pas clair.

[JeSuisConscient]

bonjour à toi
tu es sous mysql
tu sais il y a un trés bon outil qui permets de concevoir des bases de données sous mysql
et un outil qui est gratuit
mysql workbench manager
http://www.mysql.fr/products/workbench/

[A voir en vidéo sur Futura]

[JeSuisConscient]

non c'est correct de faire tes mots clefs ainsi
si tu as peur de la redondance concernant les mots clefs dans ce cas la tu fais un table mot clef
et si tu auras une relation n,n entre mots clefs et articles

[ibliss11]

Salut JeSuisConscient, et merci.

J'ai déjà compilé mysql workbench (mon dieu que c'est lourd), je n'ai pas encore eu le temps de l'utiliser mais ça a l'air cool pour les gros projets.
Je ne pensais pas que c'était utile de l'utiliser pour ma petite base de données bibliographiques.
Mais du coup comme tu m'en parles ça me motive pour jeter un oeil.

Sinon merci pour ta réponse, je pense que je vais rester sur ce modèle du coup.

J'avais néanmoins une dernière concernant la redondance.
Je pars du principe que mes tables principales ne doivent pas être redondantes, et que je dois faire porter toute la redondance de ma base sur les tables d'associations. Est-ce correct ?

[JeSuisConscient]

tu dois définir les cardinalités entre les données ,
et si tu veux faire une base de puriste tu dois appliquer les formes normales

[JeSuisConscient]

voici le code généré correspondant a ton model

-- MySQL Workbench Synchronization
-- Generated: 2014-11-10 11:37
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: O_MIG

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

CREATE TABLE IF NOT EXISTS `mydb`.`PERSONNE` (
  `idPERSONNE` INT(11) NOT NULL,
  `nom` VARCHAR(45) NULL DEFAULT NULL,
  `prenom` VARCHAR(45) NULL DEFAULT NULL,
  `sexe` VARCHAR(45) NULL DEFAULT NULL,
  `fonction` VARCHAR(45) NULL DEFAULT NULL,
  `courriel` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idPERSONNE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`DOCUMENT` (
  `idDOCUMENT` INT(11) NOT NULL,
  `titre1` VARCHAR(45) NULL DEFAULT NULL,
  `titre2` VARCHAR(45) NULL DEFAULT NULL,
  `date` VARCHAR(45) NULL DEFAULT NULL,
  `typededocument` VARCHAR(45) NULL DEFAULT NULL,
  `discipline` VARCHAR(45) NULL DEFAULT NULL,
  `diplome` VARCHAR(45) NULL DEFAULT NULL,
  `url` VARCHAR(45) NULL DEFAULT NULL,
  `nombredepages` VARCHAR(45) NULL DEFAULT NULL,
  `resume` VARCHAR(45) NULL DEFAULT NULL,
  `support` VARCHAR(45) NULL DEFAULT NULL,
  `filiere` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idDOCUMENT`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`PERSONNE_has_DOCUMENT` (
  `idPERSONNE` INT(11) NOT NULL,
  `idDOCUMENT` INT(11) NOT NULL,
  `role` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idPERSONNE`, `idDOCUMENT`),
  INDEX `fk_PERSONNE_has_DOCUMENT_DOCUMENT1_idx` (`idDOCUMENT` ASC),
  INDEX `fk_PERSONNE_has_DOCUMENT_PERSONNE_idx` (`idPERSONNE` ASC),
  CONSTRAINT `fk_PERSONNE_has_DOCUMENT_PERSONNE`
    FOREIGN KEY (`idPERSONNE`)
    REFERENCES `mydb`.`PERSONNE` (`idPERSONNE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PERSONNE_has_DOCUMENT_DOCUMENT1`
    FOREIGN KEY (`idDOCUMENT`)
    REFERENCES `mydb`.`DOCUMENT` (`idDOCUMENT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`MOTCLEF` (
  `idMOTCLEF` INT(11) NOT NULL,
  `nomMotClef` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idMOTCLEF`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`DOCUMENT_has_MOTCLEF` (
  `idDOCUMENT` INT(11) NOT NULL,
  `idMOTCLEF` INT(11) NOT NULL,
  `valeur` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idDOCUMENT`, `idMOTCLEF`),
  INDEX `fk_DOCUMENT_has_MOTCLEF_MOTCLEF1_idx` (`idMOTCLEF` ASC),
  INDEX `fk_DOCUMENT_has_MOTCLEF_DOCUMENT1_idx` (`idDOCUMENT` ASC),
  CONSTRAINT `fk_DOCUMENT_has_MOTCLEF_DOCUMENT1`
    FOREIGN KEY (`idDOCUMENT`)
    REFERENCES `mydb`.`DOCUMENT` (`idDOCUMENT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_DOCUMENT_has_MOTCLEF_MOTCLEF1`
    FOREIGN KEY (`idMOTCLEF`)
    REFERENCES `mydb`.`MOTCLEF` (`idMOTCLEF`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`UNIVERSITE` (
  `idUNIVERSITE` INT(11) NOT NULL,
  `nom` VARCHAR(45) NULL DEFAULT NULL,
  `ecole` VARCHAR(45) NULL DEFAULT NULL,
  `faculte` VARCHAR(45) NULL DEFAULT NULL,
  `departement` VARCHAR(45) NULL DEFAULT NULL,
  `rue` VARCHAR(45) NULL DEFAULT NULL,
  `ville` VARCHAR(45) NULL DEFAULT NULL,
  `courriel` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idUNIVERSITE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`PERSONNE_has_UNIVERSITE` (
  `idPERSONNE` INT(11) NOT NULL,
  `idUNIVERSITE` INT(11) NOT NULL,
  PRIMARY KEY (`idPERSONNE`, `idUNIVERSITE`),
  INDEX `fk_PERSONNE_has_UNIVERSITE_UNIVERSITE1_idx` (`idUNIVERSITE` ASC),
  INDEX `fk_PERSONNE_has_UNIVERSITE_PERSONNE1_idx` (`idPERSONNE` ASC),
  CONSTRAINT `fk_PERSONNE_has_UNIVERSITE_PERSONNE1`
    FOREIGN KEY (`idPERSONNE`)
    REFERENCES `mydb`.`PERSONNE` (`idPERSONNE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PERSONNE_has_UNIVERSITE_UNIVERSITE1`
    FOREIGN KEY (`idUNIVERSITE`)
    REFERENCES `mydb`.`UNIVERSITE` (`idUNIVERSITE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`UNIVERSITE_has_DOCUMENT` (
  `idUNIVERSITE` INT(11) NOT NULL,
  `idDOCUMENT` INT(11) NOT NULL,
  PRIMARY KEY (`idUNIVERSITE`, `idDOCUMENT`),
  INDEX `fk_UNIVERSITE_has_DOCUMENT_DOCUMENT1_idx` (`idDOCUMENT` ASC),
  INDEX `fk_UNIVERSITE_has_DOCUMENT_UNIVERSITE1_idx` (`idUNIVERSITE` ASC),
  CONSTRAINT `fk_UNIVERSITE_has_DOCUMENT_UNIVERSITE1`
    FOREIGN KEY (`idUNIVERSITE`)
    REFERENCES `mydb`.`UNIVERSITE` (`idUNIVERSITE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_UNIVERSITE_has_DOCUMENT_DOCUMENT1`
    FOREIGN KEY (`idDOCUMENT`)
    REFERENCES `mydb`.`DOCUMENT` (`idDOCUMENT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`STAGE` (
  `idSTAGE` INT(11) NOT NULL,
  `nomentreprise` VARCHAR(45) NULL DEFAULT NULL,
  `rue` VARCHAR(45) NULL DEFAULT NULL,
  `ville` VARCHAR(45) NULL DEFAULT NULL,
  `couriel` VARCHAR(45) NULL DEFAULT NULL,
  `commentaire` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`idSTAGE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

CREATE TABLE IF NOT EXISTS `mydb`.`DOCUMENT_has_STAGE` (
  `idDOCUMENT` INT(11) NOT NULL,
  `idSTAGE` INT(11) NOT NULL,
  PRIMARY KEY (`idDOCUMENT`, `idSTAGE`),
  INDEX `fk_DOCUMENT_has_STAGE_STAGE1_idx` (`idSTAGE` ASC),
  INDEX `fk_DOCUMENT_has_STAGE_DOCUMENT1_idx` (`idDOCUMENT` ASC),
  CONSTRAINT `fk_DOCUMENT_has_STAGE_DOCUMENT1`
    FOREIGN KEY (`idDOCUMENT`)
    REFERENCES `mydb`.`DOCUMENT` (`idDOCUMENT`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_DOCUMENT_has_STAGE_STAGE1`
    FOREIGN KEY (`idSTAGE`)
    REFERENCES `mydb`.`STAGE` (`idSTAGE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[JeSuisConscient]

etudie les cardinalites pour sortir des données redondantes des tables
Personne : fonction
Document : typededocument,discipline,dipl ome,support,filiere
stage : si plusieurs stages dans la meme entreprise donc tu dois creer une entité entreprise ( un stage a lieu dans cette entreprise )

formes normales