Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre habitué
    Restituer un tableau (historique) et une ligne (situation actuelle), avec la même table ?
    Bonjour,

    Ca fait longtemps !
    Peut-on gérer, simplement et efficacement,
    dans une seule table, situation historique et situation actuelle, sans tomber dans une usine à gaz ?

    Voici mon cahier des charges :
    Je souhaite gérer des affectations historiques de personnels, sur des postes de travail.

    Je veux non seulement, interroger en lecture le parcours historique complet :
    • historique_affectation

    Je veux également interroger séparément, en lecture et en écriture, une ligne particulière de l'affectation :
    • affectation_suivante
    • affectation_actuelle
    • avant-dernière affectation






    Mon historique d'affectation est initialement géré, dans la table tm_affectation, composée des 5 colonnes suivantes :
    tm_affectation (RefPersonnel,RefPoste,DateDebAffectation,DateFinAffectation,ObsAffectation)

    RefPersonnel, clef primaire et clef étrangère de la table tr_personnel
    RefPoste, clef primaire, et clef étrangère de la table tr_poste
    La clef primaire se compose de 2 colonnes : RefPersonnel,RefPoste

    Je souhaite gérer des notations historiques de personnels,
    avec une table, composée des 4 colonnes suivantes :
    tm_notation (annee_notation,RefPersonnel,note,appréciation)
    RefPersonnel, partie de clef primaire et clef étrangère de la table tr_personnel
    annee, identification relative, partie de clef primaire, indiquant une année de notation donnée.
    La clef primaire se compose de 2 colonnes : RefPersonnel,annee

    MA QUESTION EST LA SUIVANTE :
    Pour les 2 tables structurées comme ci-dessus, une vue peut être construite,
    afin de déterminer l'affectation actuelle, ou la notation actuelle.

    Evoquons, dans un premier temps, le traitement des affectations :
    Voici la requête, me permettant d'obtenir en lecture et écriture, la vue dernière_affectation, à partir des 5 colonnes :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    select RefPersonnel,RefPoste,DateDebAffectation,DateFinAffectation,ObsAffectation
    from tm_affectation
    where RefPersonnel & DateDebAffectation As LastAff in
    (
    select RefPersonnel & Max(DateDebAffectation) As LastAff from tm_affectation
    group by RefPersonnel
    )
    Comment faire mieux, étant donné qu'ici, le filtre where porte sur deux colonnes concaténées,
    dont une colonne calculée : max().
    Même si un index était posé dans les colonnes RefPersonnel et DateDebAffectation,
    notre clause where ne s'appuierait jamais sur l'index. La requête n'est pas du tout optimisée.

    Même punition pour la table des notations, pour laquelle on obtient les notations pour
    une année donnée, en concaténant les colonnes RefPersonnel et annee_notation.

    '========================
    SOLUTION ENVISAGEE :
    ajout d'une 6ème colonne 'LignAff', dont les valeurs sont réinitialisées et mises à jour, par déclencheur et procédure stockée, à chaque ajout ou suppression d'affectation.
    tm_affectation (RefPersonnel,RefPoste,DateDebAffectation,DateFinAffectation,ObsAffectation,LignAff)

    Est-il habile, de préférer gérer les tables pour lesquelles on veut disposer :
    soit d'un tableau historique, soit une ligne d'affectation précise,
    par la mise à jour automatique, lors de la saisie, du contenu d'une dernière colonne 'LignAff', de type numérique signée tinyint, indexée :
    1:= affectation à venir la plus proche
    0:= affectation actuelle
    -1:= avant dernière affectation
    -2:= antépénultième affectation
    ... etc...

    La colonne serait renseignée par calcul (déclencheur et procédure stockée),
    à chaque ajout ou suppression d'une ligne de carrière. La carrière complète d'un personnel serait renumérotée, après chaque ajout ou suppression d'une ligne de sa carrière.
    Cette écriture, automatiquement déclenchée lors de la saisie, permettrait, à contrario, d'optimiser à l'extrême, l'affichage d'une ligne spécifique d'affectation du personnel,
    par création de vues, permettant lecture et écriture d'une ligne spécifique d'affectation:
    • view_affectation_actuelle
    • view_affectation_precedente,
    • view_affectation_suivante



    Voici la requête, me permettant d'obtenir en lecture et écriture, la vue affectation_actuelle, à partir de 6 colonnes :
    On remarque le rôle important de la colonne indexée 'LignAff', dans le filtre where :
    affectation courante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select RefPersonnel,RefPoste,DateDebAffectation,DateFinAffectation,ObsAffectation
    from tm_affectation where lignaff = 0
    Voici la requête, me permettant d'obtenir en lecture et écriture, la vue avantderniere_affectation, à partir de 6 colonnes :
    On remarque le rôle important de la colonne indexée 'LignAff', dans le filtre where :
    avant dernière affectation :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    select RefPersonnel,RefPoste,DateDebAffectation,DateFinAffectation,ObsAffectation
    from tm_affectation where lignaff = -1

    A votre avis, faut-il, une ou plusieurs tables, pour gérer situation historique et situation actuelle ? Quelle est votre meilleure pratique ?

  2. #2
    Membre expérimenté
    Bonjour,

    Vu le SQL montré, ce n'est pas de l'Oracle. Peux-tu préciser le SGBD utilisé ?

    Plus généralement, si tu veux historiser, pourquoi supprimer des données ? (affectations ou notations)
    Il vaudrait mieux annuler (colonne tem_suppression ? tem_actif ? à toi de voir)
    Et pour l'ordre, il pourrait être préférable de numéroter chaque affectation pour chaque agent. Pour avoir l'affectation en cours, il suffit de chercher avec les dates DateDebAffectation,DateFinAffectation. Le reste peut dépendre du SGBD. En SQL Oracle, un RANK() OVER() serait pas mal.



  3. #3
    Membre habitué
    Merci Cincinnatus,

    Citation Envoyé par Cincinnatus

    Vu le SQL montré, ce n'est pas de l'Oracle. Peux-tu préciser le SGBD utilisé ?
    Effectivement, c'est SQL sous access.

    Citation Envoyé par Cincinnatus

    pourquoi supprimer des données ? (affectations ou notations)
    Il vaudrait mieux annuler (colonne tem_suppression ? tem_actif ? à toi de voir)
    Ok pour la colonne tem_suppression, indiquant le statut de l'enregistrement.
    Je n'ai pas compris le rôle de tem_actif.

    Citation Envoyé par Cincinnatus

    pour l'ordre, il pourrait être préférable de numéroter chaque affectation pour chaque agent.
    Est-ce bien le principe d'une identification relative,affectation_rownum ?

    Citation Envoyé par Cincinnatus
    RANK() OVER() serait pas mal
    Je dois creuser la question, ces fonctions sont une découverte pour moi.
    Un exemple, peut-être ?

    Merci beaucoup !!!

  4. #4
    Membre expérimenté
    Citation Envoyé par martinbrait Voir le message

    Je n'ai pas compris le rôle de tem_actif.
    C'est l'opposé de tem_suppression. Vision positive (actif) ou negative (suppression) au choix


    Citation Envoyé par martinbrait Voir le message

    Est-ce bien le principe d'une identification relative,affectation_rownum ?
    Je verrais ça avec une clé candidate ou primaire (id_agent, num_affectation) par exemple, mais là encore, les noms sont au choix du développeur. Mais le num_affectation étant lié à l'agent.


    Citation Envoyé par martinbrait Voir le message

    Effectivement, c'est SQL sous access.
    Là, comme je n'ai pas touché à Access depuis un moment, il faudra demander dans le sous-forum dédié :
    https://www.developpez.net/forums/f6.../requetes-sql/

###raw>template_hook.ano_emploi###