Discussion :

[pontex]

Bonjour à tous,

Je dois crée une base de donnée capable d'enregistrer différents relevés de température puis les afficher dans un navigateur sous forme de graphique.
Pour peupler cette base, j'ai différents boitiers qui enregistrent entre 1 à 15 sondes puis envoient les données à intervalle régulier (toutes les 5min) au serveur via une requette HTTP. Pour info, le boitier envoi ses données dans un tableau dont la clef correspond au nom de la température mesurée : value['piece1'] = 10°C
Ces sondes peuvent mesurer la température de différentes choses que je dois être capable d'identifier.

Exemple:
Aujourd'hui, la sonde #1 mesure la température de la pièce N°1. La sonde #2 n'est pas utilisée.
Demain, la sonde #1 mesure la température de la pièce N°2. La sonde #2 mesure la température de la pièce N°1.

Je me demande donc comment ordonner cela...
Pouvez vous m'indiquer laquelle des solutions ci-dessous est selon vous la meilleure (ou m'indiquer une autre solution plus adéquate le cas échéant):

1. 1 ligne "mesures" par enregistrement liée à une configuration donnée (1 à 15 données)
   
   • boitiers [boitier_id | localisation]
   • enregistrements [enregistrement_id | boitier_id | enregistrement_date]
   • configurations [configuration_id | name_in1 | name_in2 | name_in3 ....... name_in15]
   • mesures [mesure_id | enregistrement_id | configuration_id | value_in1 | value_in2 | value_in3 ....value_in15]
   
   Le problème est que dans la majorité des cas, le boitier n'enregistre que quelques valeurs de température (4/5) et non les 15 maxi. J'aurais donc beaucoup de valeurs NULL.
   
   
2. 1 ligne "mesures" par température mesurée pour chaque boitier
   
   • boitiers [boitier_id | localisation]
   • enregistrements [enregistrement_id | boitier_id | enregistrement_date]
   • entrées [entrée_id | entrée_nom]
   • mesures [mesure_id | enregistrement_id | entrée_id | value_entrée]
   
   Je me pose alors la question du nombre de ligne générées par ce genre de schéma: imaginons que j'ai 100 régulations qui me génèrent 1 ligne dans "enregistrement" toutes les 5min. Cela me génère entre 1 et 15 lignes associées dans la base "mesures" soit entre 1200 et 18.000 lignes par heures... soit entre 1 à 13 millions de lignes par mois...
   
   
3. 1 table pour chaque température relevée
   
   • boitiers [boitier_id | localisation]
   • enregistrements [enregistrement_id | boitier_id | enregistrement_date]
   • mesures1 [mesure1_id | enregistrement_id | value_entrée1]
   • mesures2 [mesure2_id | enregistrement_id | value_entrée2]
   • mesures3 [mesure3_id | enregistrement_id | value_entrée3]
   • ....
   • mesures15 [mesure15_id | enregistrement_id | value_entrée15]
   
   Quand est il du post traitement (PHP) où il va falloir attribuer à chaque valeur une clef pour afficher mon graph correctement ?

[escartefigue]

Bonjour,

Cette question ne concerne pas MySQL (au moins dans un premier temps), mais la modélisation.
Il convient donc de la poser ici : https://www.developpez.net/forums/f6...sation/schema/

Avant de reformuler votre demande, pensez à
- identifier vos entités : boitier, sonde, mesure etc...
- identifier vos règles de gestion, par exemple

• RG01 : 1 boitier possède 1 ou plusieurs sondes
• RG02 : 1 sonde appartient à un et un seul boitier
• RG03 : 1 sonde effectue une ou plusieurs mesures
  etc..

Préciser le vocabulaire, expliquer les termes et éliminer les synonymes

A partir de là, établir le MCD devient facile, et ensuite les tables en découlent.
Commencer par les tables est une source d'erreurs

Je me pose alors la question du nombre de ligne générées par ce genre de schéma: imaginons que j'ai 100 régulations qui me génèrent 1 ligne dans "enregistrement" toutes les 5min. Cela me génère entre 1 et 15 lignes associées dans la base "mesures" soit entre 1200 et 18.000 lignes par heures... soit entre 1 à 13 millions de lignes par mois...

En ce cas, il faudra probablement choisir un autre SGBD que MySQL, mais chaque chose en son temps, commencez par le MCD

[pontex]

Bonjour escartefigue,

J'ai suivi vos conseils (du moins si j'ai bien compris) et voilà ce que ça donne:


Ce qui, si j'ai bien tout compris, devrait me donner 3 bases : boitier, sonde, mesure.
Je m'inquiète juste de la taille de la base "mesure" et du temps de réponse lorsque je vais demander toutes les mesures d'un boitier durant les 3 derniers mois par exemple.

Y-a-t il un moyen d'optimiser le process ?

Merci

[escartefigue]

C'est beaucoup plus clair ainsi, bien que des règles écrites préalables auraient été bienvenues

Comme toutes vos relations ont une cardinalité maxi 1 d'un des deux cotés, elles ne deviendront pas des tables. Donc seules vos entité-type deviennent des tables soit 3 tables (et non 3 bases )

Il existe des moyens divers, en fonction du SGBD choisi, pour que les performances restent optimales malgré un volume d'information conséquent.
Au niveau du MCD : vous pouvez identifier les sondes et les mesures relativement au boitier, ainsi, lorsque vous interrogez les mesures par boitier, toutes les mesures seront stockées physiquement sur des emplacements disque proches ce qui optimisera vos accès
Au niveau de la base de données (en fonction du choix, les possibilités sont plus ou moins riches), vous pourrez utiliser le partitionnement, pour séparer les mesures par période (mois, trimestre, année selon le besoin) et ainsi limiter les problèmes d'accès concurrents et faciliter l'exploitation.

[SQLpro]

Votre premier modèle montre une redondance en ligne (des colonnes portent le même nom à un indice près => "tableau")
Votre troisième modèle montre une redondance en colonne (des tables portent le même nom à un indice près)

Conclusion... il reste... devinez quoi !

A +

[pontex]

Ok, je reste donc sur le choix N°2.

En creusant un peu, je me rends compte que le modèle peut être simplifié et que le nom de la sonde peut être redondant (on mesure souvent le même chose). On obtiendrait donc :

Est-ce correct ?

Je reste donc avec 3 tables mais la table "record" est liée à un boitier et à une grandeur mesurable ce qui, si j'ai bien compris la réponse d'escartefigue améliorera les performances. Est-ce exacte ?

[pontex]

Vu que je n'ai pas de réponses, soit j'ai vraiment rien compris et tout le monde se dit que mon cas est désespéré (et désespérant...), soit c'est tellement parfait que cela ne nécessite aucun commentaire
Dans tous les cas, je continue mes questions de débutant:

• La majorité des requêtes seront faites par rapport à des dates. En effet, le but est de sélectionner les mesures effectuées par un boitier entre telle et telle date et de les afficher dans un graphique.
  Je compte donc partitionner la table "record" par rapport au champ "record_date" et indexer la colonne "record_date".
  Y-a-il une différence de performance si j'utilise un DATETIME ou un TIMESTAMP sur cette colonne ?
  
  
• De même, lorsque je souhaite afficher un grosse période de temps, plutôt que d'envoyer au script qui génère mon graphique tous les points, j'aimerai envoyer des valeurs moyennes (ex: 1 valeur par jour plutôt qu'une valeur toutes les 5min).
  Est-il possible (et surtout judicieux au niveau du temps de réponse général) d'utiliser la fonction "GROUP_BY record_date" plutôt que d'effectuer un post traitement côté PHP ?
  Si cela vous semble judicieux, est-il préférable que "record_date" soit un DATETIME ou un TIMESTAMP ?

D'avance merci pour vos conseils éclairés

[escartefigue]

Vu que je n'ai pas de réponses, soit j'ai vraiment rien compris et tout le monde se dit que mon cas est désespéré (et désespérant...), soit c'est tellement parfait que cela ne nécessite aucun commentaire

Ou tout simplement que les gens s'accordent une pause le WE
ATTENTION : dans votre première version, il y avait une cardinalité 1,n entre boitier et sonde, du coup comment allez vous enregistrer les mesures ?
S'il y a vraiment plusieurs sondes par boitier, il est préférable de conserver votre modèle avec 3 entités-type : BOITIER 1,n --- contenir --- 1,1 SONDE 0,n --- effectuer --- 1,1 MESURE

La majorité des requêtes seront faites par rapport à des dates. En effet, le but est de sélectionner les mesures effectuées par un boitier entre telle et telle date et de les afficher dans un graphique.
Je compte donc partitionner la table "record" par rapport au champ "record_date" et indexer la colonne "record_date".
Y-a-il une différence de performance si j'utilise un DATETIME ou un TIMESTAMP sur cette colonne ?

Pour MySQL c'est la même chose

De même, lorsque je souhaite afficher un grosse période de temps, plutôt que d'envoyer au script qui génère mon graphique tous les points, j'aimerai envoyer des valeurs moyennes (ex: 1 valeur par jour plutôt qu'une valeur toutes les 5min).
Est-il possible (et surtout judicieux au niveau du temps de réponse général) d'utiliser la fonction "GROUP_BY record_date" plutôt que d'effectuer un post traitement côté PHP ?
Si cela vous semble judicieux, est-il préférable que "record_date" soit un DATETIME ou un TIMESTAMP ?

Privilégiez au maximum les fonctions de la base de données qui seront traitées de façon ensembliste, alors que votre traitement, pour faire la même chose, devra traiter séquentiellement

[pontex]

ATTENTION : dans votre première version, il y avait une cardinalité 1,n entre boitier et sonde, du coup comment allez vous enregistrer les mesures ?
S'il y a vraiment plusieurs sondes par boitier, il est préférable de conserver votre modèle avec 3 entités-type : BOITIER 1,n --- contenir --- 1,1 SONDE 0,n --- effectuer --- 1,1 MESURE

En réalité, il y effectivement plusieurs sondes par boitier mais elles enregistrent une température "spécifique" qui se répète d'un boitier à un autre.
Pour être plus précis, voici un exemple:

• Chaque boitier est configuré pour savoir quelle sonde mesure quelle température.
• Le boitier #1 a une sonde raccordée sur sa sortie #1 qui enregistre la température du salon. Le boitier #1 envoie donc au serveur (sous forme de tableau) toutes les 5 min: température['salon'] = XX °C
• Le boitier #2 a une sonde raccordée sur sa sortie #5 qui enregistre aussi la température du salon (mais d'une autre maison). Le boitier #2 envoie donc lui aussi au serveur: température['salon'] = XX °C
  
  La clef des éléments du tableau envoyé correspond à la température mesurée (qui est une valeur moyenne effectuée par le boitier toutes les 5 minutes).

OK pour privilégiez au maximum les fonctions de la base de données, je viens de faire quelques essais sur ma base de test et c'est effectivement beaucoup plus rapide ! Merci du conseil
Dans ce cas, y a t il un utilité de créer un Index sur la date des enregistrements (record_date) si ma clef primaire contient déjà ce champ (device_id, measure_id, record_date) ?

En relisant le post principalement par rapport à la remarque de SQLpro qui mettait en évidence la redondance des informations, je me pose encore un question, :
Toutes les dates sont espacées de 5min et sont redondantes d'un boitier à un autre dans la table "record".
Par exemple:

• Le boitier #1 envoie une mesure id=1, id=2 et id=3 à 13H00 puis à 13H05, etc.
• Le boitier #2 envoie lui aussi une mesure id=1, id=2 etc. à 13H00 puis à 13H05, etc.

Ne faut il donc pas créer une table "date" et utiliser une clef étrangère dans la table "record" ?

Dans ce cas précis, ne peut-on pas considérer la colonne date_value qui contiendra la date sous format YYYY-MM-DD HH:MM:SS comme la clef primaire de la table plutôt que d'avoir une clef primaire numérique incrémentale et un index sur la colonne date_value ?

[escartefigue]

• Chaque boitier est configuré pour savoir quelle sonde mesure quelle température.
• Le boitier #1 a une sonde raccordée sur sa sortie #1 qui enregistre la température du salon. Le boitier #1 envoie donc au serveur (sous forme de tableau) toutes les 5 min: température['salon'] = XX °C
• Le boitier #2 a une sonde raccordée sur sa sortie #5 qui enregistre aussi la température du salon (mais d'une autre maison). Le boitier #2 envoie donc lui aussi au serveur: température['salon'] = XX °C

Je suppose que les autres sondes de ces mêmes boitiers font aussi des mesures, et que vous pouvez réaffecter une sonde à une autre mesure, aussi il me semble plus sage de modéliser l'ET "SONDE"

OK pour privilégiez au maximum les fonctions de la base de données, je viens de faire quelques essais sur ma base de test et c'est effectivement beaucoup plus rapide ! Merci du conseil
Dans ce cas, y a t il un utilité de créer un Index sur la date des enregistrements (record_date) si ma clef primaire contient déjà ce champ (device_id, measure_id, record_date) ?

Et les écarts de performances sont exponentiels dès que le volume est très important (plusieurs millions de lignes et au delà)
Concernant les index :
- La présence de la date dans l'index primaire n'a pas d'intérêt puisque mesure_id est unique et ne correspond donc par la force des choses qu'à une seule date et même un seul timestamp
- Si vous devez faire des recherches par date, tout boitier confondu et toutes mesures confondues, alors un index sur la date seule est très intéressant

Ne faut il donc pas créer une table "date" et utiliser une clef étrangère dans la table "record" ?
Dans ce cas précis, ne peut-on pas considérer la colonne date_value qui contiendra la date sous format YYYY-MM-DD HH:MM:SS comme la clef primaire de la table plutôt que d'avoir une clef primaire numérique incrémentale et un index sur la colonne date_value ?

Non pas de table date pour avoir une FK dans la table RECORD (cf. point précédent)
Quelques remarques

• le format d'affichage d'une date n'est pas un format interne à la base de données.
• une clef primaire est par définition unique, si vous faites ce choix de colonne, alors vous aurez au plus une mesure par seconde
• les clefs primaires sont celles les plus utilisées pour les jointures, le type de données binaire est le plus optimisé, d'où son choix privilégié pour ce type d'identifiant
• et enfin et surtout, dans une table issue d'une relation, si la clef primaire n'était pas la somme des clefs primaires des entités-type participant à la relation, alors aucune jointure ne serait possible, ni aucune contrainte d'intégrité de type REFERENCE. On touche là aux fondamentaux d'un SGBDR

[pontex]

Bonjour escartefigue et encore merci pour votre réponse.

La présence de la date dans l'index primaire n'a pas d'intérêt puisque mesure_id est unique et ne correspond donc par la force des choses qu'à une seule date et même un seul timestamp

En réalité, dans mon deuxième schéma, je ne voyais pas la table "mesure_property" comme une mesure au temps T mais plutôt comme une grandeur mesurable (ex: température salon)
Dans ce cas, il peut y avoir plusieurs fois le même couple record_id/measure_id : même grandeur mesurable pour le même boitier mais pour une date différente.

Je n'aurai pas du changer le nom des entités en cours de discussion... Pour faire la correspondance entre mes 2 MCD :

• sonde = measure_property
• mesure = record

Je ne comprends cependant pas pourquoi vous préconisez le modèle N°1.
Si j'ai bien compris le principe, les tables seraient pour le premier modèle:

Et pour le second:


Or selon votre remarque initiale:

Au niveau du MCD : vous pouvez identifier les sondes et les mesures relativement au boitier, ainsi, lorsque vous interrogez les mesures par boitier, toutes les mesures seront stockées physiquement sur des emplacements disque proches ce qui optimisera vos accès.

J'avoue n'avoir pas compris finalement.... J'avais cru comprendre que la table mesure (renommée dans mes images record) devait contenir l'ID du boitier et l'ID de measure_property.

Dans 99% des cas, je chercherai à afficher les valeurs enregistrées d'un seul et unique boitier à la fois sur une période donnée (quelques jours, mois ou année). L'index sur la date n'a donc pas d'utilité ?


Désolé encore pour mes questions qui doivent vous sembler bêtes, je développe modestement quelques applications en amateur durant mon temps libre et c'est la première fois que je m'attarde autant sur les bases de donnée. En tout cas, c'est très enrichissant. Merci

[escartefigue]

Vous m'avez mal compris, je ne préconise absolument pas ce modèle n°1 qui n'a pas de sens : dans la table associative que vous appelez mesure_property, l'identifiant primaire ne peut pas être une identifiant propre puisqu'il s'agit d'une table associative. L'identifiant est forcément celui hérité des deux entités-type qui étaient liées avec la relation.
C'est bien votre 2ème schéma qu'il faut retenir

Ma remarque qui concernait l'identification relative, se rapportait à la toute première version du MCD, celle où vous aviez 3 entités-type, dont l'ET sonde, et 2 relations.
Elle ne tient plus (du moins en l'état) si vous éliminez l'ET sonde.
Et je reste très dubitatif concernant la suppression de l'ET sonde a partir du moment ou un boitier possède plusieurs sondes, même si une sonde mesure toujours la même pièce, il convient de maintenir cette ET

D'après ce que je comprends de votre besoin, il faut en revenir au MCD que vous avez proposé le 17-02 à 15h36

Pour les 99% de requêtes qui utilisent le critère boitier en élément de filtrage, l'index sur la date seule ne sert à rien
Mais si les 1% qui restent sont justement une extraction pour une date particulière ou une plage restreinte de dates, tous boitiers confondus, alors l'index sur la date seule sera d'autant plus nécessaire que votre base de donnée deviendra volumineuse.

Il est possible, et c'est parfois pratiqué, de créer un index pour une requête ponctuelle (par exemple exécutée une seule fois par an), et de le supprimer ensuite pour ne pas pénaliser les INSERT

[SQLpro]

...
Je compte donc partitionner la table "record" par rapport au champ "record_date" et indexer la colonne "record_date".

Ce n'est pas au stade de la modélisation que l'on se pose des questions sur les performances. C'est au stade à minima du modèle physique et un partitionnement comme une indexation n'a aucune infludence an niveau du MCD !

Y-a-il une différence de performance si j'utilise un DATETIME ou un TIMESTAMP sur cette colonne ?

TIMESTAMP est le mot clef désignant des types date+heure dans la norme SQL. DATETIME est le nom d'un type de données date+heure de certains SGBDR. Au niveau du MCD aucune importance.

[*]De même, lorsque je souhaite afficher un grosse période de temps, plutôt que d'envoyer au script qui génère mon graphique tous les points, j'aimerai envoyer des valeurs moyennes (ex: 1 valeur par jour plutôt qu'une valeur toutes les 5min).
Est-il possible (et surtout judicieux au niveau du temps de réponse général) d'utiliser la fonction "GROUP_BY record_date" plutôt que d'effectuer un post traitement côté PHP ?

Aucun traitement itératif portant sur des collections de données ne sera aussi rapide que ce que vous obtiendrez d'un SGBDR... Surtout si vous utilisez un langage comme PHP ! De plus vous commettez la même erreur trois fois de suite, pensez au développement alors que vous devez vous consacrer au MCD !!!

A +