Discussion :

[DavidleVrai]

Bonjour,

Je souhaite ajouter des tables à ma base pour faire des statistiques sur le nombre d'objets déduits ou stockés par année/mois/jour et par catégorie et sous-catégorie.
J'ai des catégories d'objets, qui peuvent posséder une sous-catégorie. Les sous-catégorie ne peuvent appartenir qu'à une catégorie.
ex:
- catégorie : outils
-> sous-catégorie : jardinage
-> sous-catégorie : maçonnerie

- catégorie : visserie (pas de sous catégorie).

ex de comptage réalisé :
40000 outils stocké et 100 détruits l'année dernière.

Sachant que je ne souhaite pas utiliser d'héritage, voici un MCD qui répond au besoin :


Le code DDL qui permet de créer les tables :

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
CREATE TABLE CATEGORIE
(
   id int,
   libelle VARCHAR(50) NOT NULL,
   CONSTRAINT CATEGORIE_PK PRIMARY KEY(id)
);
 
CREATE TABLE SOUS_CATEGORIE
(
   id INT,
   idCategorie INT NOT NULL,
   libelle VARCHAR(50) NOT NULL,
   CONSTRAINT SOUS_CATEGORIE_PK PRIMARY KEY(id),
   CONSTRAINT SOUS_CATEGORIE_CATEGORIE_FK FOREIGN KEY(idCategorie) 
       REFERENCES CATEGORIE(id)
);
 
CREATE TABLE COMPTAGE
(
   id INT,
   idCategorie,
   idSousCategorie,
   dateComptage DATE NOT NULL,
   nbDestructions INT NOT NULL,
   nbStockages INT NOT NULL,
   CONSTRAINT COMPTAGE_PK PRIMARY KEY(id),
   CONSTRAINT COMPTAGE_CATEGORIE_FK FOREIGN KEY(idCategorie) 
       REFERENCES CATEGORIE(id),
   CONSTRAINT COMPTAGE_SOUS_CATEGORIE_FK FOREIGN KEY(idSousCategorie) 
       REFERENCES SOUS_CATEGORIE(id)
);

Ma problématique est : pour faire les insertions dans la table COMPTAGE en respectant le modèle et notamment la contrainte d'exclusion, comment dois-je procéder ?

Par exemple, avec ces entrées en tables :

Code sql :

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

1
2
3
4
5
INSERT INTO CATEGORIE VALUES (1, 'outillage');
INSERT INTO CATEGORIE VALUES (2, 'visserie');
 
INSERT INTO SOUS_CATEGORIE VALUES (1, 1, 'jardinage');
INSERT INTO SOUS_CATEGORIE VALUES (2, 1, 'maconnerie');

Est-ce que je peux faire les insertions en mettant à null la clés étrangères idSousCategorie quand il n'y a pas de sous catégorie et renseigner idCategorie et idSousCategorie quand la sous catégorie existe ?

Code sql :

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

1
2
3
INSERT INTO COMPTAGE VALUES (1, 1, 1, '20200612 01:23:09 PM', 10, 100);
INSERT INTO COMPTAGE VALUES (2, 1, 2, '20200612 01:30:00 PM', 0, 50);
INSERT INTO COMPTAGE VALUES (3, 2, null, '20200612 01:40:00 PM', 1000, 10000);

Ou est-ce que je dois faire les insertions en mettant à null la clé étrangère idSousCategorie quand la sous catégorie n'existe pas et mettre à null idCategorie quand la sous catégorie existe ?

Code sql :

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

1
2
3
INSERT INTO COMPTAGE VALUES (1, null, 1, '20200612 01:23:09 PM', 10, 100);
INSERT INTO COMPTAGE VALUES (2, null, 2, '20200612 01:30:00 PM', 0, 50);
INSERT INTO COMPTAGE VALUES (3, 2, null, '20200612 01:40:00 PM', 1000, 10000);

Merci d'avance pour votre aide et à bientôt.

[SQLpro]

Votre modèle n'est donc pas bon... Une sous catégorie est une catégorie liée à une autre catégorie. Supprimez l'entité sous catégorie et faites une association réflexive.

A +

[DavidleVrai]

Une association réflexive n'impliquerait-elle pas que les sous-catégories fassent parties des catégories ? Hors dans mon cas les sous-catégories sont des notions différentes des catégories.

[SQLpro]

Et alors ? Ce sont les mêmes attributs !

Quelle différence faites vous entre une boite à l'intérieur d'une boite placée dans une boite qui est dans une boîte elle même enfermée dans une boite ? Ce sont toutes des boites ! Seule la manière de les articuler par l'association fera la différence en ce sens qu'une catégorie "première" n'est liée à aucune catégorie, tandis qu'une sous catégorie est forcément liée à une autre catégorie !

A +

[aieeeuuuuu]

bonjour,

Deux questions :
1/ Vous dites que les sous-catégories et les catégories sont des notions différentes... qu'est-ce qui les différencie ?
2/ vous dites ne pas vouloir mettre en place l'héritage... pourquoi ?

[escartefigue]

Bonjour,

Pour information, cette question est la suite d'un sujet posté ici :
https://www.developpez.net/forums/d2.../#post11582762

[DavidleVrai]

Effectivement, cette question est la suite d'un sujet que j'ai lancé pour modéliser ces tables.
Suite aux modèles qu'on m'a proposé, j'avais des questions plus de l'ordre du SQL que de la modélisation, d'où ce nouveau sujet.
Et comme je dois faire avec ce qui existe déjà, sur le sujet d'origine je m'oriente plus vers une autre solution mais ça c'est finalement un autre sujet : https://www.developpez.net/forums/d2.../#post11582762.

Finalement cet question et plus pour avoir une réponse d'ordre général sur comment fait-on pour appliquer en SQL ce genre de contrainte d'exclusion.

bonjour,

Deux questions :
1/ Vous dites que les sous-catégories et les catégories sont des notions différentes... qu'est-ce qui les différencie ?
2/ vous dites ne pas vouloir mettre en place l'héritage... pourquoi ?

Dans les faits, les tables domaine et sous-domaine sont bien des listes de code/libellé, mais la table sous-domaine ne porte pas un nom approprié. Un meilleurs nom serait plus "Caractéristique". Mais il est clair, de pars leurs applications métier, que les éléments de "sous-domaine" ne pourront jamais appartenir à "domaine" et vice versa.
Je ne veux pas mettre en place la notion d'héritage car le mapping avec l'application métier sera fait avec java/hibernate et que pour l'avoir déjà fait, je sais que ça ne se fait pas naturellement. De mémoire il faut ajouter un champ en base destiné à identifier les tables filles. Et ce n'est pas parce que je suis réfractaire à faire des héritages en BdD, je l'ai déjà fait et je le referai .

[DavidleVrai]

Et alors ? Ce sont les mêmes attributs !

Quelle différence faites vous entre une boite à l'intérieur d'une boite placée dans une boite qui est dans une boîte elle même enfermée dans une boite ? Ce sont toutes des boites ! Seule la manière de les articuler par l'association fera la différence en ce sens qu'une catégorie "première" n'est liée à aucune catégorie, tandis qu'une sous catégorie est forcément liée à une autre catégorie !

A +

(Je n'aime pas vouvoyer sur les forums, mais il semblerait que ce soit la tendance aujourd'hui. C'était mieux avant ) Excusez-moi je n'avais pas vu votre réponse. Ok je comprends, ce sont les contraintes qui permettent d'avoir les catégories et sous-catégories au sens pratique.
Mais alors toujours cette même question : comment faire ça en SQL ? Je vois bien une manière de le faire mais c'est encore avec des clés étrangères null. Les catégories "premières" ont null comme clé étrangère vers catégorie. Et pour les catégories "secondes" tout va bien. Et donc pour savoir si une entrée est une catégorie "premières", on test si sa référence à catégorie est égale à null.
J'avoue que j'ai un peu l'impression de mélanger les choux et les carottes. Mais certainement juste par habitude.

[SQLpro]

Vous êtes en train de pourrir votre base parce que vous voulez privilégier Hibernate qui est une grosse merde.
Libre à vous... mais non seulement vous ne résoudrez pas le problème, mais vous en créerez de nouveaux et votre base sera de plus en plus pourrie. Dans une application ce sont les données qui compte. pas le choix du style du code !

J'ai écrit il y a longtemps un article à charge contre les ORM et notamment contre hibernate et je constate que les développeurs sont toujours aussi stupide !
http://sqlpro.developpez.com/cours/b...s-epaisses.pdf

A +

[DavidleVrai]

Dans une application les données sont importantes, c'est vrai, mais pas seulement. La structure du code est primordiale pour que celui-ci puisse durer et évoluer efficacement, et pour ça jpa facilite une partie du travail.
Par ailleurs, je fais une erreur en disant que Hibernate est obligatoire parce que je peux aussi bien partir sur du EclipseLink, OpenJpa ou du TopLink, ce qui ne changera pas grand chose si je vous suis bien. Mais je vais lire votre article avec attention, parce qu'il est vrai que les ORM produisent parfois des requêtes ahurissantes et ont tendance à interférer avec la modélisation des bases.

Et il y a des développeurs qui ne sont pas stupides mais malheureusement ils doivent faire avec la majeure partie d'entre eux qui ne savent pas structurer une application (de la base jusqu'à l'interface). Je ne saurai pas normaliser une base en NF5 ou je ne sais combien mais j'ai quand même des notions. Si vous voyez ce qu'on doit se traîner parfois, vous seriez heureux que je pose les questions que je pose.

Mais en attendant tout ça ne répond pas à ma question qui est de savoir comment gérer en SQL ce genre de modèle et j'ai l'impression que je ne l'aurai jamais.

[StringBuilder]

Bonsoir,

Voici une solution qui permet de conserver votre schéma, en référençant les clés alternatives.

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
 
create table cat
(
	id int identity not null primary key,
	code char(3) not null unique,
	name char(20) not null
);
 
create table scat
(
	id int identity not null primary key,
	cat_code char(3) not null references cat(code),
	scat_code char(3) not null,
	unique (cat_code, scat_code),
	name char(20) not null
);
 
create table article
(
	id int identity not null primary key,
	cat_code char(3) not null references cat(code),
	scat_code char(3) null,
	constraint fk_scat foreign key (cat_code, scat_code) references scat(cat_code, scat_code),
	name char(30) not null
);
 
insert into cat (code, name) values ('001', 'Catégorie 1'), ('002', 'Catégorie 2'), ('003', 'Catégorie 3');
insert into scat (cat_code, scat_code, name) values ('001', '011', 'SCatégorie 1.1'), ('001', '012', 'SCatégorie 1.2'), ('001', '013', 'SCatégorie 1.3'), 
                                                    ('002', '021', 'SCatégorie 2.1'), ('002', '022', 'SCatégorie 2.2'), ('002', '023', 'SCatégorie 2.3'), 
													('003', '031', 'SCatégorie 3.1'), ('003', '032', 'SCatégorie 3.2'), ('003', '033', 'SCatégorie 3.3'),
													('003', '001', 'SCatégorie 3.1bis')
 
insert into article (cat_code, scat_code, name) values ('001', null, 'Article 1 cat 1 sans scat'), ('002', null, 'Article 2 cat 2 sans scat'); -- OK
insert into article (cat_code, scat_code, name) values ('001', '012', 'Article 3 cat 1 scat 1.2'), ('002', '023', 'Article 4 cat 2 scat 3.2'); -- OK
insert into article (cat_code, scat_code, name) values ('003', '012', 'Article 5 cat 3 scat 1.2'); -- KO!!!
insert into article (cat_code, scat_code, name) values ('003', '001', 'Article 6 cat 3 scat 3.1bis'); -- OK

Notez que cela permet accessoirement d'avoir scat_code non unique (scat "3.1bis").
Si cela ne vous convient pas, il suffit de rajouter une unicité sur scat_code.

[SQLpro]

Dans une application les données sont importantes, c'est vrai, mais pas seulement. La structure du code est primordiale pour que celui-ci puisse durer et évoluer efficacement, et pour ça jpa facilite une partie du travail.

Vous êtes à côté de la plaque.... Votre nom, votre date de naissance, votre sexe, sera le même pendant plus de 70 ans... Entre temps il y aura eu, le cobol, le pascal, le C, le java, le PHP, le python et j'en passe. On change de paradigme de programmation tous les 8 ans.... mais les données sont immuables !

tant que vous partirez dans la mauvaise direction,vous ferez le de m...

A +

[StringBuilder]

N'ayant pas lu le topic duquel découle celui-ci, je vais peut-être me faire l'avocat du diable (ou le sophiste, je sais pas trop).

Ici, il est question de catégorie et de sous-catégorie, des données de prime abord composées des mêmes attributs.

Cependant, il s'agit probablement d'une vision très "macro" et dans la réalité ces données hiérarchiques peuvent être totalement différentes.

On pourrait par exemple avoir des marques de voiture et des modèles : les attributs de l'une et l'autre des entités sont très différents, et il me semble peu opportun d'utiliser la même table.

Ca peut aussi être un dépôt et un emplacement dans le dépôt : à nouveau, aucune donnée commune... même pas un libellé !

Bref, je pense qu'il n'est pas forcément justifié de vouloir absolument gérer ces deux niveaux hiérarchiques avec une même entité.


Sinon, j'oubliais une solution toute simple : créer pour chaque catégorie une sous-catégorie "autres" : ainsi tous les éléments qui ne sont liés qu'à la catégorie seront liés à la sous-catégorie "autres" correspondante, et ainsi vous n'aurez besoin que de faire la clé étrangère sur la sous-catégorie.

[DavidleVrai]

Bonjour,

Il est facile de dire que je ne veux pas prendre les solutions qu'on me donne, que je pars vers un modèle pourrit, mais il faut arrêter de juger sans avoir tous les éléments. Je ne peux pas faire un post de 50 pages pour expliquer tout en détail, et je ne peux pas non plus tout dire sur le forum. J'ai essayé de donner un cas le plus approchant possible avec les connaissance que j'ai du domaine.

Je ne pars pas de 0, il y a aussi une base avec des tables déjà existantes (et une modélisation très très ... très approximative), je ne peux pas faire table rase.
Et si il s'agit de prendre les solutions données bêtement sans les comprendre, hors de question, c'est le meilleurs moyen de partir vers un modèle inadapté.

Ce qui me tue c'est d'être jugé à la va vite, alors que je ne suis pas de ceux qui vont choisir des solutions pourrit parce que ce qu'on leur propose leur parait "trop compliqué", "bizarre", "pas joli" ou je ne sais encore quels raisonnement qui relève plus du sentiment que d'un raisonnement factuel.
Si j'étais partit de 0 j'aurai sans soucis pris le modèle proposé par fsmrel dans le sujet d'origine ou encore la réflexion proposée par SQLpro mais qui je pense (et peut être que je me trompe) ne correspond pas au cas réel alors que j'ai bien compris qu'elle s'appliquée très bien pour cas approchant que j'ai donné.

Vous êtes à côté de la plaque.... Votre nom, votre date de naissance, votre sexe, sera le même pendant plus de 70 ans... Entre temps il y aura eu, le cobol, le pascal, le C, le java, le PHP, le python et j'en passe. On change de paradigme de programmation tous les 8 ans.... mais les données sont immuables !

Je me suis peut-être mal fait comprendre mais je suis d'accord que les données sont une ressource très critiques et très souvent plus critique que le code.
Mais le design du code est quand même très important. Les paradigmes ne changent pas autant que vous le dites, sinon le livre du "Gang of Four" ne resterai pas une base en matière de design pattern.

J'arrêterai là les débats peut productif avec des personnes qui ne veulent pas ou qui ne peuvent pas prendre de recule.

Voici une solution qui permet de conserver votre schéma, en référençant les clés alternatives.

Merci beaucoup, c'est l'explication que je désespérai d'avoir sur la manière de gérer les clés étrangères pour les exclusions, héritages, ... en SQL.

Cependant, il s'agit probablement d'une vision très "macro" et dans la réalité ces données hiérarchiques peuvent être totalement différentes.

On pourrait par exemple avoir des marques de voiture et des modèles : les attributs de l'une et l'autre des entités sont très différents, et il me semble peu opportun d'utiliser la même table.

Ca peut aussi être un dépôt et un emplacement dans le dépôt : à nouveau, aucune donnée commune... même pas un libellé !

Une nouvelle fois merci pour ta prise de recule sur le sujet et tes réponses qui font avancer le schmilblick.
C'est ça, j'ai donné un cas approchant qui n'était pas des mieux trouvé et en effet dans le cas réel, aucun code et libellé n'est et ne sera en commun.

Sinon, j'oubliais une solution toute simple : créer pour chaque catégorie une sous-catégorie "autres" : ainsi tous les éléments qui ne sont liés qu'à la catégorie seront liés à la sous-catégorie "autres" correspondante, et ainsi vous n'aurez besoin que de faire la clé étrangère sur la sous-catégorie.

C'est la solution vers laquelle je suis partit suite aux conseils de Paprick dans le sujet d'origine et votre réponse est complémentaire, je vais pouvoir mettre tout ça en place avec tous les éléments de réflexion à disposition.

Merci à tous pour votre aide et votre enseignement qui même pour les moins cordiaux à était très instructif.

[aieeeuuuuu]

J'avoue que j'ai un peu l'impression de mélanger les choux et les carottes.

C'est parfois nécessaire, pour faire une bonne potée... ou pour faire un bon modèle de données.
C'est le principe même de l'héritage : les choux et les carottes... ça reste des légumes, qui sont eux même des ingrédients.
On regroupe les points communs dans une même table, et on sépare les spécifiés dans des tables filles.

Il semble que dans votre cas, les catégories et les sous-catégories soient très différentes. mais elles sont quand même au moins un point commun : elles contiennent des produits.

Si c'est leur seul point commun, rien n'interdit de n'avoir qu'une seule colonne, (la clef primaire) dans la table mère.
la table produit référence alors la table mère, et vos table filles gardent les colonnes qu'elles ont aujourd'hui (juste la clef primaire qui deviendra également clef étrangère).