Discussion :

[Stefmeudon]

Bonjour à Toutes et à Tous,

Pour des raisons professionnelles, j'avais arrêter la programmation (PHP et bases SQL).

Mais une amie me demande si je peux lui développer un outil d'aide : je ne sais pas comment aborder la mise en œuvre.

Je vais faire une métaphore avec la rédaction d'un livre en plusieurs volumes. Chaque volume étant coupé en partie. Le volume I peut comporter 5 parties mais le volume II seulement 3.

Cette amie me demande " une gestion des idées à développer " : à chaque idée, elle insert son texte entre deux autres idées existantes (en précisant le volume). Un peu comme insérer une ligne sous Excel qui, à mon avis, reste la meilleure option.

Mais ma curiosité me taraude. Je me vois mal ré-indexer toute une table à chaque insertion. La piste, me parraissant la plus simple, est de créer à chaque idée un champs avec un numéro d'idée. Mais avec un espace de mille entre chaque idée. La première idée aurait ce champs à 1000, la seconde à 2000, etc.

Ainsi, à chaque nouvelle idée, pour l'insertion, je fais la différence entre ces deux champs que je divise par deux. Le résultat arrondi devient le "numéro de l'idée". Un simple tri sur " cette colonne " permet de remettre les idées dans l'ordre (avec un where qui teste le n° du volume).

Auriez-vous une autre piste à me proposer ?

Je vous remercie pour votre aide.

EDIT : correction de syntaxe

[Séb.]

Je me vois mal ré-indexer toute une table à chaque insertion

Pas toute une table, juste la position d'une partie des idées de la thématiques.

theme (id, name)
idea (id, content, position, #theme_id)

L'appli transmet la position de la nouvelle idée dans le thème :
Position 1 => Sera en première position, l'ancienne idée en position 1, et toutes les suivantes, sont décalées de 1
Position N => Sera en position N, l'ancienne idée en position N, et toutes les suivantes, sont décalées de 1

Côté SQL :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
START TRANSACTION;
 
UPDATE idea
SET position = position + 1
WHERE TRUE
    AND theme_id = :theme_id
    AND position > :position;
 
INSERT INTO idea (content, position, theme_id)
VALUES (:content, :position, :theme_id);
 
COMMIT;

Je n'y vois pas de contre-indication.

[Stefmeudon]

Merci pour cette réponse, il faut que je creuse... J'ai tout en PDO et requête préparée (même en local).

[escartefigue]

Variante de la proposition de Seb :

Si une idée ne peut exister sans son thème, alors il s'agit de ce qu'on appelle un type faible.
En ce cas, on identifie l'idée relativement au thème

pour la table thème, ça ne change rien :

TH_theme (TH_ident, TH_nom)

mais pour la table des idées, on a la structure suivante :

ID_idee (#TH_ident, ID_seq, ID_contenu, ID_position)

La clef primaire de la table ID_idee se compose de la clef étrangère TH_ident augmentée d'un chrono qui recommence à un pour chaque thème.
L'avantage de ce type d'identification, c'est qu'avec un index cluster sur la clef primaire de ID_idee, toutes les idées d'un même thème seront stockées dans un espace disque contigu, au bénéfice des performances. Sur une base de données à forte volumétrie, c'est particulièrement sensible.

À noter :

• visiblement, il s'agit d'un développement à titre privé, il n'y a donc aucune raison de se compliquer la vie en choisissant des noms d'objet en anglais. Nommez vos tables et vos colonnes en français
• il est préférable (surtout avec les noms anglais, mais pas seulement) d'associer un préfixe ou un suffixe à chaque table comme je l'ai fait (TH_ pour theme, ID_ pour idées... afin d'éviter de tomber sur des mots réservés SQL ce qui est une source d'erreur.
  Ainsi le mot "position" est un mot réservé, alors que ID_position ne l'est pas

Par ailleurs, il n'est pas nécessaire de "réindexer" à chaque insertion.
D'une part, si index il y a, il est par défaut mis à jour en temps réel lors de l'insertion (il existe des index différés, mais c'est optionnel)
D'autre part, indexer sur le numéro de position n'est utile que si on recherche sur ce numéro de position, ce qui est peu probable.
Pour restituer les idées dans le bon ordre, un simple ORDER BYsuffit, pas besoin d'index

[vanagreg]

@escartfigue

Je n'ai pas compris l'histoire de l'index cluster. Si tu veux intercaler une idée entre 2 idées, tu indiques le numéro de l'index existant que tu veux remplacer par ta nouvelle idée et la renumérotation se fait automatiquement?

[escartefigue]

Je parle d'index au sens de l'objet SQL du terme, et non au sens plus général.

L'index cluster c'est l'index selon lequel les données sont physiquement rangées dans les fichiers (les table space) après réorganisation.

Donc, quand on a une table bien organisée, les recherches qui exploitent directement¹ l'index cluster permettent de se positionner sur la valeur d'index, puis de parcourir séquentiellement les données sans faire d'allers-retours incessants entre data et index.

Avec l'identification relative indexée en cluster, les performances sont de tout premier ordre.

1 directement : c'est à dire si la requête utilise le chemin d'accès de l'index cluster pour satisfaire sa recherche. À différencier du passage d'un index lambda à l'index cluster, passage requis par certains SGBD (MySQL, SQL server...) qui n'adressent les données que depuis l'index cluster.

[Stefmeudon]

Merci pour cette piste @escartefigue.