AcumatiCares
Introduction
Dans une application de base de données, les clés de table jouent un rôle important. De plus, lorsque vous concevez de nouveaux écrans ou même un module, le choix du schéma de clés à utiliser n'influencera pas seulement la façon dont vous coderez vos DAC, mais aussi, dans une certaine mesure, la facilité d'utilisation et d'intégration de votre application. Au début de mon travail avec Acumatica, j'avais l'habitude de me gratter la tête en prenant des décisions sur le choix de mes clés ou sur l'utilisation d'un ID par rapport à un CD dans les demandes de renseignements génériques.
Pourquoi s'en préoccuper ?
Décider de changer la clé primaire d'une des tables est très difficile une fois que vous avez installé l'application sur d'autres sites, ce qui signifie généralement que vous devrez vivre avec votre choix pendant très longtemps, voire pour toujours. Avant d'aller plus loin, définissons ce que nous entendons par clés[1].
Clé primaire (PK) : Une colonne ou un groupe de colonnes dans une table qui identifie de manière unique chaque ligne de cette table. Les clés primaires ne peuvent pas être dupliquées, ce qui signifie que la même valeur ne peut pas apparaître plus d'une fois dans la table. Une table ne peut avoir plus d'une clé primaire.
Clé candidate: Il s'agit également d'une clé unique permettant d'identifier un enregistrement de manière unique dans une table, mais une table peut avoir plusieurs clés candidates. Des exemples de clés candidates typiques sont les courriels, le nom de l'entreprise, l'identifiant de l'employé, lenuméro DUNS[2].
Clé alternative: Une colonne ou un groupe de colonnes dans une table qui identifie de manière unique chaque ligne d'une table particulière. Une table peut avoir plusieurs choix de clé primaire, mais une seule peut être définie comme clé primaire. Toutes les clés qui ne sont pas la clé primaire sont appelées clés alternatives.
Clé naturelle : Une colonne ou un groupe de colonnes dans un tableau qui identifie de manière unique chaque ligne de ce tableau d'une manière humainement reconnaissable. Un code ou un numéro de document est souvent considéré comme une clé naturelle.
Clé de substitution: Une clé artificielle qui vise à identifier de manière unique chaque enregistrement est appelée clé de substitution. Ce type de clé est utilisé lorsque vous n'avez pas ou ne voulez pas utiliser une clé naturelle comme clé primaire.
Clé étrangère (Foreign Key, FK): Une colonne ou un groupe de colonnes dans une table qui est la clé primaire d'une autre table.
Différence entre clé et index
Comme indiqué précédemment, une clé est une colonne ou un groupe de colonnes, ou encore un groupe de valeurs. Un index, quant à lui, aide les bases de données à localiser l'endroit physique où la ligne est stockée sur la base des valeurs fournies par la clé. L'index est donc une sorte de dictionnaire des clés et des emplacements. La base de données utilise l'index pour trouver rapidement la ligne correspondant aux valeurs de la clé.
Quel schéma utiliser ?
Certaines applications de base de données n'utilisent souvent qu'un seul schéma (type) pour identifier les clés. Dans le passé, j'ai travaillé avec un ERP qui utilisait des GUID pour toutes ses clés, un autre qui n'utilisait que des clés naturelles et un troisième qui n'utilisait que des nombres entiers de substitution. Comme nous le verrons, chacun de ces systèmes a ses avantages et ses inconvénients. Acumatica utilise plusieurs schémas pour les clés de table en fonction des besoins et du contexte, ce qui rend difficile le choix de l'un par rapport à l'autre, mais ils ont tous leur place dans la base de données. Commençons par les clés les plus simples et progressons vers les plus complexes.
La clé naturelle
Une clé naturelle est une valeur simple (telle qu'une chaîne de caractères) qui identifie clairement la signification de la ligne. Si je dis par exemple "US", "CA" ou "MX", vous savez immédiatement de quels pays il s'agit. Si je dis "H0H 0H0[3]" ou "90210[4]", vous les connaissez probablement aussi - bien que ce soit un peu plus difficile. Enfin, si je dis "NONTAXABLE", je n'ai pas besoin d'expliquer quoi que ce soit. Bien qu'il soit rare de nos jours que quoi que ce soit soit ne soit pas imposable en soi.
Pour: Très reconnaissable, plus facile à comprendre, plus court que les autres, donne un contexte d'utilisation, plus facile à utiliser dans les requêtes de base de données.
Inconvénients: la taille (généralement fixe) doit être décidée à l'avance afin que les codes ne puissent pas grandir plus facilement. Les petites tailles conduisent à des codes méconnaissables. Une fois la clé enregistrée, elle ne peut être modifiée sans difficulté.
La clé de substitution pour les nombres entiers
L'entier de substitution est l'une des clés les plus utilisées dans les applications de base de données. Elles prennent très peu de place (typiquement 32 bits), peuvent augmenter automatiquement et le nombre de lignes peut être assez important (environ 2 à 4 milliards selon qu'elles sont signées ou non). Une telle clé sera visible ou non en fonction de son utilisation.
Avantages: Stockage et accès à la base de données efficaces, la plupart des tables ont un petit nombre de lignes, les clés de signification sont plus faciles à lire et à utiliser dans les requêtes, auto-incrémentables, modèle simple et apprécié par la plupart des développeurs.
Inconvénients: leurs valeurs ne sont pas faciles à reconnaître, et il faut généralement une table jointe pour en comprendre la signification. Une fois la clé enregistrée, elle ne peut pas être modifiée facilement, voire pas du tout.
Convient pour:
- Tableaux à plusieurs lignes
- Table de traitement des lignes
- Séquences de lignes ordonnées
Quelques exemples :
Clé GUID de substitution
Un identifiant unique universel (UUID) ou identifiant unique mondial(GUID) est une valeur de 128 bits (16 octets) qui est normalement divisée en cinq groupes de longueurs différentes. Le GUID de substitution est largement utilisé dans de nombreuses applications de base de données. Sa force réside dans son universalité et dans la probabilité quasi nulle d'une collision. Ils sont uniques non seulement sur un système donné, mais aussi sur tous les systèmes. Cela les rend idéaux pour l'échange de données ou d'applications personnalisées entre systèmes. Veuillez noter qu'il existe plusieurs variantes définies par la norme RFC 4122, mais nous ne les aborderons pas ici. Il existe également d'autres GUID non conformes.
Avantages: Universels et uniques de par leur nature. Ils sont bien documentés et normalisés. Leur nature méconnaissable en fait un bon candidat pour les références "secrètes". Étant donné un GUID, il est pratiquement impossible de deviner une valeur future, bien que certaines variantes soient plus faibles à cet égard.
Inconvénients: ils sont difficiles à écrire et impossibles à mémoriser. Par conséquent, vous devez utiliser une instruction de jointure ou une action de copier-coller pour les utiliser dans des requêtes de base de données. Leur valeur est pratiquement méconnaissable, de sorte qu'ils ne donnent aucune indication sur leur utilisation spécifique.
Quelques exemples :
Qu'en est-il des clés Acumatica ?
Acumatica a fait un excellent travail pour choisir ses clés, même si cela ne semble pas être le cas à première vue.
La clé naturelle
De nombreuses tables Acumatica utilisent une clé naturelle lorsque cela est approprié.
Quelques exemples :
Convient pour:
- Noms de code (TaxZoneID, TermsID, CountryID, StateID, etc.)
- Noms des classes (ItemClassID, CustomerClassID, etc.)
- Documents (SOOrder.OrderType, SOOrder.OrderNbr, GLDocBatch.Module, GLDocBatch.BatchNbr, etc.)
La clé de substitution améliorée pour les nombres entiers
Lorsque j'ai commencé à travailler avec Acumatica, ce schéma de clé m'a intrigué jusqu'à ce que je comprenne vraiment la puissance qui se cache derrière. Ce schéma de clé est composé de 2 parties :
- La clé de substitution réelle, c'est-à-dire l'ID, qui est la clé physique réelle de la table (dans la plupart des cas, un entier de 32 bits, mais parfois un entier de 64 bits).
- La clé naturelle visible, également appelée CD, est la clé visible par l'utilisateur.
L'idée sous-jacente est que la table utilise l'ID entier avec tous ses avantages, tandis que l'utilisateur voit le CD avec tous ses avantages. L'astuce derrière le rideau est que la table physique utilisera l'ID comme clé, mais la DAC (Data Access Class) utilisera le CD comme clé afin d'ordonner les lignes à l'écran par CD au lieu de l'ID. Cela permet à l'utilisateur de naviguer d'un CD à l'autre de manière naturelle tout en conservant la table avec une clé entière. En outre, toutes les clés étrangères faisant référence à la table utiliseront l'ID comme clé physique, mais afficheront à nouveau le CD pour l'utilisateur. Ce schéma est utilisé pour les tables les plus importantes telles que tous les comptes commerciaux(clients, fournisseurs, succursales, sociétés, employés) mais aussi les tables les plus utilisées(articles en stock, entrepôts, comptes GL, sous-comptes, projets, tâches, actifs, itinéraires, etc.)
L'autre caractéristique intéressante de ce système est que l'utilisateur peut également modifier le CD lié à l'ID à l'aide d'une action globale appelée CHANGE ID. Grâce à cette action, l'utilisateur peut "recoder" les clés au fur et à mesure de l'évolution de l'entreprise et de la mise en œuvre.
Convient pour:
- Comptes d'entreprise (principalement parce que vous pourriez vouloir changer la clé plus tard)
- Clé modifiable des tableaux les plus utilisés
Dans les deux GIST suivantes, remarquez que la clé physique de la table (voir[BAccount_PK] dans BAccount Table) est différente de la clé du CAD (voir IsKey = true dans le champ AcctCD ).
Voir l'utilisation de l'action CHANGE ID:
La clé GUID de substitution améliorée
Très similaire à son homologue pour les nombres entiers, ce système de clé est composé de deux parties :
- La véritable clé de substitution, c'est-à-dire l'ID, est la véritable clé physique GUID de la table.
- La clé naturelle visible, généralement une chaîne de caractères, est la clé visible par l'utilisateur.
Convient pour:
- Références aux objets du système
- Identificateurs globaux de valeurs partagées
Dans les deux GIST suivantes, remarquez que la clé physique de la table (voir[WebHook_PK] dans WebHook Table) est différente de la clé du CAD (voir IsKey = true dans le champ Name ).
Contrairement à l'ID/CD typique, les écrans utilisant ce schéma n'ont pas d'action globale pour CHANGER D'ID, bien qu'il soit facile d'en créer une.
La clé de détail
Une clé de détail est une clé utilisée pour une table qui représente les enfants d'une autre table. En règle générale, on prend le tableau récapitulatif (le parent) et on lui ajoute un autre champ clé. Là encore, il existe plusieurs modèles, et nous verrons lequel est privilégié.
Le simple
Une conception simple pour ce type de clé de détail consiste à prendre la clé de résumé et à ajouter la valeur de la clé primaire candidate pour le détail. Cette conception (assez rare) ne doit être privilégiée que si vous souhaitez simplifier l'unicité de votre clé, mais sans bénéficier des avantages de la conception suivante.
Exemples : GITable, CashAccountDetail, État
Le bon
Une bonne conception pour les clés de détail est de prendre la clé Résumé et d'y ajouter une LineNbr. Acumatica privilégie cette solution dans la majorité des cas pour de multiples raisons :
- C'est facile : un simple nombre entier et voilà !
- C'est automatisé : en utilisant l'attribut LineNbrAttribute, vous pouvez générer automatiquement le LineNbr.
- Il est possible de le trier et de le glisser/déposer : si votre DAC implémente ISortable ET que vous utilisez un PXOrderedSelect
- Cela permet de tenir compte des "doublons" lorsque l'on considère la clé primaire candidate qui se trouve généralement sur la ligne de détail(InventoryID par exemple).
- Il autorise les valeurs nulles pour les clés candidates, ce qui rendrait généralement la clé unique.
Exemples : SOLine, POLine, ContractDetail, ARTran, APTran, GLTran, etc.
Le partage
Un autre choix de conception intéressant pour une table détaillée consiste à ne pas utiliser du tout la clé de résumé et à créer une clé composée d'un entier de substitution et à ajouter le NoteID du résumé en tant que "parent". Cette conception permet à la table de détail (telle que CRRelation) d'être partagée par plusieurs autres tables de résumé (telles que CRLead, CRCase, CROpportunity, CRContact, BAccount, etc.) Toutefois, cela nécessite généralement une version spéciale de PXSelect (telle que CRRelationsList) pour gérer la relation avec le parent.
Exemple : CRRelation
Conclusion
J'espère vous avoir aidé à percer le mystère du choix des clés dans Acumatica. Ce qu'il est important de retenir, c'est que le principe de "taille unique" ne s'applique pas à Acumatica. L'utilisation d'une clé naturelle, bien que le schéma le plus facile à utiliser, n'est pas le meilleur choix pour tous les contextes.
Au fil du temps, j'ai remarqué que si vous comprenez les clés d'une application, vous comprendrez beaucoup de choses d'un seul coup d'œil.
Je vous souhaite à tous de bonnes questions.
____________________________
Notes de bas de page
[1] Acumatica étant une application multi-tenant, ses clés de table comprennent presque toujours un champ CompanyID. Par souci de simplicité, nous ne tiendrons pas compte de ce fait.
[2] Le système de numérotation universel des données, abrégé en DUNS ou D-U-N-S, est un système propriétaire développé et géré par Dun & Bradstreet qui attribue un identifiant numérique unique, appelé "numéro DUNS", à une seule entité commerciale.
[3] Code postal du Père Noël (au Canada évidemment).
[4] Célèbre série télévisée américaine destinée aux personnes âgées comme moi.
