Alternative entre plusieurs type d'objets

"BOUDOU Joseph" <jogo@matabio.net> a écrit dans le message de groupe de
discussion : 20100923104406.2e1c4747.jogo@matabio.net...

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.

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...

la problématique que tu soulève est aisée à programmer avec un
langage objet

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 <jogo@matabio.net>

Le Thu, 23 Sep 2010 10:44:06 +0200, BOUDOU Joseph a écrit:

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.

Oui. Ou utiliser l'héritage dans PostgreSQL mais ce n'est pas sans
limitations...

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.

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.

Et même comme ça, il peut exister un investisseur qui n'est référencé
ni dans Joueurs, ni dans Societes.

Un trigger peut vérifier cela.

Une autre solution est d'inverser la référence :

Ce qui, sur votre exemple bien précis, me paraît plus collé au besoin.

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.

Ce qui peut arriver, non, dans la modélisation ?

D'autre part ça me
parait plus difficile à "étendre", si par exemple on veut ajouter une
troisième alternative.

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
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Bonjour,


Le 23/09/2010 10:44, BOUDOU Joseph a écrit :

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.

lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/modelisation/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 !

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,
...
)

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/sqlaz/ddl/?page=partie1#L1
Et complétez pas cela :
http://sqlpro.developpez.com/cours/motsreserves/

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),
...
)

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

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) ;

inutile, car les clef primaire sont indexées

- triggers sur l'insertion dans Joueurs et Societes pour vérifier que le type
correspond à ce qu'on inserre ;

inutile car la vue s'en occupe !

- REVOKE UPDATE (type) ON TABLE Investisseurs.

inutile car la colonne n'existe pas du fait de la vue. Elle n'est donc
pas misajourable !

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,

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/modelisation/heritage/#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 *************************

Bonjour, et merci de m'avoir répondu,

Sur fr.comp.applications.sgbd, Patrick Mevzek disait :

Dans joueurs/societes , un index unique devrait suffire non, plutôt
que des clefs primaires ?

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.

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.

Oui. Voir la réponse à F. Brouard.

Et même comme ça, il peut exister un investisseur qui n'est
référencé ni dans Joueurs, ni dans Societes.

Un trigger peut vérifier cela.

Je ne vois vraiment pas comment. Quand ce trigger serait-il lancé ?

Une autre solution est d'inverser la référence :

Ce qui, sur votre exemple bien précis, me paraît plus collé au besoin.

Mais cette solution est-elle normalisée ?

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.

Ce qui peut arriver, non, dans la modélisation ?

Non, cela ne doit pas arriver. Et la partie applicative suppose que
ce n'est pas le cas et agit en conséquence.

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.

Oui, merci. Ça me fait réfléchir.


--
BOUDOU Joseph <jogo@matabio.net>

Sur fr.comp.applications.sgbd, SQLpro disait :

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.

lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/modelisation/heritage/

Merci beaucoup. Cette article est effectivement intéressant. Ma
première solution est donc bien la méthode classique pour résoudre
mon soucis.

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...

Oui, mais Power AMC coûte bien trop cher pour moi qui ne fait du SQL
que pour mon plaisir (malheureusement). Et puis transformer un modèle
conceptuel en base de données SQL me plaît beaucoup.

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,
...
)

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.

Oui, c'est vrai et ce n'est pas comme cela que j'avais implémenté ma
table. En fait, chaque société agit dans un secteur, et chaque secteur
appartient à une catégorie. Et il est plus simple pour l'application de
considérer les joueurs comme une de ces catégories. Ainsi la vue serait
plutôt :

SELECT i.*, COALESCE(c.categorie, 'U') as categorie
FROM Investisseurs i
LEFT OUTER JOIN Societes s ON i.id = s.est
LEFT OUTER JOIN C_Secteur c ON s.secteur = c.id

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...

Oui, d'ailleurs à l'époque où j'ai commencé cette base de données je
croyais qu'il était impossible d'avoir un tel nom de colonne.

Au passage les colonnes "est" devrait être "id".

Oui encore. Mais changer le nom d'une colonne est délicat, maintenant.

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é).

Il me semble que nommer une clef étrangère avec le même nom que ce
qu'on aurait mis pour la liaison correspondante dans un schéma UML est
une bonne idée. Par exemple le dirigeant d'une société, représenté par
une clef étrangère sur Investisseurs(id) est nommée "dirigeant".

- index unique sur les clef étrangère (qui deviennent des clefs
primaires) ;

inutile, car les clef primaire sont indexées

C'est une des raisons qui font que c'est un clef primaire. Pas
question de rajouter une contrainte d'unicité à une clef primaire,
bien sûr.

- triggers sur l'insertion dans Joueurs et Societes pour vérifier
que le type correspond à ce qu'on inserre ;

inutile car la vue s'en occupe !

Il faut alors un trigger pour vérifier que l'Investisseur n'est pas
déjà dans l'autre table. Le genre de trigger que vous présentez dans
votre article.

Une autre solution est d'inverser la référence :
[snip]

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.

Vous parlez toujours de la première solution, non ? Dans ce cas
l'exclusion mutuelle était gérée avec les triggers vérifiant le type.
En fait j'ai de tels triggers pour l'insertion dans d'autre tables où
la catégorie de l'investisseur compte.

Voici dans la papier cité des exemples de triggers de gestion de
l'exclusion mutuelle :
http://sqlpro.developpez.com/cours/modelisation/heritage/#L5

Il reste cependant le possibilité d'avoir un investisseur qui n'est
ni une société, ni un joueur. Et je ne sais pas comment éviter cela.


--
BOUDOU Joseph <jogo@matabio.net>

Le Sun, 26 Sep 2010 10:58:14 +0200, BOUDOU Joseph a écrit:

Dans joueurs/societes , un index unique devrait suffire non, plutôt que
des clefs primaires ?

D'une part il faut aussi la contrainte NOT NULL et d'autre part cette

Un élément peut avoir la contrainte NOT NULL sans être une clef primaire.

clef étrangère sera la colonne de jointure "naturelle". Il me parait
donc normal d'avoir une clef primaire.

??

Quand vous faites une jointure entre a.X et b.Y vous définissez et X et Y
comme clefs primaires ? A chaque fois ?

Et même comme ça, il peut exister un investisseur qui n'est
référencé ni dans Joueurs, ni dans Societes.

Un trigger peut vérifier cela.

Je ne vois vraiment pas comment. Quand ce trigger serait-il lancé ?

Un trigger attaché à toute opération d'ajout/modification dans
investisseur qui lance une procédure stockée qui vérifie ce qui se passe
dans les tables joueurs/societes.

--
Patrick Mevzek . . . . . . . . . . . . . . Dot and Co
<http://www.dotandco.net/> <http://www.dotandco.com/>
<http://www.dotandco.net/ressources/icann_registrars/prices>
<http://icann-registrars-life.dotandco.net/>

Sur fr.comp.applications.sgbd, SQLpro disait :

> Il reste cependant le possibilité d'avoir un investisseur qui
> n'est ni une société, ni un joueur. Et je ne sais pas comment
> éviter cela.
>
>
C'est possible si votre SGBDR gère la déférabilité des contraintes et
les assertions :
[snip]
Mais rare sont les SGBDR à accepter la déférabilité des contraintes !

Pour ceux que ça intéresse, postgresql v.8.4 gère la déférabilité des
contraintes. Par contre il ne connaît pas les assertions et n'accepte
pas de sous-requêtes dans les contraintes CHECK.

Il a cependant une solution à mon problème. En effet, il est possible
de déferrer des triggers AFTER ROW avec la commande CREATE CONSTRAINT
TRIGGER. Ce n'est pas standard, mais ça fonctionne.

Merci à tous de m'avoir éclairci les idées,

--
BOUDOU Joseph <jogo@matabio.net>