Aide pour modélisation sur MySQL

Version imprimable

Bonjour à tous,
Je souhaiterais votre avis sur les améliorations, erreurs, taille des champs texte et autres problèmes de conception de ma base de données mysql. Si vous pouviez m'aider à optimiser le tout ce serait vraiment sympa. Je me suis basé sur un modèle que vous pouvez voir à cette adresse http://www.databaseanswers.org/data_...logs/index.htm. Voici le dump que je viens d'effectuer sur ma table.

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
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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Serveur: localhost
-- Généré le : Mercredi 30 Juillet 2008 à 19:19
-- Version du serveur: 5.0.27
-- Version de PHP: 5.2.0
-- 
-- Base de données: `cmsproject`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `attribute_definitions`
-- 
 
CREATE TABLE `attribute_definitions` (
  `attribute_id` int(10) unsigned NOT NULL auto_increment,
  `attribute_name` varchar(255) collate latin1_general_ci NOT NULL,
  `attribute_data_type` varchar(255) collate latin1_general_ci NOT NULL,
  `attribute_description` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`attribute_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_contents`
-- 
 
CREATE TABLE `catalog_contents` (
  `catalog_entry_id` int(10) unsigned NOT NULL auto_increment,
  `catalog_level_number` int(10) NOT NULL,
  `parent_entry_id` int(10) NOT NULL,
  `previous_entry_id` int(10) NOT NULL,
  `next_entry_id` int(10) NOT NULL,
  `manufacturer` varchar(255) collate latin1_general_ci NOT NULL,
  `product_reference_number` varchar(255) collate latin1_general_ci NOT NULL,
  `product_description` varchar(255) collate latin1_general_ci NOT NULL,
  `photo_filename` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_entry_name` varchar(255) collate latin1_general_ci NOT NULL,
  `price_in_dollars` float(10,2) NOT NULL,
  `price_in_euros` float(10,2) NOT NULL,
  `price_in_pounds` float(10,2) NOT NULL,
  `capacity` float(10,3) NOT NULL,
  `length` float(10,3) NOT NULL,
  `height` float(10,3) NOT NULL,
  `width` float(10,3) NOT NULL,
  `other_details` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`catalog_entry_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_contents_additional_attributes`
-- 
 
CREATE TABLE `catalog_contents_additional_attributes` (
  `catalog_entry_id` int(10) NOT NULL,
  `catalog_level_number` int(10) NOT NULL,
  `attribute_id` int(10) NOT NULL,
  `attribute_value` varchar(255) collate latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_entry_definitions`
-- 
 
CREATE TABLE `catalog_entry_definitions` (
  `catalog_level_number` int(11) NOT NULL,
  `attribute_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_structure`
-- 
 
CREATE TABLE `catalog_structure` (
  `catalog_level_number` int(10) unsigned NOT NULL auto_increment,
  `catalog_id` int(10) unsigned NOT NULL,
  `catalog_level_name` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_level_description` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`catalog_level_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalogs`
-- 
 
CREATE TABLE `catalogs` (
  `catalog_id` int(10) unsigned NOT NULL auto_increment,
  `catalog_name` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_publisher` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_description` varchar(255) collate latin1_general_ci NOT NULL,
  `date_of_publication` timestamp NOT NULL default '0000-00-00 00:00:00',
  `date_of_latest_revision` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`catalog_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Pensez-vous que je doive réellement passer en InnoDB pour utiliser les Foreign Keys car je n'ai pas l'impression que ce soit indispensable. Quel que soit votre avis il est le bienvenu car je souhaiterais partir sur de bonnes bases pour le développement de mon projet sous PHP.

Merci d'avance.

30/07/2008, 21h07
qi130

Si tu restes en MyIsam, la gestion des FK (intégrité référentielle) sera à ta charge...
... et donc, pourquoi vouloir gérer ça toi même quand InnoDB peut le faire, et de manière optimisée en plus ?

Pour ton modèle, sans cahier des charges, on aura du mal à en faire une critique.
30/07/2008, 21h15
alejandro

Merci pour votre réponse.
En fait le modèle que je voudrais reproduire est celui indiqué sur le lien au dessus du code. Je ne sais pas comment gérer ce que l'auteur appelle les FK (Foreign Keys) et les PF (Primary Foreign) via phpMyAdmin. Ensuite le genre de conseils que je voudrais seraient sur la taille de mes champs etc. Depuis tout à l'heure j'ai switché mes tables en InnoDB mais je ne sais pas du tout l'utiliser et je cherche depuis tout à l'heure comment mettre en place les relations indiquées par l'auteur.
Merci pour votre réponse néammoins ^^
31/07/2008, 07h05
Antoun
Citation:

Envoyé par alejandro

Je ne sais pas comment gérer ce que l'auteur appelle les FK (Foreign Keys) et les PF (Primary Foreign) via phpMyAdmin. [...] Depuis tout à l'heure j'ai switché mes tables en InnoDB mais je ne sais pas du tout l'utiliser et je cherche depuis tout à l'heure comment mettre en place les relations indiquées par l'auteur.

Une relation lie généralement une clé étrangère (FK) d'une table à la clé primaire (PK) d'une autre table (ou bien de la même table dans le cas des relations dites réflexives, aussi appelées "autojointures", comme par exemple sur ton schéma la relation de catalog_contents.parent_entry_id vers catalog_contents.catalog_entry_id).

Les clés primaires se mettent en place facilement sous phpMyAdmin, avec le bouton http://antoun.developpez.com/temp/b_primary.png à droite de la définition de chaque colonne.

phpMyAdmin ne gère pas les clés étrangères graphiquement. Il faut aller dans la fenêtre SQL et passer le code suivant (exemple pour la FK catalog_id sur la table catalog_structure) :
Code:

1 2 alter table catalog_structure add foreign key (catalog_id) references catalogs (catalog_id)
Dit autrement, on modifie la table catalog_structure pour y ajouter une FK sur catalog_id ; cette FK référence la colonne catalog_id de la table catalogs.

Citation:

Envoyé par alejandro

Ensuite le genre de conseils que je voudrais seraient sur la taille de mes champs etc.

Au passage, dans une base de données, on parle plutôt de colonnes que de champs.
Pour la taille, la consigne est en gros "il faut que ça soit assez grand pour stocker tes données, et si tu gâches de la place en mettant des tailles un peu trop grandes c'est pas très grave".
31/07/2008, 13h52
alejandro

Merci beaucoup de l'intérêt que vous avez portés à mon sujet.
Une dernière question que je me pose concerne la PF (Primary Foreign), est-il possible de mettre ces clés en place dans une table InnoDB et quelle est la différence avec une simple FK ? Est-ce une FK qui est primaire tout simplement ? Et dans cette éventualité, est-il possible de définir plusieurs clés primaires dans une même table comme dans la table Catalog_Entry_Definitions ? Merci d'avance pour votre aide.
31/07/2008, 14h59
CinePhil

Citation:

Envoyé par Antoun

phpMyAdmin ne gère pas les clés étrangères graphiquement.

8O8O8O8O8O

Affiche une table InnoDB.
Juste sous la liste des colonnes, il y a le lien 'Gestion des relations'. Il suffit de cliquer dessus. :yaisse2:
31/07/2008, 15h02
Antoun

Citation:

Envoyé par alejandro

Merci beaucoup de l'intérêt que vous avez portés à mon sujet.
Une dernière question que je me pose concerne la PF (Primary Foreign), est-il possible de mettre ces clés en place dans une table InnoDB et quelle est la différence avec une simple FK ? Est-ce une FK qui est primaire tout simplement ?

disons plus précisément que ce sont des colonnes qui à la fois font partie d'une PK et portent des FK.

Citation:

Envoyé par alejandro

Et dans cette éventualité, est-il possible de définir plusieurs clés primaires dans une même table comme dans la table Catalog_Entry_Definitions ? Merci d'avance pour votre aide.

Pas plusieurs clés primaires, mais une clé primaire composée de plusieurs colonnes.
31/07/2008, 15h08
Antoun

Citation:

Envoyé par CinePhil

8O8O8O8O8O

Affiche une table InnoDB.
Juste sous la liste des colonnes, il y a le lien 'Gestion des relations'. Il suffit de cliquer dessus. :yaisse2:

Bah je ne vois pas... mais j'ai seulement la version de Free, càd la 2.11.3... il y a peut-être plus récent ?

Merci de l'info en tout cas !
31/07/2008, 15h11
CinePhil

Au boulot j'ai la version 2.6.1 de phpMyAdmin donc ça devrait y être en version plus récente mais peut-être pas au même endroit. Faudrait que je regarde chez-moi.
Par contre bien sûr cette option n'apparaît qu'avec une table InnoDB !

Merci à tous pour l'intérêt que vous avez porté à mon sujet. Je pense avoir réussi à reproduire le modèle de base de données fourni par Barry Williams. Voici le dump SQL de la base :

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
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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Serveur: localhost
-- Généré le : Jeudi 31 Juillet 2008 à 21:11
-- Version du serveur: 5.0.27
-- Version de PHP: 5.2.0
-- 
-- Base de données: `product_catalogs`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `attribute_definitions`
-- 
 
CREATE TABLE `attribute_definitions` (
  `attribute_id` int(10) unsigned NOT NULL auto_increment,
  `attribute_name` varchar(255) collate latin1_general_ci NOT NULL,
  `attribute_data_type` varchar(255) collate latin1_general_ci NOT NULL,
  `attribute_description` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_contents`
-- 
 
CREATE TABLE `catalog_contents` (
  `catalog_entry_id` int(10) unsigned NOT NULL auto_increment,
  `catalog_level_number` int(10) unsigned NOT NULL,
  `parent_entry_id` int(10) unsigned default NULL,
  `previous_entry_id` int(10) unsigned default NULL,
  `next_entry_id` int(10) unsigned default NULL,
  `manufacturer` varchar(255) collate latin1_general_ci NOT NULL,
  `product_reference_number` varchar(255) collate latin1_general_ci NOT NULL,
  `product_description` varchar(255) collate latin1_general_ci NOT NULL,
  `photo_filename` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_entry_name` varchar(255) collate latin1_general_ci NOT NULL,
  `price_in_dollars` float(10,2) NOT NULL,
  `price_in_euros` float(10,2) NOT NULL,
  `price_in_pounds` float(10,2) NOT NULL,
  `capacity` float(10,3) NOT NULL,
  `length` float(10,3) NOT NULL,
  `height` float(10,3) NOT NULL,
  `width` float(10,3) NOT NULL,
  `other_details` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`catalog_entry_id`),
  KEY `catalog_level_number` (`catalog_level_number`),
  KEY `parent_entry_id` (`parent_entry_id`),
  KEY `previous_entry_id` (`previous_entry_id`),
  KEY `next_entry_id` (`next_entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_contents_additional_attributes`
-- 
 
CREATE TABLE `catalog_contents_additional_attributes` (
  `catalog_entry_id` int(10) unsigned NOT NULL,
  `catalog_level_number` int(10) unsigned NOT NULL,
  `attribute_id` int(10) unsigned NOT NULL,
  `attribute_value` varchar(255) collate latin1_general_ci NOT NULL,
  KEY `catalog_entry_id` (`catalog_entry_id`),
  KEY `catalog_level_number` (`catalog_level_number`),
  KEY `attribute_id` (`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_entry_definitions`
-- 
 
CREATE TABLE `catalog_entry_definitions` (
  `catalog_level_number` int(10) unsigned NOT NULL,
  `attribute_id` int(10) unsigned NOT NULL,
  KEY `catalog_level_number` (`catalog_level_number`),
  KEY `attribute_id` (`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalog_structure`
-- 
 
CREATE TABLE `catalog_structure` (
  `catalog_level_number` int(10) unsigned NOT NULL auto_increment,
  `catalog_id` int(10) unsigned NOT NULL,
  `catalog_level_name` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_level_description` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`catalog_level_number`),
  KEY `catalog_id` (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `catalogs`
-- 
 
CREATE TABLE `catalogs` (
  `catalog_id` int(10) unsigned NOT NULL auto_increment,
  `catalog_name` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_publisher` varchar(255) collate latin1_general_ci NOT NULL,
  `catalog_description` varchar(255) collate latin1_general_ci NOT NULL,
  `date_of_publication` int(10) unsigned NOT NULL,
  `date_of_latest_revision` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`catalog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
 
-- 
-- Contraintes pour les tables exportées
-- 
 
-- 
-- Contraintes pour la table `catalog_contents`
-- 
ALTER TABLE `catalog_contents`
  ADD CONSTRAINT `catalog_contents_ibfk_1` FOREIGN KEY (`catalog_level_number`) REFERENCES `catalog_structure` (`catalog_level_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `catalog_contents_ibfk_2` FOREIGN KEY (`parent_entry_id`) REFERENCES `catalog_contents` (`catalog_entry_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `catalog_contents_ibfk_3` FOREIGN KEY (`previous_entry_id`) REFERENCES `catalog_contents` (`catalog_entry_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `catalog_contents_ibfk_4` FOREIGN KEY (`next_entry_id`) REFERENCES `catalog_contents` (`catalog_entry_id`) ON DELETE SET NULL ON UPDATE CASCADE;
 
-- 
-- Contraintes pour la table `catalog_contents_additional_attributes`
-- 
ALTER TABLE `catalog_contents_additional_attributes`
  ADD CONSTRAINT `catalog_contents_additional_attributes_ibfk_1` FOREIGN KEY (`catalog_entry_id`) REFERENCES `catalog_contents` (`catalog_entry_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `catalog_contents_additional_attributes_ibfk_2` FOREIGN KEY (`catalog_level_number`) REFERENCES `catalog_entry_definitions` (`catalog_level_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `catalog_contents_additional_attributes_ibfk_3` FOREIGN KEY (`attribute_id`) REFERENCES `catalog_entry_definitions` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
-- 
-- Contraintes pour la table `catalog_entry_definitions`
-- 
ALTER TABLE `catalog_entry_definitions`
  ADD CONSTRAINT `catalog_entry_definitions_ibfk_1` FOREIGN KEY (`catalog_level_number`) REFERENCES `catalog_structure` (`catalog_level_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `catalog_entry_definitions_ibfk_2` FOREIGN KEY (`attribute_id`) REFERENCES `attribute_definitions` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE;
 
-- 
-- Contraintes pour la table `catalog_structure`
-- 
ALTER TABLE `catalog_structure`
  ADD CONSTRAINT `catalog_structure_ibfk_1` FOREIGN KEY (`catalog_id`) REFERENCES `catalogs` (`catalog_id`) ON DELETE CASCADE ON UPDATE CASCADE;

N'hésitez pas à commenter si le tout ne semble pas correspondre ou si vous voyez des erreurs ou de mauvaises correspondances. Pour moi en tout cas celà me semble résolu ^^ Merci encore à la communauté.