Discussion :

[djezair31]

Bonjour,

Je développe une application de gestion de vente (ventes, fournisseurs, clients, produits, ...) pour une nouvelle entreprise. Pour commencer, je dois créer une base de donnée. Pourriez-vous me dire si le schéma ci-dessous est correct et optimisé.

Code SQL :

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
52
53
54
55
56
    CREATE TABLE IF NOT EXISTS `company` (
      `SIRET` varchar(50) NOT NULL,
      `nom` varchar(50) NOT NULL,
      `description` varchar(500) NOT NULL,
      `enable` ENUM('YES', 'NO') DEFAULT 'YES',
      `level` int(1) NOT NULL,
      PRIMARY KEY  (SIRET),
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
    // contactType can be one of the 3 values : email, phone, fax
    CREATE TABLE IF NOT EXISTS `contactType` (
      id UNSIGNED INT NOT NULL auto_increment,
      `contactType` ENUM('email', 'phonenumber', 'faxnumber')
      `mobile` ENUM('YES', 'NO') default 'NO',
      PRIMARY KEY  (id),
      UNIQUE KEY type (contactType)
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
    CREATE TABLE IF NOT EXISTS `contacts` (
      id UNSIGNED INT NOT NULL auto_increment,
      `SIRET` varchar(50) NOT NULL,
      `contactType` varchar(50) NOT NULL, // A reference to contactType just above
      `contactref` varchar(50) NOT NULL, // Phone number, fax number or email adress
      PRIMARY KEY  (id),
      UNIQUE KEY type (SIRET)
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
    CREATE TABLE IF NOT EXISTS `customers` (
      id UNSIGNED INT NOT NULL auto_increment,
      `SIRET` varchar(50) NOT NULL,
      PRIMARY KEY  (id),
      UNIQUE KEY type (SIRET)
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
    CREATE TABLE IF NOT EXISTS `supplier` (
      id UNSIGNED INT NOT NULL auto_increment,
      `SIRET` varchar(50) NOT NULL,
      PRIMARY KEY  (id),
      UNIQUE KEY type (SIRET)
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
    CREATE TABLE IF NOT EXISTS `industry` (
      id UNSIGNED INT NOT NULL auto_increment,
      `industry` varchar(250) NOT NULL,
      PRIMARY KEY  (id),
      UNIQUE KEY type (activite)
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
    CREATE TABLE IF NOT EXISTS `entreprisePerIndustry` (
      id UNSIGNED INT NOT NULL auto_increment,
      `industry_id` varchar(250) NOT NULL, // Chemical, Computer, Consulting, ...
      FOREIGN KEY (industry_id) REFERENCES industry(id) ON DELETE CASCADE,
      `SIRET` varchar(50) NOT NULL,
      PRIMARY KEY  (id),
      UNIQUE KEY type (industry_id)
    ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

[CinePhil]

Qu'est-ce que le "sirep" ? L'équivalent du "SIRET" français ?

La présence de ce sirep dans presque toutes les tables fait-elle référence au sirep de la table company ?
Si oui, c'est une erreur !
Il faut faire référence à la clé primaire de la table, pas à une clé alternative !

Vous devriez proposer un diagramme entité / association ou un MCD, ce serait plus facile de comprendre la nature des associations entre les tables.

Et sans les règles de gestion, il est difficile de se prononcer sur la pertinence du modèle.

Il y aura sans doute aussi des choses à dire sur le choix des types et tailles des colonnes, sur l'indexation, sur le choix du moteur SQL... mais commençons par le commencement : la conception !

[djezair31]

Faute de frappe. Il s'agit bien du SIRET. Je viens de mettre à jour mon code (SIRET, InnoDB, UTF8)

Le SIRET des autres tables fait bien référence au SIRET de la table COMPANY.
Me conseillez-vous de remplacer (Dans toutes les tables autre que company)

`SIRET` varchar(50) NOT NULL,

par
`company_siret` varchar(50) NOT NULL,
FOREIGN KEY (company_siret) REFERENCES company(siret) ON DELETE CASCADE,

Merci

[fsmrel]

Bonjour,


« Ceux qui ont oublié le passé sont condamnés à le revivre. » (Goethe, etc.)

Je reprends une discussion de l’an dernier, dans laquelle les termes SOCIETE par COMPANY sont interchangeables et où l’on a pour identifier les sociétés les attributs IdSociete, CodeSociete, puis NoSiren (Numéro Siren des entreprises).


J'avais alors écrit :


--------------------- début de citation ---------------------------------

Une règle de bonne modélisation : dans la base de données, les valeurs prises par l’attribut CodeSociete de la table SOCIETE devront être fournies par le système (par exemple au moyen du mécanisme d’auto-incrémentation, lequel est proposé par tous les SGBD). Il y a deux raisons à cela : un identifiant ne doit pas être porteur de sens et il doit être invariant.

A propos de l’absence de sens, je cite l’excellent Yves Tabourier qui écrivait il y a trente ans dans le contexte Merise (De l’autre côté de MERISE page 81) :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Considérez la recommandation de Tabourier comme une règle d’or qui n’a pas vieilli (Notez au passage que le terme « propriété » utilisé par Tabourier est interchangeable avec celui d’« attribut »).

Imaginez une table des automobiles pour laquelle l’identifiant serait constitué du numéro d’immatriculation selon l’ancien système français : que faire en cas de changement de ce numéro (suite à un changement de propriétaire) répliqué dans plusieurs tables ? Que faire lors du passage au nouveau système d’immatriculation ?


A propos de l’invariance : un attribut jouant le rôle d’identifiant ne doit pas changer de valeur. Je reprends l’exemple que j’utilise invariablement, avec lequel je me situe au niveau de la base de données, car c’est là que la patate chaude atterrit et que les drames se jouent :

Les concepteurs d’un projet d’une grande banque avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type Entreprise). Au niveau de la base de données, par le jeu des liens inter-tables (clé primaire - clé étrangère), le numéro Siren se propageait dans de nombreuses tables. Or, ce numéro est fourni par l’INSEE, lequel envoyait tous les mois les correctifs modifiant le Siren des nouvelles entreprises (10% d’entre elles à peu près). Les concepteurs en avaient tenu compte et me montrèrent le modèle correspondant à la mise à jour des tables impliquées : une usine à gaz ! J’avais fait observer que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit), du fait d’une activité de mise à jour excessive et en plus, délicate à ordonnancer. Sans que j'ai eu à le leur demander, les concepteurs définirent un nouvel attribut, non porteur de sens, artificiel (non significatif) et invariant, destiné à être l’attribut servant pour la clé primaire de la table Entreprise, propagé en conséquence dans les autres tables, en lieu et place de l’attribut NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus que la seule table Entreprise, les utilisateurs ayant bien évidemment toujours accès au contenu de l’attribut NoSiren, servant pour une clé alternative (et n’ayant donc pas perdu sa propriété d’unicité).
Même si on admet que la structure du numéro SIREN a peu de chances d’évoluer en tant que tel, les corrections apportées avaient de quoi ficher la zoubia dans le système.

En ce qui vous concerne, suite à tout ce qui précède, si les valeurs prises par l’attribut CodeSociete peuvent être remplacées parce que l’utilisateur peut avoir besoin d’agir ainsi, alors il faudra définir un attribut supplémentaire (appelons-le IdSociete), non porteur de sens et invariant, qui servira pour la clé primaire de la table SOCIETE, tandis que l’attribut CodeSociete fera l’objet d’une clé alternative pour que soit respectée la règle d’unicité des clés.

Diagramme conceptuel :



(ai) signifie que CodeSociete est identifiant alternatif.


Diagramme logique dérivé :


Et si d’aventure il fallait aussi prendre en compte le numéro Siren des sociétés :


La table SOCIETE aurait pour clé primaire {IdSociete} et elle aurait pour clés alternatives {CodeSociete} et {NoSiren}.

N.B. Pour des raisons historiques, une table SQL ne peut avoir qu’une seule clé qui puisse être qualifiée de primaire. Ainsi, une seule des 3 clés de la table SOCIETE serait primaire, les autres étant alors alternatives, mais cela n’est absolument pas gênant.[/QUOTE]


--------------------- fin de citation ---------------------------------

=>

En ce qui vous concerne, reste à mettre en oeuvre une clé primaire pertinente et ravaler SIRET au rang de clé alternative.

[fsmrel]

La théorie permet que des clés étrangères fassent référence à des clés alternatives aussi bien qu’à des clés primaires.

Il faut faire référence à la clé primaire de la table, pas à une clé alternative !

C’est bien ce qu’a fait djezair31. Il est préférable de dire : « Éviter de bâtir des clés de référence (disons primaires) à partir de données sujettes à être modifiées ».

[CinePhil]

Je viens de mettre à jour mon code

Peut-être ai-je mal vu ou la mémoire qui flanche à l'approche du demi-siècle mais il me semble que dans la première version postée figurait un identifiant entier avant la colonne alors appelée "sirep" et que celle-ci était la clé primaire, d'où ma remarque.

Mais j'avais aussi en tête le fait de ne pas utiliser le SIRET comme clé primaire ! Je l'aurais dit en beaucoup moins complet et documenté que toi François !

Comme dit dans mon premier message, il y aurais encore bien des choses à dire sur le modèle physique proposé, même si certains points ont été corrigés (moteur InnoDB notamment).

Dans son dernier message, djezair31 évoque une clé étrangère alors qu'aucune ne figure dans son code SQL, même modifié. Cela fait partie des points qui seront à mettre en oeuvre mais la charrue tire toujours les boeufs dans cette affaire car nous n'en savons pas plus sur les règles de gestion qui doivent être prise en compte dans l'élaboration d'un MCD alors que nous avons là directement le script de création de la BDD.

[fsmrel]

@Philippe,

Je suis apparemment tombé sur une version corrigée par djezair31 de son 1er message, d’où un léger décalage de ma part dans l’interprétation des échanges.

Cela dit, don’t Worry ! Ma remarque portait plus sur la forme que sur le fond, à propos duquel nous savons bien que nous sommes en phase, mais de son côté le lecteur débutant pourrait se poser des questions sur le sens de ce que nous écrivons...

Il est évident que la moindre des choses de la part de djezair31 et plus généralement de l’ensemble des forumeurs est de mettre la charrue derrière les boeufs et pas l'inverse, fournir les énoncés des règles de gestion du système qu’ils échafaudent, impérativement doublés d’un MCD pour lever les ambiguïtés inhérentes à la langue et nous permettre de nous focaliser à notre tour sur les points délicats de leurs modèles.

Keep up the good job!


@ djezair31,

Pour une approche pertinente de ce qu’on appelle une clé dans le contexte des base de données relationnelles, voyez le paragraphe 3.2 « L'approche analytique, ses outils » de l’article Bases de données relationnelles et normalisation.