Discussion :

[Perplexe]

Bonjour, et désolé de ce titre imprécis mais je ne sais pas comment mieux définir le problème en quelques mots (=> recherche infructueuse).

J'ai une simple table 'membres' (n°, nom, email...), et une table 'contrats' reprenant leurs infos de contrat comme :

Table 'contrats' :
- membre_no
- poste (par ex. : 'commandant', ou 'mécanicien',...un 15aine en tout)
- navigation (par ex. : 'long-cours', 'cabotage'...)
- type (CDD, CDI, XXX,... 5 ou 6)
- ... en tout une vingtaine de catégories contenant chacune un certain nombre de possibilités, (toutes exclusives les unes des autres).

Je veux proposer d'envoyer à ces membres des offres, pubs, sondages, en me basant sur ces infos de la table 'contrats'.

Par exemple, tous les membres avec les infos contrats suivantes :
- poste = 'commandant'
- navigation = *
- type = 'CDI' OU 'CDD'
etc..

Avant d'envoyer l'offre, je veux savoir combien j'ai de destinataires en fonction de ma sélection;

De plus, chaque membre doit savoir combien d'offres sont disponibles pour lui.

Je pensais donc créer une table 'offres' sur ce modèle :

N° (autoincrement)
type d'offre (1 code genre : PUB)
et pour chaque catégorie, un champ texte assez long pour recevoir toute les options retenues séparées par une virgule :
poste (ex. : 'CDT' pour commandant)
navigation (ex. : *)
type (ex. : 'CDI, CDD')

Et utiliser ces requêtes pour mes deux besoins :

Connaître le nombre d'offres de type PUB pour 1 membre :
SELECT COUNT(N°) FROM offres WHERE type='PUB' AND poste LIKE ('CDT') AND type LIKE ('CDI','CDD')...

Connaître le nombre de membres ciblés par une offre :
SELECT * FROM membres m JOIN contrats c ON n°=membre_no WHERE contrats.poste='CDT' AND contrats.contrat='CDI' OR contrats.type='CDD'...

Question : Quel est votre avis : est-ce que ça tient debout et auriez-vous une autre organisation plus pertinente en tête ?

J'espère avoir été suffisamment clair. Merci de m'avoir lu et plus encore de répondre.

Px

[CinePhil]

Ton modèle de données est inabouti.
Si je comprends bien, un Contrat se rapporte à un et un seul Membre ?
Il en découle le MCD :
Contrat -1,1----Se rapporter----0,n- Membre

Mais je vois aussi que tes contrats :
- concernent un seul poste (parmi une quinzaine) ;
- pour un seul type de navigation (parmi une liste définie) ;
- sont d'un seul type (parmi une liste définie).

Lorsqu'un attribut peut prendre un certain nombre de valeurs définies, il faut externaliser l'attribut dans une entité qui référence les valeurs possibles.
Ainsi on aura le MCD (en changeant un peu la partie déjà faite pour faciliter la représentation ici) :
Membre -0,n----Signer-------1,1- Contrat
Poste -0,n----Concerner----1,1--------|
Navigation -0,n----Concerner----1,1-|
FormeContrat -0,n----Typer----1,1---|

Et donc les tables :
Membre (M_Id, M_Nom, ...)
Poste (P_Id, P_Sigle, P_Libelle, ...)
Navigation (N_Id, N_Sigle, N_Libelle, ...)
FormeContrat (FC_Id, FC_Sigle, FC_Libelle, ...)
Contrat (C_Id, C_IdMembre, C_IdPoste, C_IdNavigation, C_IdFormeContrat, ...)

La table contrat n'accueille que les identifiants des autres entités de référence. cela s'appelle une clé étrangère.

Par contre, je ne vois pas très bien ce que sont tes offres. Mais tu peux appliquer le même principe que ci-dessus pour le type de l'offre.
TypeOffre -0,n----Typer----1,1- Offre

[Perplexe]

Je comprends bien ton analyse, mais elle complexifierait beaucoup ma base (un contrat est composé de 10 paramètres différents, mais tous connus et finis) sans que le bénéfice m'en apparaisse évident (je peux me tromper).

En fait, un membre aura plusieurs contrats dans le temps, chaque contrat pouvant durer 3 ou 4 mois, et c'est sur le plus récent de ces contrats que doit être sélectionnée l'offre. D'où cette relation 1 membre => plrs contrats.

Quand bien même, si une offre s'adresse à 4 postes différents, comment enregistrer cette information pour qu'elle puisse servir dans les deux sens (combien de membres doivent recevoir mon offre / combien d'offre différentes un membre donné doit recevoir).

Désolé si tout semble bancale dans mon raisonnement.

Px

[CinePhil]

Quand bien même, si une offre s'adresse à 4 postes différents, comment enregistrer cette information pour qu'elle puisse servir dans les deux sens

MCD :
Offre -0,n----Adresser----0,n- Poste

Tables :
Offre (O_Id, O_Texte,...)
Poste (P_Id, P_Sigle, P_Libelle, ...)
Offre_Poste(OP_IdOffre, OP_IdPoste, ...)

C'est le principe même des BDDR !

[Oishiiii]

J'allais répondre beaucoup plus tôt, avant CinePhil, mais j'ai était happé par une réunion. Je le re-colle ici, cela sera peut être utile :

La table "Offre", semble très bien oui.

Mais il y aura maintenant des informations communes aux tables "Offres" et "Contrats" et "redondantes" pour chaque lignes. Il faudrait sortir chacune de ses informations dans une table.
Je pense à vos colonnes Poste, Navigation, Type, etc....

Exemple (clé primaire soulignées, clé étrangères en italiques) :

Code :

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

1
2
3
4
Membre(mbr_id, mbr_nom)
         1       Pierre
         2       Paul

Code :

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

1
2
3
4
5
Type_Contrat(tpc_id, tpc_libelle)
           1       CDI
           2       CDD
           3       CDT

Code :

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

1
2
3
4
Navigation(nav_id, nav_libelle)
           1      long-cours
           2      cabotage

Code :

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

1
2
3
4
Type_Offre(tpo_id, tyo_libelle)
                  1        PUB
                  2        SONDAGE

Code :

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

1
2
3
4
Contrat(ctt_id, mbr_id, tpc_id, nav_id)
        1         2      2      2
        2         1      3      1

Code :

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

1
2
3
4
Offre(off_id, off_nom, tpo_id, tpc_id, nav_id)
     1     Offre_1      1        2          2
     2     Offre_2      2        2          2

Ensuite en SQL.

Pour trouver le nombre d'Offre "PUB" pour le membre 1 :

Code SQL :

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

1
2
3
4
5
6
SELECT Count(*) AS nb_offre
FROM Contrat
    JOIN Offre
        USING(tpc_id, nav_id)
WHERE mbr_id=1
AND tpo_id=2

On effectue la jointure explicitement sur tpc_id et nav_id (toutes les colonnes communes entre les tables Offre et Contrat).
Une jointure naturelle devrait suffire :

Code SQL :

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

1
2
3
4
5
SELECT Count(*) AS nb_offre
FROM Contrat
    NATURAL JOIN Offre
WHERE mbr_id=1
AND tpo_id=2

Pour le nombre de membres ciblés par une Offre bien précise :

Code SQL :

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

1
2
3
4
5
SELECT Count(DISTINCT mbr_id)
FROM Contrat
    JOIN Offre
        USING(tpc_id, nav_id)
WHERE off_id=1

Donc PS:
Je n'avais pas pensé à associer la table des Offre/PUB/etc.. avec celle des Contrats comme le propose Cinephil. Je les ai vu comme deux choses différentes.
Mais si c'est possible, cela simplifiera les manipulations en SQL.

[Perplexe]

MCD :
Offre -0,n----Adresser----0,n- Poste

Tables :
Offre (O_Id, O_Texte,...)
Poste (P_Id, P_Sigle, P_Libelle, ...)
Offre_Poste(OP_IdOffre, OP_IdPoste, ...)

C'est le principe même des BDDR !

Merci de ta réponse.

Donc si je te suis, chaque paramètre qui caractérise un contrat donne lieu à la création de 2 tables. Une qui contient les variantes de chaque paramètre (Table poste : commandant, mécanicien...; table type:CDD, CDI, XXX...; etc...), et une table chargée d'assurer la liaison entre la table offre et la table paramètre (dans notre exemple, Poste) ?

Ça signifie 20 tables au minimum ? Il n'existe pas de solution moins complexe ?

Désolé si je suis à coté de la plaque, et merci de ton temps.

Px

[Perplexe]

Oishiiii, si j'ai bien compris votre longue réponse (merci pour ça), je ne suis pas certain qu'elle réponde à 100% à mon besoin :

Code :

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

1
2
3
4
Offre(off_id, off_nom, tpo_id, tpc_id, nav_id)
     1     Offre_1      1        2          2
     2     Offre_2      2        2          2

Dans ce cas, chaque offre ne concerne par exemple qu'un seul type de navigation. Or j'ai besoin pour chaque paramètre constitutif d'un contrat (poste, type, navigation,...) de spécifier 1 ou plusieurs critères (jusqu'à TOUT qui est plus facile à gérer).

Donc nous il faudrait quelque chose comme :

Code :

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

1
2
3
4
Offre(off_id, off_nom, tpo_id, tpc_id, nav_id)
     1     Offre_1      1        2          2;5
     2     Offre_2      2        2          2

Est-ce que ma remarque est pertinente ou je n'ai pas bien compris ?

Px

[Oishiiii]

Dans ce cas.
Il faut suivre ce qu'a expliqué CinePhil.

Lorsqu'un attribut peut prendre un certain nombre de valeurs définies, il faut externaliser l'attribut dans une entité qui référence les valeurs possibles.

Ça, c'est un fait qui s'applique dans tous les cas.
Chaque "paramètre constructif" d'un contrat doit être sorti dans une table.
Comme ceci par exemple :

Poste (P_Id, P_Sigle, P_Libelle, ...)
Navigation (N_Id, N_Sigle, N_Libelle, ...)

Ensuite, pour le nombre de tables cela va dépendre de l'association, du lien entre les contrats (ou les offres) et ces "paramètres".

Si pour un Contrat n'est concerné/associé qu'un seul Poste ou un seul type de navigation. De la même manière qu'un contrat ne concerne qu'un seul Membre, il suffit de placer une clé étrangère dans la table Contrat :

Membre -0,n----Signer-------1,1- Contrat
Poste -0,n----Concerner----1,1--------|
Navigation -0,n----Concerner----1,1-|

Et donc les tables :
Membre (M_Id, M_Nom, ...)
Poste (P_Id, P_Sigle, P_Libelle, ...)
Navigation (N_Id, N_Sigle, N_Libelle, ...)

Contrat (C_Id, C_IdMembre, C_IdPoste, C_IdNavigation, C_IdFormeContrat, ...)

Par contre effectivement;
Si un Contrat concerne plusieurs Poste ou type de Navigation, etc..
Il faut ajouter une "table jointure" permettant la liaison entre les deux tables. Et il n'y a pas de clé étrangère dans la table Contrat.

Si une Offre peux s'adresser à plusieurs Poste, entre les tables Poste et Offre, on va créer la "table jointure" Offre_Poste.

MCD :
Offre -0,n----Adresser----0,n- Poste

Tables :
Offre (O_Id, O_Texte,...)
Poste (P_Id, P_Sigle, P_Libelle, ...)
Offre_Poste(OP_IdOffre, OP_IdPoste, ...)

Exemple :

Code :

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

1
2
3
4
Offre (O_Id, O_Texte,...)
       1    Offre_1
       2    Offre_2

Code :

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

1
2
3
4
Poste (P_Id, P_Sigle, P_Libelle, ...)
        1     CDI     Contrat à Durée Indéterminée
        2     CDD    Contrat à Durée Déterminée

Code :

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

1
2
3
4
5
Offre_Poste(OP_IdOffre, OP_IdPoste, ...)
               1               1
               1               2
               2               2

L'Offre_1 concerne à la fois les CDI et CDD.
L'Offre_2 ne concerne que les CDD.

Peut-être faudrait-il reprendre la discussion dans un forum plus approprié (Général Développement/Conception/Modélisation/Schéma) et partir d'un MCD ?

[Perplexe]

Ok, j'ai compris. Je vous remercie tous les deux.

Je vais faire des tests et je posterai la solution retenue ici.

Bonne journée et merci encore.

Px

[Perplexe]

Re-bonjour, me voilà avec la solution retenue, pour ceux qui auront la patience de relire le thread.

J'ai finalement opté pour une table SELECTION contenant le numéro de l'offre (sondage, offre d'emploi, pub...) et autant de champs que de critères (cf. ci-dessus : type de contrat, poste occupé, type de navigation, tranche de salaire, etc).

Mon formulaire permet de sélectionner les critères et les concatène sous forme de codes (4 caractères) dans chaque champs critère de la table SELECTION. Par ex., le champs contrat peut recevoir la valeur "CDI,CDD".

Connaître le nbre de membre correspondant à la sélection :
Ma requête de sélection des membres correspondant aux critères est donc construite en PHP avec un explode de chaque champ critère de la table SELECTION soit :

Code :

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

SELECT COUNT(DISTINCT member_id) FROM members WHERE member_id>0 AND (contrat='CDI' OR contrat='CDD') AND (poste='...' OR poste='...' OR poste='...')...

Lister les offres correspondant à un membre:
Une fois la sélection terminée, l'utilisateur clique sur un lien qui déclenche la création dans une table OFFRESELECT d'une ligne par correspondance membre/offre :

Code :

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

INSERT INTO OFFRESELECT (offre_type, offre_ref, member_id) SELECT 'sondage', $sondage_no, member_id FROM member WHERE member_id>0 AND (contrat='CDI' OR contrat='CDD') AND (poste='...' OR poste='...' OR poste='...')...

De cette façon, lors de la visite d'un membre sur sa page, je n'ai plus qu'à interroger la table OFFRESELECT pour savoir quelles offres afficher.

Conclusion:
Cette manip me permet de ne pas créer 20 tables supplémentaires avec toutes le code associé et les risques d'erreur de développement que cela ajoute.

Si vous avez des commentaires, suggestions ou critiques, n'hésitez pas !

Merci pour tout.

Px