Questions conceptions BDD sur mySQL - multiclé, relation 1-1, centralisation données

Bonjour tout le monde !

Je me pose plusieurs questions sur la conception de ma base de données centré sur MySQL 5.

Premiere question :
Est-il possible d'avoir une clé étrangère dans une table pour plusieurs autres tables en fonction de la donnée/ligne ?

Explication :
J'ai les 3 tables suivantes :
VILLE (id_ville, nom_ville, ...., id_région)
REGION (id_region, nom_region, ...., id_pays)
PAYS (id_pays, nom_pays,....)

Enfin, j'ai une autre table qui est par exemple MEMBRE avec un champ "localisation" en clé étrangere (ou clé tout court). Est-il possible que "localisation" puisse être une clé soit de VILLE, soit de REGION, soit de PAYS selon la précision fourni par le membre ? (Et avoir en plus par exemple un champ type_localisation qui indiquerai si la clé correspond à VILLE ou REGION ou PAYS)

Y a-t-il un moyen de modélisé ceci par des contraintes BDD/MySQL ?
Cette méthode est réalisable sans clé étrangere avec des contraintes dans le code PHP qui utilisera la BDD, mais dans ce cas, est-ce déconseillé (car trop de dépendance du code PHP) ?

L'intéret d'une telle methode serait de n'avoir que 2 champs(types + id) au lieu de n champs. Sachant également qu'à partir d'un ID on peut en connaitre les autres (Si on connait la VILLE, on connait sa REGION et son PAYS, Si on connait la REGION, on connait le PAYS mais pas la ville)

Est-il preferable d'avoir dans la table MEMBRE, un id pour chaque table de localisation ou de suivre cette méthode (ou une autre) ?

Ce cas là, je le vois apparaitre également dans d'autres cas dans ce que je veux concevoir. Ici, le gain serait assez faible (il n'y a au final qu'une colonne de gagner mais on pourrait très bien ajouté une Table ADRESSE, ZONE(s) voir peut-etre plus).

Deuxième question :

Est-il préférable de centraliser plusieurs informations similaires dans une même table (avec une indexation) ou de séparer en créant bien plus de relation TABLE-TABLE ?

Je me pose cette question pour le cas suivants :

Je souhaite stocker divers messages (ex: dans une table MESSAGE) tous composés d'un texte puis d'un titre. Tous ces messages auront les mêmes relations avec les autres tables (ex: AUTEUR, LANGUES, TRADUCTION....)
Cependant, chaque message peut-etre de type différent : guide, question (faq), expérience, astuces....dont chaque aura des attributs totalements différents.

Dans ce cas, faut-il centraliser tous les "MESSAGES" et les reliés à un type (second cas de la premiere question, clé + type pour ciblé vers la bonne table) ou mettre les (même) informations du messages (titre, texte, date..) dans chaque type d'écrit ? Dans le premier cas, tous les messages étant centralisé dans une même table, à long terme, la recherche ne sera-t-elle pas trop "longue" (par exemple si il y a des centaines de guides, des centaines d'astuces, des milliers de questions,...) ? Dans le second cas, il faut recréer autant de fois les relations assigné à MESSAGE qu'il y a de table correspondant à un type (guides, astuces, questions...).


Troisieme question :

Est-ce aisé de faire une relation 1-1 dans le cas suivant ? :
Séparer une table MEMBRE en deux avec d'un coté les champs obligatoires (pseudo, pass, mail, date...) et d'un autre les champs facultatifs (ou facultatifs dans la plupart des cas) (nom, prenom, adresse, telephone....).
Je pense que cette méthode optimise le stockage. (sur 1000 membres, je pense que seulement 200 environs auront rempli une information facultative, non ?)


Merci beaucoup pour vos réponses.

Bonne lecture !
et n'hésitez pas à me demander des détails ou de mieux expliquer si besoin ;)

A+
Cbil

Hello,

Voici mon humble avis sur chacun des points:

Est-il possible d'avoir une clé étrangère dans une table pour plusieurs autres tables en fonction de la donnée/ligne ?
Non, à moins de mettre chaque localisation (région, ville, pays) dans une seule et même table localisation avec un type de localisation. Il est peut-être possible de simuler cela avec une vue, mais niveau performance, ce serait à débattre et je ne n'ai pas de bench sous la main.

Est-il préférable de centraliser plusieurs informations similaires dans une même table (avec une indexation) ou de séparer en créant bien plus de relation TABLE-TABLE ?
Une table avec un type Message, ça me parait bien.

Est-ce aisé de faire une relation 1-1 dans le cas suivant ?
Possible, oui, Aisé, je ne pense pas. Le gain en place n'est pas forcément évident (Utilisation de Varchar par exemple) face aux jointures et autres problématiques d'insertion ou de suppression.

Merci Adjanakis pour ta réponse.

Ca va m'aider à faire des choix.

Je pense que pour localisation, le mieux est de faire 3 table associatif (une pour la ville, une pour le pays et une pour la région) avec des clés étrangères.

ville_membre (id_vm, id_ville, id_membre)
pays_membre (id_pm, id_pays, id_membre)
region_membre (id_rm, id_region, id_membre)

avec sans doute (facultatif) toujours le champ localisation mais qui indique s'il s'agit d'un pays, d'une ville, ou d'une region (afin de savoir directement ou faire la requete). Au final, ca détruit mon idee, mais ca évite d'avoir des valeurs null si l'on créé 3 champ "ville, pays, region" dans membre.

Pour les deux autres réponses, je n'ai rien d'autre à rajouter, je suis assez d'accord avec toi ;) .

Je ne met pas tout de suite "résolu", au cas où je me pose encore des questions / et pour indiquer mes choix finaux.

N'hésitez pas me faire part de vos remarques ^^.
et encore merci Adjanakis :D

a+
Cbil

Je vais essayer d'apporter ma pierre a l'edifice.

Question 1 :
La solution d'un champs "poubelle" ou l'on pourrait y retrouver des données dont les relations ne suivent pas une regle stricte est absolument a proscrire. Cela posera des problemes :

- Si les typages sont différents, la manipulations des données va etre hasardeuse
- Si une ville a le meme nom qu'une région par exemple, comment faire la différence ? ...

De plus, quelles sont les cardinalités de la relation Membre - Ville ?? si c'est du 1-1, alors les tables

ville_membre (id_vm, id_ville, id_membre)
pays_membre (id_pm, id_pays, id_membre)
region_membre (id_rm, id_region, id_membre)

n'ont pas d'interet.
Seul l' ID ville dans la table membre est pertinent (avec celui ci vous retrouverez aisément la region et le pays)

Question 2
Une table avec le type de message est une bonne solution

Question 3
La separation des données n'a pas lieu d'etre dans ce cas la

Voila, j'espere que cela peut vous aider
Bon courage

Salut Yanika_bzh et merci pour tes ajouts.

Je reviens sur la question 1 pour apporter plus de precision.

-relation membre - ville c'est du 1-n vu qu'une ville peut avoir plusieurs membre.
- la table ville (ou pays ou region) sera utilisé (normalement) par plusieurs autres tables comme membre (même relation appriori)

- Si les typages sont différents, la manipulations des données va etre hasardeuse :
je comptais utiliser le même type de clé primaire pour les 3 tables...mais c'est vrai que vu que le nombre de pays et nettement inferieur au nombre de ville, utilisé la même "grandeur" d'entier est inadapté.
- Si une ville a le meme nom qu'une région par exemple, comment faire la différence ? ...,
c'est pour ca que je comptais utilisé un autre champ de style "type de localisation", avec "ville, region, ou pays (ou autre)".
-Seul l' ID ville dans la table membre est pertinent (avec celui ci vous retrouverez aisément la region et le pays)
en fait, c'est à partir de l'inverse que mon idee s'est basé. Si on ne veut indiquer que le pays par exemple.

Mais de toute facon, l'idee de clé étrangere multiple est en effet à proscrire, j'en suis convaincu :)

En fait, du coup, le mieux serait finalement de creer id_ville, id_pays, et id_region dans la table membre, puis le membre choisit la precision qu'il souhaite donner ;)

Question 2 : je reviens dessus, car je suis pas sur, en relisant, d'avoir bien saisi vos deux reponses (qui sont identique)
Une table avec le type de message est une bonne solution

En fait, en disant ca, vous pensez qu'il faut :
- centraliser les donnees dans une seul table "MESSAGE" avec le type de message et d'autres tables (GUIDE, QUESTION, ...) avec les attribut selon le type de message.
- centraliser l'ensemble dans une seule table avec les attributs de tous les types (les attributs de guides avec les attributs question, par exemple).
- autre ??? :?

Question 3 : Tout a fait d'accord et ca confirme : question close :)


Merci beaucoup à vous deux !

a+
Cbil