Discussion :

[martinbrait]

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 ?

[Cincinnatus]

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.

[martinbrait]

Merci 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.

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.

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 ?

RANK() OVER() serait pas mal

Je dois creuser la question, ces fonctions sont une découverte pour moi.
Un exemple, peut-être ?

Merci beaucoup !!!

[Cincinnatus]

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

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.

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/