Discussion :

[Mathaousse]

Bonjour,

Premièrement cette discussion est peut être à ranger dans Merise plus que dans Mysql donc si c'est le cas veuillez m'en excuser.

Je suis en train de modéliser sur workbench et j'ai un incompréhension que vous allez facilement lever.



Cela se situe au niveau du Mot de passe pour la double authentification. Je l'ai mis sur une table à part car comme il est censé changer souvent par rapport aux attributs de la table Utilisateur Premium (de ce que j'en ai lu lors de mes recherches sur divers forums mais est-ce exact pour autant?). Entre les tables "Utilisateur Premium" et " Mot de passe double authentification" quand je mets une relation identifiée j'ai obligatoirement la clé étrangère qui passe en clef primaire et je ne comprends pas pourquoi car je peux identifié une ligne de la table sans cette clé (d'après moi bien sûr )

Merci de votre aide!

[escartefigue]

bonjour,

L'effet produit est tout à fait normal, c'est la définition même de l'identification relative : la table fille hérite de l'identifiant de la table mère comme constitutif de sa clef primaire

Cela étant pourquoi modéliser une table spécifique, le mot de passe est a priori en relation 1,1 avec l'utilisateur (qu'il soit premium ou pas), il pourrait donc être rapatrié dans la table utilisateur.
ou dans la table utilisateur_premium si c'est un mot de passe spécifique aux prémium

De plus
- un mot de passe sur 255 car est invraissemblable, car impossible à mémoriser, or, il est fortement recommandé de ne jamais écrire son mot de passe mais de l'apprendre par cœur, imaginez avec 255...
- une adresse postale est normalisée, la norme est publique et diffusée par la poste, une petite recherche Google vous donnera toutes les infos
- vous seriez gagnant en créant une table des adresses, avec un type adresse (de livraison, de facturation, d'habitation principale, d'habitation secondaire....). Ca vous permettra de répéter les adresses autant de fois que nécessaire pour un utilisateur
- même remarque pour les téléphones (fixe pro, fixe perso, portable pro etc...)

[Mathaousse]

Merci pour vos remarques.
Pour explication, l'utilisateur a deux mots de passe, un qu'il choisit lui-même (dans la table utilisateur) et celui envoyé et choisi par le "système" pour la double authentification. A ce jour je ne connais pas la taille de ce mot de passe donc j'ai laissé 255 pour que ça saute aux "yeux" De plus ce mot de passe change fréquemment (a chaque reconnexion de l'utilisateur) par rapport aux données de l'utilisateur premium (l'utilisateur n'a pas besoin) et c'est pour cela que je l'ai mis dans une table à part.

Pour ce qui est des numéros de téléphone je vais corriger le tir car je vais en avoir effectivement besoin dans d'autres tables.
Par contre pour l'adresse postale c'est juste pour mettre sur la facture de l'utilisateur dans le cas où l'accès soit payant. Est il pour autant nécessaire de la parser en différentes colonnes ( code postal, ville...)?

[escartefigue]

Même si vous n'utilisez l'adresse que pour un usage simple, une adresse mal libellée peut faire l'objet d'un rejet par la poste avec retour à l'expéditeur.
C'est pour cette raison, et aussi pour éviter les erreurs (délivrance à un mauvais destinataire, imaginez les conséquences pour un mot de passe...), que la poste diffuse gratuitement la norme à utiliser.

Et si un jours votre besoin de gestion des adresses évolue, vous serez déjà prêt à le supporter sans avoir à modifier votre base de données

CQFD

[Artemus24]

Salut Mathaousse.

Il n'y a aucune raison d'externaliser dans une table à part votre mot de passe.

Je ne voie pas non plus, la raison pour créer deux tables, une de nom "utilisateur" et l'autre de nom "utilisateur premium".

Comme le dit Escartefigue, vous devez externaliser les numéros de téléphone et les adresses de votre utilisateurs dans des tables séparées.
Cela doit correspondre à la première forme normal, c'est-à-dire que vos données doivent être atomique.

Pourquoi mettez-vous "char" plutôt que "varchar" ?
On met "char" quand on a toujours la même longueur, sinon on fait du gaspillage de mémoire pour rien. Le mieux est de mettre "varchar"

Pourquoi limitez-vous la taille de vos chaînes de caractères à des valeurs arbitraire ?
Cela ne va pas vous coûter plus en espace d'occupation, si vous mettez la taille maxi de 255.

Pour votre mot de passe, vous devez le chiffrer.
Avec la fonction "md5()", vous pouvez mettre un char(32) car la longueur sera toujours fixe.

Si vous avez peur d'avoir une table un peu trop volumineuse, utilisez la compression en mettant cette déclarative :

Code :

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

row_format=compressed;

@+

[escartefigue]

Pourquoi mettez-vous "char" plutôt que "varchar" ?
On met "char" quand on a toujours la même longueur, sinon on fait du gaspillage de mémoire pour rien. Le mieux est de mettre "varchar"

Ce n'est pas si simple.

Il faut tenir compte de la fréquence des mises à jour sur la colonne concernée, quand ces mises à jour modifient la longueur effective de la donnée.
Il faut également tenir compte de la présence ou non d'index sur la colonne concernée
Plus la longueur moyenne est faible, plus il y a de changements de longueur et plus il y a d'index, plus il faut privilégier le char fixe
à l'inverse, plus la longueur moyenne est forte, moins il y a de changements de longueur et s'il n'y a pas d'index alors le varchar est préférable
L'explication vient des déplacements des pages (supports physiques des données) lors des changements de longueur d'un varchar, ce phénomène peut être très couteux.

N'oublions pas non plus que le varchar impose une gestion de l'attribut longueur et ajoute les octets pour stocker cette longueur.

N'oublions pas non plus (bis) la compression des données, effectuée soit par le SGBD (ca dépend des sgbd) soit par le réseau.

Attention aussi : la réponse n'est pas toujours la meme selon le SGBD, certains sont plus à l'aise avec du char, d'autres avec du varchar

Compte tenu de tout ça, on peut considérer qu'en deçà de 30 à 50 caractères, le varchar ne présente que très peu d'intérêt, à moduler selon le contexte.

Typiquement, une colonne code article, de format char(10) sera le plus souvent beaucoup plus efficiente qu'une colonne varchar(10) même si certains codes articles ne font que 4 ou 5 caractères

[Artemus24]

Salut Escartefigue.

Il faut tenir compte de la fréquence des mises à jour sur la colonne concernée, quand ces mises à jour modifient la longueur effective de la donnée.

Sur le nom, le prénom, l'adresse email, voire aussi le login, cela ne change pas tous les jours.
Je ne pense pas que ce soit un argument fort qui justifie le choix du "char".

Il faut également tenir compte de la présence ou non d'index sur la colonne concernée

Sur ce point, je ne saurai dire comment MySql gère les chaînes de caractères.
Mais a priori, je pense que dans l'index, MySql gère le char, car je ne voie pas comment l'index pourrait gérer une chaîne de longueur variable.

L'explication vient des déplacements des pages (supports physiques des données) lors des changements de longueur d'un varchar, ce phénomène peut être très couteux.

C'est le split de page. Sous DB2, on gère cela avec "PCTFREE". Je ne sais pas comment cela se gère avec MySql.

N'oublions pas non plus que le varchar impose une gestion de l'attribut longueur et ajoute les octets pour stocker cette longueur.

1 octet pour une longeur max de 255 caractères.
2 octets pour une longueur comprise entre 256 et 65535 caractères.

Ce n'est pas un argument fort pour l'acceptation ou le rejet du varchar.
Mais si le nombre de lignes est important, cet octet n'est plus négligeable.

N'oublions pas non plus (bis) la compression des données, effectuée soit par le SGBD (ca dépend des sgbd) soit par le réseau.

A vrai dire, concernant le char ou le varchar, je me pose seulement deux questions :

1) est-ce que le varchar est préférable à la compression des données ou pas ?

2) dans un langage de programmation, on utilise un masque qui permet d'accéder directement à la donnée, même si celle-ci est variable.
Dans un SGBD, on n'utilise pas cette technique, mais plutôt celle de l'adressage relatif dans la ligne et dans la page.
Où si tu préfères, on gère un ensemble de tableaux de pointeurs qui pointent soit sur le début de la ligne, ou soit dans la ligne sur le début de chaque colonne.

En tout cas, c'est ainsi que je ferai la gestion des données dans la page.
Ce qui signifie qu'à chaque modification de la longueur d'une des colonne, il faut refaire tout les calculs.

Mon interrogation concerne alors le temps d'accès à la données.

Attention aussi : la réponse n'est pas toujours la même selon le SGBD, certains sont plus à l'aise avec du char, d'autres avec du varchar

Sous DB2, je privilégiais le char, tandis que sous MySql le varchar.

Compte tenu de tout ça, on peut considérer qu'en deçà de 30 à 50 caractères, le varchar ne présente que très peu d'intérêt, à moduler selon le contexte.

La moyenne n'est pas un argument pour privilégier le char ou le varchar.

Si tu as une seule chaîne de caractères à mettre dans la ligne, même si elle varie très peu, disons entre 10 et 30 caractères, je préfère mettre un "varchar".
Le problème se pose s'il y a plusieurs chaînes de caractères dans la ligne.
J'aurai tendance à les mettre tous en "char" sauf le dernier, que je mets en "varchar" et en fin de ligne.

Typiquement, une colonne code article, de format char(10) sera le plus souvent beaucoup plus efficiente qu'une colonne varchar(10) même si certains codes articles ne font que 4 ou 5 caractères

Tu te focalises trop sur la longueur de la donnée. Ce n'est pas un argument fort selon moi.

Mais si tu as dix lignes, avec un format en "char(10)", cela occupe 10 * 10 = 100 caractères.
Si tu mets "varchar(10)", en moyenne tu as seulement 5 caractères.
Ce qui donne alors 10 * (1 + 5) = 60 caractères. Soit une réduction de 40% !
Si tu veux gagner plus, utilises la compression des données.

Ce n'est pas si simple.

Je te rejoins sur cette affirmation !
Mais d'après ce que j'ai compris de tes arguments, nous n'avons pas les mêmes critères.

@+

[escartefigue]

Sur le nom, le prénom, l'adresse email, voire aussi le login, cela ne change pas tous les jours.
Je ne pense pas que ce soit un argument fort qui justifie le choix du "char".

Il est possible en effet que la longueur de ces attributs varie peu, mais comme je l'indiquai plus haut, il vaut également regarder s'il y a des index sur ces colonnes
Un index (non unique bien sur) sur le nom est une chose fréquente
Et surtout je répondais à cette phrase beaucoup trop catégorique :

Pourquoi mettez-vous "char" plutôt que "varchar" ?
On met "char" quand on a toujours la même longueur, sinon on fait du gaspillage de mémoire pour rien. Le mieux est de mettre "varchar"

C'est le split de page. Sous DB2, on gère cela avec "PCTFREE". Je ne sais pas comment cela se gère avec MySql.

Et le PCTFREE ne fait que limiter les risques, il ne les supprime pas, ce d'autant moins qu'il ne s'applique qu'au chargement initial et après réorg (ou rechargement en mode replace)

1 octet pour une longeur max de 255 caractères.
2 octets pour une longueur comprise entre 256 et 65535 caractères.
Ce n'est pas un argument fort pour l'acceptation ou le rejet du varchar.
Mais si le nombre de lignes est important, cet octet n'est plus négligeable.

La taille dépend des SGBD, mais ca reste dans tous les cas très petit, quoi qu'il en soit, je ne dis pas que c'est un argument fort, je dis que c'est un argument supplémentaire, nuance

1) est-ce que le varchar est préférable à la compression des données ou pas ?

Vu qu'il existe des compression matérielles, réseau et SGBD et en fonction du type de plate-forme, de réseau ou de SGBD, la réponse c'est ... ça dépend

Mon interrogation concerne alors le temps d'accès à la données.

Ce n'est pas directement le temps d'accès qui change, mais le temps de mise à jour (qui peut du coup provoquer une attente des threads en lecture)

Sous DB2, je privilégiais le char, tandis que sous MySql le varchar.

En effet, je mentionnais plus haut que selon le SGBD, la façon de traiter l'un et l'autre format n'est pas la même

La moyenne n'est pas un argument pour privilégier le char ou le varchar.
Si tu as une seule chaîne de caractères à mettre dans la ligne, même si elle varie très peu, disons entre 10 et 30 caractères, je préfère mettre un "varchar".
Le problème se pose s'il y a plusieurs chaînes de caractères dans la ligne.
J'aurai tendance à les mettre tous en "char" sauf le dernier, que je mets en "varchar" et en fin de ligne.

Non seulement la moyenne est bien un argument mais aussi, certains SGBD positionnent physiquement les colones varchar en fin de ligne, même si vous les avez décrites en début (ex : pour DB2 il s'agit du RRF qui existe depuis la V9)

Tu te focalises trop sur la longueur de la donnée. Ce n'est pas un argument fort selon moi.
Mais si tu as dix lignes, avec un format en "char(10)", cela occupe 10 * 10 = 100 caractères.
Si tu mets "varchar(10)", en moyenne tu as seulement 5 caractères.
Ce qui donne alors 10 * (1 + 5) = 60 caractères. Soit une réduction de 40% !
Si tu veux gagner plus, utilises la compression des données.

Ce qu'il faut comprendre c'est que il faut choisir entre de la place disque supplémentaire d'un coté (char), et du déplacement de pages de données de l'autre (varchar)
Il ne s'agit donc pas de décider une fois pour toutes que telle ou telle solution est la meilleure, il s'agit de connaitre les arguments qui plaident en faveur du char ou du varchar, et de prendre la décision en conséquence.

Encore une fois, je réagissais sur votre assertion excessive, que revoici

Pourquoi mettez-vous "char" plutôt que "varchar" ?
On met "char" quand on a toujours la même longueur, sinon on fait du gaspillage de mémoire pour rien. Le mieux est de mettre "varchar"

[escartefigue]

j'ai retrouvé ce post de SQLPro qui corrobore mes explications précédentes

http://blog.developpez.com/sqlpro/p9...uel_difference

Ce n'est pas pour rien qu'il existe plusieurs types de données, il faut savoir en faire bon usage

[Artemus24]

Salut Escartefigue.

Son accès est donc plus lent, car il faut parcourir toutes les colonnes précédentes du fait de la longueur variable.

Cette affirmation est fausse !

La technique de l'adressage (base ; déplacement) n'est pas utilisé dans l'organisation des données.
Pourquoi ? Car comme le dit SQLPRO, il faut recalculer la position de chaque colonne afin d'atteindre celle qui nous intéresse.
Et ça, c'est très pénalisant en terme de performance !!!

On utilise des tableaux de pointeurs afin d'accéder directement à la données, même si elle est variable.

L'inconvénient de cette méthode, c'est que l'insertion ou la mise à jour va recréer ces pointeurs !
Donc nécessairement plus coûteuse en terme de performance. Et par voie de conséquence, le select sera bien plus rapide.

De plus, il y a dans la ligne tout un contexte qui décrit son contenu.
Par exemple, il y a le fameux "rowid", les marqueurs NULL, la longueur des chaînes de caractères, et d'autres informations pour faciliter l'accessibilité et la gestion de ces données.

Maintenant, je ne sais pas comment ont été développés ces SGBDR. Il est fort possible que des mauvais choix ont été fait !

Il y a une mauvaise image qui continue d'être véhiculé pour le type relationnelle, qui consisterait à mettre en vrac les données, sans organisation, comme dans un fichier séquentiel.
Au niveau de la page, il existe une organisation que je qualifie de type réseau, même pour les bases de type relationnelle.
Cela a été instaurer, juste pour améliorer la performance des accès.

Pour couronner le tout, certains SGBDR mal conçus comme MySQL, transforment les VARCHAR(n) en CHAR(n) donc de longueur fixe pour certaines opérations relationnelles comme les tris ou les groupages

Je ne suis pas entièrement d'accord avec cette affirmation.
Il faut faire la distinction entre le stockage de l'information et l'usage que l'on en fait, c'est-à-dire la partie utilise.
Faire un tri sur le stockage de la chaîne de caractères est une absurdité. Car la longueur est une contrainte technique et n'a rien à voir avec la donnée elle-même.
Par contre, il est plus difficile de gérer les chaînes de caractères uniquement sur leur longueur, que de les gérer avec une marque de fin, comme en 'C'.

Comme j'ai fait de l'assembleur IBM 370, je sais qu'il existe des instructions qui permettent de faciliter les comparaisons entre chaînes de caractères.
Et la comparaison se fait toujours caractères par caractères, quoi que l'on dise.
C'est pourquoi, je ne suis pas certain de cette affirmation qui demande une preuve.

Encore une fois, je réagissais sur votre assertion excessive, que revoici

On met "char" quand on a toujours la même longueur, sinon on fait du gaspillage de mémoire pour rien. Le mieux est de mettre "varchar"

C'est la distinction que l'on peut faire entre une colonne de longeur fixe et une colonne de longueur variable.
Je ne voie pas pourquoi cette définition basique serait fausse.

Il ne s'agit donc pas de décider une fois pour toutes que telle ou telle solution est la meilleure, il s'agit de connaitre les arguments qui plaident en faveur du char ou du varchar, et de prendre la décision en conséquence.

Dans le principe, je suis d'accord.

Mais une base de données, c'est quoi au juste ? Est-ce une organisation fonctionnelle ou bien un casse-tête technique ?
Nécessairement, l'approche ne sera pas la même.

Même si pour la plupart des DBA, cela ne pose pas de problème, je considère que laisser le choix entre char et varchar est une erreur.
La gestion des chaînes de caractères a subit une grosse différence dans le monde la programmation, entre celle qui gère une marque de fin (comme en C) et celle qui gère une longueur.
L'aspect longueur variable est une contrainte technique et n'a pas lieu d'être dans le typage des données.

C'est comme si on laissant le choix entre un integer qui se lit en interne de gauche à droite (BULL), et celle qui se lit de droite à gauche (IBM).
La question ne se pose pas en temps normal, sauf lors d'une migration de BULL vers IBM.
C'est pourquoi, on passe fréquemment par une représentation numérique étendu et non par sa représentation binaire.

Vu que tes propos sont intéressant, je te mets un +1.

@+