conception base avec relation deux à plusieurs
Le
Benoit Izac
Bonjour,
Je bute sur la modélisation de ma base de donnée et j'ai beau tourner le
problème dans tous les sens, je ne vois pas quelle solution adopter.
J'ai une table SITE :
- site_id -> clé primaire
- nom
- longitude
- latitude
- adresse
- etc.
Je souhaite créer des liaisons point à point entre ces sites. J'ai donc
une relation deux à plusieurs : si je crée une table LIEN, un lien
contient exactement 2 sites et un site peut apparaître dans 0 ou
plusieurs liens.
J'ai des informations
- qui sont propres au lien : bande de fréquence utilisée, débit,
etc.
- qui sont dépendantes du lien et du site : hauteur de
l'installation sur le site, type d'antenne, etc.
Jusqu'ici, pas de problème, je crée une table LIEN :
- site_id -> clé primaire
- bande de fréquence
- débit
puis une table intermédiaire SITE_LIEN :
- site_id -+
- lien_id -+-> clé primaire
- hauteur
- antenne
Rajoutons une contrainte : je dois conserver le sens du lien : site de
départ (A), site de départ (B). Je modifie donc ma table SITE_LIEN :
- lien_id -+
- coté (A ou B) -+-> clé primaire
- site_id
- hauteur
- antenne
À ce stade, j'ai déjà quelques questions :
1) comment afficher une ligne avec les informations suivantes ?
- lien_id
- site_id où le coté est A
- site_id où le coté est B
2) comment afficher tous les sites qui appartiennent à un lien comme il
suit ?
- site_id
- site_id du distant
Autre chose : j'ai lu qu'il ne fallait pas stocker des informations que
l'on pouvait calculer. Il se trouve qu'il y des informations que je peux
calculer si je connais une extrémité ; par exemple, l'azimut (angle par
rapport au nord) est égal à celui du site distant + ou - 180°. La
fréquence est dite « haute » si celle d'en face est dite « basse ».
Avec toutes ces contraintes, j'en arrive à me dire qu'il est plus simple
de supprimer la table intermédiaire et d'avoir une table LIEN comme il
suit :
- lien_id
- bande de fréquence
- débit
- id_site_A
- antenne_A
- hauteur_A
- azimut_A
- fréquence_A
- id_site_B
- antenne_B
- hauteur_B
Mais ça me crée d'autre problème. Pour répondre à la question 2), je
dois faire deux requêtes puis une union (non testé) :
CREATE VIEW site_A AS
SELECT id_site_A AS local, id_site_B AS distant FROM LIEN;
CREATE VIEW site_B AS
SELECT id_site_B AS local, id_site_A AS distant FROM LIEN;
SELECT * FROM site_A
UNION
SELECT * FROM site_B;
Bref, mon manque d'expérience m'empêche de savoir quelle solution
adopter. Pouvez-vous m'indiquer d'une part si ces deux solutions sont
viables et d'autres part quels sont leurs avantages/inconvénients ?
Merci.
--
Benoit Izac
Je bute sur la modélisation de ma base de donnée et j'ai beau tourner le
problème dans tous les sens, je ne vois pas quelle solution adopter.
J'ai une table SITE :
- site_id -> clé primaire
- nom
- longitude
- latitude
- adresse
- etc.
Je souhaite créer des liaisons point à point entre ces sites. J'ai donc
une relation deux à plusieurs : si je crée une table LIEN, un lien
contient exactement 2 sites et un site peut apparaître dans 0 ou
plusieurs liens.
J'ai des informations
- qui sont propres au lien : bande de fréquence utilisée, débit,
etc.
- qui sont dépendantes du lien et du site : hauteur de
l'installation sur le site, type d'antenne, etc.
Jusqu'ici, pas de problème, je crée une table LIEN :
- site_id -> clé primaire
- bande de fréquence
- débit
puis une table intermédiaire SITE_LIEN :
- site_id -+
- lien_id -+-> clé primaire
- hauteur
- antenne
Rajoutons une contrainte : je dois conserver le sens du lien : site de
départ (A), site de départ (B). Je modifie donc ma table SITE_LIEN :
- lien_id -+
- coté (A ou B) -+-> clé primaire
- site_id
- hauteur
- antenne
À ce stade, j'ai déjà quelques questions :
1) comment afficher une ligne avec les informations suivantes ?
- lien_id
- site_id où le coté est A
- site_id où le coté est B
2) comment afficher tous les sites qui appartiennent à un lien comme il
suit ?
- site_id
- site_id du distant
Autre chose : j'ai lu qu'il ne fallait pas stocker des informations que
l'on pouvait calculer. Il se trouve qu'il y des informations que je peux
calculer si je connais une extrémité ; par exemple, l'azimut (angle par
rapport au nord) est égal à celui du site distant + ou - 180°. La
fréquence est dite « haute » si celle d'en face est dite « basse ».
Avec toutes ces contraintes, j'en arrive à me dire qu'il est plus simple
de supprimer la table intermédiaire et d'avoir une table LIEN comme il
suit :
- lien_id
- bande de fréquence
- débit
- id_site_A
- antenne_A
- hauteur_A
- azimut_A
- fréquence_A
- id_site_B
- antenne_B
- hauteur_B
Mais ça me crée d'autre problème. Pour répondre à la question 2), je
dois faire deux requêtes puis une union (non testé) :
CREATE VIEW site_A AS
SELECT id_site_A AS local, id_site_B AS distant FROM LIEN;
CREATE VIEW site_B AS
SELECT id_site_B AS local, id_site_A AS distant FROM LIEN;
SELECT * FROM site_A
UNION
SELECT * FROM site_B;
Bref, mon manque d'expérience m'empêche de savoir quelle solution
adopter. Pouvez-vous m'indiquer d'une part si ces deux solutions sont
viables et d'autres part quels sont leurs avantages/inconvénients ?
Merci.
--
Benoit Izac
Poser une question
c'est aussi un peu logique, car sinon, pas de problème pour modéliser...
Le 01/11/2011 09:59, Benoit Izac a écrit :
bon, alors je résume, en gros t'as un graphe orienté, ce qui reste une
structure de données des plus élémentaires: des sites (les sommets) et
des liens (les aretes):
donc t'as grossomodo 2 entités:
Site (id, nom, gps_x, gps_y, ... )
Lien (id_src, id_dst, bande_frequence, debit, ...).
L'id du site est la PK, les deux ids de liens sont des FK et constituent
toutes les deux une PK de ta table lien (tu peux créer un identifiant si
tu peux avoir plusieurs connexions entre deux meme site ou pour diverses
raison techniques).
si t'as une info dépendante du site (ex: affaiblissement, tu as
grossomodo deux solutions:
- si les attributs ne sont pas tres nombreux, tu modifie la table lien:
Lien (id_src, id_trg, bande_frequence, debit, affaiblissement_src,
affaiblissement_trg, ...).
, tu créé en effet une entité lien avec une laison many to many genre:
Site (id, nom, gps_x, gps_y, ... )
Lien (id, bande_frequence, debit, ...)
et une table d'association:
Lien_Sites (id_site, id_lien, orientation (src ou cible),
affaiblissemet, etc.).
dans ce cas, tu peux mettre une contrainte d'unicité sur (id_lien,
orientation) pour éviter d'avoir plusieurs sources ou destinations
tu peut avoir une PK (ou unicité) sur (id_site,id_lien)...
dans le premier cas le plus simple:
select src.id, l.debit, trg.id
from lien l
join site src on lien.id_src = site.id
join site trg on lien.id_trg = site.id
dans le second cas:
select ....
from lien l
join lien_site ls_src on l.id = ls_src.id_lien
and ls_src.orientation = 1
join site src on ls_src.id_site = src.id
join lien_site ls_trg on l.id = ls_trg.id_lien
and ls_trg.orientation = 0
join site trg on ls_trg.id_site = trg.id
premier cas: select * from site where id in (select id_src from lien
where id lien = ?)
union select * from site where id in (select id_trg from lien....)
deuxieme cas:
select * from site where id in (select * id_site from lien_site where
id_lien = ? )
en général, on parle d'attribut calculé:
par exemple t'as la TVA, le HT et le TTC... stocker les 3 n'a pas de
sens car à partir de 2 tu peux en recalculer un 3°...
par contre, en effet, si tu prend l'état civil d'une personne, t'as sexe
M/F sur un seul champ: tu vas pas dire qu'il faut pas stocker F parce
que tu peux le déduire si t'as pas M....
donc si t'as deux type de fréquence H/B... ben tu créé un champ et tu
met la valeur qui va bien...
si par contre t'as une règle qui dit si bande_de_frequence = ??? alors
c'est bas... et que tu stocke la bande de fréquence, en effet, tu stocke
pas la fréquence basse...
au pire, si t'as des règles lourdes à coder et que tu veux éviter la
redondances, tu peux utiliser des mécanismes de vues par exemple,
oui, dépend de l'évolution de ton logiciel... tu va pas créer une table
et plein de jointure pour 1 attributs... si a terme t'en a 10 la
question se pose... si a terme t'as des connexion multiples aussi...
en l'état avec aussi peu de champs, les deux solutions sont correctes...
la solution compliquées sera cependant plus évolutive dans le temps...
dans tous les cas, tu peux utiliser des vues (j'ai le terme "requete
nommées") pour simplifier un certain nombre de requetes...
d'ailleurs, si tu base tout sur ces vues (sauf insert/delete/update), tu
pourras plus facilement faire évoluer ton schéma sans trop impacter le
reste de l'application ultérieurement....
le 01/11/2011 à 11:24, pif a écrit dans le message
Effectivement. Je pensais avoir tout de même bien décrit le problème.
Peut-être, je ne connais absolument rien en théorie des graphes. Si tu
me dis que c'est indispensable pour résoudre mon problème, je veux bien
y jeter un ½il.
Ok, c'est ce que j'ai décrit.
[je le rappelle pour plus de clarté
Site (id, nom, gps_x, gps_y, ... )
Lien (id_src, id_dst, bande_frequence, debit, ...).
]
Pour moi c'est un peu plus simple (tu as supprimé lien_id mais ça ne
change rien) :
SELECT id_src, id_dst FROM lien;
[
Site (id, nom, gps_x, gps_y, ... )
Lien (id, bande_frequence, debit, ...)
Lien_Sites (id_site, id_lien, orientation (src ou cible),
affaiblissemet, etc.)
]
Il faut que je teste car j'ai du mal a suivre la requête (sans doute
parce que je n'ai pas l'habitude de lire cette syntaxe).
Non, pour moi, ça liste tous les sites, mais pas le distant. Ça
correspond à ce que je faisais à la fin de mon post initial avec des
CREATE VIEW et UNION.
Même remarque que précédemment et c'est justement ça mon problème,
lister tous les sites de la table de liaison, c'est facile mais faire la
liaison entre src et dst, je ne sais pas faire.
Non, c'est différent, dans le cas de l'état civil, tu peux avoir soit
M ou F et tu n'as pas de contrainte. Si par contre tu prends un couple
(non homosexuel), c'est différent tu ne peux pas avoir deux M ou deux
F dans le même couple. Je veux éviter d'une part, d'avoir des
incohérences (deux bandes hautes dans le même lien, la somme des azimut
qui n'est pas égal à 360°), d'autre part de consommer de l'espace
lorsque ce n'est pas nécessaire. Je pense que seule la table à plat
(sans la table de liaison) me le permet.
Il y a une fréquence basse et une fréquence haute dans la bande de
fréquence.
Il y en a bien plus, c'était juste pour l'exemple.
C'est ce qui me semble aussi.
Ok, merci pour la réponse.
--
Benoit Izac
non, en informatique, on retrouve grossomodo deux structures des données
essentiellement: les espaces multidimensionner et les graphes (réseau de
transport de communication, sociaux, etc ...). T'as pas besoin de
connaitre la théorie des graphes, mais je t'en recommande la lecture,
c'est au programme de la première année d'IUT...
je mentionnais ca c'est c'est un terme on ne peut plus courant dans le
métier...
ben la jointure que j'avais indiqué, c'était pour retrouver le nom du
site ou autre....
c'est du SQL standard
ben les sites sont toujours distants les un des autres... dans tous les
cas, t'as toutes les billes... ce que tu as a faire c'est de requetes
SQL de base avec des jointures, si t'en a jamais fait, je te recommande
une formation ou te d'intéresser un peu plus à ca avant de te lancer...
de toute façon tu y couperas pas...
tu fais 2 jointures, c'est tout...
l'espace te pose pas trop de question... une base correcte devrait
largement tenir ce que tu lui demande... je connais pas la volumétrie,
mais j'ai pas peur pour quelques champs vides...
la; je maitrise pas les questions métiers, donc c'est pas la peine
d'aller plus loin.. t'as les éléments de base, je t'ai expliqué ce qu'on
appelle champ calculé... c'est un attribut qui peut etre calculé à
partir des autres. A toi de voir si tu peux le faire ou pas...
le 01/11/2011 à 14:22, pif a écrit dans le message
Je n'ai pas précisé : j'ai terminé mes études il y a plus de 15 ans et
je ne travaille pas dans l'informatique. J'admets donc bien volontiers
mes lacunes dans ce domaine et c'est pourquoi je demandais si ça avait
un quelquonque intérêt pour mon besoin.
[...]
Par exemple avec la table link_site (site_id, link_id, side) ?
CREATE VIEW site_A SELECT * FROM link_site WHERE side = "A";
CREATE VIEW site_B SELECT * FROM link_site WHERE side = "B";
SELECT site_A.site_id AS site_A, site_B.site_id AS site_B
FROM site_A, site_B
WHERE site_A.link_id = site_B.link_id;
Je pense que c'est correct mais je ne sais pas le faire en une seule
requête.
Est-ce qu'une contrainte CHECK serait la solution pour éviter les
incohérences ?
--
Benoit Izac
c'est bien ce que je pensais après ta réponse ;)
quand tu fais une jointure, c'est plus propre (lisible) de l'exprimer
avec un join plutot qu'avec un where et des "="... conformément à la
norme SQL ANSI
un create view, c'est pareil qu'un select :
si t'as une table lien avec src et dest:
create view v1 as select sa.*, sb.* from
lien ls
join site sa on ls.idsrc = sa.id
join site sb on ls.idtrg = sb.id
si t'as une table avec une orientation (donc deux lignes pour les deux
sites associés à un seul lien)
create view v2 as
select sa.*, sb.*
from lien_site lsa
join site sa on ls.id_site = sa.id
join lien_site lsb on lsa.id_lien = lsb.id_lien
and lsa.orientation = 1 and lsb.orientation = 0
join site sb on lsb.id_site = sb.id
quelques incohérences ?
t'as des contraintes d'intégrité (unicité, etc. ) qui sont déjà
intéressantes... tu peux ajouter des contraintes checks, et meme faire
bien plus complexe en associant une fonction stockée à tes contraintes
check...
tu peux meme aller plus loin avec des triggers et du PL sql...
mais étant donné que t'es débutant en matière de SQL, je te recommande
d'y aller progressivement... commence pas bétonner ton schéma avec les
contraintes existantes, tu verras ensuite pour les check et triggers....