[mysql]tables dans les regles de l'art

Le
Gilles RONSIN
Salut,
je débute dans le monde du SGDB pour le boulot et je suis dans une
impasse sur la structure des tables.
Situation:
-on gère des boitiers qui communiquent 0 à n paramètres (dans l'exemple
j'en met que 2)
-on stocke dans une table historique toutes les communications qui
arrivent
-on dispose dans une table la configuration actuelle

pour l'instant on a :
boitier(_idboit_,num,param1,param2)
historique(_id_,date,idboit,param1,param2,param3,param4)
donc simplissime à chaque connexion :
insert historique (idboit,date) value ('leboitier',NOW())
si on reçoit rien
-> on ne fait rien d'autre
si on reçoit un paramètre concernant la configuration du boitier
-> update historique set param2='val1' (reporté suite à un traitement)
update boitier set param2='val1'
si on reçoit plusieurs paramètres dont l'un est un évenement (param3)
-> update historique set param2='val2',param3='val3'
update boitier set param2='val2'
ainsi on suit l'historique des évenement et des configuration d'un
boitier par
select * from historique where idboit='leboitier'
et on connait l'état du boitier par
select * from boitier where idboit='leboitier'

le problème est que comme le nombre de paramètres est important et le
nombre de boitier va augmenter aussi, la taille de la table historique
devient impressionnant. de plus il y a des redondances. enfin j'ai des
doutes sur la célérité des requètes lorsque la table historique sera
bien remplie (notamment pour faire des statistisques sur tel ou tel
paramètre pour un boitier donné)

J'ai tenté de comprendre la méthode Merise pour placer mes idées, fait
des essais avec differentes combinaisons, mais je n'arrive pas a
trouver une structure cohérente ni les requetes sql qui me satisfasse.
pour l'instant j'en suis au niveau suivant:
boitier(_idboit_,#param1,#param2)
config(_idconf_,param1,param2)
historique(_id_,date,#idboit,#param1,#param2,param3,param4)

à la réception d'un paramètre boitier je fais
insert into historique (idboit,date) values ('leboitier',NOW())
insert into config (param1,param2) values ('val1','val2')
si val1!="" -> update boitier set param1=last_index()
si val2!="" -> update boitier set param2=last_index()
update historique set param1='val1',param2='val2',param3
='val3',param4='val4' where id=(l'index de la ligne précédemment
ajoutée)

pour récupérer mon historique je fais
select h.date,h.idboit,c.param1,c.param2,h.param3,h.param4
from historique as h
join config as c
on h.param1=c.idconf or h.param2=c.idconf
where h.idboit='leboitier'
order by h.date

pour le boitier je n'ai pas encore trouvé de solution satisfaisante.

En gros c'est très lourd et je ne suis même pas certain que c'est plus
efficace. Je ne pense pas faire les bons choix de la structure et
serais ravi d'avoir vos opinions.
Merci

--
Site : http://gilles.ronsin.free.fr
Recherches ciblées http://gilles.ronsin.free.fr/#search
Il est impossible pour un optimiste d'être agréablement surpris.
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Patrick Mevzek
Le #21851671
Le Wed, 08 Aug 2007 16:01:25 +0200, Gilles RONSIN a écrit:
-on gère des boitiers qui communiquent 0 à n paramètres (dans l'exemple
j'en met que 2)
-on stocke dans une table historique toutes les communications qui
arrivent
-on dispose dans une table la configuration actuelle

pour l'instant on a :
boitier(_idboit_,num,param1,param2)
historique(_id_,date,idboit,param1,param2,param3,param4)



Il n'y a pas nécessairement toujours une seule bonne façon de construire
son schéma, même en le gardant normalisé, en particulier quand on veut
tenir compte des performances.
Donc, il peut y avoir plusieurs façons de faire, selon le genre
d'extractions dont vous avez besoin, et selon les méthodes de mises à jour.
Avant de regarder de ce côté là, je pense qu'il y a déjà quelques
problèmes de normalisation à évoquer

1) votre n il est constant au cours du temps ? identique pour tous les
boitiers ?
Si vous répondez non à une de ces questions, alors il y a un problème pour
la table boitier.
Comme je dis dans mon cours « un bon schéma n'a pas besoin de changer au
cours du temps », c'est à dire en particulier qu'il ne doit pas y avoir
besoin d'ajouter des attributs (colonnes) plus tard, parce qu'on se rend
compte qu'on en a besoin.
Alors oui, techniquement, on peut changer le schéma d'une table (ALTER
TABLE ...) mais c'est signe d'un problème de schéma bien souvent.
Dans votre cas de figure, si les boitiers n'ont pas tous le même nombre de
paramètres, vous vous retrouverez avec des trous (NULL)
Si vous avez un paramètre à ajouter, il faut créer un nouvel attribut.
Il y a donc peut-être un effort à faire déjà là.

2) même problématique pour l'autre table.
Sans compter les redondances que vous évoquez vous-même.

3) si les *valeurs* de vos paramètres sont discrètes et peu nombreuses il
peut être intéressant de les stocker dans une table à part et de faire des
jointures. Mais cela dépend vraiment des valeurs (entier ? chaînes de
caractères ? etc.) et de l'usage.

Maintenant pour la modélisation, si j'ai bien compris votre problème (pas
sûr, parce que param3 et param4 je ne sais pas ce que c'est), c'est un cas
classique de table mise à jour dont vous souhaitez conserver les mises à
jour.

Il y a en gros deux pistes, chacune avec des avantages et inconvénients.

A) soit une table d'archivage identique à la table archivée avec un
attribut supplémentaire indiquant quand l'archivage a eu lieu
donc pour vous ca serait en gros (à modifier selon mes dires plus haut)
boitier (id,num,p1,p2)
boitier_archive (id,num,p1,p2,archived_on)

Procédure lors d'une mise à jour :
- INSERT INTO boitier_archive SELECT *,NOW() FROM boitier WHERE id=...
- UPDATE boitier SET ... WHERE id=...

A noter que l'insertion avant le UPDATE peut être faite automatiquement
via une procédure stockée et un déclencheur dans le SGBD.

B) soit on veut éviter encore plus les redondances, et donc on ne va
archiver que ce qui change.
C'est un peu plus compliqué à mettre en oeuvre, on peut éventuellement
toujours s'en sortir avec un déclencheur, mais en général on fait ca du
coté applicatif
On aurait donc quelque chose du style
boitier_archive (id,field_name,field_old_value,field_new_value,archived_on)

Donc si on met à jour p1 et p2 dans boitier,
on aurait deux lignes dans boitier_archive avec field_name='p1' et 'p2'
et les anciennes+nouvelles valeurs correspondantes.

L'option B continue de fonctionner même si boitier change de schéma, alors
que dans l'option A il faudra aussi changer le schéma de boitier_archive
(ce qu'on va typiquement oublier de faire, d'où problèmes après).


Maintenant si vous rassemblez tout cela et que vous souhaitez gérer un
nombre quelconque (dans le temps et l'espace) de paramètres pour vos
boitiers, une seule et même table peut faire l'affaire (ou non).

boitier (id,nom,param_name,param_value,communication_date)

Si une même communication met à jour plusieurs boitiers, vous pourriez
avoir une table communication avec un id et une date, et au lieu d'avoir
l'attribut date dans boitier cela serait une référence à l'id de la table
communication.

Pour un boitier donné, la configuration actuelle correspond aux tuples (la
ligne) avec la valeur communication_date la plus élevée. Et vous pouvez
gérer autant de paramètres différents que vous souhaitez : chaque
paramètre est dans un seul tuple.

Bien sûr se pose après la question des performances, en insertion et en
sélection (il n'y a pas d'UPDATE normalement sur cette table). La table
communication pourrait donner de meilleures performances pour les
jointures.

Une technique classique employée pour ce genre de données « temporelles »
est de partitionner la table : cela peut être fait automatiquement par le
SGBDR ou géré par vos soins.
En gros, on décide d'une périodicité (le mois, la semaine, la journée,
l'année, etc. ca dépend du volume) et on créé des tables pour chaque
segment temporel.
Si on prend le mois par exemple, vous auriez
boitier_200701
boitier_200702
boitier_200703
etc.
Toutes les tables ont le même schéma et ne gérent que les données
relatives à leur segment temporel.

Il faut après adapter un peu la logique, ou se baser sur les
fonctionalités de partitions du SGBDR, ou écrire des déclencheurs pour :
1) créer automatiquement les tables en fonction des besoins (dès qu'on
entre dans un nouveau mois)
2) faire les insertions dans la bonne table
3) selon les sélections, faire des unions

Après réflexion sur le schéma il faut le tester, pour les performances, il
est difficile de les deviner, cela dépend d'énormément de paramètres
(n'oubliez pas les index pertinents en particulier). Il faut donc créer
les tables, mettre des données dedans, et faire des tests avec les
opérations qu'on utilisera réellement après.
Il est important de mettre des données, soit identiques à celles utilisées
plus tard, soit sinon statistiquement semblables (en terme de répartition
des valeurs notamment). Sinon cela faussera les résultats.
Si vous hésitez entre deux schémas, vous faites cela pour les deux, et
vous pourrez comparer les résultats des performances, sur votre cas
particulier.
Mais il est important avant de commencer cela que d'avoir identifier
_toutes_ les requêtes SQL qu'on aura besoin d'effectuer après, car le
schéma, quand il y a plusieurs possibilités, dépend des requêtes qu'on
aura besoin de faire.

J'espère vous avoir donné quelques pistes de réflexion.

BTW, tout ce qui précède n'a rien de spécifique à MySQL. Ma réponse est
généraliste, et en terme de SGBDR je pense qu'il vaut mieux essayer de ne
pas penser en terme d'un seul moteur. Cela donne trop de problèmes après
sinon.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
Dépêches sur le nommage
Gilles RONSIN
Le #21851661
Patrick Mevzek 2007 16:42:47, écrivait ceci:

Merci beaucoup pour votre réponse rapide et complète.
Elle m'aide beaucoup pour ma compréhension et ma façon d'appréhender le
problème. Je commençais un peu à me noyer dans tous les exemples lus
sur google.
Mais les choses commencent à s'éclaircir (je suis débutant en la
matière).

Le Wed, 08 Aug 2007 16:01:25 +0200, Gilles RONSIN a écrit:
-on gère des boitiers qui communiquent 0 à n paramètres (dans
l'exemple j'en met que 2)
-on stocke dans une table historique toutes les communications
qui arrivent -on dispose dans une table la configuration actuelle

pour l'instant on a :
boitier(_idboit_,num,param1,param2)
historique(_id_,date,idboit,param1,param2,param3,param4)



Avant de regarder de ce côté là, je pense qu'il y a
déjà quelques problèmes de normalisation à évoquer



Ça c'est certain :-)

1) votre n il est constant au cours du temps ? identique pour tous
les boitiers ?


oui. c'est un nombre fini et connu de paramètres par contre on ne sait
pas combien seront transmis.

Si vous répondez non à une de ces questions, alors il y a un
problème pour la table boitier.
Comme je dis dans mon cours « un bon schéma n'a pas besoin de
changer au cours du temps », c'est à dire en particulier qu'il ne
doit pas y avoir besoin d'ajouter des attributs (colonnes) plus
tard, parce qu'on se rend compte qu'on en a besoin.



Je suis à priori tout à fait d'accord avec ce principe, c'est
d'ailleurs pourquoi je prend le temps de la réflexion avant de me
lancer. J'en suis au stade de simulations avec des modèles simplifiés.

Alors oui, techniquement, on peut changer le schéma d'une table
(ALTER TABLE ...) mais c'est signe d'un problème de schéma bien
souvent. Dans votre cas de figure, si les boitiers n'ont pas tous
le même nombre de paramètres, vous vous retrouverez avec des trous
(NULL) Si vous avez un paramètre à ajouter, il faut créer un
nouvel attribut. Il y a donc peut-être un effort à faire déjà là.



Ça ne sera surement pas nécessaire d'avoir à modifier dynamiquement les
structures des tables.

2) même problématique pour l'autre table.
Sans compter les redondances que vous évoquez vous-même.



En fait, je distingue maintenant plusieurs types de paramètres :
les états (ce sont les param1 et param2 dans mon exemple que j'aurai dû
baptiser conf1 et conf2) qui sont des valeurs uniques et peu variables
dans le temps. Il n'y a pas de raison de les mettre dans une table
séparée. Je reviens donc à un champ dans la table historique. Je verrai
plus tard si je maintiens la dernière configuration connue dans la
table boitier ou si je vais la chercher dans la table historique.

3) si les *valeurs* de vos paramètres sont discrètes et peu
nombreuses il peut être intéressant de les stocker dans une table
à part et de faire des jointures. Mais cela dépend vraiment des
valeurs (entier ? chaînes de caractères ? etc.) et de l'usage.



les autres valeurs (param3, param4) sont en fait des mesures transmises
par les boitiers mais sous forme de chaînes de caractères. Par contre
de la même façon on ne sait pas quelles mesures vont être envoyées ni
si elles vont être transmisent d'ailleurs. Par contre on connait
l'exhaustivité des différentes mesures possibles.

Maintenant pour la modélisation, si j'ai bien compris votre
problème (pas sûr, parce que param3 et param4 je ne sais pas ce
que c'est), c'est un cas classique de table mise à jour dont vous
souhaitez conserver les mises à jour.



Je pense même que c'est basique (pour un habitué). ;-)

Il y a en gros deux pistes, chacune avec des avantages et
inconvénients.

A) soit une table d'archivage identique à la table archivée avec
un attribut supplémentaire indiquant quand l'archivage a eu lieu
donc pour vous ca serait en gros (à modifier selon mes dires plus
...
A noter que l'insertion avant le UPDATE peut être faite
automatiquement via une procédure stockée et un déclencheur dans
le SGBD.



Ça, il va falloir que je creuse. Je n'ai pas encore de notion de
déclencheur.

B) soit on veut éviter encore plus les redondances, et donc on ne
va archiver que ce qui change.
C'est un peu plus compliqué à mettre en oeuvre, on peut
éventuellement toujours s'en sortir avec un déclencheur, mais en
général on fait ca du coté applicatif
...
L'option B continue de fonctionner même si boitier change de
schéma, alors que dans l'option A il faudra aussi changer le
schéma de boitier_archive (ce qu'on va typiquement oublier de
faire, d'où problèmes après).



L'idée me plait. Je vais essayer d'approfondir.

Maintenant si vous rassemblez tout cela et que vous souhaitez
gérer un nombre quelconque (dans le temps et l'espace) de
paramètres pour vos boitiers, une seule et même table peut faire
l'affaire (ou non).

boitier (id,nom,param_name,param_value,communication_date)



J'y avais pensé :) mais l'analyse des mesures en devient beaucoup plus
compliqué.

Si une même communication met à jour plusieurs boitiers, vous
pourriez avoir une table communication avec un id et une date, et
au lieu d'avoir l'attribut date dans boitier cela serait une
référence à l'id de la table communication.



Heureusement un boitier communique ses paramètres.

Pour un boitier donné, la configuration actuelle correspond aux
tuples (la ligne) avec la valeur communication_date la plus
élevée. Et vous pouvez gérer autant de paramètres différents que
vous souhaitez : chaque paramètre est dans un seul tuple.
...
Il faut après adapter un peu la logique, ou se baser sur les
fonctionalités de partitions du SGBDR, ou écrire des déclencheurs
pour : 1) créer automatiquement les tables en fonction des besoins
(dès qu'on entre dans un nouveau mois)
2) faire les insertions dans la bonne table
3) selon les sélections, faire des unions



Je ne pense pas que ce schéma soit adapté à mon cas.

Après réflexion sur le schéma il faut le tester, pour les
performances, il est difficile de les deviner, cela dépend
...
J'espère vous avoir donné quelques pistes de réflexion.



Tout à fait. Je renouvelle mes remerciements.

BTW, tout ce qui précède n'a rien de spécifique à MySQL. Ma
réponse est généraliste, et en terme de SGBDR je pense qu'il vaut
mieux essayer de ne pas penser en terme d'un seul moteur. Cela
donne trop de problèmes après sinon.



Pour l'instant je ne peux même pas dire que j'en connais un. A peine je
balbutie sur mysql ;-)

--
Site : http://gilles.ronsin.free.fr
Recherches ciblées http://gilles.ronsin.free.fr/#search
Il est impossible pour un optimiste d'être agréablement surpris.
Publicité
Poster une réponse
Anonyme