Alternative entre plusieurs type d'objets
Le
BOUDOU Joseph
Bonjour,
Voici un problème qui se pose à moi assez souvent et avec lequel je
suis mal à l'aise. Il s'agit se situation ou un "objet" est soit un
truc soit un machin. Par exemple un compte en banque appartient soit à
un particulier (avec nom, prénom, civilité) soit à une entreprise
(nom, raison sociale, type).
Il me semble que la solution "orthodoxe" est de faire une table
contenant ce qu'il y a de commun, puis une table pour chaque cas
particulier.
Exemple concret et vécu. Dans un jeu de simulation économique, un
investisseur est soit un joueur, soit une société créée par un joueur.
CREATE TABLE Investisseurs (
id SERIAL PRIMARY KEY,
nom TEXT NOT NULL UNIQUE,
type CHAR(1) DEFAULT 'U' NOT NULL,
solde NUMERIC(14,2) DEFAULT 0 NOT NULL,
)
CREATE TABLE Joueurs (
est INTEGER PRIMARY KEY REFERENCES Investisseurs(id) ON DELETE CASCADE,
passwd CHAR(13) NOT NULL,
)
CREATE TABLE Societes (
est INTEGER PRIMARY KEY REFERENCES Investisseurs(id) ON DELETE CASCADE,
description VARCHAR(4000),
cours NUMERIC(14,2) DEFAULT 100 NOT NULL CHECK(cours > 0),
)
Le soucis est que pour conserver les contraintes d'intégrité, il me faut
tout un arsenal :
- index unique sur les clef étrangère (qui deviennent des clefs primaires) ;
- triggers sur l'insertion dans Joueurs et Societes pour vérifier que le type
correspond à ce qu'on inserre ;
- REVOKE UPDATE (type) ON TABLE Investisseurs.
Et même comme ça, il peut exister un investisseur qui n'est référencé ni
dans Joueurs, ni dans Societes.
Une autre solution est d'inverser la référence :
CREATE TABLE Investisseurs (
id SERIAL PRIMARY KEY,
nom TEXT NOT NULL UNIQUE,
joueur INTEGER UNIQUE REFERENCES Joueurs(id),
societe INTEGER UNIQUE REFERENCES Societes(id),
CHECK ((joueur IS NULL AND societe IS NOT NULL) OR
(societe IS NULL AND joueur IS NOT NULL)),
solde NUMERIC(14,2) DEFAULT 0 NOT NULL,
)
CREATE TABLE Joueurs (
id SERIAL PRIMARY KEY,
passwd CHAR(13) NOT NULL,
)
CREATE TABLE Societes (
id SERIAL PRIMARY KEY,
description VARCHAR(4000),
cours NUMERIC(14,2) DEFAULT 100 NOT NULL CHECK(cours > 0),
)
Et là le soucis est inverse, c'est à dire qu'on peut avoir une société ou un
joueurs qui n'est pas un investisseur. D'autre part ça me parait plus
difficile à "étendre", si par exemple on veut ajouter une troisième
alternative.
Bref, quelle est la "bonne" façon de modéliser ce genre de situation ?
J'utilise Pg-8.4, mais j'aimerais surtout avoir une réponse "théorique".
Merci de m'avoir lu jusqu'ici,
--
BOUDOU Joseph <jogo@matabio.net>
Voici un problème qui se pose à moi assez souvent et avec lequel je
suis mal à l'aise. Il s'agit se situation ou un "objet" est soit un
truc soit un machin. Par exemple un compte en banque appartient soit à
un particulier (avec nom, prénom, civilité) soit à une entreprise
(nom, raison sociale, type).
Il me semble que la solution "orthodoxe" est de faire une table
contenant ce qu'il y a de commun, puis une table pour chaque cas
particulier.
Exemple concret et vécu. Dans un jeu de simulation économique, un
investisseur est soit un joueur, soit une société créée par un joueur.
CREATE TABLE Investisseurs (
id SERIAL PRIMARY KEY,
nom TEXT NOT NULL UNIQUE,
type CHAR(1) DEFAULT 'U' NOT NULL,
solde NUMERIC(14,2) DEFAULT 0 NOT NULL,
)
CREATE TABLE Joueurs (
est INTEGER PRIMARY KEY REFERENCES Investisseurs(id) ON DELETE CASCADE,
passwd CHAR(13) NOT NULL,
)
CREATE TABLE Societes (
est INTEGER PRIMARY KEY REFERENCES Investisseurs(id) ON DELETE CASCADE,
description VARCHAR(4000),
cours NUMERIC(14,2) DEFAULT 100 NOT NULL CHECK(cours > 0),
)
Le soucis est que pour conserver les contraintes d'intégrité, il me faut
tout un arsenal :
- index unique sur les clef étrangère (qui deviennent des clefs primaires) ;
- triggers sur l'insertion dans Joueurs et Societes pour vérifier que le type
correspond à ce qu'on inserre ;
- REVOKE UPDATE (type) ON TABLE Investisseurs.
Et même comme ça, il peut exister un investisseur qui n'est référencé ni
dans Joueurs, ni dans Societes.
Une autre solution est d'inverser la référence :
CREATE TABLE Investisseurs (
id SERIAL PRIMARY KEY,
nom TEXT NOT NULL UNIQUE,
joueur INTEGER UNIQUE REFERENCES Joueurs(id),
societe INTEGER UNIQUE REFERENCES Societes(id),
CHECK ((joueur IS NULL AND societe IS NOT NULL) OR
(societe IS NULL AND joueur IS NOT NULL)),
solde NUMERIC(14,2) DEFAULT 0 NOT NULL,
)
CREATE TABLE Joueurs (
id SERIAL PRIMARY KEY,
passwd CHAR(13) NOT NULL,
)
CREATE TABLE Societes (
id SERIAL PRIMARY KEY,
description VARCHAR(4000),
cours NUMERIC(14,2) DEFAULT 100 NOT NULL CHECK(cours > 0),
)
Et là le soucis est inverse, c'est à dire qu'on peut avoir une société ou un
joueurs qui n'est pas un investisseur. D'autre part ça me parait plus
difficile à "étendre", si par exemple on veut ajouter une troisième
alternative.
Bref, quelle est la "bonne" façon de modéliser ce genre de situation ?
J'utilise Pg-8.4, mais j'aimerais surtout avoir une réponse "théorique".
Merci de m'avoir lu jusqu'ici,
--
BOUDOU Joseph <jogo@matabio.net>
Poser une question
Je ne vais pas être d'un grand secours car je fais des choses simplettes
en SGBD, mais la problématique que tu soulève est aisée à programmer avec un
langage objet comme le C# qui permet l'héritage et l'abstraction. Dans ce
cas on aurait une classe abstraite "compte_bancaire" avec toutes ses
caractéristiques de base et deux classes dérivées "cb_privé" et "cb_pro" qui
introduiraient en plus leurs propres spécificités. Mais comment mettre ça en
sauce SGBD est une bonne question...
Oui, en orienté objet c'est facile. Il existe bien des notions de
tables héritées en SQL (enfin avec Postgres en tous cas). Mais, à ma
connaissance, il n'est pas possible d'avoir une table "virtuelle" où il
serait impossible d'avoir un enregistrement qui ne serait présent que
dans cette table. Et puis surtout je ne trouve pas que cela s'accorde
bien avec le reste du langage (d'ailleurs ça marche très mal avec
Postgres). En C il n'y a pas d'objets, mais on peut faire ce dont je
parle en utilisant un union.
--
BOUDOU Joseph
Oui. Ou utiliser l'héritage dans PostgreSQL mais ce n'est pas sans
limitations...
Index sur les clefs c'est toujours une bonne chose, cf autres fils de
discussion.
Vous pourriez avoir aussi une séquence unique, pour générer les id de
toutes les tables, vu qu'un élément est dans l'une ou l'autre.
Vous pouvez "tricher" en partitionnant la séquence, par exemple nombres
pairs vs nombres impairs et chaque sous-séquence est attribuée à une
table.
Dans joueurs/societes , un index unique devrait suffire non, plutôt que
des clefs primaires ?
Avec deux tables, le type n'est pas indispensable, vu que vous pouvez
vous en sortir avec 2 LEFT OUTER JOIN. Ce qui résout le problème du
REVOKE et du trigger sur l'insertion.
Un trigger peut vérifier cela.
Ce qui, sur votre exemple bien précis, me paraît plus collé au besoin.
Ce qui peut arriver, non, dans la modélisation ?
Autre piste, comme variation de votre dernier exemple et qui résout les
problèmes d'extensions futures:
* table investisseurs sans attribut joueur ni societe
* table supplémentaire investisseurs_type définie avec:
- investisseur_id en référence sur investisseurs.id
- type (éventuellement), codant la table fille concernée
- type_id contenant l'id dans la table joueurs/societes
mais alors on perd l'intégrité référentielle, en tout cas sans trigger
Ou alors
investisseur_id
joueur_id
societe_id
en ayant les références et en vérifiant que l'un vaut NULL.
Les séquences d'id sont ainsi décorrélées.
Ce ne sont que quelques pistes, en espérant que ca vous donne des idées.
--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
Le 23/09/2010 10:44, BOUDOU Joseph a écrit :
lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/.../heritage/
Cette technique de modélisation s'appelle l'héritage.
Si vous conceviez vos bases à partir d'un MCD (modèle conceptuel de
données), ce mécanisme serait automatisé par l'outil de modélisation;
Par exemple Power AMC, sait faire cela très bien... et n'oublie pas les
bonne clef, index et la façon de gérer l'héritage, y compris s'il est
exclusif en écrivant à votre place les trigger d'exclusion mutuelle !
La colonne type est inutile si elle sert à départager entre joueur et
société et même dangereuse car il y a redondance de l'information ! En
effet, vous savez si un Investisseur est un joueur ou une société pas la
jointure. Ceci est facilité si vous créez une vue pour ce faire comme ceci :
CREATE VIEW V_Investisseurs_Enfants
AS
SELECT T.*, CASE
WHEN J.est IS NOT NULL THEN 'J'
WHEN S.est IS NOT NULL THEN 'S'
END AS Type
FROM Investisseurs AS T
LEFT OUTER JOIN Joueurs AS J
ON T.id = J.est
LEFT OUTER JOIN Societes AS S
ON T.id = S.est
Au passage nommer une colonne "Type" n'est pas une bonne idée car comme
c'est un mot clef de SQL, vous allez au devant de problèmes de
génération d'erreur de syntaxe...
Lisez les articles que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/...partie1#L1
Et complétez pas cela :
http://sqlpro.developpez.com/cours/motsreserves/
CASCADE,
Au passage les colonnes "est" devrait être "id". En effet dans un modèle
de données, la même information doit avoir partout le même nom, ce qui
est le cas des clef étrangères qui doivent avoir le même nom que la clef
primaire de référence (sauf impossibilité). La encore un outil de
modélisation, n'aurait pas fait la faute
inutile, car les clef primaire sont indexées
inutile car la vue s'en occupe !
inutile car la colonne n'existe pas du fait de la vue. Elle n'est donc
pas misajourable !
Ce qu'en revanche vous avez oublié c'est de gérer l'exclusion mutuelle
entre joueurs et investisseur qui se règle à coup de déclencheurs.
Voici dans la papier cité des exemples de triggers de gestion de
l'exclusion mutuelle :
http://sqlpro.developpez.com/cours/...ritage/#L5
Il sont écrit pour SQL Server, mais vous aurez toute facilité de les
traduire en PostGreSQL !
Notez qu'ils ont été écrit automatiquement pas Power AMC !
A +
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
Sur fr.comp.applications.sgbd, Patrick Mevzek disait :
D'une part il faut aussi la contrainte NOT NULL et d'autre part cette
clef étrangère sera la colonne de jointure "naturelle". Il me parait
donc normal d'avoir une clef primaire.
Oui. Voir la réponse à F. Brouard.
Je ne vois vraiment pas comment. Quand ce trigger serait-il lancé ?
Mais cette solution est-elle normalisée ?
Non, cela ne doit pas arriver. Et la partie applicative suppose que
ce n'est pas le cas et agit en conséquence.
Oui, merci. Ça me fait réfléchir.
--
BOUDOU Joseph