Heritage de tables

En statistique nous sommes souvent confronté à trois grands types de variables:
- Binaire
- Catégorielle (qualitative)
- Quantitative (continue ou discrète).

Lorsque l'on doit stocker ce type de variable dans une base de donnée, il serait tentant d'utiliser trois tables différentes et de stocker le type de la variable dans une table de définition des variables:
-- Définition de la variable (métadonnée)
tblVariable
VarID int;
type int; (1-Binaire, 2-Categorielle, 3-Quantitative)
Code varchar(10)

-- valeur de la variable binaire
tblBinaire
ID int;
VarID int;
Valeur BIT;

-- valeur de la variable catégorielle
tblCategorielle
ID int;
VarID int;
Valeur varchar(10);

-- valeur de la variable quantitative
tblQuantitative
ID int;
VarID int;
Valeur DECIMAL 10.4;

Le problème de cette approche est qu'il est difficile de créer une intégrité référencielle entre la table Variable et les autres tables puisque le type de variable définira avec quelle table on doit faire le lien et donc les ID entre les différentes tables pourront se recouvrir. Il est donc difficile de définir une FOREIGN key entre 3 tables pouvant avoir des ID Identique et une table source...

Une approche plus simple pourrait considérer la table variable comme une table de base dont hérite les autres tables.

CREATE TABLE tblVariable
(
varid SERIAL PRIMARY KEY,
Code varchar(10);
)

CREATE TABLE tblVariableInst
(
id SERIAL PRIMARY KEY, -- idéalement autonumber
varid INT
FOREIGN KEY (varid) REFERENCES tblVariable(varid)
)

CREATE TABLE tblBinaire
(
id INT UNSIGNED PRIMARY KEY NOT NULL,
Valeur BIT,
FOREIGN KEY (id) REFERENCES tblVariableInst(varid)
)

CREATE TABLE tblCategorielle
(
id INT UNSIGNED PRIMARY KEY NOT NULL,
Valeur Varchar(10),
FOREIGN KEY (id) REFERENCES tblVariableInst(varid)
)

CREATE TABLE tblQuantitative
(
id INT UNSIGNED PRIMARY KEY NOT NULL,
Valeur NUMERIC 10.4;
FOREIGN KEY (id) REFERENCES tblVariableInst(varid)
)

Pour créer une nouvelle variable liée à une variable modèle 1234 par exemple.
@var_id=INSERT INTO tblVariableInst (1234) RETURN ID
INSERT INTO tblQuantitative (@var_id, 9.25)

Ces deux lignes devront être placée dans une transaction, pour éviter d'avoir des ID de VarInstance perdu en cas de problème.

L'avantage de cette approche est que l'intégrité référentielle est parfaitement maintenue entre la table de définition tblVariable (métadonnée) et les tables filles.

Rmk: En sql server on utilisera @@IDENTITY_SCOPE pour obtenir l'ID crée dans la table de base. Par contre en PostgreSQL il est possible de demander au INSERT de retourner le dernier ID crée. L'ID crée pour toutes les tables filles est stocké dans la table tblVariableInst (Variable instance) qui contient une clé étrangère sur la table de définition.