Discussion :

[Krystal_]

Bonjour,

Je viens à vous concernant la largeur des clés primaire. J'hésite à remplacer une clé primaire multi-colonne par une clé auto-incrémentée.

Contexte : une application d'analyse de ce qui est connecté sur des équipements réseaux (switch) avec historisation incrémentielle (mise à jour d'une colonne témoin indiquant la dernière fois qu'une info est avérée ou insertion d'une nouvelle info)
J'ai les tables suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Switch (
  ID,
  Nom
)

Report (
  ID,
  Date
)

Port (
  ID,
  Nom
)

Pour chaque port de chaque switch, on peut avoir 0 à N point de données. On a donc une table de jointure entre les trois avec une clé primaire constituées des trois clés primaires des tables de base :

Code :

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

1
2
3
4
5
6
ReportData (
  ReportID, SwitchID, PortID,
  Actif,
  ...
)

Maintenant, pour chaque ligne de ReportData, qui correspond à une photo de la config d'un port d'un switch à la date du report, on peut avoir 1 à N Vlan. On ajoute une table Vlan et une table de jointure :

Code :

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

1
2
3
4
5
6
7
8
9
Vlan (
  ID,
  Nom
)

ReportDataXrefVlan (
  ReportID, SwitchID, PortID, VlanID
)

Ma question est la suivante : ne serait-il pas plus intéressant, pour la table reportData, d'ajouter une clé primaire auto-incrémentée au lieu d'avoir une clé primaire aussi large qui "complique" les choses lorsqu'on veut faire des jointures dessus ?
Car après les vlans, on a les adresses mac. On va donc introduire une table de jointure dont la clé primaire sera composée maintenant de 5 colonnes (les 4 de reportDataXrefVlan + celle d'une nouvelle table contenant les adresse mac).
Et après les adresses mac, on aura une table supplémentaire qui correspond à la source de données attestant de la présence de cette adresse mac sur le vlan X du port Y du switch Z à un report T. On aura donc une clé primaire auto-incrémentée pour cette dernière table (car pas de clé naturelle, une source de données peut donneur plusieurs résultats sur un même switch/port/vlan/mac) et 5 colonnes servant uniquement à faire le lien avec le reste.

D'avance merci pour vos interventions.

[fsmrel]

Bonsoir Krystal,



Le nombre d’attributs composant une clé peut donner à réfléchir, mais si un DBA y est sensible, il vérifie au plu tôt qu’il n’y a pas lieu de s’alarmer quant à performance et à l’encombrement. De même, le nombre de fois où l’opérateur JOIN est présent dans une requête ne peut pas être un élément d’appréhension (la prétendue complexité peut du reste être « encapsulée » dans des vues). Je vous renvoie à l’exemple venant d’une application opérationnelle depuis 25 ans : voyez le 4e exemple de Dénormalisation vs amélioration (optimisation), ainsi que Identification relative versus identification absolue.

Cela dit, vos clés multi-attributs successives vérifient-elles le principe d’irréductibilité des clés ?



D’après la structure de la table REPORT, un report n’est jamais qu’une date. C’est bien cela ? Contient-elle d'autres attributs ?

Pour chaque port de chaque switch

Pardonnez-moi mon inculture en ce qui concerne les réseaux, mais peut-on interpréter cela ainsi :

Un switch est composée de plusieurs ports et un port appartient à un switch et un seul ;

Sinon quelle lecture faut-il faire le la relation entre ces deux éléments ? La composition d'un switch est-elle sujette à varier dans le temps ?


Comment un Vlan se comporte-t-il par rapport aux autres types d’entités ? Un Vlan est composé de quoi ? De quoi est-il le composant ? Sinon, quelle est la nature de la relation qui lie un Vlan et le reste ?


Pourriez-vous traduire sous forme de prédicat le triplet {Report, Switch, Port} ?

Exemple (corrigez-moi bien sûr) : A la date Report, le switch Switch comporte le port Port.

Même chose pour le quadruplet {Report, Switch, Port, Vlan} ;

Puis pour le quintuplet {Report, Switch, Port, Vlan, Mac}.

Etc.


Maintenant, que l’on choisisse une clé singleton de préférence à une clé à N attributs, l’unicité de cette dernière devra de toutes façons être garantie (donc au niveau physique on aura plus d'un index, d'où un surcoût des mises à jour). Incidemment plutôt qu’une clé singleton, on peut aussi préférer une clé à 2 attributs, à 3 attributs, etc.


Quel votre SGBD ?

[Krystal_]

Bonjour,

J'utilise Postgres comme SGBDR.

La comparaison de consommation d'espace disque entre l'identification relative et absolue est édifiante. J'avais bien conscience de la taille que peut occuper un index, mais je n'aurais jamais pensé que ça serait à ce point plus volumineux que 2-5 colonnes! Et effectivement garder l'ensemble du quintuplet comme clé primaire permet de ne pas avoir à passer par les tables intermédiaires pour revenir plus tôt dans la "chaîne de jointure".

Concernant l'irréductibilité des clés, elles sont bien irréductibles selon moi. Le but étant d'historiser, deux lignes de n'importe laquelle de ces tables peuvent être strictement identiques, la seule différence étant la date à laquelle le report aura été fait.

L'entité REPORT contient deux attributs {Datetime, ScriptSourceID}

Pardonnez-moi mon inculture en ce qui concerne les réseaux, mais peut-on interpréter cela ainsi :


Un switch est composée de plusieurs ports et un port appartient à un switch et un seul ;

Physiquement, c'est tout à fait correct. Un port n'appartient jamais qu'à un switch à un moment donné.
Les switch que je considère sont modulaires. Le switch est en fait un chassis avec processeur, carte-mère, ... et on peut y ajouter ou retirer des modules. Dans chaque module il y a plusieurs ports, parfois 8, parfois 24, etc. Dans mon application les modules n'ont aucune importance, la notion n'existe donc tout simplement pas, je considère donc un switch comme un agrégat de ports.
Les ports sont nommés et les noms peuvent varier d'un switch à l'autre (A1, A2, A3, .., A24, B1, B2, ..., B24, 1/1, 1/2, ...., 1/24).
De part l'aspect modulaire du switch, les ports qui le constituent sont sujet à changement, aussi bien en termes de nombre que de noms (on peut très bien retirer le module des ports A1 ... A24 et à la place mettre le module des ports F1 ... F24)

Concernant les vlan, il sont communs à tous les switchs et ont deux attributs {ID, Nom}. Les deux sont uniques mais le Nom peut évoluer dans le temps.
A tout instant, chaque port peut être associé à 1 ou plusieurs Vlan.
Sur chaque VLAN d'un port, on peut "voir" une ou plusieurs adresses MAC et une adresse MAC peut en même temps être vue sur plusieurs VLAN (et donc en théorie sur des ports différents de switch différent)
Pour chaque adresse MAC, on aura un point de données disant à quoi elle correspond. Ce point de données est constitué des attributs suivants {Date, Port distant, SourceDeDonneeID, Hote distant, Libelle distant, Dernier report vu}

Pourriez-vous traduire sous forme de prédicat le triplet {Report, Switch, Port} ?

Exemple (corrigez-moi bien sûr) : A la date Report, le switch Switch comporte le port Port.

C'est tout à fait correct.

{Report, Switch, Port, Vlan} : A la date Report, le switch Switch comporte le port Port, le port Port est associé au vlan Vlan
{Report, Switch, Port, Vlan, Mac} : A la date Report, le switch Switch comporte le port Port, le port Port est associé au vlan Vlan, l'adresse mac Mac est vue sur le vlan Vlan

Edit:
Pour que les choses soient peut être plus claire, j'ai sorti PGModeler et j'ai fais le modèle physique correspondant à ce que j'avais sur papier. Les noms en jaunes "rel_*" ne sont pas le nom des clés étrangères, c'est propre à l'outil de modélisation et n'apparait pas dans le SQL généré.
Il y a une différence notable par rapport à ce que je disais auparavant, à savoir que j'ai renversé la relation avec l'adresse mac. On peut en effet avoir une information sur un appareil connecté à un switch mais sans savoir à quelle adresse mac il est lié. Le quintuplet {Report, Switch, Port, Vlan, Mac} n'a donc plus de sens.

[fsmrel]

Bonsoir Krystal,


Merci pour la présentation détaillée. Bien que ça donne envie, je ne me lancerai quand pas tout de suite dans le montage de switchs...

Un port n'appartient jamais qu'à un switch à un moment donné.

Il existe donc la dépendance fonctionnelle {port, date} -> {switch}.

Si la date est assimilable à celle de la table REPORT, alors la dépendance fonctionnelle devient {port, report} -> {switch}, auquel cas, la clé de la table PORT_CONFIG de votre diagramme est réductible à la paire {port_id, report_id}.

A défaut, un port a la faculté de la bilocation :

report_id    switch_id    port_id
r1           s1           p1
r1           s2           p1

Qu’en est-il exactement ?

[Krystal_]

Bonjour,

Je me suis peut-être mal exprimé. Physiquement, un port n'appartient jamais qu'à un switch à un moment donné.
Mais dans l'application, il n'y a pas de différence entre le port A1 du switch S1 et le port A1 du switch S2, il n'y aura qu'une seule ligne dans la table PORT. Un port peut donc (et sera dans 99% des cas) présent dans plusieurs switch. La date est en effet assimilable à la date de la table REPORT.

[fsmrel]

Bonsoir Krystal,

Dans l'application, il n'y a pas de différence entre le port A1 du switch S1 et le port A1 du switch S2, il n'y aura qu'une seule ligne dans la table PORT.

D’accord. Le contenu de la table PORT_CONFIG peut donc ressembler à ceci :

report_id    switch_id    port_id
---------    ---------    -------
r1           s1           p1
r1           s1           p2
r1           s1           p3
r1           s2           p1
r1           s2           p2
r1           s2           p4
...          ...          ...

Une question à propos de cette table : pour une paire {report_id, switch_id}, la valeur prise par l’attribut port_id peut-elle être modifiée ?

Si la réponse est négative, la clé primaire {report_id, switch_id, port_id} de cette table est légitime : le SGBD en interdira la modification si la table PORT_VLAN comporte une clé étrangère {report_id, switch_id, port_id} faisant référence à la clé primaire de la table PORT_CONFIG (l’interdiction de la modification jouera grâce à la clause ON UPDATE NO ACTION ou RESTRICT de la contrainte référentielle).

Si la réponse est positive, vous avez le choix :

— Conserver {report_id, switch_id, port_id} comme clé primaire, en demandant au SGBD de répercuter la modification sur la table PORT_VLAN (clause ON UPDATE CASCADE de la contrainte référentielle) ;

—Faire de {report_id, switch_id, port_id} une clé alternative (unicité garantie) et mettre en œuvre une clé primaire toute neuve, disons {port_config_id}, l’attribut port_config_id remplaçant le triplet dans la table PORT_VLAN.

J'ai renversé la relation avec l'adresse mac. On peut en effet avoir une information sur un appareil connecté à un switch mais sans savoir à quelle adresse mac il est lié. Le quintuplet {Report, Switch, Port, Vlan, Mac} n'a donc plus de sens.

Quelles tables sont porteuses de l’information sur un appareil connecté à un switch ? S’agit-il de la table DATA_SOURCE ? N’ayant pas PGModeler, j’utilise MySQL Workbench pour montrer à quel stade j’en suis de l’approche de votre modélisation (je n’ai considéré que les attributs participant aux clés). Ci-dessous, je considère une ébauche du cas où l’on connaît l’adresse mac :





J’ai du mal à interpréter votre diagramme pour la partie où l’on ne connaît pas l’adresse mac (qui plus est, je suis béotien pour tout ce qui touche aux réseaux...) Je n’ai pas fait figurer la table DATA_SOURCE : en toute innocence, je dirais qu’elle est a priori en relation avec la table PORT_VLAN, mais l’adresse mac a-t-elle une influence dans cette affaire ? la table DATA_SOURCE est-elle plutôt en relation avec la table PORT_VLAN_MAC (qui diffère pour le moment de votre table PORT_IDENTITY) ? Qu’en est-il du port distant (représenté par l’attribut distant_port_id) ?

Dans votre table PORT_IDENTITY, existe-t-il une relation ou contrainte entre les attributs report_id et last_report_id ?

[Krystal_]

Une question à propos de cette table : pour une paire {report_id, switch_id}, la valeur prise par l’attribut port_id peut-elle être modifiée ?

Non, cette valeur ne peut être modifiée. D'une façon générale, il n'y a pour ainsi dire aucune mise à jour qui se fait sur les tables de ce schéma, presque exclusivement des insertions.

Quelles tables sont porteuses de l’information sur un appareil connecté à un switch ? S’agit-il de la table DATA_SOURCE ?

c'est la table PORT_IDENTITY qui est porteuse d'information (moi et le nom des tables, on est pas très copain, souvent du mal à trouver un nom concis mais malgré tout équivoque). La table DATA_SOURCE sert uniquement à définir d'où viennent les données (dhcp, dns, db access interne, table arp, ...) qui attestent de l'identité d'un appareil connecté sur un switch.
Son contenu sera similaire à ceci :

data_source_id    name
--------------    ------
1                  DHCP
2                  DNS
3                  ARP
4                  AXDB
5                  PC-DTA
6                  LMHOST

On aura donc selon moi une clé étrangère entre PORT_IDENTITY et DATA_SOURCE pour savoir de quelle source de données est originaire chaque ligne de PORT_IDENTITY. Une ligne de PORT_IDENTITY ne peut provenir que d'une et une seule DATA_SOURCE.

En fait votre schéma est très exactement ce vers quoi je me dirigeais au début. La table PORT_IDENTITY avait alors une clé étrangère vers votre table PORT_VLAN_MAC sur le quintuplet {report_id, switch_id, port_id, vlan_id, mac_address_id}.
Mais ils 'avère qu'on peut avoir une information sur un appareil connecté à un switch, mais sans savoir à quelle adresse MAC cette information correspond.
Mon schéma répond à ce besoin, mais il crée un autre problème...on peut en effet alors associer une information sur un appareil connecté à une adresse MAC qui n'a pas été vue sur le port! Problème que ne pose pas votre schéma.

Qu’en est-il du port distant (représenté par l’attribut distant_port_id) ?

Les switch possèdent des ports, les appareils qui sont connectés en possèdent également. par exemple si on connecte deux switch ensemble, le port A1 du switch S1 sera par exemple connecté au port B7 du switch S2. La colonne distant_port_id permet d'identifier le port de l'appareil connecté sur le switch. la colonne est nullable car ce port distant n'est pas toujours connu.

Dans votre table PORT_IDENTITY, existe-t-il une relation ou contrainte entre les attributs report_id et last_report_id ??

Absolument aucune (enfin si on veut vraiment pousser, on pourrait dire que la seule contrainte est que last_report_id est plus grande ou également à report_id). Quand on voit la même information dans des rapports consécutifs, au lieu d'insérer une nouvelle ligne identique dans PORT_IDENTITY, on met à jour la colonne last_report_id pour concrétiser cette information "dernière fois qu'on a eu cette information".

[fsmrel]

Bonsoir Krystal,

Cette valeur ne peut être modifiée.

D’accord. On peut donc conserver les clés primaires multi-attributs.

moi et le nom des tables, on est pas très copain

Rassurez-vous, on en est à peu près tous là !

La table DATA_SOURCE sert uniquement à définir d'où viennent les données (dhcp, dns, db access interne, table arp, ...)

Faisons abstraction des tables PORT_IDENTITY et PORT_VLAN_MAC.

Quel empêchement y aurait-t-il à connecter directement les tables PORT_CONFIG et DATA_SOURCE ?

Si empêchement il y a, qu’est-ce qui empêcherait qu’on puisse connecter directement les tables PORT_VLAN et DATA_SOURCE ?

[Krystal_]

D'empêchement, je n'en vois aucun. A chaque triplet {report_id, switch_id, port_id} on peut effectivement relier 0 à N DATA_SOURCE et à chaque DATA_SOURCE on peut relier 0 à N triplet {report_id, switch_id, port_id}.
Ce faisant on renverse le schéma, au leu d'avoir report -> switch -> port -> vlan -> mac -> équipement on aurait report -> switch -> port -> data source. Mais je ne vois pas trop en quoi cela serait mieux.

[fsmrel]

Bonsoir Krystal,

A chaque triplet {report_id, switch_id, port_id} on peut effectivement relier 0 à N DATA_SOURCE et à chaque DATA_SOURCE on peut relier 0 à N triplet {report_id, switch_id, port_id}

On aurait donc quelque chose comme ceci :





Où la table PORT_DATA_SOURCE est indépendante des vlans et des macs : avalisez-vous cette situation ?

Au lieu d'avoir report -> switch -> port -> vlan -> mac -> équipement on aurait report -> switch -> port -> data source. Mais je ne vois pas trop en quoi cela serait mieux.

Vous utilisez ici d’une part le terme « équipement » et d’autre part le terme « data source ». Sont-ils interchangeables ?

Le but de la manœuvre est de faire jouer l’indépendance des données et faire en sorte que chaque attribut ait toujours un sens (avec pour conséquence le bannissement du bonhomme Null, synonyme d’inconnu, de peut-être, d’inapplicable, etc.) Ainsi, dans quelles conditions, à leur tour les attributs fetch_date, host, name, distant_port_id ne sont pas marqués Null ? (pour l’attribut mac_address_id, d’accord, c’est quand on connaît l’adresse machine, d’où la table PORT_VLAN_MAC...)

[Krystal_]

Où la table PORT_DATA_SOURCE est indépendante des vlans et des macs : avalisez-vous cette situation ?

Ca n'est pas incorrect vis-à-vis de mes règles, mais ça me dérange.

Je crois que nous ne sommes pas tout à fait sur la même longueur d'onde vis-à-vis de cette table DATA_SOURCE. C'est une table que SQLPro qualifierait de table de référence.
En fait on pourrait très bien l'oublier complètement et la remplacer par une seule et unique colonne de type texte dans la table PORT_IDENTITY de mon schéma. C'est donc vraiment une caractéristique, un attribut de l'entité PORT_IDENTITY.
J'en ai fait une table pour éviter la redondance et ne pas enfreindre les formes normales.

Vous utilisez ici d’une part le terme « équipement » et d’autre part le terme « data source ». Sont-ils interchangeables ?

Non, ils ne sont pas interchangeable. "Equipement" est une ligne de PORT_IDENTITY et "data source" est une ligne de la table DATA_SOURCE qui est une caractéristique de la table PORT_IDENTITY.

Ainsi, dans quelles conditions, à leur tour les attributs fetch_date, host, name, distant_port_id ne sont pas marqués Null ? (pour l’attribut mac_address_id, d’accord, c’est quand on connaît l’adresse machine, d’où la table PORT_VLAN_MAC...)

Cela dépend de la data source dont est issue la ligne.
je récapitule :

• Du switch je récupère directement la liste des ports ainsi que leur configuration et les adresses mac qui sont vues dessus (remplissage des tables PORT_CONFIG, PORT_VLAN, PORT_VLAN_MAC)
• Pour chaque adresse MAC précédemment récupérées, je vais interroger différentes DATA_SOURCE et chacune d'entre elle peut me donner jusqu'à 4 informations :
  
  • fetch_date -> qui correspond à la date de l'information dans la DATA_SOURCE, et n'est en aucune façon liée à la date de la table REPORT
  • host -> qui correspond au nom de l'équipement correspondant à l'adresse MAC
  • name -> qui correspond à un libelle textuel de l'équipement, indépendant de la colonne host, correspondant à l'adresse MAC
  • distant_port_id -> qui correspond au port de l'équipement correspondant à l'adresse MAC qui est connecté au port du switch
• Avec les informations obtenues, je remplis une table non présente sur votre schéma mais présente sur le mien, PORT_IDENTITY

Ces 4 colonnes seront ou non null en fonction de la data source dont l'information est issue.
Par exemple :

• venant de la data source ARP, les colonnes host et distant_port_id seront null
• Venant la data source DHCP, seule la colonne distant_port_id sera null
• Venant de la data source LLDP, ... ça dépend! Parfois aucune colonne ne sera null, parfois host et distant_port_id seront null

La raison de ce dernier point est que chaque équipement peut répondre différemment à une "requête LLDP", en fonction du système d'exploitation par exemple. Certains seront très gentils et donneront leur nom, le port sur lequel est connecté le cable. Mais d'autres seront avares et ne donneront pas ces informations.

Il y a en tout 9 sources de données. Chacune peut donner de 2 à 4 informations, mais pas forcément toujours les mêmes en fonction de l'équipement considéré. Et on peut tout à fait imaginer rajouter des sources de données par la suite.
Je n'aime guère les null non plus, mais avec toutes ces "contraintes", je ne voyais pas comment m'en passer pour ces 4 colonnes.

[fsmrel]

Bonsoir Krystal,


D’accord pour la table PORT_IDENTITY, elle a son existence propre, je la prend en compte et revois ma copie. Cette table a pour clé primaire {port_identity_id}.

A partir de là, mon interprétation est la suivante, dites-moi quand je me trompe... :

La table PORT_IDENTITY est associée à la table PORT_VLAN. Pour un port_vlan on peut avoir plusieurs port_identity ; pour un port_identity on a au moins un et au plus un port_vlan.

Un port_identity est facultativement associé à au plus une seule mac_address_id (adresse machine) ; une mac_address_id peut être associée à plusieurs port_identity. Puisqu’un port_identity n’est pas nécessairement associé à une mac_address_id, je préfère pour ma part mettre en œuvre une table PORT_IDENTITY_MAC (de clé primaire {port_identity_id}) associant les tables PORT_IDENTITY et MAC_ADRESS (exit le bonhomme Null) :





Comme dans votre diagramme, la table DATA_SOURCE est associée à la table PORT_IDENTITY, mais vous écrivez :

Pour chaque adresse MAC précédemment récupérée, je vais interroger différentes DATA_SOURCE

Question : la table DATA_SOURCE ne sert-elle que lorsqu'on connaît l’adresse MAC ? Si oui, autant la mettre en relation avec la table PORT_IDENTITY_MAC, sinon elle st à sa place. Qu’en est-il exactement ?


Vous écrivez aussi :

Pour chaque adresse MAC précédemment récupérée, je vais interroger différentes DATA_SOURCE et chacune d'entre elle peut me donner jusqu'à 4 informations :

• fetch_date -> qui correspond à la date de l'information dans la DATA_SOURCE, et n'est en aucune façon liée à la date de la table REPORT ;
• host -> qui correspond au nom de l'équipement correspondant à l'adresse MAC ;
• name -> qui correspond à un libelle textuel de l'équipement, indépendant de la colonne host, correspondant à l'adresse MAC ;
• distant_port_id -> qui correspond au port de l'équipement correspondant à l'adresse MAC qui est connecté au port du switch.

Si ces attributs ne sont valorisés (non marqués Null) que lorsqu’ils sont liés l’adresse MAC, ils devraient migrer vers la table PORT_IDENTITY_MAC.

Vous écrivez encore :

Ces 4 colonnes seront ou non null en fonction de la data source dont l'information est issue.
Par exemple :

• venant de la data source ARP, les colonnes host et distant_port_id seront null
• Venant la data source DHCP, seule la colonne distant_port_id sera null
• Venant de la data source LLDP, ... ça dépend! Parfois aucune colonne ne sera null, parfois host et distant_port_id seront null

Du point de vue théorique, il faudrait mettre en œuvre une table par attribut « nullable », exemple :

Un port_identity à adresse MAC est parfois associé à un host ; un host est associé à au moins et au plus un port_identiy à adresse _mac :

[ PORT_IDENTITY_MAC ]--0,1--------( rel_port_host ) -----1,1--[ HOST ]

Maintenant, au lieu de mettre en œuvre une table HOST, vous pouvez conserver l’attribut host dans la table PORT_IDENTITY_MAC, et éviter la présence de Null en utilisant plutôt une valeur de type varchar, mais de longueur égale à zéro.


Un scénario selon lequel on met en évidence une table HOST (même principe pour name et distant_port_id) :





En ce qui concerne l’attribut last_report_id, puisque le report ne dépend pas de l’adresse MAC, il est à sa place dans PORT_IDENTITY :





Pour ma part, je tendrais donc vers quelque chose comme ceci :




Reste le cas de fetch_date : attribut ou table, selon sa « nullabilité ».

[Krystal_]

Bonjour,

Puisqu’un port_identity n’est pas nécessairement associé à une mac_address_id, je préfère pour ma part mettre en œuvre une table PORT_IDENTITY_MAC (de clé primaire {port_identity_id}) associant les tables PORT_IDENTITY et MAC_ADRESS (exit le bonhomme Null) :

Ce qui me dérange avec cette solution, c'est la difficulté sinon l'impossibilité de mettre en place la contrainte qui dit qu'on ne peut lier une ligne de PORT_IDENTITY qu'à une adresse MAC qui a effectivement été vue sur le quadruplet {report_id, switch_id, port_id, vlan_id} de la ligne PORT_IDENTITY considérée. Cette contrainte était naturelle et aisée avec votre schéma précédent avec la table PORT_VLAN_MAC via l'intégrité référentielle.

Question : la table DATA_SOURCE ne sert-elle que lorsqu'on connaît l’adresse MAC ? Si oui, autant la mettre en relation avec la table PORT_IDENTITY_MAC, sinon elle st à sa place. Qu’en est-il exactement ?

Non, elle sert même si on ne connaît pas l'adresse MAC.

Vous écrivez aussi :

Pour chaque adresse MAC précédemment récupérée, je vais interroger différentes DATA_SOURCE et chacune d'entre elle peut me donner jusqu'à 4 informations :

• fetch_date -> qui correspond à la date de l'information dans la DATA_SOURCE, et n'est en aucune façon liée à la date de la table REPORT ;
• host -> qui correspond au nom de l'équipement correspondant à l'adresse MAC ;
• name -> qui correspond à un libelle textuel de l'équipement, indépendant de la colonne host, correspondant à l'adresse MAC ;
• distant_port_id -> qui correspond au port de l'équipement correspondant à l'adresse MAC qui est connecté au port du switch.

Si ces attributs ne sont valorisés (non marqués Null) que lorsqu’ils sont liés l’adresse MAC, ils devraient migrer vers la table PORT_IDENTITY_MAC.

Ma formulation était des plus maladroite, je le vois maintenant en vous relisant. On va effectivement avoir ces informations pour chaque adresse MAC, mais on pourra avoir ces informations et ce même si on ne connaît l'adresse MAC.

Maintenant, au lieu de mettre en œuvre une table HOST, vous pouvez conserver l’attribut host dans la table PORT_IDENTITY_MAC, et éviter la présence de Null en utilisant plutôt une valeur de type varchar, mais de longueur égale à zéro.

En utilisant un varchar de longueur zéro, on ne sera alors plus à-même de différencier l'absence d'information d'une information existante mais de longueur zéro. Cela ne me semble pas très élégant.
Cela me permet toutefois de sauter sur une autre question tout à fait liée. Le marqueur Null est effectivement une problématique à part entière et je sais qu'il y a eu de nombreuses critiques à son encontre, même par notre cher M. Codd.
Toutefois, de nombreux intervenants qui sont, de mon point de vue, pourvu de solides connaissances du monde relationnel admettent que vouloir l'éliminer complètement n'est pas forcément pertinent. Typiquement dans le cas qui est le mien, la signification du Null est claire : l'information n'existe pas. Je n'ai donc pas à me battre pour savoir si le Null signifie la non-existance, la non-applicabilité ou toute autre signification qui peut se cacher derrière ce marqueur.
Du coup, ma question est la suivante : ne peut-on pas admettre son utilisation pour notamment pour ces 4 colonnes ? Est-ce un choix "philosophique" et donc personnel ou bien y a t'il d'autres considérations que j'aurais omis ?

Merci déjà pour votre temps et vos réponses fort tout aussi agréables à lire qu'instructives.

Edit :

Je viens de penser à deux potentielles solution pour le problème de l'adresse mac qui n'est pas connue;

La première, qui ne change rien à la représentation des choses, et qui repart du schéma que vous avez mis dans ce message est la suivante : quid 'une clé étrangère dont une des parties pourrait être nulle ? Typiquement on aurait donc une table PORT_IDENTITY avec une clé étrangère vers PORT_VLAN_MAC sur le quintuplet {report_id, switch_id, port_id, vlan_id, mac_address_id} mais on autoriserait la colonne mac_address_id a être nulle et on permettrait à la contrainte d'être valide malgré ce null. Je vois que c'est possible avec Postgres en spécifiant la clause MATCH SIMPLE lors de la création de la contrainte.

La seconde consisterait à avoir une adresse MAC "bidon" à laquelle je rattacherais toutes les lignes de PORT_IDENTITY qui n'ont pas d'adresse MAC. L'adresse est toute trouvée, ça serait l'adresse MAC de broadcast. Cette adresse MAC ne peut en effet être détenue par aucune carte réseau et ne devrait donc jamais être vue sur le port d'un switch. C'est un "petit bidouillage" qui simplifierait grandement le schéma sans mettre en péril la logique des données.

[fsmrel]

Bonsoir Krystal,

Je viens de penser à deux potentielles solutions pour le problème de l'adresse mac qui n'est pas connue;

Une 3e voie : au lieu d’identifier PORT_IDENTITY de façon absolue, l’identifier de façon relative. L’attribut port_identity_id est à incrémenter relativement au quadruplet {report_id, switch_id, port_id, vlan_id}, auquel cas PORT_IDENTITY a pour clé le quintuplet {report_id, switch_id, port_id, vlan_id, port_identity_id} :





En ce qui concerne le diagramme général, il y a plus d’un scénario, mais partant du principe que pour une instance de PORT_VLAN, il peut y avoir plus d’une instance de PORT_IDENTITY, et que pour une instance de PORT_IDENTITY, il y a au plus une instance de MAC_ADDRESS, on aurait ceci :






Selon que pour une instance de PORT_IDENTITY il y a facultativement ou non au plus une instance de HOST, de NAME et de DISTANT_PORT_ID (tout ceci restant indépendant de MAC_ADRESS), la modélisation diffère : je compléterai en conséquence.

On va effectivement avoir ces informations pour chaque adresse MAC, mais on pourra avoir ces informations et ce même si on ne connaît l'adresse MAC.

Donc la table DATA_SOURCE reste associée à la table PORT_IDENTITY.

Le marqueur Null est effectivement une problématique à part entière et je sais qu'il y a eu de nombreuses critiques à son encontre, même par notre cher M. Codd.

Ted Codd était plutôt pour, puisqu’il a récupéré et étendu les tables de vérité de Sequel 2 (SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control, Chamberlin & al, 1976), basées sur une logique ternaire au lieu de la logique binaire classique.

En passant, voici les tables de vérité de Sequel 2 :




Pour les tables de vérité de Codd, voyez The Relational Model for Database Management, Version 2 aux paragraphes 8.9 et 8.10.

Une controverse terrible a eu lieu en 1993, qu’on peut retrouver notamment au chapitre 9 « Much Ado about Nothing » de Relational Database Writings 1991-1994, et poursuivie dans les chapitres 5 à 10 de Relational Database Writings 1994-1997.

Avec cette logique trivaluée, il y a des évidences, des tautologies qui n’en sont plus, telle celle-ci : SI p ALORS p au cas où p est inconnu. Par exemple : « Si je chante alors je chante » peut poser problème. Même chose pour une contradiction telle que p ET NON p : « Je chante et je ne chante pas » n’est plus une contradiction. De la même façon, dans le cas du biconditionnel, p SI ET SEULEMENT SI p, on retrouve les mêmes problèmes : « Je chante si et seulement si je chante » n’est plus une tautologie.


Incidemment, la logique quadrivaluée de Codd est "inapplicable" (sic), car, entre autres, des lois fondamentales telles que les lois de De Morgan sont mises en échec :

¬(p ET q)

et

¬p OU ¬q

ne sont plus des formules équivalentes : si p et q prennent respectivement les valeurs de vérité APPLICABLE et INAPPLICABLE, la 1re expression sera évaluée à INAPPLICABLE tandis que la 2e sera évaluée à APPLICABLE. De la même façon, l’équivalence suivante ne vaut plus :

∀x (p) ≡ ¬∃x (¬p)

Ou, si vous préférez, "Tous les hommes sont mortels" n’est plus équivalent à "Il n’existe pas d’homme non mortel".

Le théorème de Heath est mis en échec, alors qu’il est au cœur de la théorie de la normalisation...

Null est un inhibiteur de l’optimiseur des SGBDR.

Etc.


Pour sa part, la norme SQL n’est pas en reste et commet une magnifique boulette :

« The data type boolean comprises the distinct truth values True and False. Unless prohibited by a NOT NULL constraint, the boolean data type also supports the truth value Unknown as the null value. This specification does not make a distinction between the null value of the boolean data type and the truth value Unknown that is the result of an SQL <predicate>, <search condition>, or <boolean value expression>; they may be used interchangeably to mean exactly the same thing. »

Comme le note malicieusement Peter Gulutzan (SQL-99 Complete, Really), selon la norme il revient au même de dire : « Je ne sais pas » et « Je sais que l’information est absente » !


Pour ma part, il y a plus de 25 ans, je me suis rallié à la position de Hugh Darwen (initialement exposée dans « Adventures in Relationland », article intégré dans Relational Database Writings 1985-1989). Voyez par exemple son article How To Handle Missing Information Without Using NULL. Ainsi, par décomposition verticale (projection), ai-je mis par exemple en œuvre les tables PORT_IDENTITY_MAC et HOST. Respect des lois du Relationland oblige... M'en voudrez-vous ?

[Krystal_]

Voyez par exemple son article How To Handle Missing Information Without Using NULL. Ainsi, par décomposition verticale (projection), ai-je mis par exemple en œuvre les tables PORT_IDENTITY_MAC et HOST. Respect des lois du Relationland oblige... M'en voudrez-vous ?

J'ai lu cet article il n'y a pas si longtemps quand je faisais quelques recherches sur ce Null qui fait couler tant d'encre !
Vous en vouloir ? Non en aucune façon.

Par contre, après une nouvelle analyse de l'existant, une nouvelle contrainte vient pointer son nez. Il semble qu'il y ai des cas ou on peut avoir des lignes PORT_IDENDITY qui ne sont pas rattaché à un VLAN.
Je suis donc reparti d'une feuille blanche en considérant vos différents schémas. Désolé de l'allure spartiate du schéma, mais je n'ai pas d'outil de modélisation sous la main si ce n'est du papier...Je ferai une version au propre dès que possible.

Pour résoudre le problème du VLAN et de la MAC qui peuvent ou non se rattacher, voici ce que j'ai fait :
* A chaque port_config peuvent se rattacher de 0 à N vlan (table de jointure portvlan avec les clés primaires des deux tables)
* A chaque port_vlan peuvent se rattacher 0 à N MacAddress (table de jointure portvlanmac avec les clés primaires des deux tables)
* A chaque port_config peuvent se rattacher 0 à N port_identity
* A chaque port_identity peut se rattacher 0 ou 1 vlan (portidentityid est la clé primaire, pour garantir la contrainte max 1 vlan par port_identity)
* A chaque port_identity peut se rattacher 0 ou 1 mac (portidentityid est la clé primaire, pour garantir la contrainte max 1 mac par port_identity)
* A chaque port_identity peut se rattacher 0 ou 1 distantport (portidentityid est la clé primaire, pour garantir la contrainte max 1 distantport par port_identity)
* A chaque port_identity peut se rattacher 0 ou 1 Host (table host), 0 ou 1 Name (table name) et 0 ou 1 fetchdate (table fetchdate)
Le reste est assez équivoque.

Qu'en pensez-vous ?

J'avoue par contre que de voir toutes ces clés étrangères avec 3, 4 ou 5 colonnes fait assez mal à des yeux non-habitués

[SQLpro]

Première chose votre titre :

"Quand une clé primaire multi-colonne est-elle trop large ?"

Réponse : dès qu'elle est multicolonne !

Bonjour,

Je viens à vous concernant la largeur des clés primaire. J'hésite à remplacer une clé primaire multi-colonne par une clé auto-incrémentée.

N'hésitez pas. Si vous avez ce pouvoir là, alors changez immédiatement sans vous poser de question.

EXPLICATION :
Pour que les requêtes soient efficaces, les moteurs relationnels utilisent un optimiseur qui recherche à travers les différentes possibilités algorithmiques et combinaisons la meilleur. Pour cela l'optimiseur doit disposer de statistiques afin d'évaluer le nombre de ligne qui va être pris en compte dans chaque opération (on appelle cela la cardinalité d'une opération).
Si la clef ne comporte qu'une seule colonne, la statistique est directe : je sais que pour telle valeur de la clef, j'aurai tant de ligne. Si le critère de filtrage est une fourchette, cela va lui indiquer approximativement le nombre de ligne de manière assez précise.
Si la clef comporte plusieurs colonnes, c'est une autre paire de manche !
En effet si nous prenons 3 colonnes (A, B, C), il faudrait, pour être précis dans les statistiques, pouvoir disposer des statistiques, sur :

• La colonne A seule.
• La colonne A + B
• La colonne A + C
• La colonne B + C
• La colonne A + B + C

je vous laisse deviner ce que cela donnerait avec 10 colonnes... !


Heureusement les SGBDR ne font pas cela. Ils calculent des statistiques pour chaque colonne :

• La colonne A seule.
• La colonne B seule
• La colonne C seule

Mais pour résoudre le calcul d'estimation de cardinalité, ils doivent alors procéder à des estimations par corrélation des statistiques... Le problème est que :
Si je corrèle deux estimations sûre à 90%, je vais avoir une marge d'incertitude de l'ordre de 25%
Si je corrèle trois estimations sûre à 90%, je vais avoir une marge d'incertitude de l'ordre de 50%
....

Donc plus j'ajoute de colonne dans ma clef, plus le calcul de cardinalité devient incertain....

Mais cela si les statistiques sont relativement homogènes et les données bien distribuées, ce qui est rarement le cas.

Par exemple, si la première colonne possède peu de valeur distincte (sexe par exemple), alors la marge d'incertitude de départ devient assez forte et la statistique globale épouvantablement incertaine.....
C'est le cas malheureusement de de certains logiciels qui commence la clef de toute table par l'entité de l'organisation (site géographique par exemple) dont le nombre de valeurs est généralement très limité, voire unique !

Par exemple dans votre cas si ReportID possède le plus faible éventail, de valeurs distinctes par rapport aux autres colonnes, alors le résultat sera potentiellement catastrophique lors der la montée en charge.

C'est comme cela que l'on fabrique des veaux en production !

A +

[fsmrel]

Bonsoir,


Krytal, je vais regarder cette histoire de vlan.

N'hésitez pas. Si vous avez ce pouvoir là, alors changez immédiatement sans vous poser de question.

Holà ! Comme tu y vas ! On doit se poser des questions, notamment en ce qui concerne l’effet cluster (au sens DB2 du terme c'est-à-dire faire en sorte que l’IO bound soit minimal), en termes de démultiplication des index, du facteur de filtrage de la 1re colonne (en l’occurrence ReportId), en fonction de ce que dit l’explain des requêtes sensibles. J’ai engagé mon entreprise quant aux performances des applications chez mes clients (banque assurance, industrie, retraite, etc.) et y ai exercé la fonction de DBA DB2 pendant 20 ans sur leurs bases de données centrales, avec des clés multi-colonnes et ça n’a jamais posé de problème. Lis ce que j’ai écrit à ce sujet dans mon article sur la normalisation.


Cela dit, quand le modèle sera stabilisé, on regardera avec Krystal dans quelles conditions réduire le nombre de colonnes (à n-1, n-2, etc.) de certaines clés primaires (suite à prototypage des performances), mais pour le moment c’est prématuré.

[SQLpro]

Holà ! Comme tu y vas ! On doit se poser des questions, notamment en ce qui concerne l’effet cluster (au sens DB2 du terme c'est-à-dire faire en sorte que l’IO bound soit minimal), en termes de démultiplication des index, du facteur de filtrage de la 1re colonne (en l’occurrence ReportId), en fonction de ce que dit l’explain des requêtes sensibles. J’ai engagé mon entreprise quant aux performances des applications chez mes clients (banque assurance, industrie, retraite, etc.) et y ai exercé la fonction de DBA DB2 pendant 20 ans sur leurs bases de données centrales, avec des clés multi-colonnes et ça n’a jamais posé de problème. Lis ce que j’ai écrit à ce sujet dans mon article sur la normalisation.


Cela dit, quand le modèle sera stabilisé, on regardera avec Krystal dans quelles conditions réduire le nombre de colonnes (à n-1, n-2, etc.) de certaines clés primaires (suite à prototypage des performances), mais pour le moment c’est prématuré.

Oui mais ce qui est fait en amont est en général plus pertinent et moins couteux qu'en aval.... Et les si les techniques ont évoluées en 20 ans, les volumétries d’aujourd’hui sont bien plus importantes que par le passé et les goulets d'étranglements plus rapidement obtenu par la montée en volumétrie.
Combien de temps t'a t-il fallut attendre pour avoir une base de 1 To avec DB2 ?
Aujourd'hui il arrive que ceci se produise en quelques mois !
Il y a 3 ans, j'ai eu un client dont les bases montaient à 20 To en quelques jours !!!!! (données des circuits de "passagers" et trajets en IDF, par les bornes téléphonique)

A +

[Krystal_]

Par exemple dans votre cas si ReportID possède le plus faible éventail, de valeurs distinctes par rapport aux autres colonnes, alors le résultat sera potentiellement catastrophique lors der la montée en charge.

A ce niveau là pas de soucis, c'est la colonne qui possède le plus large éventail. Entre 2 et 10 report sont fait toutes les 1/2 heures, 24/7.
Quant à la volumétrie, elle sera faible. J'avais estimé l'évolution de l'espace sur un modèle non-normalisé (une table unique de 17 colonnes avec des null dans tous les sens) à moins de 1gio par an.

[fsmrel]

Désolé Krytal, je dois encore différer la prise en compte de votre dernier problème, je suis retardé...

ce qui est fait en amont est en général plus pertinent et moins couteux qu'en aval.

Je t’ai expliqué qu’on passe notamment par un prototype de performances. La modélisation ne se fait quand même pas sur des a priori, au doigt mouillé.

Combien de temps t'a t-il fallut attendre pour avoir une base de 1 To avec DB2 ?

Il suffit d’attendre que le besoin s’en fasse sentir. Par exemple, en 1987 je m’occupais de la base de données Clients d’une banque, comptant alors de l’ordre de 1500000 de clients. Pour l’ensemble des tables, comptons 10 GO (volume plus que conséquent pour l’époque). Il faudrait donc que la banque ait aujourd’hui 150000000 clients pour que la base arrive au TO. Tu me diras que la banque offre aujourd’hui des services qui n’existaient pas à l’époque, mais c’est périphérique, ce qui m’intéresse c’est le cœur de la base de données, la gestion des comptes des clients.

Par ailleurs les TO que tu évoques concernent « plusieurs » tables (10 ? 100 ? 1000 ?)

Il y a 3 ans, j'ai eu un client dont les bases montaient à 20 To en quelques jours !!!!! (données des circuits de "passagers" et trajets en IDF, par les bornes téléphonique)

Avec un minimum de réflexion et d’astuce, on doit pouvoir réduire ça. Ainsi, il y a une vingtaine d'années, Socrate (le système de réservation de la SNCF) déversait quotidiennement tous les titres de transport (billets) du jour chez son back-office Aristote. Mais un aller « Gare Saint-Lazare Pont-Cardinet » (donc un ticket à 1 franc, intra Paris) occupait pratiquement autant de place qu’un « Brest-Nice ». Pour réduire la volumétrie de la table des titres, un ingénieur a eu l’idée d'optimiser, de faire un seul titre pour tous les « Gare Saint-Lazare Pont-Cardinet » du jour et d’ajouter une colonne pour le nombre de titres correspondant (même principe pour tous les « Gare Saint-Lazare Massy-Palaiseau » et autres du même tonneau) : je ne te parle pas de l’économie réalisée, sans perte de fonctionnalité.

Mais ne confondons pas modélisation et optimisation, même si les deux ont leur rôle à jouer.