Discussion :

[Cedric3111]

Bonjour tout le monde,

Je développe un outil permettant de faire du monitoring sur des offres d'emplois classées par pays. Les résultats devront s'afficher dans une grille updatable.

Pour le cas précis de états unis (et uniquement dans ce cas), je voudrais renvoyer l'état correspondant. C'est pour ca que j'ai pensé concevoir ma base de données ainsi avec une table de mapping :

//Table principale
ADVERTISING
------------
ad_id: Number
ad_entity :Varchar
ad_creator : Varchar
ad_country_fk : Number

//Table contenant tous les pays
COUNTRY
------------
co_id: Number
co_name :Varchar

//Table contenant les 51 états des USA
STATE
------------
st_id: Number
st_name :Varchar

//Table de mapping
COUNTRY_STATE_MAPPING
------------
map_coun_id: Number
map_st_id :Varchar

La première chose que je voudrais savoir, est-ce la solution idéale? (sachant qu'il n'y'a qu'un seul pays nécessitant une référence vers la table STATE)?

J'utilise donc cette requête (je cherche à ramaner chaque offre avec le pays correspondant et son état s'il existe) :

Code :

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

1
2
3
4
5
SELECT distinct a.ad_id,a.ad_entity,c.coun_name,st.stat_name
FROM iiiadvertising.advertising a
INNER JOIN iiiadvertising.country c ON a.ad_country_fk=c.coun_id
INNER JOIN iiiadvertising.country_state_mapping map ON c.coun_id=map.map_coun_fk
INNER JOIN iiiadvertising.state st ON map.map_stat_fk=st.stat_id;

Mais le problème est que cette requête me retourne 20 000 lignes alors qu'elle est sensée m'en ramener que 23.

Donc j'ai essayé avec une une requête imbriquée comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
select a.ad_entity,a.ad_country_fk,map_coun_fk
from iiiadvertising.advertising a, iiiadvertising.status s, iiiadvertising.user u
where a.ad_status_fk=s.st_id
and a.ad_marketer_fk=u.us_id
and a.ad_country_fk in (SELECT c.coun_id
                        FROM iiiadvertising.country c,
                        iiiadvertising.country_state_mapping map,
                        iiiadvertising.state st
                        WHERE c.coun_id=map.map_coun_fk
                        AND map.map_stat_fk=st.stat_id);

Ceci me retourne le nombre de ligne attendu. Mais comment accéder aux champs st.st_name, et co.co_name? (Déjà est-ce possible?).

Sinon est-ce un problème de conception de la base? Ou est-ce que mes requêtes sont hors-sujet?

Merci d'avance,
Ced

[CinePhil]

//Table contenant les 51 états des USA
STATE
------------
st_id: Number
st_name :Varchar

//Table de mapping
COUNTRY_STATE_MAPPING
------------
map_coun_id: Number
map_st_id :Varchar

map_st_id fait référence à st_id et doit être de type number au lieu de varchar !

Ceci dit, tu n'as pas besoin de la table COUNTRY_STATE_MAPPING.

Tu as la règle de gestion suivante :
Un COUNTRY peut être composé de plusieurs STATE et un STATE entre dans la composition d'un seul COUNTRY.

MCD :
COUNTRY -0,n----composer----1,1- STATE

Tables :
COUNTRY (co_id, co_name)
STATE (st_id, st_co_id, st_name)

J'utilise donc cette requête (je cherche à ramaner chaque offre avec le pays correspondant et son état s'il existe)

ADVERTISING
------------
ad_id: Number
ad_entity :Varchar
ad_creator : Varchar
ad_country_fk : Number

Comment trouves-tu à quel état se rapporte l'annonce ?

Règle de gestion :
Une ADVERTISING peut concerner un STATE et un STATE peut être concerné par une ADVERTISING.

MCD :
ADVERTISING -0,1----concerner----0,n- STATE

Tables :
STATE (st_id, st_co_id, st_name)
ADVERTISING (ad_id, ad_entity, ad_creator, ad_country_fk)
ADVERTISING_STATE (as_id_advertising, as_id_state)

Pour assurer la cohérence des données, il faudra prévoir un trigger qui n'autorisera l'ajout dans la table ADVERTISING_STATE que si ADVERTISING.ad_country_fk est bien l'identifiant des USA.

Maintenant, comme tu as la grande majorité des pays qui n'ont pas d'états, si tu veux les annonces de tous les pays, il te faut des jointures externes.

Code :

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

1
2
3
4
5
SELECT a.ad_id, a.ad_entity, c.coun_name, st.stat_name
FROM iiiadvertising.advertising a
INNER JOIN iiiadvertising.country c ON a.ad_country_fk = c.coun_id
LEFT JOIN iiiadvertising.advertising_state as ON as.as_id_advertising = a.coun_id
	LEFT JOIN iiiadvertising.state st ON as.as_id_state = st.stat_id;

À lire : mon billet de blog sur l'écriture des règles de gestion et la déduction du MCD.