Discussion :

[sam01]

Bonjour à tous,

j'ai créé 3 tables :

1) Contient
2) Pays
3) Ville

et j'ai créé une table destination.

Dans cette table destination, j'ai les champs suivants :

Code :

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

1
2
3

id_des	id_adh	continent	pays	ville

id_des ==> identifiant destination
id_adh ==> identifiant adhérent
continent ==> numéro du continent
pays ==> numéro du pays
ville ==> numéro de la ville


Quand un adhérent s'inscrit et que sa ville d'habitation n'existe pas, elle est créée automatiquement dans la table ville.

Lorsqu'un adhérent choisit comme destination une ville qui n'existe pas, elle automatiquement ajoutée dans la table ville. J'ai fait cela afin d'éviter d'avoir une table destination des noms de villes déjà présent dans la table ville.

Jusque là tout va bien.

Mais ce que je n'avais pas prévu, c'est qu'il y a des adhérents qui choisissent comme destination :

bord de mer
Sud de la France
Andalousie...

Donc là, je me trouve un peu coincé car ce ne sont pas des villes, et cela me gêne de les mettre dans la table ville...

Que me conseillez-vous ? Dois-je mettre quand même ces destinations dans la table ville ?

Merci d'avance pour votre aide.

[JPhi33]

Bonjour sam01,

1) Contient

Continent ?

Dans cette table destination, j'ai les champs suivants :

id_des ==> identifiant destination
id_adh ==> identifiant adhérent
continent ==> numéro du continent
pays ==> numéro du pays
ville ==> numéro de la ville

Une remarque au passage.
Une ville appartenant forcément à un pays et un pays étant forcément situé sur un continent, il serait judicieux de lier les tables Ville, Pays et Continent.

Code :

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

1
2
[ Ville ]--1,1----( appartient à )----0,n->[ Pays ]--1,1----( est situé sur )----0,n->[ Continent ]

De cette manière, seule la clé étrangère "numéro de la ville" est nécessaire dans la table Destination.

Mais ce que je n'avais pas prévu, c'est qu'il y a des adhérents qui choisissent comme destination :

bord de mer
Sud de la France
Andalousie...

Donc là, je me trouve un peu coincé car ce ne sont pas des villes, et cela me gêne de les mettre dans la table ville...

Que me conseillez-vous ? Dois-je mettre quand même ces destinations dans la table ville ?

Effectivement, une première approche peut consister à considérer que la table Ville ne contient pas que des villes. Il faudrait donc la rebaptiser "Lieu" ou "Site" (ou autre chose) et considérer que les adhérents ne choisissent pas forcément une ville. Dans ce cas, il est difficile de lier cette table avec Pays (sauf si les adhérents doivent obligatoirement préciser le pays).

En fait, il faudrait expliquer un peu plus en détail le fonctionnement de ton application.

[sam01]

Bonjour JPhi33 et merci pour ta réponse.

Je te confirme que mes tables Continent - Pays - Ville sont bien liées entre elles.

Donc j'ai bien la clé étrangère "numéro de la ville" qui est dans la table Destination.

Lorsque un adhérent choisit une destination, il doit effectivement choisir d'abord le continent, ensuite il est obligé de choisir un pays, et ensuite, il choisit ce qu'il veut.

Donc comme tu le dis, il faudrait que je change la dénomination de la table en l'appelant Lieu par exemple.

Ce qu'il y a c'est que dans ma table ville, j'ai le champ géolocalisation. Donc, si l'adhérent met "bord de mer" par exemple, et bien ce champ ne sera tout simplement pas renseigné.

je viens de réaliser une chose. Si je considère une table "Lieu" et que je mets les noms de villes, et autres lieu du type "Bord de Mer", "A la montagne",
je vais me retrouver avec une table contenant des doublons, puisque l'adhérent est d'abord obligé de renseigner le continent, le pays et ensuite le lieu (la ville ou autres).
Donc pour chaque pays, il y a la possibilité d'avoir "Bord de mer". Ce qui veut dire que dans la table "Lieu", il y aura des doublons du type "Bord de mer", "A la Montagne"...

Est-ce que cela pose problème ?

[JPhi33]

Lorsque un adhérent choisit une destination, il doit effectivement choisir d'abord le continent, ensuite il est obligé de choisir un pays, et ensuite, il choisit ce qu'il veut.

Puisqu'un pays ne se situe que sur un continent et que les tables Continent et Pays sont liées, seule la clé étrangère qui référence la table Pays est utilise dans la table Destination.

Pour la ville, c'est plus compliqué dans la mesure où l'adhérent (si j'ai bien compris) peut soit choisir dans la liste des villes enregistrées pour le pays qu'il a sélectionné, soit saisir ce qu'il veut. D'où la présence des "Bord de mer", "A la montagne" et autres qui te posent problème.

Donc pour chaque pays, il y a la possibilité d'avoir "Bord de mer". Ce qui veut dire que dans la table "Lieu", il y aura des doublons du type "Bord de mer", "A la Montagne"...
Est-ce que cela pose problème ?

Oui, c'est un sérieux problème car il y a fort à parier qu'au bout d'un certain temps, ta table Lieu soit truffée de "Bord de mer", "A la plage", "A la montagne" avec toutes les variantes possibles et imaginables et d'autres qu'on n'imagine même pas !

Le seul moyen de garantir que la table Ville ne contienne que des villes, c'est d'obliger l'adhérent à choisir dans une liste de villes... Mais cela sous-entend que tu prévoies la liste de toutes les villes de tous les pays à l'avance !!!

Si tu autorises la saisie d'un lieu quelconque, il faut t'attendre aux effets de bord dont je parle plus haut.

[sam01]

Donc je suis coincé.
Je ne vois vraiment pas comment forcer les adhérents à choisir une ville comme destination.
A moins de mettre en commentaire :

Vous devez choisir une ville"

Encore faudrait-il qu'ils respectent la consigne. Ce qui n'est pas gagné.
Il y a aussi une autre option , ç serait de rajouter une champ dans la table destination qui indiquerait le périmètre : 10km - 50km - 100km aux alentours.
Ca permettrait à l'adhérent de ne pas limiter son choix à une ville uniquement.

[StringBuilder]

Juste une proposition à 2 centimes.

"Bord de mer", "A la montagne", c'est pas une ville, on est d'accord.
Mais ce n'est pas non plus un LIEU (pas plus qu'une destination !).

En effet, même une grande ville est assimilable à un point sur une carte.

Mais "bord de mer" ou "à la montagne", même si on sait de quelle mer il s'agit ou de quelle montagne, c'est tout sauf un point sur la carte.

En revanche, dans tous les cas, j'imagine que ton application à pour but de proposer des voyages à des adhérents, ou de recenser les voyages de adhérents.
Donc si on va "au bord de la mer", on va de toute façon, à un moment où à un autre, aller dans une ville.

Mais aussi, une chose importante, c'est que :

1/ En France, on peut aller au bord de la mer :
- Au bord de la mer du nord
- Au bord de la manche
- Au bord de l'atlantique
- Au borde de la méditerranée

2/ Au bord de la méditerrannée on peut être :
- En France (dans un certain nombre de villes)
- En Espagne
- En italie
...
Mais aussi sur un autre continent, si on va :
- En Algérie
- En Egyte
...
Et même encore un autre si on va :
- En Palestine
- En Israël
...
Et je parle pas des oceans...

On a la même chose avec les montagnes, qui servent souvent de démarcation entre deux pays, voir entre deux continents.

Alors j'aurais une autre proposition :

Une nouvelle table "type destination" qui contiendra :
- Bord de mer
- Montagne
- Etc.

Une nouvelle table "type destination précise" qui contiendra :
- Les noms des mers, montagnes, etc.
- Un lien vers le type de destination

Et une seconde table "ville type" qui fera le lien entre une ville (donc destination) et X types de destination précis.

Ensuite, en parallèle du choix continent->pays->ville tu pourras demander à choisir le type de destination et le type précis, avant de laisser l'adhérent saisir choisir parmi les villes qui répondent aux critères.

Le revers de la médaille, c'est qu'il va falloir se palucher les nouvelles villes une à une pour savoir si elles sont au bord de la mer, à la montagne, et/ou je ne sais quel autre critère.

[StringBuilder]

Sinon, je pense à un autre truc...

A la base, tu veux que ton gars puisse choisir une "destination" bord de mer en france par exemple.

Donc dans tous les cas, ma solution ne marche pas.

Il faut alors feinter et passer par une sorte de métamodèle.

Plus qu'une seule table fourre tout (gnark) :

destination
id
type_id -> type(id)
nom
gps

type
id
nom
sélectionnable (O/N) qui indique si l'adhérant peut choisir ce type comme destination

hierarchie
id
destination_mere -> destination(id)
destination_fille -> destionation(id)

Ensuite, à l'écran, tu affiche comme premier nivrau :
select nom from destination where id not in (select destination_fille from hierarchie)

Quand l'utilsateur choisi la destination :
- Tu charges un niveau inférieur avec :
select nom from destination where id in (selection destination_fille from hierarchie where id = @id)
- Si le type de la destination déjà choisie est "selectionnable", tu laisses la possibilité de choisir cette destination sans choisir de fille

Et ainsi de suite jusqu'à ce que plus aucune destination_fille n'existe.

Tu peux aussi ajouter une colonne dans "type", qui indique si c'est un type "géographique" (ville, etc). ou un type euh... "subjectif" (bord de mer, etc.) et les séparer dans deux listes distinctes à chaque niveau.

[sam01]

Bonjour StringBuilder,

j'avoue que j'ai un peu de mal à comprendre ta deuxième proposition. Notamment l'histoire du sélectionnable ou non.

Pourrais-tu me donner un exemple avec des nom de ville concret et des destination type "Bord de mer" etc.. dans tes tables.

Merci d'avance pour ton aide.

[sam01]

Je viens de penser à un autre truc.
Si je créais tout simplement une table thème qui contiendrait les champs :

Code :

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

1
2
3
4
5
6
id_th ==> identifiant thème
Plage
Bord de mer
Montagne
Campagne

Reste à savoir comment lier cette table ?
Faut-il la lier à la table continent, pays ou ville ?

C'est pas si simple vu qu'un adhérent peut choisir un continent comme destination, et choisir un thème. Ou il peut encoure choisir un pays et choisir un thème ou non.. etc...

La deuxième question que je me pose est : Faut-il que je laisse un champ libre pour saisir le thème, ou alors je mets une liste prédéfinie non modifiable sous forme de checkbox par exemple.

Qu'en pensez-vous ?

[StringBuilder]

Ça c'est ma première proposition (qui est un peu plus évoluée).

Il faut que ce soit en parallèle au choix continent>pays>ville

En effet, si je m'arrière à "pays", et que je coche "bord de mer", il faut que je puisse obtenir la liste de toutes les stations balnéaires du pays.

Le lien type de destination doit se faire, je pense, au niveau de la ville (dans tous les cas, au moment du voyage, on part bien en direction d'une ville, ne serait-ce que pour trouver un hébergement).

En revanche, je préconise aussi un "type précis", dans lequel on précise quelle mer, quelle montagne, etc.

Personnellement, quand je veux partie "à la mer", je pense à la méditerranée, à ou la limite l'atlantique. Clairement pas la mer du nord ni la manche.

-------------------

Quant à ma seconde proposition, il s'agit d'avoir des hiérarchies alternatives.

Par exemple, je choisi le continent "Asie", puis je choisi "bord de mer", puis "pacifique", et je choisi enfin un pays qui répond à ces critères avant de choisir ma ville.

Je reviens le temps de te faire un exemple de script SQL.

[StringBuilder]

Code sql :

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
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
drop table destination_lien;
drop table destination;
drop table theme;
 
create table theme
(
	theme_id int not null primary key,
	theme_nom varchar(50) not null,
	theme_alternate bit not null
);
 
create table destination
(
	destination_id int not null primary key,
	destination_nom varchar(50) not null,
	theme_id int not null references theme(theme_id)
);
 
create table destination_lien
(
	destination_lien_id int identity not null primary key,
	destination_mere int not null references destination(destination_id),
	destination_fille int not null references destination(destination_id),
	constraint ck_destination_lien check (destination_mere <> destination_fille),
	constraint cu_destonation_lien unique (destination_mere, destination_fille)
);
go
 
create trigger trg_destination_lien on destination_lien
after insert
as
begin
	if (select count(*) from inserted) > 0
	begin
		insert into destination_lien (destination_mere, destination_fille)
		select distinct dl1.destination_mere, i.destination_fille
		from inserted i
		inner join destination d on d.destination_id = i.destination_fille
		inner join theme t on t.theme_id = d.theme_id and t.theme_alternate = 1
		inner join destination_lien dl1 on dl1.destination_fille = i.destination_mere
		inner join destination_lien dl2 on dl2.destination_mere = dl1.destination_mere
		where not exists (select null from destination_lien dl where dl.destination_mere = dl1.destination_mere and dl.destination_fille = i.destination_fille);
	end;
end;
go
 
-- Les thèmes
insert into theme (theme_id, theme_nom, theme_alternate) values (1, 'Continent', 0);
insert into theme (theme_id, theme_nom, theme_alternate) values (2, 'Pays', 0);
insert into theme (theme_id, theme_nom, theme_alternate) values (3, 'Ville', 0);
insert into theme (theme_id, theme_nom, theme_alternate) values (4, 'Bord de mer', 1);
insert into theme (theme_id, theme_nom, theme_alternate) values (5, 'Montagne', 1);
 
-- Les destinations
insert into destination (destination_id, destination_nom, theme_id) values (1, 'Europe', 1);
insert into destination (destination_id, destination_nom, theme_id) values (2, 'Asie', 1);
insert into destination (destination_id, destination_nom, theme_id) values (3, 'France', 2);
insert into destination (destination_id, destination_nom, theme_id) values (4, 'Espagne', 2);
insert into destination (destination_id, destination_nom, theme_id) values (5, 'Italie', 2);
insert into destination (destination_id, destination_nom, theme_id) values (6, 'Corée', 2);
insert into destination (destination_id, destination_nom, theme_id) values (7, 'Brest', 3);
insert into destination (destination_id, destination_nom, theme_id) values (8, 'Nice', 3);
insert into destination (destination_id, destination_nom, theme_id) values (9, 'Rome', 3);
insert into destination (destination_id, destination_nom, theme_id) values (10, 'Chamonix', 3);
insert into destination (destination_id, destination_nom, theme_id) values (11, 'Gunsan', 3);
insert into destination (destination_id, destination_nom, theme_id) values (12, 'Mediterranée', 4);
insert into destination (destination_id, destination_nom, theme_id) values (13, 'Atlantique', 4);
insert into destination (destination_id, destination_nom, theme_id) values (14, 'Mer de Chine', 4);
insert into destination (destination_id, destination_nom, theme_id) values (15, 'Pyrénnées', 5);
insert into destination (destination_id, destination_nom, theme_id) values (16, 'Alpes', 5);
insert into destination (destination_id, destination_nom, theme_id) values (17, 'Roses', 3);
insert into destination (destination_id, destination_nom, theme_id) values (18, 'Suisse', 2);
insert into destination (destination_id, destination_nom, theme_id) values (19, 'Genève', 3);
 
-- Les liens
-- Europe
insert into destination_lien (destination_mere, destination_fille) values (1, 3);
insert into destination_lien (destination_mere, destination_fille) values (1, 4);
insert into destination_lien (destination_mere, destination_fille) values (1, 5);
insert into destination_lien (destination_mere, destination_fille) values (1, 18);
-- Asie
insert into destination_lien (destination_mere, destination_fille) values (2, 6);
-- France
insert into destination_lien (destination_mere, destination_fille) values (3, 7);
insert into destination_lien (destination_mere, destination_fille) values (3, 8);
insert into destination_lien (destination_mere, destination_fille) values (3, 10);
-- Espagne
insert into destination_lien (destination_mere, destination_fille) values (4, 17);
-- Italie
insert into destination_lien (destination_mere, destination_fille) values (5, 9);
-- Suisse
insert into destination_lien (destination_mere, destination_fille) values (18, 19);
-- Corée
insert into destination_lien (destination_mere, destination_fille) values (6, 11);
-- Brest
insert into destination_lien (destination_mere, destination_fille) values (7, 13);
-- Nice
insert into destination_lien (destination_mere, destination_fille) values (8, 12);
insert into destination_lien (destination_mere, destination_fille) values (8, 16);
-- Rome
insert into destination_lien (destination_mere, destination_fille) values (9, 12);
-- Chamonix
insert into destination_lien (destination_mere, destination_fille) values (10, 16);
-- Gunsan
insert into destination_lien (destination_mere, destination_fille) values (11, 14);
-- Roses
insert into destination_lien (destination_mere, destination_fille) values (17, 15);
insert into destination_lien (destination_mere, destination_fille) values (17, 12);
-- Genève
insert into destination_lien (destination_mere, destination_fille) values (19, 16);
 
-- Maintenant, on se connecte à l'application. Le point d'entrée, c'est toutes les destinations qui ne sont jamais filles :
select d.destination_id, d.destination_nom
from destination d
where d.destination_id not in (select dl.destination_fille from destination_lien dl);
/*
destination_id destination_nom
-------------- --------------------------------------------------
1              Europe
2              Asie
*/
 
-- Ok, on part pour l'Europe
select t.theme_nom, d.destination_id, d.destination_nom
from destination_lien dl
inner join destination d on d.destination_id = dl.destination_fille
inner join theme t on t.theme_id = d.theme_id
where dl.destination_mere = 1;
/*
theme_nom                                          destination_id destination_nom
-------------------------------------------------- -------------- --------------------------------------------------
Pays                                               3              France
Pays                                               4              Espagne
Pays                                               5              Italie
Bord de mer                                        12             Mediterranée
Bord de mer                                        13             Atlantique
Montagne                                           15             Pyrénnées
Montagne                                           16             Alpes
Pays                                               18             Suisse
*/
 
-- Le mieux est de créer une liste par thème, pour plus de clareté
 
-- On part pour la méditerranée
select t.theme_nom, d1.destination_id, d1.destination_nom
from destination_lien dl
inner join destination d1 on d1.destination_id = dl.destination_fille
inner join destination_lien d2 on d2.destination_mere = d1.destination_id
inner join theme t on t.theme_id = d1.theme_id
where dl.destination_mere = 1
and d2.destination_fille = 12;
/*
theme_nom                                          destination_id destination_nom
-------------------------------------------------- -------------- --------------------------------------------------
Pays                                               3              France
Pays                                               4              Espagne
Pays                                               5              Italie
*/
-- La Suisse a disparu puisque qu'elle n'est pas au borde de la méditerranée
 
-- Je choisi maintenant la France
select t.theme_nom, d1.destination_id, d1.destination_nom
from destination_lien dl
inner join destination d1 on d1.destination_id = dl.destination_fille
inner join destination_lien d2 on d2.destination_mere = d1.destination_id
inner join theme t on t.theme_id = d1.theme_id
where dl.destination_mere = 3
and d2.destination_fille = 12;
/*
theme_nom                                          destination_id destination_nom
-------------------------------------------------- -------------- --------------------------------------------------
Ville                                              8              Nice
*/
-- Il ne me reste plus que Nice

Le trigger sert à propager les thèmes aux parents (si "Nice" est au bord de la "Méditerranée", alors la "France" et l'"Europe" aussi). Ca évite de faire de la récursivité.
Attention, le trigger doit lui-même se déclencher récursivement, il faut donc activer l'option (présente, mais désactivée par défaut dans SQL Server par exemple)

Quand le gars se retrouve sans ville, quand il saisi la ville :
- La rattacher au niveau précédent (le pays)
- La rattacher au thème demandé (si présent)

Par exemple :

Code sql :

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

1
2
3
4
 
insert into destination (destination_id, destination_nom, theme_id) values (20, 'Sète', 3);
insert into destination_lien (destination_mere, destination_fille) values (3, 20);
insert into destination_lien (destination_mere, destination_fille) values (20, 12);

Et ainsi, si on recherche de nouveau "en france au bord de la méditerranée :

Code sql :

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

1
2
3
4
5
6
7
8
 
select t.theme_nom, d1.destination_id, d1.destination_nom
from destination_lien dl
inner join destination d1 on d1.destination_id = dl.destination_fille
inner join destination_lien d2 on d2.destination_mere = d1.destination_id
inner join theme t on t.theme_id = d1.theme_id
where dl.destination_mere = 3
and d2.destination_fille = 12;

On a bien le bon résultat :

Code sql :

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

1
2
3
4
5
 
theme_nom                                          destination_id destination_nom
-------------------------------------------------- -------------- --------------------------------------------------
Ville                                              8              Nice
Ville                                              20             Sète

[sam01]

Bonjour StringBuilder,
Merci beaucoup pour ton aide.
Mais le modèle que tu me proposes est beaucoup trop évolué.
En plus ça m'obligerait à refondre tout mon système de base de données.
Je me contenterais quelque chose de plus simple.
Dans mon application, lors du choix de la destination, les adhérents choisissent d'abord le continent, ensuite il en découle la liste des pays correspondants, ensuite si la ville n'existe pas, l'adhérent créé lui-même le nom de la ville et c'est à ce moment là que certaines personnes mettent :"bord de mer" etc...
Tu vas trouver que j'insiste mais comme je ne souhaite pas refondre toute mon application, j'aimerais
tout simplement proposer aux adhérents un liste prédéfinie. Type de destination :

Code :

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

1
2
3
4
5
Bord de mer
Montagne
Campagne
Parc d'attraction

Je ne souhaite pas me palucher , comme tu dis, toutes les villes qui sont au bord de mer d'un pays.
C'est pourquoi j'aimerais tout simplement m'arrêter là. mon but est d'éviter de retrouver dans ma table ville des champs tels :"bord de mer" etc...

Par contre, si je créé une telle table, tec-ce que d'où faire un lien avec la table continent, pays ou ville ?

[StringBuilder]

Soit tu fais le lien entre ville et theme, et à ce moment, tu es obligé d'une manière ou d'une autre de faire le lien entre les deux (soit en laissant les utilisateur enrichir la base, soit faisant toi-même).

Ou alors tu modifies ta table ville avec un flag "is_theme" par exemple, et tu stockes directement les thèmes dedans.

Tu affiches alors deux listes :
- celle avec is_theme = true
- celle avec is_theme = false + possibilité d'ajouter une ville

Je pense que c'est le plus simple.