question general sur innodb

**gtraxx** · 03/08/2009, 11h38

Bonjour à tous, je souhaite plus de renseignement sur la gestion des relations avec innoDB.
En général, je travail avec MyIsam sur de petit projet avec des CMS léger.
Mais une conception conséquente est en cours de développement et je souhaite alors travailler avec InnoDb et la gestion des relations.
J'ai lu ce tutoriel : http://sony-noel.developpez.com/tuto...page=innodb#LX
Mais certaine zone d'ombre sont toujours dans ma petite tête

.
Est ce vrai que l'ordre de création des tables est importante avec ce mode ?
Est ce plus difficile à appréhender ?
Cela réduit il les jointures manuelle ?
Bref j'aimerai savoir de petite chose avant de ma lancer surtout au niveau de la conception des tables et des requêtes.
Je travail souvent avec de grosse jointure sous myIsam mais un aspect plus "automatisé" avec les clés étrangère m'intrigue fortement.

**ced** · 03/08/2009, 12h16

Bonjour,

Est ce vrai que l'ordre de création des tables est importante avec ce mode ?

Forcément, puisqu'on peut définir des clefs étrangères. Il faut donc que la table référencée soit créée avant la table qui la référence.

Est ce plus difficile à appréhender ?

Non, pas nécessairement. Il faut juste préciser le moteur à chaque création de table, penser à indexer correctement les colonnes qui seront référencées, et créer les clefs étrangères.
Ne t'inquiète pas, en cas de problème, nous sommes là pour t'aider

.

Cela réduit il les jointures manuelle ?

Non, ça ne réduit pas les jointures d'une requête. En revanche, ça assure un contrôle de l'intégrité des données au niveau des requêtes d'insertion, de mise à jour et de suppression.
Ainsi, il n'est pas possible d'insérer une donnée référencée avec une valeur qui n'existe pas dans la colonne référencée. De même sur la mise à jour...
Enfin, pour la suppression, il est possible de limiter la suppression de lignes d'une table référencée si il existe des lignes qui la référencent dans une autre table (ou alors de tout supprimer en cascade, sans avoir à faire 2 opérations de suppression).

Voici un lien vers un tutoriel SQL qui explique un peu plus en détail ce qu'est l'intégrité référentielle : http://sqlpro.developpez.com/cours/s...partie2#L7.1.7

ced

**gtraxx** · 03/08/2009, 22h06

Donc en gros cela n'altère pas ma méthode de travail.
Je construit mes requêtes de la même manière avec cependant un contrôle supplémentaire d'intégrité référenciel.
Prenons un exemple de requête :

Code :

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

SELECT c.*, sa.*, co.*, p.idpays, p.pays FROM client c LEFT JOIN secteur_activite sa ON ( c.idsecteur = sa.idsecteur ) LEFT JOIN communes co ON ( c.idcommune = co.idcommune ) LEFT JOIN pays p ON ( co.idpays = p.idpays )

Je suppose que le fais d'utiliser l'intégrité référenciel va aider à contrôler l'insertion.
Bien sur il faut alors repenser les tables pour faire des tables de référence

**ced** · 04/08/2009, 09h10

Non, le fait de disposer d'un contrôle, côté SGBD, de l'intégrité référentiel ne devrait pas changer le modèle de tes tables, si celui-ci est correct.
Simplement, tu n'as plus à vérifier toi-même si une valeur référencée existe déjà ou pas.

ced

**gtraxx** · 05/08/2009, 12h22

Je suis entrain de créer une base de donnée sur ce modèle et je doit donc faire des relations.
Les tables :

t_profil
t_score
t_amis
t_redact
r_profil

t_profil

idprofil
nom
prenom
siteweb

t_score

idscore
score

t_amis

idamis
amis

t_redact

idredactions
texte1
texte2

r_profil

idprofil
idscore
idamis
idredactions

D'autres tables viendrons s'ajouter, mais je pense faire également une table spécifique pour l'activation par email, donc en gros
t_activation

idprofil
code

Je pense la lié provisoirement avec le profil avec la clé idprofil afin de pouvoir supprimé un enregistrement si le code est activé.(libéré de la mémoire)

Le schéma vous semble il correct ?
J'ai fais autant de table séparé vu que chacune d'entre elle dois être indépendante mais toutes lié à la table profil

**ced** · 05/08/2009, 12h44

Ça devient plus un problème de conception/modélisation qu'un problème technique MySQL, à présent

.
Il vaut mieux poser une nouvelle question dans le forum Schéma.
A moins qu'il ne reste des problèmes avec InnoDB ?

ced

**gtraxx** · 05/08/2009, 13h50

En faites je voulais juste savoir si l'ordre de création des tables vous sembles correct et surtout pour savoir comment faire pour la liaison des tables ou du moins faire en sorte de contrôler les références.
La table maitresse est profil et les autres tables ne sont en faites que la continuité de celui-ci.
J'ai séparé afin de pouvoir les traités séparément au besoin, j'avoue que les jointures vont être hard.
la table de références est r_profil qui au besoin va servir de plaque tournante vers les autres tables dont j'ai besoin .
Afin d'utiliser l'intégrité référentielle,
Je souhaite par exemple vérifié si un pseudo est déjà pris lors de l'enregistrement (d'ou l'intérêt de innodb).
Comment s'y prendre ? Est ce que l'ordre de création des tables est correct ?
Quel requêtes faire pour cette vérification (FOREIGN KEY) ?
Je n'ai pas encore fais joujou avec FOREIGN KEY mais autant le faire correctement.
Ensuite au besoin, je posterai pour ma structure dans un nouveau FIL

**ced** · 05/08/2009, 14h39

L'ordre de création des tables va être vérifié par MySQL quand tu vas créer tes contraintes de clefs étrangères. Si la table référencée n'existe alors pas, tu récupèreras une erreur.
L'ordre que tu donnes a l'air correct.

Pour vérifier si un pseudo est déjà pris, il faut que tu ajoutes une contrainte d'unicité (UNIQUE) sur la colonne pseudo dans la table des profils. Dans ce cas, si tu tentes d'insérer un pseudo qui existe déjà, MySQL lèvera une erreur.

ced

**gtraxx** · 07/08/2009, 10h22

Merci, j'ai une autre question.
Je prend un exemple, disons que j'ai une table profil et une table rédaction.
Je souhaite faire en sorte que l'insertion d'une fiche (rédaction) se fasse uniquement si je fais partie de la table profil.
Donc en gros créer une intégrité référentielle entre profil et rédaction.
Lorsque je créer les tables ou dois je mettre FOREIGN KEY ??
Est ce la table rédaction ?

Code :

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

1
2
3
4
5
6
7
CREATE TABLE profil
(idprofil             INTEGER
 )
CREATE TABLE redaction
(idred             INTEGER,
 idprofil             INTEGER      FOREIGN KEY REFERENCES profil (idprofil)
 )

note: je n'ai pris qu'un exemple simple qui entrera en compte dans ma conception contenant plus de 70 tables

Je dois aussi pouvoir supprimer un profil mais sans pour autant supprimé la rédaction de l'auteur (sans retourner d'erreur mysql)

**ced** · 07/08/2009, 10h45

On est purement dans le conceptuel...
La question qu'il faut que tu te poses, c'est de savoir si une fiche a un profil. Si j'ai bien compris, le profil correspond au rédacteur de la fiche, c'est ça ?
Donc, oui : à un rédacteur correspondent de 0 à N fiche, et une fiche a un (et un seul) rédacteur.
Ca se traduit, comme tu l'as écrit, par une clef étrangère dans la table des fiches vers la table des profils. Au passage, le rédacteur étant obligatoire, il faudra mettre NOT NULL à la colonne identifiant le profil dans la table redaction.

ced

**gtraxx** · 07/08/2009, 12h15

Oui c'est exactement cela, une fiche ne peu être créer sans rédacteur d'ou l'intérêt d'utiliser innodb dans mon projet.
J'ai fais un petit test:

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 `profil` (
  `idprofil` tinyint(1) NOT NULL auto_increment,
  `login` varchar(50) NOT NULL,
  PRIMARY KEY  (`idprofil`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
 
INSERT INTO `profil` (`idprofil`, `login`) VALUES
(1, 'aurelien'),
(2, 'jean');
 
CREATE TABLE IF NOT EXISTS `redaction` (
  `idredaction` tinyint(1) NOT NULL auto_increment,
  `idprofil` tinyint(1) NOT NULL,
  `content` varchar(20) NOT NULL,
  PRIMARY KEY  (`idredaction`),
  KEY `idprofil` (`idprofil`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
INSERT INTO `redaction` (`idredaction`, `idprofil`, `content`) VALUES
(1, 1, 'test'),
(2, 1, 'test');
 
ALTER TABLE `redaction`
  ADD CONSTRAINT `redaction_ibfk_1` FOREIGN KEY (`idprofil`) REFERENCES `profil` (`idprofil`);

Mais un truc m'interpelle, j'utilise phpmyadmin pour mes tests et j'ai vu deux options supplémentaires lors de la gestion des relations.
ON DELETE

cascade
set null
no action
restrict

ON UPDATE

cascade
set null
no action
restrict

A quoi corresponde ses deux options et surtout que sont ses fameux paramètres

.
Je ne trouve aucune documentation sur ses paramètres et encore moins des exemples

**ced** · 07/08/2009, 14h19

Continuons avec l'exemple entre les tables profil et redaction.
Tu as créé des fiches dans redaction dont le rédacteur est référencé dans profil.
Il se peut que tu fasses une mise à jour (bien que ça ne soit pas recommandé) de l'identifiant du rédacteur dans la table profil (c'est pour "ON UPDATE"). Ou, plus probable, il se peut que tu supprimes un profil (c'est pour "ON DELETE").
Voici ce qui se passera en fonction du paramètre retenu sur l'un ou l'autre :

CASCADE : la mise à jour de l'identifiant se fait toute seule dans la table redaction sur les fiches rédigées par le profil que tu mets à jour. Dans le cas d'un DELETE, la suppression d'un profil entraîne automatiquement la suppression de toutes les fiches de cet auteur (d'où le mot CASCADE : le SGBD supprime en cascade les lignes correspondantes) ;
SET NULL : la mise à jour de l'identifiant dans la table profil, ou la suppression du profil, entraîne la mise à jour de l'identifiant du rédacteur de fiche correspondant dans la table redaction en mettant dedans la valeur NULL ;
NO ACTION : la mise à jour de l'identifiant dans la table profil, ou la suppression du profil, n'entraîne aucune action sur l'identifiant du rédacteur de fiche correspondant dans la table redaction. C'est le comportement qu'on a avec les tables MyISAM : il n'y a pas de contrôle d'intégrité ;
RESTRICT : on ne peut pas mettre à jour un identifiant de profil, ni supprimé un profil, tant qu'il existe des fiches dont il est le rédacteur. Ca bloque et un message d'erreur indique que la suppression ne peut pas se faire.

Voilà pour les explications

.

Sinon, petit détail :

Oui c'est exactement cela, une fiche ne peu être créer sans rédacteur d'ou l'intérêt d'utiliser innodb dans mon projet.

En fait, en MyISAM, en mettant NOT NULL sur la définition de la colonne du rédacteur, on arrive à la même chose : on oblige à renseigner un rédacteur... Par contre, ce qu'apporte InnoDB, c'est le fait que ce rédacteur est nécessairement un rédacteur renseigné dans la table profil.
Je pinaille

, mais c'est important que ça soit clair.

ced

**gtraxx** · 10/08/2009, 10h12

Il se peut que tu fasses une mise à jour (bien que ça ne soit pas recommandé) de l'identifiant du rédacteur dans la table profil (c'est pour "ON UPDATE"). Ou, plus probable, il se peut que tu supprimes un profil (c'est pour "ON DELETE").

J'ai entre vu que ce n'étais pas recommandé à cause des ressources demandé pour la suppression et update en cascade.
Ce qui reviens à le faire avec PHP

.

Par contre, ce qu'apporte InnoDB, c'est le fait que ce rédacteur est nécessairement un rédacteur renseigné dans la table profil.

C'est pour cette raison que je souhaite utilisé Innodb sachant que la base de donnée sera énorme

(casse tête en vue).
Je reviendrai vers vous pour d'autres renseignements technique sur la gestion relationnelle et surtout son optimisation (multi-jointure bien pensée).
merci beaucoup

question general sur innodb

MySQL

Discussions similaires

Partager

Partager