Discussion :

[boguerdan]

Bonjour à toutes et tous,
je suis actuellement entrain de développer un site internet avec une belle base assez complexe (enfin pour moi) et je viens de m'appercevoir d'un léger problème de conception que je n'avais pas prévu évidement .

En gros on a des utilisateur qui peuvent avoir plusieurs adresses d'un côté,
de l'autre nous avons des utilisateurs qui posséde des objets,
le problème est qu'un objet ne peut être qu'a un endroit a la fois (contrainte physique ,
mais surtout cet endroit doit faire partie des adresse du propriétaire de l'objet.

Un petit mcd pour illustrer tout ça:


Comment dois je faire pour déclarer la relation entre la table objet (fk_address) et la table Address (id).

Il faut donc préciser que la foreign key doit être dans la liste des adresses de l'user.

J'ai fouillé un peu du côté des 'Constraint' en suivant ce tuto http://sqlpro.developpez.com/cours/s...partie2#L7.2.4
mais je ne suis pas sur que cela correspondre tout a fait a mon cas de figure, quelqu'un pourrais t'il m'aiguiller sur la façon de procédé pour garantir l'intégrité de mes données.

En plus il me semble qu c'est un cas assez classique, ce qui est encore plus frustrant pour moi de ne pas trouvé de réponse dans la doc.

Merci d'avance de votre aide.

[escartefigue]

Bonjour,

Quelques remarques concernant votre modèle :

• Le point le plus important, est que les FK ne doivent pas apparaitre dans le modèle conceptuel, elles n'ont pas de sens à ce stade, elles n'apparaissent que dans le modèle logique
• Utilisez de préférence des verbes pour nommer les associations, cet usage permet de lire plus facilement les associations dans les 2 sens
• Les cardinalités s'écrivent toujours sous la forme "mini,maxi", n,1 entre "USER" et "Appartient" est donc inadéquat
• "Adress_user" n'est pas une entité-type, mais le résultat d'une relation entre "USER" et "ADRESSE"
• Qu'en est il des dates ?

Voici une proposition qui s'appuie sur une supposition selon laquelle un USER n'a qu'une et une seule adresse à un instant "t"
Si toutefois, plusieurs adresses sont possible à la même date, ça ne change rien d'un point de vue MCD, il faudra par contre intervenir dans le MLD pour supprimer la date de la PK

Pièce jointe 233567

Grâce à l'identification de l'OBJET relativement au USER, la dérivation du MLD permet d'avoir le US_id comme composante de la PK de l'objet
Par conséquence, la table issue de la relation "Situer" hérite également de cet identifiant, c'est ce qui va nous permettre de coder la contrainte facilement entre cette table, et celle issue de la relation "Habiter"
Voici le MPD résultant du modèle conceptuel

Pièce jointe 233569

Voici un extrait du DDL généré par Power AMC pour la table "Situer" en ayant choisi un script SQL server 2014

Code :

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

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
create table SITUER (
   US_ID                numeric              not null,
   OB_ID                numeric              not null,
   AD_ID                numeric              not null,
   CA_DATE              datetime             not null,
   constraint PK_SITUER primary key (US_ID, OB_ID, AD_ID, CA_DATE)
)
go

create nonclustered index SITUER_FK on SITUER (US_ID ASC,  OB_ID ASC)
go

create nonclustered index SITUER2_FK on SITUER (AD_ID ASC)
go

alter table SITUER
   add constraint FK_SITUER_SITUER_OBJET_OB foreign key (US_ID, OB_ID)
      references OBJET_OB (US_ID, OB_ID)
go

alter table SITUER
   add constraint FK_SITUER_SITUER2_ADRESSE_ foreign key (AD_ID)
      references ADRESSE_AD (AD_ID)
go

Il vous suffit d'ajouter une contrainte comme suit

Code :

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

1
2
3
4
alter table SITUER
add constraint FK_SITUER_SITUER__HABITER 
               foreign key (US_ID, AD_ID)
               references HABITER (US_ID, AD_ID)

Cette contrainte ne vérifie pas que la résidence est la résidence en cours pour le USER (dans le cas où les multi-adresses sont liées aux dates)

[boguerdan]

Tout d'abord, merci d'avoir pris le temps de me répondre aussi précisément, cette façon de procéder (mcd, mpd etc...) est nouvelle pour moi, et je me suis frotter a un logiciel pour en générer hier soir et ça prend un peu de temps tout de même, donc merci encore.

Ensuite, votre proposition sur le fait d'utiliser des dates, me semble tout à fait pertinente, en effet un user ne peux, comme un objet être à deux endroit en même temps, à moins qu'une théorie quantique ne s'en mêle

Du coup la contrainte vérifie bien que l'adresse ou se situe l'objet fait partie des adresses de l'utilisateur, pile poil ce que je voulais merci

Je m'en vais tenter d'intégrer cette solution à mon projet, je reviendrai vers vous si besoin est.

En tout cas grâce à vous j'ai pas mal de grain à moudre avec ces nouveau concept, malheureusement les BDD sont souvent la dernière roue du carrosse, mais je vais essayer de prendre du temps en amont pour éviter d'avoir de mauvaise surprise par la suite.

[boguerdan]

Re bonjour,
j'ai une petite question qui me tourmente, dans la transition entre le MCD et le MLD je m’aperçois qu'il n'y a pas de table représentant les date.
Du coup je me questionne, je serai dans un premier temps tenté de me dire que une telle table ne serait pas bien utile car elle ne représenterai que des 'moments', puis je me suis demandé si cela pouvais en fait être une bonne pratique pour faciliter les recherche dans la BDD, une indexation et des fk venant des autres table pourrai permettre des recherche plus rapide, ou bien c'est superflu?

Je ne sais pas si je suis suffisamment explicite avec ma question existentielle....

[escartefigue]

C'est une question pertinente en effet

La table n'a pas été générée, car dans mon exemple, je n'en avais pas besoin, et du coup, j'ai décoché la case "générer" dans power-amc

Dans certains cas, une table des périodes est nécessaire, typiquement une BDD agenda, auquel cas il suffit de laisser la case "générer" à oui, qui est la valeur par défaut.

[escartefigue]

cette façon de procéder (mcd, mpd etc...) est nouvelle pour moi, et je me suis frotter a un logiciel pour en générer hier soir et ça prend un peu de temps tout de même, donc merci encore.

C'est la seule façon de procéder qui soit viable, elle permet souvent d'éviter bien des erreurs
1°) recueil des règles métier
2°) rédaction du dictionnaire de données
3°) réalisation du MCD
4°) validation du MCD par les experts métier, retour en 2 ou 3 si nécessaire (clarification/complément des règles et du MCD)
5°) génération du MLD (facultatif)
6°) génération du MPD en fonction du choix du SGBD, et correction du DDL (ajout des index de perf, des contraintes etc...)

Concernant les logiciels de modélisation, il en existe des gratuits qui sont très bons et que vous pouvez télécharger facilement.
Par exemple JMerise ou DBMain.
Il existe des aides dans ce forum sur ces outils : comment les télécharger, mode d'emploi...

[boguerdan]

Je suis actuellement en train de tenter d'intégrer les contraintes,
en ce qui concerne les 2 première, aucun souci, mais la derniére ne fonctionne pas j'ai une jolie erreur laconique du type:

General error: 1215 Cannot add foreign key constraint

J'ai donc bien relu ma requête et il n'y a pas d'erreur d'écriture syntaxique.
Toutefois en changeant le nom de la clé ad_id, en id (référence a la pk de la table habite) cela fonctionne d'ou ma question;
est il obligatoire d'avoir une référence à la clé primaire de la table inscrite en reference dans une déclaration de contrainte de table?

une fois la requete traduite avec le nom de mes table et champs j'ai ceci;

Code :

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

1
2
3
4
 ALTER TABLE address_object 
ADD CONSTRAINT fk_address_objet_address_user 
FOREIGN KEY (user_id,address_id) 
REFERENCES address_user(user_id,address_id)

qui ne fonctionne pas, par contre;

Code :

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

1
2
3
4
 ALTER TABLE address_object 
ADD CONSTRAINT fk_address_objet_address_user 
FOREIGN KEY (user_id,address_id) 
REFERENCES address_user(user_id,id)

celle-ci fonctionne d'où ma question.

J'ai également lu sur un forum que le fait que des fk portant le même nom pouvais crée des problèmes (même si je ne comprend pas bien comment puisque la table cible est explicite), du coup j'ai aussi tenter de changer le champ en address2_id, sans succès.

[chal1803]

Bonjour à tous,

j'ai une petite remarque sur la solution d'escartefigue:

Voici une proposition qui s'appuie sur une supposition selon laquelle un USER n'a qu'une et une seule adresse à un instant "t"
Si toutefois, plusieurs adresses sont possible à la même date, ça ne change rien d'un point de vue MCD, il faudra par contre intervenir dans le MLD pour supprimer la date de la PK

Dans l'état du MLD actuel, le USER peut déjà avoir plusieurs adresses différentes à la même date.
La date reprise en PK permet d'avoir les Tuples, par exemple :
User 1, Adr 1, Date 1
User 1, Adr 1, Date 2
C'est à dire de garder l'ancienne date du début de l'occupation si un USER revient à une de ces anciennes adresses.

Il faut différencier le multi-adresse et l'historisation.

Il vous suffit d'ajouter une contrainte comme suit

Code :

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

1
2
3
4
alter table SITUER
add constraint FK_SITUER_SITUER__HABITER 
               foreign key (US_ID, AD_ID)
               references HABITER (US_ID, AD_ID)

Cette contrainte ne vérifie pas que la résidence est la résidence en cours pour le USER (dans le cas où les multi-adresses sont liées aux dates)

La contrainte ne fonctionnera pas si le couple (US_ID, AD_ID) dans HABITER n'est pas soumis à une contrainte UNIQUE

[chal1803]

Toutefois en changeant le nom de la clé ad_id, en id (référence a la pk de la table habite) cela fonctionne d'ou ma question;
est il obligatoire d'avoir une référence à la clé primaire de la table inscrite en reference dans une déclaration de contrainte de table?

1. Les attributs définis en tant que FK doivent avoir un TYPE strictement identique aux attributs correspondant de la clause REFERENCES.
2. Des plus, il doit y avoir une contrainte d'unicité ou de FK sur les attributs repris dans la clause REFERENCES.
3. La création de la contrainte ne passera pas si des données déjà présentes dans la base contrarient l'intégrité référentielle

[escartefigue]

Dans l'état du MLD actuel, le USER peut déjà avoir plusieurs adresses différentes à la même date.
La date reprise en PK permet d'avoir les Tuples, par exemple :
User 1, Adr 1, Date 1
User 1, Adr 1, Date 2
C'est à dire de garder l'ancienne date du début de l'occupation si un USER revient à une de ces anciennes adresses.

Tout à fait, je me suis mal exprimé, merci pour cette précision

@Boguerdan : d'une façon générale, il est préférable de vérifier les cas de violation de la contrainte avant de l'installer. Par exemple avec des requêtes de type (not) EXISTS

[boguerdan]

Salut, chal1803,

1. Les attributs définis en tant que FK doivent avoir un TYPE strictement identique aux attributs correspondant de la clause REFERENCES.

3. La création de la contrainte ne passera pas si des données déjà présentes dans la base contrarient l'intégrité référentielle

En ce qui concerne ces deux points aucun soucis, par contre

2. Des plus, il doit y avoir une contrainte d'unicité ou de FK sur les attributs repris dans la clause REFERENCES.

pour celui ci, la reférence dans la table (ad_id) correspond bien à une FK donc théoriquement pas de problème ici non plus si je te suis bien.

Du coup dans ton message précédent tu dis:

La contrainte ne fonctionnera pas si le couple (US_ID, AD_ID) dans HABITER n'est pas soumis à une contrainte UNIQUE

Par rapport à ce que je veux réaliser je pensais mettre un nouveau champs (address_rang) dans lequel l'user indiquerai si il s'agit d'une résidence principale, secondaire etc.....

du coup si au lieu du couple (US_ID,AD_ID) nous avions en plus un paramètre de ce type, nous pourrions avoir une contrainte unique sur ces enregistrement. Que pensez vous de cette solution, et est ce possible de faire référence a plus de 2 champs?

[escartefigue]

pour celui ci, la reférence dans la table (ad_id) correspond bien à une FK donc théoriquement pas de problème ici non plus si je te suis bien.

Non, car j'avais négligé le fait (honte à moi ) que cette clef n'est pas unique dans HABITER, du coup la contrainte ne peut pas être déclarée

Par rapport à ce que je veux réaliser je pensais mettre un nouveau champs (address_rang) dans lequel l'user indiquerai si il s'agit d'une résidence principale, secondaire etc.....
du coup si au lieu du couple (US_ID,AD_ID) nous avions en plus un paramètre de ce type, nous pourrions avoir une contrainte unique sur ces enregistrement. Que pensez vous de cette solution, et est ce possible de faire référence a plus de 2 champs?

Pour cet usage, il est préférable d'utiliser une nouvelle entité-type dans le MCD "TYPE_ADRESSE" 0,n --- Typer --- 1,1 "ADRESSE", avec comme attributs id_type, code_type, description etc...
Mais ça ne changera rien au problème de contrainte : dans la table ADRESSE vous aurez en ce cas une FK id_type, mais vous n'aurez toujours pas la FK us_id puisque une adresse peut être partagée par plusieurs users
Du coup pas possible de poser la contrainte sur la table adresse pour vérifier le user
Et pas non plus sur la table habiter, pour la raison évoquée plus haut.
Avec un modèle classique du type USER 0,n --- Habiter --- 1,1 ADRESSE, on peut utiliser l'id relative (1,1) et du coup bénéficier le l'identifiant USER dans la table adresse, et la la contrainte devient possible car là, la clef est bien unique

[chal1803]

Oui Boguerdan, il est tout à fait possible de définir une contrainte de type FK sur 2,3 ou plus d'attributs...
Mais il faut bien comprendre que l'unicité doit être garantie, pour 3 attributs par ex., sur la concaténation ou le "trinôme" formé
par les valeurs des différents attributs qui sont référencés. Pour une FK sur 3 attributs A1,A2,A3, l'unicité doit
être garantie sur la valeur du trinôme formé par {A1,A2,A3} (ensemble), dans la table référencée.

@escartefigue
tout à fait d'accord pour le reste.

Bonne nuit.