Quand une propriété devient-elle objet ?

Bonjour,

J'aurais une question d'ordre extrêmement général: j'appréhende la méthode Merise afin de créer une base de données. Je suis en train de créer un dictionnaire d'objets et je me rend compte qu'il y a peu d'objets naturels qui s'en détachent.

Par exemple j'ai une liste de produits référencés comme suit :

1.1.1
1.1.2
1.2.1
2.1.1

Où le premier chiffre est le code produit et les 2 autres un incrément (ce qui est humainement très compréhensible) de sous-produit et de sous-sous-produit :) style 1.1.1 = produit 1 sous-produit 1 sous sous produit 1. De plus le produit 1 peut se retrouver avec un sous produit et sous sous produit identique pour une autre condition genre :

Site production 1 : 1.1.1
Site production 2: 1.1.1
Dans l'absolu les sous et sous sous produits des 2 sites n'ont rien en commun mais c'est la notation en place...

Je me heurte à un problème qui est :

Dois-je créer une table :

Element
idElement
code produit
code sous produit
code sous sous produit

ou 3 tables :
produit sous-produit et sous sous produit

Bref, je suis en peu perdu et je voudrais savoir comment savoir si une propriété mérite d'être extraite d'une table pour constituer un objet à part entière. De plus un code étant déjà en place, je me demande si je considère qu'un 'sous produit 1' est en relation 1,n avec produit puisque 'sous produit 1' va forcément se retrouver sur tous les produits (et à ce moment 1 sera PK) ou si je mets un identifiant unique PK et '1' serait en quelque sorte le nom du sous produit.

En vous remerciant,

C. Tobini

Bonjour C. Tobini,

Cela fait à peu près deux mois que nous avons discuté des concepts entité-relation et relationnels, notamment des entités faibles, des clés primaires et alternatives.

Et voilà maintenant que les utilisateurs vous font des misères :

Citation:

---

Site production 1 : 1.1.1
Site production 2: 1.1.1
Dans l'absolu les sous et sous sous produits des 2 sites n'ont rien en commun mais c'est la notation en place...

---

J’ai oublié à l’époque de vous préciser qu’au niveau relationnel, l’utilisateur ne doit avoir aucun pouvoir de décision quant aux valeurs prises par les clés primaires. Ça n’est pas une loi, mais un bon moyen de ne pas faire de cauchemars.

Supposons donc qu’un produit ait pour valeur de clé primaire <1>, que l’un de ses sous-produits ait pour valeur de clé primaire <1,1> et que l’un des sous-sous-produits ait pour valeur de clé primaire <1,1,1>.
Supposons encore que le même produit ait un autre sous-produit de clé primaire <1,2> et un sous-sous-produit de clé primaire <1,2,1>.

Comme l’utilisateur veut gérer son propre système de codification, vous définissez une clé alternative à cet effet (que vous pouvez qualifier informellement de "clé utilisateur" dans les discussions). Et comme chaque site de production veut voir la valeur 1.1.1 quand nous avons de notre côté <1,1,1> et <1,2,1>, pour dédoublonner, vous faites participer la clé primaire du site de production à la clé alternative (qui sera donc simultanément clé étrangère par en relation avec la table des sites), laquelle est le point d’entrée dans le système pour l’utilisateur, mais en aucun cas celui-ci n’aura accès à la clé primaire.

Concernant les produits, les sous-produits, etc., l’identifiant au niveau Merise correspond évidemment à votre clé primaire.

Au niveau SQL, pour faire prendre en compte les clés alternatives, vous utilisez la clause UNIQUE de l’instruction Create Table.

Evitez les cauchemars...

Bonjour fsmrel et merci encore de votre intervention,

J'ai relu les articles en question du forum et je lis sur le net des articles relatifs à la construction d'une base de données, j'avoue qu'il est plus que temps d'investir dans un ouvrage de qualité (vous en citez 2 que je vais me procurer).

Mon problème réside dans le fait que j'assimile des notions que j'avais survolées en formation universitaire et je tente d'appréhender la démarche complète de création des entités-types (dépendances fonctionnelles, élimination de la redondance...) ce qui n'est pas simple en raboutant les données de cours du net qui quelquefois se contredisent...

Merci pour les infos concernant ce post, je vois désormais une piste :-)

J'aurais 2 petites questions supplémentaires, j'ai eu des exemples sur le net des axiomes d'Armstrong mais ce n'est pas clair, je voudrais m'appuyer sur un exemple que vous avez donné dans un précédent post :

Tout d'abords (un partie de) la théorie que j'ai trouvé sur le net:

augmentation : si X -> Y, alors XZ -> Y pour tout groupe Z d'attributs appartenant au schéma de relation.

J'avoue ne pas saisir la notion de Z appartenant au schéma relation, la participation de Z à cette relation et son intégration à X. Il y a également sur le net une variante si X -> Y, alors XZ -> YZ

décomposition : si X -> Y et Z contenu dans Y, alors X -> Z.

Idem pour Z contenu dans Y.

Vous donnez un exemple sur un cas concret :

DF1 : {Course, Jockey} -> Cheval
DF2 : {Course, Jockey} -> Dossard
DF3 : {Course, Cheval} -> Jockey
DF4 : {Course, Cheval} -> Dossard
DF5 : {Course, Dossard} -> Jockey
DF6 : {Course, Dossard} -> Cheval

déduction selon les axiomes d'Armstrong :

DF7 : {Jockey, Cheval, Course} -> Dossard ____ (axiome d’augmentation et règle de décomposition)
DF8 : {Course, Dossard, Cheval} -> Jockey ____ (idem)
Etc.

Etant un peu perdu dans ces notions pourriez vous svp me guider sur l'application des axiomes pour cet exemple ?

Une petite question également sur les couvertures minimales, j'ai cet exemple d'un cours PDF:

http://img143.imageshack.us/img143/447/couvminrl2.jpg

Autant je comprends la décomposition, autant je ne saisis pas comment procéder à l'élimination et le remplacement en question pourriez vous me guider également vers la méthode à appliquer (pas forcément la réponse mais la démarche).

En vous remerciant,

C. Tobini

Bonsoir,

Citation:

---

Envoyé par ctobini

il est plus que temps d'investir dans un ouvrage de qualité (vous en citez 2 que je vais me procurer).

---

Quels sont-ils, que je confirme ?

Citation:

---

Envoyé par ctobini

je tente d'appréhender la démarche complète de création des entités-types (dépendances fonctionnelles, élimination de la redondance...) ce qui n'est pas simple en raboutant les données de cours du net qui quelquefois se contredisent...

---

En gros, il y a deux approches : une approche descendante et une approche ascendante.
La première fait plutôt appel à la perception que l’on du cas à modéliser, et repose pas mal sur l’intuition, sans trop chercher à dépiauter finement l’information (on aboutit à un dossier de conception générale, avec un MCD lisible par tous, montrant les choses à haute altitude). Au fur et à mesure que l’on sent que l’on est sur la bonne voie, on approfondit, pour aboutir à une conception beaucoup plus détaillée.
La seconde approche consiste plutôt à mettre à plat l’information, en l’atomisant au maximum (attributs) au sein d’une immense relation dite universelle, abordée selon une approche anatomique, mécanique, qu’il faut normaliser (4e forme normale au minimum) à partir des contraintes traduites en dépendances fonctionnelles (traduction en fait des règles de gestion des données), pour produire les entités-types... Cette seconde approche requiert une excellente connaissance de la théorie relationnelle et des techniques associées (utilisation des axiomes d’Armstrong, détermination de la couverture minimale, des clés candidates, vérification de la 5e forme normale etc.), toutes choses nécessitant du temps, une rigueur extrême, une grande habitude et... beaucoup d’aspirine). Elle est beaucoup moins pratiquée, car difficile, longue et source de grosses migraines (voyez l’exercice amorcé avec le document PDF évoqué ci-dessous, et qui ne donne lieu au final qu’à un modèle plutôt riquiqui...)
Le mieux est de panacher : approche descendante, vérification de la normalisation (BCNF) pour éliminer les redondances, etc., révision avec les utilisateurs des modèles échafaudés pour s’assurer qu’on ne fait pas fausse route. La technique du yoyo en quelque sorte, mais mettant en jeu des ensembles de taille raisonnable. A noter que l’approche descendante, menée sérieusement, élimine pratiquement tous les cas de viols potentiels de 4e et 5e formes normales (aux innocents les mains pleines !)

L’exercice est difficile, et l’on ne peut être vraiment rassuré que lorsque le projet concerné est en production et fonctionne (ce qui au demeurant n’est pas si facile à prouver...)

Citation:

---

Envoyé par ctobini

augmentation : si X -> Y, alors XZ -> Y

---

Ceci n’est pas précisément l’axiome d’augmentation, lequel est le suivant :

Soient X, Y et Z des sous-ensembles dont les éléments sont des attributs d’une relation R.

Si X-> Y alors XZ -> YZ

XZ représente ici l’union (au sens de la théorie des ensembles) de X et Z (X U Z). Même principe pour YZ.
Par exemple, si A, B, C, D, E, F, G, H, I, J sont des attributs de R, avec :

X = {A, B, C}
Y = {D, E, F, G}
Z = {H, I, J}

On peut lire les choses ainsi :

Si {A, B, C} -> {D, E, F, G}
Alors {A, B, C, H, I, J} -> {D, E, F, G, H, I, J}

Attention, Z n’est pas intégré à X, en fait on procède à l’union de X et Z. Même chose dans le cas de Y.

Le prétendu axiome d’augmentation : si X -> Y, alors XZ -> Y n’est qu’une règle inférée de cet axiome et de la règle de décomposition :

Si X -> Y alors XZ -> YZ ____ (augmentation)
Si XZ -> YZ alors XZ -> Y ___ (décomposition)

Citation:

---

Envoyé par ctobini

pourriez vous svp me guider sur l'application des axiomes pour cet exemple ?

---

Cas de DF7 :

Je rappelle la règle d’Union, inférée des axiomes d’Armstrong, selon laquelle :
si X -> Y et X-> Z alors X -> YZ. (J’avais probablement oublié de mentionner cette règle).

On part de DF3 et DF4

DF3 : {Course, Cheval} -> {Jockey}
DF4 : {Course, Cheval} -> {Dossard}

(Les dépendants, c’est-à-dire les attributs situés à droite étant singletons, je ne les ai pas mis entre crochets, mais par précaution, je le fais ici.)

Par application de la règle d’Union :

{Course, Cheval} -> {Jockey, Dossard}

Puis, par augmentation :

{Course, Cheval, Jockey} -> {Jockey, Dossard}

Et par décomposition :

{Course, Cheval, Jockey} -> {Dossard}

Concernant l’exemple du cours (fichier PDF) :

La couverture minimale (je préfère irréductible, qualificatif plus approprié) doit être débarrassée de la DF

CE -> A

car on sait la produire à partir de C -> A (donné) à qui l'on fait subir une augmentation puis une décomposition :

1)____CE -> AE (augmentation)
2)____CE -> A (décomposition)

Pour montrer que CG -> B peut être inférée des autres DF, on peut utiliser une autre règle inférée des axiomes, celle de Composition, selon laquelle :

Si X -> Y et Z -> T alors XZ -> YT.

Ainsi, par union de CG -> D et C -> A, on obtient :

CG -> AD

puis par augmentation (par C) :

CG -> ACD

et par transitivité (puisque ACD -> B est donnée) :

CG -> B

Etc.

Bonjour,

Merci de votre réponse, j'avoue ne pas avoir le temps dans l'immédiat de lire la totalité de votre réponse, je le ferai d'ici ce week-end. Pour info, les 2 ouvrages en question sont :

'The Relational Database Dictionary' (chez O'Reilly) et 'Introduction aux Bases de Données' (Chris Date) en version française donc :)

Merci effectivement de me confirmer ceci, car je pense passer commande ce week-end, dans la mesure où le second ouvrage a environ 8 jours de délai de livraison. Le premier est dispo en version PDF en revanche, pratique.

Merci et à bientôt,

C. Tobini

Bonjour,

Les ouvrages sont les bons.
Concernant 'The Relational Database Dictionary' le bouquin tient dans la poche (18x10,5 cm) ce qui est pratique quand on prend les transports en commun.
Pour l'autre ouvrage, je ne l'ai qu'en version anglaise. Quoi qu'il en soit, faites attention à commander la dernière édition (la 8e).

Bonne lecture et bon week-end.

Bonjour et merci pour vos réponses concernant le PDF que j'ai donné en exemple, c'est compris maintenant.

Merci également (et surtout puique c'est l'intitulé du post :) ) pour votre explication quant au problème que j'ai actuellement.

Voici ce que vous proposez :

Citation:

---

Supposons donc qu’un produit ait pour valeur de clé primaire <1>, que l’un de ses sous-produits ait pour valeur de clé primaire <1,1> et que l’un des sous-sous-produits ait pour valeur de clé primaire <1,1,1>.
Supposons encore que le même produit ait un autre sous-produit de clé primaire <1,2> et un sous-sous-produit de clé primaire <1,2,1>.

Comme l’utilisateur veut gérer son propre système de codification, vous définissez une clé alternative à cet effet (que vous pouvez qualifier informellement de "clé utilisateur" dans les discussions). Et comme chaque site de production veut voir la valeur 1.1.1 quand nous avons de notre côté <1,1,1> et <1,2,1>, pour dédoublonner, vous faites participer la clé primaire du site de production à la clé alternative (qui sera donc simultanément clé étrangère par en relation avec la table des sites) [...]

---

D'après vos indications est-ce donc une organisation telle que :

http://img115.imageshack.us/img115/4...eenshotfd8.png

avec une clause UNIQUE (idProduit, idSous_produit, idSous_sous_produit, idSite) sur la table Sous_sous_produit ?

que vous proposez ?

C. Tobini

Bonsoir,

Je vous soumets une solution.

Je rappelle le principe que je propose, car à mon sens fondamental concernant les clés primaires :

Les utilisateurs n’ont pas accès à ces clés. En tout cas, ils ont interdiction d’en fournir les valeurs et de remplacer celles-ci : il en va de la stabilité de la base de données, de la performance des requêtes et des traitements. A la limite, on peut les autoriser à voir ces clés primaires, dans la mesure où elles sont lisibles.

En contrepartie, ces utilisateurs sont habilités à définir leurs propres clés, lesquelles sont qualifiées d’alternatives. Ces clés sont pour eux des points d’entrée dans le système et ils en déterminent les valeurs comme bon leur semble : en l’occurrence, appellera une telle clé : clé utilisateur. Ceci se veut l’écho de ce que vous écrivez :

Citation:

---

Site production 1 : 1.1.1
Site production 2: 1.1.1
Dans l'absolu les sous et sous sous produits des 2 sites n'ont rien en commun mais c'est la notation en place...

---

C’est-à-dire que si les produits ne sont pas spécifiques aux sites (ils le seraient que l’approche serait la même, toutes choses égales par ailleurs), les sous-produits le sont. Du point de vue de la modélisation, cela revient à dire qu’il doit y avoir une relation entre le sous-produit et le site. Du même coup, le sous-sous-produit hérite de cette relation.

De manière pragmatique, une clé utilisateur peut être composée d’éléments appartenant à l’utilisateur, modifiables par lui, et d’éléments complémentaires inaltérables, permettant au besoin de dédoublonner les jumeaux parfaits.

Par exemple, un site a pour clé primaire {Id_Site} et pour clé alternative {User_Site}. Le système attribue les valeurs pour {Id_Site}. L’utilisateur habilité attribue les valeurs {User_Site} pour les différents sites (1 ou "S1", etc.) Cette clé alternative n’est évidemment à mettre en œuvre que si fonctionnellement le besoin s’en fait sentir.

De la même façon, un produit a pour clé primaire {Id_Produit} et le système en attribue les valeurs. L’utilisateur habilité définit les valeurs {User_Produit} pour les différents produits, comme il l’entend. Il est vrai que depuis toujours, les utilisateurs aiment bien maîtriser les valeurs des "codes-produits", "codes-sous-produits", etc.

Un sous-produit a pour clé primaire {Id_Produit, Id_Site, Id_Sous_Produit} et le système attribue les valeurs de Id_Sous_Produit, relativement à {Id_Produit, Id_Site}. L’utilisateur habilité définit les valeurs {User_Sous_Produit} pour les différents sous-produits, comme il l’entend.

La clé utilisateur (alternative, rappelons-le) est composée du couple {User_Sous_Produit, Id_Site} : Deux utilisateurs de sites différents peuvent donc fournir la même valeur pour des sous-produits différents, pas de problème, l’attribut Id_Site permet de garantir l’unicité des valeurs de la clé utilisateur. Rappelons que l’utilisateur d’un site donné n’a besoin de manipuler que le seul attribut User_Sous_Produit, l’attribut Id_Site lui est indifférent (contrairement à ce qui se passe pour le système).

Un sous-sous-produit a pour clé primaire {Id_Produit, Id_Site, Id_Sous_Produit, Id_Sous_Sous_Produit} et le système attribue les valeurs de Id_Sous_Sous_Produit relativement à (Id_Produit, Id_Site, Id_Sous_Produit). L’utilisateur habilité définit les valeurs {User_Sous_Sous_Produit} pour les différents sous-sous-produits. Une fois de plus, la présence de l’attribut Id_Site permet de résoudre les problèmes de gémellité de sous-sous-produits entre sites :

La clé utilisateur est composée du couple {User_Sous_Sous_Produit, Id_Site} : Deux utilisateurs de sites différents peuvent donc fournir la même valeur pour des sous-sous-produits différents, pas de problème, l’attribut Id_Site permet de garantir l’unicité des valeurs de la clé utilisateur. Rappelons que l’utilisateur d’un site donné n’a besoin de manipuler que le seul attribut User_Sous_Produit, l’attribut Id_Site lui est indifférent (comme dans le cas du sous produit).

Le MLD ci-dessous résume graphiquement la situation. Comme DB Designer ne semble pas connaître le concept de clé alternative, j’ai ajouté à la main la clause UNIQUE pour les clés utilisateurs (Instructions Create Table à la suite du graphique).

http://img440.imageshack.us/img440/3...tsmldv2om2.png

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

------------------------------------------- -- Base de données Produits (SQL Server) -- ------------------------------------------- CREATE TABLE Site ( Id_Site INTEGER NOT NULL, Nom_Site CHAR(48) NOT NULL, User_Site CHAR(16) NOT NULL, PRIMARY KEY(Id_Site), UNIQUE(User_Site) ); CREATE TABLE Utilisateur ( Id_User INTEGER NOT NULL, Id_Site INTEGER NOT NULL, Nom_User CHAR(48) NOT NULL, PRIMARY KEY(Id_User), FOREIGN KEY(Id_Site) REFERENCES Site(Id_Site) ON DELETE NO ACTION ); CREATE TABLE Produit ( Id_Produit INTEGER NOT NULL, Nom_Produit CHAR(48) NOT NULL, User_Produit CHAR(16) NOT NULL, PRIMARY KEY(Id_Produit), UNIQUE(User_Produit) ); CREATE TABLE Sous_Produit ( Id_Produit INTEGER NOT NULL, Id_Site INTEGER NOT NULL, Id_Sous_Produit INTEGER NOT NULL, Nom_Sous_Produit CHAR(48) NOT NULL, User_Sous_Produit CHAR(16) NOT NULL, PRIMARY KEY(Id_Produit, Id_Site, Id_Sous_Produit), UNIQUE(User_Sous_Produit, Id_Site), FOREIGN KEY(Id_Produit) REFERENCES Produit(Id_Produit) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY(Id_Site) REFERENCES Site(Id_Site) ON DELETE NO ACTION ); CREATE TABLE Sous_Sous_Produit ( Id_Produit INTEGER NOT NULL, Id_Site INTEGER NOT NULL, Id_Sous_Produit INTEGER NOT NULL, Id_Sous_Sous_Produit INTEGER NOT NULL, Nom_Sous_Sous_Produit CHAR(48) NOT NULL, User_Sous_Sous_Produit CHAR(16) NOT NULL, PRIMARY KEY(Id_Produit, Id_Site, Id_Sous_Produit, Id_Sous_Sous_Produit), UNIQUE(User_Sous_Sous_Produit, Id_Site), FOREIGN KEY(Id_Produit, Id_Site, Id_Sous_Produit) REFERENCES Sous_Produit(Id_Produit, Id_Site, Id_Sous_Produit) ON DELETE CASCADE );

---

Requêtes

La contrepartie avec ce système est que lorsqu’un utilisateur accède par le biais de ses propres clés à un sous-produit ou à un sous-sous-produit, (ou le crée, le modifie, ou le supprime) on doit toujours fournir dans les requêtes la valeur du site Id_Site associé au sous-produit (normalement, un utilisateur est attaché à un site). Sinon, gare à la casse ! Mais c'est le prix à payer, si pour deux sites on peut avoir même User_Sous_Produit ou même User_Sous_Sous_Produit...

Observations

La modélisation que je vous ai proposée permet de garantir que pour un site donné, on ne pourra pas avoir deux sous-produits ayant même valeur (User_Sous_Produit), même chose pour les sous-sous-produits. Si vous estimez que la propagation de l’attribut Id_Site engendre un système trop sophistiqué, vous pouvez vous en dispenser, mais dans ces conditions vous ne pourrez plus contrôler la présence de sous-sous-produits doublons, sinon par la mise en œuvre de mécanismes du genre triggers ou procédures stockées. A vous de réfléchir.

Pour information, j’ai écrit :

Citation:

---

Envoyé par fsmrel

Si vous estimez que la propagation de l’attribut Id_Site engendre un système trop sophistiqué, vous pouvez vous en dispenser, mais dans ces conditions vous ne pourrez plus contrôler la présence de sous-sous-produits doublons, sinon par la mise en œuvre de mécanismes du genre triggers ou procédures stockées.

---

Le Standard SQL permet de définir des contraintes. Ainsi, au cas où vous ne propageriez pas l'attribut Id_Site dans Sous_Sous_Produit, pour interdire les doublons des codes de l‘utilisateur concernant les sous-sous-produits, vous disposez à cet effet de l’instruction Create Assertion. Exemple (non testé) :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Create Assertion Assert_SSP Check (Not exists ( Select SP.Id_Site, SSP.User_Sous_Sous_Produit From Sous_Produit As SP, Sous_Sous_Produit As SSP Where SP.Id_Produit= SSP.Id_Produit And SP.Id_Sous_Produit= SSP.Id_Sous_Produit Group by Id_Site, User_Sous_Sous_Produit Having Count (*) > 1 ) ) ; -- Ou encore Create Assertion Assert_SSP Check (Unique ( Select SP.Id_Site, SSP.User_Sous_Sous_Produit From Sous_Produit As SP, Sous_Sous_Produit As SSP Where SP.Id_Produit= SSP.Id_Produit And SP.Id_Sous_Produit= SSP.Id_Sous_Produit ) ) ;

---

Mais à ce jour, les SGBD ne proposent pas cette instruction. Dommage...

Bonjour et désolé de ma réponse un peu tardive,

Merci beaucoup pour cet exemple très détaillé, c'est agréable pour la compréhension !

J'aurais besoin que vous validiez quelques petits points de ce que je pense avoir compris d'un point de vue fonctionnel de votre schéma :

Pour rappel :

http://img444.imageshack.us/img444/6...ldv2om2oy1.png

- les PK comme Id_site son donc gérés par le système et s'auto-incrémentent

- tel que je le vois, User_site est optionnel et sert à distinguer diverses implications sur un même site (ex: Nom_cite est une ville et User_site est le secteur de rattachement d'un utilisateur si plusieurs secteurs).

- Dans mon exemple de Produit 18.1.2 si ce produit est 'Carte mère' par exemple, 'Carte mère' sera Nom_Produit de la table Produit et '18' sera User_Produit (et ainsi de suite pour Sous- et Sous-sous-Produit).

De cette manière si 'Carte mère' et '18' appartient déjà au schéma, le Produit '18' sera unique et chaque utilisateur en fonction du site impliqué pourra décliner '18' en Sous- et Sous-sous-Produit en liant par clés alternatives les 3 tables.

Est-ce correct ?

J'ai effectivement jeté un oeil sur les assertions, c'est dommage que ça ne soit pas implémenté...

En vous remerciant,

C. Tobini

Bonsoir C. Tobini,


Vous écrivez :

Citation:

---

les PK comme Id_site son donc gérés par le système et s'auto-incrémentent

---

Les PK comme Id_Site sont effectivement gérées par le système, mais au sens large (vous faites partie du système) : elles peuvent être auto-incrémentées, mais si cela ne vous convient pas, vous pouvez affecter les valeurs vous-même, par requête ou par programme. Vous pouvez utiliser des timestamps, des entiers, par incrément, ou pourquoi pas par hachage si la situation l’exige (contentions), tous les coups sont permis. La règle de base est qu’une valeur de clé primaire ne change pas, d’où la nécessité que l’utilisateur n’ait aucun pouvoir pour changer la valeur d’une telle clé.

Citation:

---

tel que je le vois, User_site est optionnel et sert à distinguer diverses implications sur un même site (ex: Nom_cite est une ville et User_site est le secteur de rattachement d'un utilisateur si plusieurs secteurs).

---

Je n’ai pas tout compris et j’ai un point d’interrogation au-dessus de la tête... Un utilisateur peut-il être en relation avec plusieurs sites ? Si oui, il faut le modéliser. Merci d’illustrer votre propos par l’exemple.

Citation:

---

De cette manière si 'Carte mère' et '18' appartient déjà au schéma, le Produit '18' sera unique et chaque utilisateur en fonction du site impliqué pourra décliner '18' en Sous- et Sous-sous-Produit en liant par clés alternatives les 3 tables.

---

Là aussi, pourriez-vous donner un exemple complet d’affectation des valeurs par les utilisateurs, concernant les sous-produits et les sous-sous-produits ?

Quant à lier les 3 tables par clés alternatives (AK), je rappelle que les seuls liens prévus sont de type PK-FK, sinon, si vous mettez les AK dans le coup, vous redonnez à l’utilisateur la possibilité de modifier les liens, ce qui viole la règle de base évoquée ci-dessus...

Que l’utilisateur modifie les valeurs des AK, c’est son droit, elles lui appartiennent, mais pas les liens d’intégrité définis par vous-même.

Bon courage pour cette reprise,

Fsmrel

Bonjour,

Je vais donner un exemple concret, en faisant table rase de mon exemple précédent, j'ai apparement mal compris certaines choses :?

Prenons l'exemple d'un produit 16.18.36, produit n°16, son Sous-Produit 18 et Sous-Sous-Produit 36.

Voici la représentation instinctive :

http://img407.imageshack.us/img407/5...eenshotlo1.png

Le produit 16 est commun à tous les sites (bien que certains sites puissent ne pas le 'traiter' et donc ne pas avoir de Sous et Sous-sous-Produits associés). Les Sous et Sous-sous-Produits sont uniques pour un site, un Utilisateur peut donc saisir Sous-Produit 18 et Sous-sous-produit 36 ainsi que les noms associés.

Les points que je ne saisi pas sont les suivants :

- La notion de 'point d'entrée' pour les clés utilisateurs comme User_Site. D'un point de vue fonctionnel, si un utilisateur est rattaché à un seul site, à quoi sert d'attribuer un User_Site unique ? Par ailleur si l'utilisateur peut modifier son User_Site et si un Id_Site identifie un site et est PK si je prends cet exemple :

http://img244.imageshack.us/img244/9...enshot7lh0.png

L'utilisateur Alpha étant également sur Site_France (Id_Site 54) veut saisir son User_Site, la PK est rompue en cas de tentative d'ajout. A moins que :

http://img154.imageshack.us/img154/2...enshot4by3.png

Pour un site donné, le fait d'avoir plusieurs User_Site necéssite d'avoir plusieurs tables (mais ça m'étonnerais, je pense que j'interpréte mal...).

- Les utilisateurs ne pouvant agir sur les clés primaires, selon votre exemple les PK Produits, Sous-Produit et Sous-sous-Produit doivent-ils à ce moment être fixés par le système et l'exemple 16.18.36 sont alors saisis par l'utilisateur en tant que clé utilisateur ?

Par exemple :

http://img187.imageshack.us/img187/4...enshot1tz8.png

A ce moment le système gère les clés (ce qui est plus fiable) et les clés utilisateurs permettent de maintenir l'unicité entre Produit, Sous-Produit, Sous-sous-Produit et Site, reflétant la réalité.

En vous remerciant,

C. Tobini

Bonsoir C. Tobini,


Avant de poursuivre le fil de notre histoire, je reprends un exemple que j’utilise de temps à autre sur ce forum, afin de montrer l’importance du caractère invariant des identifiants des entités-types et donc des clés primaires au niveau tabulaire.

Les concepteurs d’une grande banque française avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type Entreprise). Au niveau tabulaire, par le jeu des liens clé primaire - clé étrangère, l’attribut NoSiren se propageait dans une trentaine de tables. Balek ! Le numéro Siren est établi par un organisme extérieur à la banque, à savoir l’INSEE. J’avais fait observer que l’INSEE envoyait tous les mois les nombreux correctifs modifiant le Siren des nouvelles entreprises (10% d’entre elles à peu près) et que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit). Une trentaine de tables à mettre à jour quant à leur valeur de clé primaire et/ou étrangère induit une activité de mise-à-jour excessive et en plus, délicate à ordonnancer. L’identifiant de l’entreprise fit donc l’objet d’un nouvel attribut, non porteur d’information, artificiel et invariant, Entreprise_Id, destiné à être clé primaire de la table Entreprise et propagé en conséquence dans les autres tables, en lieu et place de l’attribut NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus une trentaine, mais la seule table Entreprise. L’utilisateur Tartempion avait bien évidemment toujours accès à l’attribut NoSiren, devenu clé alternative de cette table (et n’ayant donc pas perdu sa propriété d’unicité), l’organisation technique ayant lieu sous le capot, de façon totalement transparente pour Tartempion.

Concernant votre représentation "instinctive" :

http://img407.imageshack.us/img407/5...eenshotlo1.png

Figure 1 - La clé primaire n’est pas valorisée par le système, mais par l’utilisateur.

Si les valeurs prises par le produit, le sous-produit et le sous-sous-produit (respectivement 16, 18, 36) ne sont pas remplaçables et respectent donc le principe d’invariance que je vous propose, alors les attributs User_Produit, User_Sous_Produit et User_Sous_Sous_Produit sont inutiles. En revanche, si tel n’est pas le cas et que l’utilisateur veut pouvoir remplacer ces valeurs à son gré, alors changement de chanson. C’est le système qui doit affecter les valeurs prises par les attributs Id_Produit, Id_Sous_Produit et Id_Sous_Sous_Produit, tandis que les valeurs 16, 18 et 36 sont hébergées par User_Produit, User_Sous_Produit et User_Sous_Sous_Produit, attributs dont l’utilisateur est libre de faire ce qu’il veut.

Un exemple d’affectation des valeurs par le système :

Code:

---

1 2 3 4 5 6 7 8 9 10

Id_Produit Id_Sous_Produit Id_Sous_Sous_Produit ... 1000 1 1 1000 1 2 1000 1 3 1000 2 1 1000 2 2 1000 2 3 ...

---

Concernant votre deuxième figure :

http://img244.imageshack.us/img244/9...enshot7lh0.png

Figure 2 - Sites et utilisateurs

dans mon message du 24 avril, j’ai écrit :

Citation:

---

... L’utilisateur habilité attribue les valeurs {User_Site} pour les différents sites (1 ou "S1", etc.) Cette clé alternative n’est évidemment à mettre en œuvre que si fonctionnellement le besoin s’en fait sentir.

---

Le problème du banquier est une illustration de ce que j’ai écrit à propos du besoin fonctionnel : on est obligé de mettre en oeuvre l’attribut NoSiren. Mais, je ne sais pas si dans votre cas il en va de même pour l’attribut User_Site (et bien sûr tous les autres User_xyz).

Supposons que, pour désigner le site "Site France", les utilisateurs n’aient pas d’état d’âme à utiliser la valeur "54" attribuée par le système (attribut Id_Site) et donc que la valeur "S1" (attribut User_Site) ne leur soit d’aucune utilité. L’attribut Id_Site suffit et l’attribut User_Site doit disparaître. Mais une fois de plus, ne perdons pas de vue le caractère invariant que nous avons voulu pour la clé primaire : pour reprendre votre exemple, si le système a attribué au site "Site France" la valeur "54", c’est définitif et la valeur "55" ne pourra jamais la remplacer.

Supposons maintenant que les utilisateurs ne puissent pas se contenter de cette solution, parce que depuis toujours, ils utilisent la valeur "S1" en association avec "Site France" et que pour eux il soit hors de question d’utiliser la valeur "54" qui leur serait imposée par l’informaticien. Supposons encore que vous négociiez avec eux et qu’ils acceptent le caractère invariant de "S1" : User_Site peut alors devenir clé primaire et c’est l’attribut Id_Site qui peut disparaître du modèle. Mais vous les connaissez, ils finissent toujours par demander et justifier des modifications et "S1" finira un jour par être remplacé par "S5", en violation du principe d’invariance convenu avec vous : il est de votre devoir de leur démontrer pourquoi "S1" ne sera jamais remplacé pour désigner "Site France". S’ils vous suivent, faites-leur signer leur accord (en fait le dossier de conception générale).

Au contraire, si vos utilisateurs tiennent absolument à faire ce qu’ils veulent de leur "S1", le plus sage est donc bien de définir Id_Site comme clé primaire prenant la valeur définitive "54" et User_Site comme clé alternative, prenant la valeur "S1". Ainsi, comme dans le cas du numéro Siren, si la clé User_Site, passe à "S5" par décision unilatérale du chef des utilisateurs, peu importe, seule la table Site est impactée et il n’y n’a aucune incidence sur les autres tables. La seule contrainte qu’il ait à respecter est l’unicité des valeurs prises par User_Site, attribut qui est donc un point d’entrée dans le système : comme dans le cas du numéro Siren, j’entends par point d’entrée dans le système la possibilité qu’ont les utilisateurs d’accéder aux données d’une ligne de la table Site (et aux tables qui lui sont liées, directement ou non) à partir d’une valeur qu’ils fournissent pour User_Site.

Par exemple si le chef des utilisateurs pose la question « Quels sont les noms des produits et sous-produits pour le site S1 » ? L’équivalent en SQL sera le suivant :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Select Nom_Produit, Nom_Sous_Produit From Site NATURAL JOIN Sous_Produit NATURAL JOIN Produit Where User_Site = 'S1' ; ou encore : Select Nom_Produit, Nom_Sous_Produit From Site As S, Produit As P, Sous_Produit As SP Where User_Site = 'S1' And S.Id_Site = SP.Id_Site And SP.Id_Produit = P.Id_Produit ; ou n’importe quelle expression équivalente, façon Inner join.

---

Vous remarquerez qu’il n’est pas fait mention dans ces requêtes de la valeur de la clé primaire {Id_Site}, à savoir "54", en effet seul le SGBD a besoin de s’en servir lors des opérations de jointure. J’espère avoir clarifié le rôle de l’attribut User_Site.

J’aurai peut-être dû mettre en évidence l’expression "utilisateur habilité". En effet, ça n’est pas n’importe quel utilisateur qui a le pouvoir de remplacer "S1" par "S5" pour l’attribut User_Site, sinon ça serait la pagaille. L’utilisateur habilité est une personne de l’entreprise (ou une petite cellule), appelons-la Le Chef, seule autorisée à effectuer la modification et ce, quel que soit le site touché.

Ainsi, on ne peut pas trouver au même moment la situation que vous décrivez dans la figure 2 ci-dessus : à l’instant t1, Mr_Lambda et Mr_Alpha font partie du même site, identifié par sa clé primaire Id_Site = "54" (et sa clé alternative User_Site = "S1"). Si à t2 Le Chef a remplacé "S1" par "S5", alors ce site est identifié par Id_Site = "54" (et User_Site = "S5") et par voie de conséquence, Mr_Lambda et Mr_Alpha font encore partie du même site. On ne peut pas avoir simultanément des valeurs différentes de User_Site pour Mr_Lambda et Mr_Alpha : la figure 2 représente une situation impossible.

En conséquence, vous obtiendrez le même résultat (le nom des produits et des sous-produits auxquels ont accès nos deux utilisateurs), quelle que soit la requête que vous soumettiez :

Code:

---

1 2 3 4 5 6 7 8 9 10

Select Nom_Produit, Nom_Sous_Produit From Utilisateur NATURAL JOIN Sous_Produit NATURAL JOIN Produit Where Nom_Utilisateur = 'Mr_Alpha' ; ou : Select Nom_Produit, Nom_Sous_Produit From Utilisateur NATURAL JOIN Sous_Produit NATURAL JOIN Produit Where Nom_Utilisateur = 'Mr_Lambda' ;

---

Citation:

---

Les utilisateurs ne pouvant agir sur les clés primaires, selon votre exemple les PK Produits, Sous-Produit et Sous-sous-Produit doivent-ils à ce moment être fixés par le système et l'exemple 16.18.36 sont alors saisis par l'utilisateur en tant que clé utilisateur ?

---

La réponse est affirmative.

Pour résumer

Scénario 1 :

L’utilisateur accepte le principe de l’attribution des valeurs des clés primaires par le système (et le principe de l’invariance de ces clés) et accepte aussi l’abandon des clés alternatives. On arrive au schéma :

http://img517.imageshack.us/img517/6...cenariokj9.jpg
___________Figure 3

Scénario 2 :

L’utilisateur n’accepte pas le principe de l’attribution des valeurs des clés primaires pour les produits, sous-produits et sous-sous-produits par le système mais il accepte le principe de l’invariance : les valeurs proposées par l’utilisateur peuvent être utilisées pour les clés primaires et les clés alternatives n’ont plus lieu d’être. (On suppose que la clé alternative User_Site ne l’intéresse pas (quoi qu’il en soit, cela ne pose pas de problème)). On arrive au schéma :

http://img391.imageshack.us/img391/6...cenariomq9.jpg
___________Figure 4

Scénario 3 :

L’utilisateur n’accepte pas le principe de l’attribution des valeurs des clés primaires pour les produits, sous-produits et sous-sous-produits par le système et il rejette aussi le principe de l’invariance : les clés alternatives doivent être mises en oeuvre. (On suppose toujours que la clé alternative User_Site ne l’intéresse pas). On arrive au schéma :

http://img260.imageshack.us/img260/6...cenariotn2.jpg
_______Figure 5

Dans ces conditions, la clause Unique utilisée pour l’instruction Create Table Sous_Produit est à aménager (cf. mon message du 24 avril), sinon contrairement à ce qui a été dit au départ, pour deux sites distincts l’attribut User_Sous_Produit ne pourrait prendre la même valeur ("18" dans l’exemple). On codera donc :

UNIQUE (User_Produit, User_Sous_Produit, Id_Site)

Même principe pour la table Sous_Sous_Produit :

UNIQUE (User_Produit, User_Sous_Produit, User_Sous_Sous_Produit, Id_Site)

On peut aussi obtenir le schéma :
http://img178.imageshack.us/img178/1...cenariobi8.jpg
__________Figure 6

Mais il est à éviter, car que se passe-t-il si l’utilisateur remplace la valeur 16 par la valeur 87 pour le produit "AFX" ? (Table Produit)
On va se retrouver avec une valeur de produit orpheline au niveau Sous_Produit : "16.18" et qu’il faudra donc remplacer par "87.18", mais comment faire, sinon monter une usine à gaz, comme l’avait prévu initialement la banque confrontée au problème du numéro Siren ?

A l’occasion de cette promenade, j’espère vous avoir au passage donné quelques éléments vous permettant de débattre avec les utilisateurs quant aux conséquences du choix des clés...

Bonjour et désolé pour ce retard, projets obligent !

Un grand merci pour ces explications, je cerne beaucoup mieux les différentes options que je peux envisager et discuter avec les utilisateurs, et je vois qu'il y a pas mal de concessions possibles avec eux dans l'organisation des données !

Une chose est certaine dans tout ceci, cette option n'est pas envisageable pour des raisons assez évidentes de changement d'humeur des utilisateurs :D

http://img512.imageshack.us/img512/8...eenshoteq4.png

Je tiens également a vous préciser que votre implication et la qualité de vos explications sont extrémement appréciables et rares !

Encore merci et peut-être à bientôt pour un futur blocage 'databasesque' :lol:

Bonne journée,

C. Tobini

Bonsoir C. Tobini,

Je vous remercie à mon tour. Ce fut un exercice intéressant et je suis heureux d'avoir pu vous apporter quelques éclairages.

A vous de jouer !

Citation:

---

Envoyé par fsmrel

...
Avant de poursuivre le fil de notre histoire, je reprends un exemple que j’utilise de temps à autre sur ce forum, afin de montrer l’importance du caractère invariant des identifiants des entités-types et donc des clés primaires au niveau tabulaire.

Les concepteurs d’une grande banque française avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type Entreprise). Au niveau tabulaire, par le jeu des liens clé primaire - clé étrangère, l’attribut NoSiren se propageait dans une trentaine de tables. Balek ! Le numéro Siren est établi par un organisme extérieur à la banque, à savoir l’INSEE. J’avais fait observer que l’INSEE envoyait tous les mois les nombreux correctifs modifiant le Siren des nouvelles entreprises (10% d’entre elles à peu près) et que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit). Une trentaine de tables à mettre à jour quant à leur valeur de clé primaire et/ou étrangère induit une activité de mise-à-jour excessive et en plus, délicate à ordonnancer. L’identifiant de l’entreprise fit donc l’objet d’un nouvel attribut, non porteur d’information, artificiel et invariant, Entreprise_Id, destiné à être clé primaire de la table Entreprise et propagé en conséquence dans les autres tables, en lieu et place de l’attribut NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus une trentaine, mais la seule table Entreprise. L’utilisateur Tartempion avait bien évidemment toujours accès à l’attribut NoSiren, devenu clé alternative de cette table (et n’ayant donc pas perdu sa propriété d’unicité), l’organisation technique ayant lieu sous le capot, de façon totalement transparente pour Tartempion.

---

Vieux débat éternellement recommencé sur le choix de l'identifiant de l'objet ...

Le problème dans le choix d'un identifiant "non naturel" c'est qu'on va se retrouver avec deux index au lieu d'un seul, ce qui n'est pas toujours optimal sur les performances en mise à jour ...

Quand aux autres tables dépendantes (une trentaine ça me semble beaucoup pour l'exemple cité) la solution proposée va induire une forte utilisation des jointures ce qui, malgré tout, peut aussi poser des problèmes de performances ...

Le monde n'est ni tout noir ni tout blanc et toute solution va avoir des avantages mais aussi des inconvénients ...

Bonsoir,

Citation:

---

Envoyé par Luc_Orient

Vieux débat éternellement recommencé sur le choix de l'identifiant de l'objet ...

---

Sans doute, mais cela fait 20 ans que de mon côté le débat est clos ! Quel que soit le projet à traiter, au niveau de l’étape de conception je cherche les entités-types (se comptant en général sur les doigts d’une main) qui feront ramer les batchs de nuit et du week-end, voire qui une fois par trimestre feront démarrer en retard les traitements transactionnels, au risque de jeter le discrédit sur toute la production. Dans l’exemple que vous citez, il s’agit évidemment de l’entreprise : l’attribut Entreprise_Id fera donc l’objet de soins particulièrement attentifs, car il sera propagé en cascade dans toute la base, du fait de l’héritage très utilisé au niveau conceptuel, ainsi que de l’usage intensif de l’identification relative. Entreprise_Id se retrouve dans pratiquement toutes les tables. Le défi est le suivant : mettre en place des identifiants invariants tout en conservant les identifiants naturels, et sans dégradation des performances des applications.

Je rappelle qu’en batch, l’ennemi c’est l’I/O bound (ou I/O wait), qui force l’application à se tourner les pouces (rendement CPU faible) pour cause d’attente de fin d’entrés/sorties. Et le choix du système d’identification est très loin d’y être étranger.

Au niveau des transactions lourdes, ce phénomène d’I/O bound est encore sensible. Au niveau des transactions légères, l’utilisateur ne le ressent pas, seules les statistiques de Prod et autres tableaux de bord pouvant montrer que la vigilance ne doit pas se relâcher.

Même avec un SGBD comme IMS/DL1 l’I/O bound est sensible (clash par exemple entre les tables Clients et Comptes) et j’y étais déjà confronté dans les années soixante-dix...

Bien sûr, il y a toujours des surprises possibles et l’on ne peut absolument pas se dire que tout est définitivement acquis.

Citation:

---

Envoyé par Luc_Orient

Le problème dans le choix d'un identifiant "non naturel" c'est qu'on va se retrouver avec deux index au lieu d'un seul, ce qui n'est pas toujours optimal sur les performances en mise à jour ...

---

C’est exact et vous avez parfaitement raison de le souligner. Prenons le cas de DB2 que nous connaissons tous deux. Dans le prolongement de ce qui précède, Entreprise_Id figurera comme attribut générique de chaque index cluster concerné. Étant donné que l’attribut NoSiren est la clé d’accès aux données pour l’utilisateur, il est nécessaire de lui affecter le 2e index (non cluster) que vous évoquez. Lors des opérations de mise à jour, en l’occurrence dans la prise en compte en batch des nouvelles entreprises, l’index cluster n’engendre pratiquement pas l’I/O bound. Concernant de deuxième index, on prototype, afin de mesurer le ralentissement provoqué. Si ce ralentissement est acceptable, on en reste là. Si maintenant il est trop important, on drope l’index et on le recrée ultérieurement, quand on en a besoin, disons avant le démarrage des traitements diurnes et avant les traitements en fin de batch, ne nécessitant pas de mise à jour mais utilisant l’attribut NoSiren. Évidemment, cela demande un minimum d’organisation, mais c’est le prix à payer, car des index comme ça, il peut y en avoir beaucoup dans une base de données...

Pour l’anecdote, je me souviens d’un traitement batch de mise à jour qui durait 9 heures (on change de banque, mais on garde DB2, en l’occurrence dans sa version 2...), temps de traitement jugé à juste titre trop important, puisque la fenêtre batch accordée n’était que de 1 heure 30 : l’action envisagée était de dénormaliser la table coupable (2 millions de lignes, autant dire pas grand-chose), car comme par hasard il y avait de la jointure et il est d’usage de dire que la jointure ça coûte la feau des pesses. J’ai suggéré de n’en rien faire mais plutôt de droper les 4 index non cluster pesant de tout leur poids sur cette malheureuse table et de les recréer (avec réorg en prime) une fois l’opération terminée. Temps du traitement batch : 5 minutes... (Je ne me souviens plus du temps de recréation des index, mais on était plus qu’au large dans la fenêtre !)

Citation:

---

Envoyé par Luc_Orient

Quand aux autres tables dépendantes (une trentaine ça me semble beaucoup pour l'exemple cité) la solution proposée va induire une forte utilisation des jointures ce qui, malgré tout, peut aussi poser des problèmes de performances ...

---

J’ai expliqué plus haut pourquoi il y avait tant de tables impliquées, ce que l’on peut résumer ainsi : propagation en cascade de l’attribut générique Entreprise_Id (ou NoSiren à l'origine, mutatis mutandis).

Les jointures sur cet attribut ne sont pas onéreuses, car il correspond à la première colonne des index concernés, qui ne l’oublions pas sont de type cluster, ils "marchent" de façon synchrone. Quand il y a jointure avec des tables non dotées de l’attribut Entreprise_Id, il est évident que l’on retrouve des problèmes d’I/O bound (ça pourrait être le cas de la table Site de l’ami Ctobini si les sites se comptaient par centaines de mille), mais ceci peut être la conséquence d’une modélisation conceptuelle n’ayant pas tenu compte de cet aspect des choses (table Adresse de clé primaire AdresseId par exemple, alors qu’une adresse est la propriété d’une entreprise et peut donc être identifiée relativement à Entreprise_Id...)

Citation:

---

Envoyé par Luc_Orient

Le monde n'est ni tout noir ni tout blanc et toute solution va avoir des avantages mais aussi des inconvénients ...

---

Ô oui ! Aussi faut-il se donner les moyes de faire en sorte que les inconvénients pèsent le moins possible. Tâche souvent délicate, mais qui, je l’ai déjà dit, fait partie des charmes du métier...