Discussion :

[cléclé64]

Bonjour,
Je suis élève-ingénieure, actuellement en mission de terrain en entreprise.
Je dois faire le travail suivant: mettre en place un système de gestion de stocks, appro, commandes, factures pour une petite PME. Je me suis donc tournée vers Access, j'ai commencé à faire des tables, j'aimerais avoir quelques renseignements sur l'arborescence (si ceci est possible à faire sur Access).
En fait je suis dans une société qui fabrique des petites éoliennes, et selon les éoliennes, certains composants sont communs à deux types d'éolienne, et de plus il y a une arborescence entre les composants, par exemple:
On a une nomenclature d'un composant d'une éolienne et ce composant assemblé qui est donc composé d'autres composants qui peuvent être oui ou non des composants assemblés.
Je voulais donc savoir d'une part si il était possible de faire sur Access une telle manipulation, et d'autre part, comment faut-il s'y prendre au niveau des stocks pour gérer ceci.

Je comprends que ceci n'est pas forcément très clair donc si vous voulez plus de précision, n'hésitez pas à me le demander.

merci d'avance pour ceux qui pourront m'aider

[marot_r]

Ok, autant te prévenir tout de suite, la gestion de composer dans une base de données relationnelle est une plaie. Elles ne sont simplement pas faite pour cela.

Mais on peut y arrivre quand même.

Le truc est d'avoir la structure suivante :

Article

ClefArticle

Composant

ClefComposant

ArticleComposant cette table te permet de dire de quoi ton article est composé.

ClefArticle
ClefComposant

Si tu n'as pas de Article composé d'article cela demeure simple, sinon il faut gérer tous les Composants comme des articles et faire des articles avec des article et avoir certain article qui ne se décompose pas. C'est un peu comme un arbre génalogique ou chaque personne peut être un pêre ou un mêre tout en étant une personne.

Cela donne :

Article
ClefArticle

ArticleArticle
ArticleComposé
ArticleComposant

Avec une double relation sur Article pour ArticleArticle.

Si tu peux trouver dans le commerce un logiciel qui fait cela je t'invite fortement à considérer l'option.

A+

[f-leb]

... la gestion de composer dans une base de données relationnelle est une plaie. Elles ne sont simplement pas faite pour cela.

comment ça pas faites pour ça ?

Il me semble que les problèmes de nomenclatures dans les SGBDR sont assez classiques, non ?

Je mets un lien du forum SQL sur les nomenclatures pour donner une idée:
http://www.developpez.net/forums/d91...s/#post5205169

Pour le traitement récursif sous Access, il faudra passer par du code VBA.

Problème classique mais pas simple lorsqu'on débute

[marot_r]

Il me semble que les problèmes de nomenclatures dans les SGBDR sont assez classiques, non ?

Bonjour F-Leb, tu as parfaitement raison sur ce point cependant tu auras sans doute noté que la gestion d'arborescence en SQL fait appel soit à du code VBA soit à des extensions propriétaires.

Tu ne peux pas écrire facilement : select * from article et obtenir une liste des articles et de leur composantes ou gérer le décompte automatique de pièces quand tu utilises des sous-ensembles composés. Ça marche beaucoup mieux avec des bases de données Objets mais c'est encore peu répandu.

A+

[cléclé64]

Merci pour vos réponses très rapides. Vu le peu de temps que j'ai pour faire ceci, et étant une personne des moins futés pour manipuler un tel logiciel, je pense que je vais jeter un coup d'oeil du côté des GPAO.
Encore merci

[fsmrel]

Bonsoir,

Bonjour F-Leb, tu as parfaitement raison sur ce point cependant tu auras sans doute noté que la gestion d'arborescence en SQL fait appel soit à du code VBA soit à des extensions propriétaires.

Ce que vous écrivez vaut pour ACCESS ou MySQL parce que ces SGBD ne permettent pas de programmer des requêtes récursives. Hélas pour cléclé64...

Sinon, la norme SQL fournit la syntaxe générale des requêtes récursives. En première approche, voyez l’article de SQLpro à ce sujet. Les requêtes développées par l’auteur et qui servent à illustrer l’utilisation des recherches récursives ne correspondent aucunement à des extensions propriétaires. L’auteur utilise SQL Server 2003 pour illustrer son propos, mais les requêtes qu’il propose sont portables dans DB2, Oracle, Sybase iAnywhere PostgreSQL ou tout autre SGBD utilisant les « WITH queries » de la norme, en particulier les CTE (common tables expressions) hébergeant l’opérateur UNION ALL pour effectuer les traitements récursifs (ou itératifs, comme vous voulez).

Si les WITH queries sont des extensions propriétaires, alors on est dans le domaine de la copropriété...

Tu ne peux pas écrire facilement : select * from article et obtenir une liste des articles et de leur composantes ou gérer le décompte automatique de pièces quand tu utilises des sous-ensembles composés.

Je ne vois pas ce qu’il y a de sorcier avec SQL. Pour parler des pièces, je reprends un des exemples proposés par Donald Chamberlin (père de SQL) et qui figure dans son article Recursion in SQL: Tips and Techniques. Database Programming and Design, paru en mai 1996 dans Database Programming and Design. Cet exemple concerne plus précisément les pièces qui entrent dans la composition des ailes d’avion.

Table des Pièces :



(J’ai utilisé le type Texte pour la clé primaire car c’est plus parlant, mais en principe on utilise le type Entier).

Table des composants et des composés :



Sous forme de graphe :



Le modèle logique correspondant est celui-ci :


PIECE a pour clé primaire {PieceId}
COMPOSITION a pour clé primaire {ComposantId, ComposeId}.
ComposantId fait référence à PieceId (clé étrangère), même chose concernant ComposeId.

Si par exemple on veut savoir combien il faut de rivets pour une aile :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
WITH COMPOSANT_VUE (Composant, Quantité) AS
   ((SELECT Composant, Quantité
     FROM   COMPOSITION
     WHERE  Composé = 'aile')
UNION ALL
   (SELECT y.Composant, x.Quantité * y.Quantité
    FROM   COMPOSANT_VUE AS x JOIN COMPOSITION AS y
              ON x.Composant = y.Composé))
SELECT SUM(Quantité) AS Quantité
FROM   COMPOSANT_VUE 
WHERE  Composant = 'rivet' ;

Réponse : 183.

Je ne vois pas quelle difficulté il y a pour décompter les pièces.

la gestion de composer dans une base de données relationnelle est une plaie. Elles ne sont simplement pas faite pour cela.

Au vu de ce qui précède, voilà un argument que l'on peut qualifier de ignoratio elenchi.

Ça marche beaucoup mieux avec des bases de données Objets mais c'est encore peu répandu.

Ça marche déjà pas mal avec les SGBDR mentionnés plus haut, même s'ils ne méritent sans doute pas le label "SGBD déductifs" car trop généralistes. Mais je ne demande qu’à être convaincu qu'avec les SGBDOO ça marche beaucoup mieux. Merci de nous en faire la démonstration.

[f-leb]

Bonjour à tous,

mais même sans WITH Queries, je ne vais pas céder aussi facilement moi

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
25
26
27
28
29
30
31
32
33
Public Sub compter(idpiece As String, idPieceACompter As String, ByRef QtteTotal As Integer, Optional nb As Integer = 1)
  '
  Dim qdf As DAO.QueryDef
  Dim rcs As DAO.Recordset
  '
   QtteTotal = QtteTotal + _
        nb * Nz(DLookup("[Quantite]", "COMPOSITION", "[ComposantId]='" & idpiece & "' AND [ComposeId]='" & idPieceACompter & "'"), 0)
 
  ' ici j'utilise une requête enregistrée et paramétrée, nommée "RSelect" dont voici le SQL:
  ' PARAMETERS [Référence pièce ?] Text ( 12 );
  ' SELECT COMPOSITION.ComposeId, COMPOSITION.Quantite
  ' FROM COMPOSITION
  ' WHERE COMPOSITION.ComposantId = [Référence pièce ?] ;
  '
  Set qdf = CurrentDb.QueryDefs("RSelect")
      qdf.Parameters("[Référence pièce ?]") = idpiece
 
  Set rcs = qdf.OpenRecordset
 
  If Not rcs.EOF Then
    rcs.MoveFirst
        Do While Not rcs.EOF
            If rcs.Fields(0) <> idPieceACompter Then
              Call compter(rcs.Fields(0), idPieceACompter, QtteTotal, rcs.Fields(1)) ' <==appel récursif
            End If
        rcs.MoveNext
        Loop
  End If
 
  Set qdf = Nothing
  Set rcs = Nothing
 
End Sub

Et la procédure principale à appeler :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Public Sub appel()
Dim qt As Integer
'
qt = 0
    Call compter("aile", "rivet", qt)         ' réponse: 183
    ' Call compter("aileron", "rivet", qt)    ' réponse: 13
    ' Call compter("train", "rivet", qt)      ' réponse: 20
    ' Call compter("aile", "charniere", qt)   ' réponse: 5
 
MsgBox (qt)
'
End Sub

A codeur vaillant rien d'impossible, non mais…

[fsmrel]

Bonjour Fabien Talon,

Juste une petite remarque : le code SQL « WITH » est portable en l’état, par exemple depuis SQL Server vers mon SGBD favori, DB2 for z/OS, mais sur mon mainframe, je ne saurai pas exécuter votre jolie procédure sans la réécrire dans un langage connu du système d’exploitation (z/OS) maqué avec DB2...

[marot_r]

Merci fsmrel, pour le cours

Même si

Code :

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

1
2
3
4
5
6
7
8
9
10
11
WITH COMPOSANT_VUE (Composant, Quantité) AS
   ((SELECT Composant, Quantité
     FROM   COMPOSITION
     WHERE  Composé = 'aile')
UNION ALL
   (SELECT y.Composant, x.Quantité * y.Quantité
    FROM   COMPOSANT_VUE AS x JOIN COMPOSITION AS y
              ON x.Composant = y.Composé))
SELECT SUM(Quantité) AS Quantité
FROM   COMPOSANT_VUE 
WHERE  Composant = 'rivet' ;

n'est pas vraiment 'simple' selon moi mais je suis un peu alergique au SQL.

En passant comme mon latin est un peu vieux vue que ça doit faire plus de 25 ans que je n'en ai pas fait je vais prendre ignoratio elenchi comme un compliment.

A+

[gpinson]

Bonjour,
Heu comment fait-on fonctionner cette instruction WITH ?
MS SQL2000 ou Access ne reconnait pas cette instruction.

Serveur : Msg 156, Niveau 15, État 1, Ligne 1
Syntaxe incorrecte vers le mot clé 'WITH'

Ou alors il y a quelque chose que je n'ai pas compris...
Une idée ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
WITH COMPOSANT_VUE (Composant, Quantité) AS
   ((SELECT Composant, Quantité
     FROM   COMPOSITION
     WHERE  Composé = 'aile')
UNION ALL
   (SELECT y.Composant, x.Quantité * y.Quantité
    FROM   COMPOSANT_VUE AS x JOIN COMPOSITION AS y
              ON x.Composant = y.Composé))
SELECT SUM(Quantité) AS Quantité
FROM   COMPOSANT_VUE 
WHERE  Composant = 'rivet' ;

Merci
Geo

[f-leb]

bonjour gpinson,

Ce que vous écrivez vaut pour ACCESS ou MySQL parce que ces SGBD ne permettent pas de programmer des requêtes récursives.

Adieu les WITH Queries sous Access

[fsmrel]

Bonsoir,

En passant comme mon latin est un peu vieux vue que ça doit faire plus de 25 ans que je n'en ai pas fait je vais prendre ignoratio elenchi comme un compliment.

Confidence pour confidence, pour ma part ça fait plus de 50 ans, mais je n’ai pas tout oublié. Disons donc que je constate que vous croyez à cette légende qui avait cours jadis, selon laquelle le Modèle Relationnel de Données aurait été incapable de traiter de la récursivité. Voici toutefois ce qu’écrivait Ted Codd (père du Modèle Relationnel), dans son ouvrage de 1990, The Relational Model for Database management: version 2, pages 140 à 143 (et ça n’est pas du latin) :



Etc. Notez que Codd cite ce qu’il écrivit en 1979 dans Extending the Database Relational Model to Capture More Meaning : je vous invite à le lire. Pas d’allergie en vue, SQL en est absent, langage qui donnait aussi des boutons à Codd.

Cela dit, de 1979 à 2011, ça fait quand même un bail que le Modèle relationnel traite de la récursivité dans le contexte des bases de données...

Pour plus d’information sur la fermeture transitive (transitive closure), je vous invite à vous reporter à la discussion ici (mais à part la brève mention qui est faite de l'opérateur relationnel TCLOSE, il y a du SQL, désolé).

[marot_r]

Merci fsmrel pour cette précision.

A+