Modèle objet-relationel¶

ORDBMS : Object-Relational DBMS

Contacts normalisés¶

`contacts1` Schéma relationnel¶

DROP SCHEMA IF EXISTS contacts1 CASCADE;
CREATE SCHEMA contacts1;
SET search_path TO contacts1;

CREATE TABLE users
(
    uid               INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username          VARCHAR(15) NOT NULL,
    email             TEXT        NOT NULL,
    firstname         TEXT,
    lastname          TEXT,
    emergency_contact TEXT
);

INSERT INTO users (username, email, emergency_contact)
VALUES ('denis', 'denis.rinfret@example.com', 'help@example.com'),
       ('minh', 'minh@example.com', 'contact@example.com');

SELECT *
FROM users;

uid	username	email	firstname	lastname	emergency_contact
1	denis	denis.rinfret@example.com	None	None	help@example.com
2	minh	minh@example.com	None	None	contact@example.com

Que devons-nous faire pour permettre à un utilisateur d’avoir plus d’un contact ?¶

Si le nombre de contacts est fixe, disons n contacts, alors nous pourrions avoir n colonnes de contacts, tant que n est petit.
Mais si n n’est pas petit ou si n est inconnu, alors nous devons avoir une autre table, une table contacts, pour préserver la première forme normale.
La première forme normale stipule que chaque valeur de colonne doit être atomique.

`contacts2` Schéma relationnel¶

DROP SCHEMA IF EXISTS contacts2 CASCADE;
CREATE SCHEMA contacts2;
SET search_path TO contacts2;

CREATE TABLE users
(
    uid       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username  VARCHAR(15) NOT NULL,
    email     TEXT        NOT NULL,
    firstname TEXT,
    lastname  TEXT
);

CREATE TABLE contacts
(
    cid   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL,
    uid   INTEGER REFERENCES users (uid)
)

 INSERT INTO users (username, email)
 VALUES ('denis', 'denis.rinfret@example.com'),
        ('minh', 'minh@example.com');

INSERT INTO contacts (email, uid)
VALUES ('help@example.com', 1),
       ('minh@example.com', 1),
       ('contact@example.com', 2),
       ('ha@example.com', 2);

SELECT *
FROM users;

uid	username	email	firstname	lastname
1	denis	denis.rinfret@example.com	None	None
2	minh	minh@example.com	None	None

SELECT *
FROM contacts;

cid	email	uid
1	help@example.com	1
2	minh@example.com	1
3	contact@example.com	2
4	ha@example.com	2

Jointure nécessaire pour obtenir toutes les données¶

SELECT *
FROM users u
         INNER JOIN contacts c ON u.uid = c.uid;

uid	username	email	firstname	lastname	cid	email_1	uid_1
1	denis	denis.rinfret@example.com	None	None	1	help@example.com	1
1	denis	denis.rinfret@example.com	None	None	2	minh@example.com	1
2	minh	minh@example.com	None	None	3	contact@example.com	2
2	minh	minh@example.com	None	None	4	ha@example.com	2

Les jointures peuvent être lentes.
Mais sans normalisation, les données peuvent être redondantes et des anomalies peuvent apparaître.
Une SGDB object-relationalle (SGBDOR, ou ORDBMS) peut aider.

Contacts dénormalisés¶

`contacts3` Schéma objet-relationnel¶

DROP SCHEMA IF EXISTS contacts3 CASCADE;
CREATE SCHEMA contacts3;
SET search_path TO contacts3;

CREATE TABLE users
(
    uid                INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username           VARCHAR(15) NOT NULL,
    email              TEXT        NOT NULL,
    firstname          TEXT,
    lastname           TEXT,
    emergency_contacts TEXT[]
);

INSERT INTO users (username, email, emergency_contacts)
VALUES ('denis', 'denis.rinfret@example.com',
        ARRAY ['help@example.com', 'minh@example.com']),
       ('minh', 'minh@example.com',
        ARRAY ['contact@example.com', 'ha@example.com']);

SELECT uid, emergency_contacts
FROM users;

uid	emergency_contacts
1	["help@example.com", "minh@example.com"]
2	["contact@example.com", "ha@example.com"]

SELECT uid,
       username,
       email,
       firstname,
       lastname,
       unnest(emergency_contacts)
FROM users;

uid	username	email	firstname	lastname	unnest
1	denis	denis.rinfret@example.com	None	None	help@example.com
1	denis	denis.rinfret@example.com	None	None	minh@example.com
2	minh	minh@example.com	None	None	contact@example.com
2	minh	minh@example.com	None	None	ha@example.com

Trouver les utilisateurs avec un courriel spécifique comme premier contact d’urgence¶

SELECT uid, username, email
FROM users
WHERE emergency_contacts[1] = 'help@example.com';

uid	username	email
1	denis	denis.rinfret@example.com

Trouver un utilisateur avec un courriel spécifique comme contact d’urgence (n’importe quelle position)¶

SELECT uid, username, email
FROM users
WHERE 'ha@example.com' = ANY (emergency_contacts);

uid	username	email
2	minh	minh@example.com

Trouver les utilisateurs qui sont listés comme contact d’urgence d’autres utilisateurs¶

SELECT uid, username, email
FROM users
WHERE email IN (SELECT unnest(emergency_contacts)
                FROM users);

uid	username	email
2	minh	minh@example.com

SELECT distinct uid, username, email
FROM users
         INNER JOIN
     (SELECT unnest(emergency_contacts) AS emergency_email
      FROM users) AS all_contacts
     ON email = emergency_email;

uid	username	email
2	minh	minh@example.com

Sans `unnest` ni sous-requêtes¶

SELECT u1.uid, u1.username, u1.email
FROM users u1
         INNER JOIN users u2
                    ON u1.email = ANY (u2.emergency_contacts);

uid	username	email
2	minh	minh@example.com

Types définis par les utilisateurs¶

User-Defined Types (UDT)

`contacts4` Schéma objet-relationnel¶

DROP SCHEMA IF EXISTS contacts4 CASCADE;
CREATE SCHEMA contacts4;
SET search_path TO contacts4;

CREATE TYPE contact_type AS ENUM ('emergency', 'friend',
    'family', 'colleague');

CREATE TYPE contact AS
(
    email TEXT,
    type  contact_type
);

CREATE TABLE users
(
    uid       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username  VARCHAR(15) NOT NULL,
    email     TEXT        NOT NULL,
    firstname TEXT,
    lastname  TEXT,
    contacts  contact[]
);

INSERT INTO users (username, email, contacts)
VALUES ('denis', 'denis.rinfret@example.com',
        ARRAY [('help@example.com', 'emergency')::contact,
            ('minh@example.com', 'friend')::contact]),
       ('minh', 'minh@example.com',
        ARRAY [('contact@example.com', 'family')::contact,
            ('ha@example.com', 'colleague')::contact]);

SELECT uid, contacts
FROM users;

uid	contacts
1	{"(help@example.com,emergency)","(minh@example.com,friend)"}
2	{"(contact@example.com,family)","(ha@example.com,colleague)"}

SELECT uid,
       username,
       email,
       (unnest(contacts)::contact).*
FROM users;

uid	username	email	email_1	type
1	denis	denis.rinfret@example.com	help@example.com	emergency
1	denis	denis.rinfret@example.com	minh@example.com	friend
2	minh	minh@example.com	contact@example.com	family
2	minh	minh@example.com	ha@example.com	colleague

SELECT uid,
       username,
       email,
       (unnest(contacts)::contact).email AS contact_email,
       (unnest(contacts)::contact).type  AS contact_type
FROM users;

uid	username	email	contact_email	contact_type
1	denis	denis.rinfret@example.com	help@example.com	emergency
1	denis	denis.rinfret@example.com	minh@example.com	friend
2	minh	minh@example.com	contact@example.com	family
2	minh	minh@example.com	ha@example.com	colleague

Trouver tous les contacts d’urgence d’un utilisateur¶

SELECT *
FROM (SELECT uid,
             username,
             email,
             (unnest(contacts)::contact).email AS contact_email,
             (unnest(contacts)::contact).type  AS contact_type
      FROM users) AS temp
WHERE contact_type = 'emergency';

uid	username	email	contact_email	contact_type
1	denis	denis.rinfret@example.com	help@example.com	emergency

Trouver les utilisateurs sans contacts d’urgence¶

SELECT uid, username, email
FROM users
WHERE 'emergency' NOT IN (SELECT (unnest(contacts)::contact).type);

uid	username	email
2	minh	minh@example.com

SELECT uid, username, email
FROM users
WHERE 'emergency' != ALL (SELECT (unnest(contacts)::contact).type);

uid	username	email
2	minh	minh@example.com

JSONB à la place de tableaux¶

`contacts5` Schéma objet-relationnel¶

DROP SCHEMA IF EXISTS contacts5 CASCADE;
CREATE SCHEMA contacts5;
SET search_path TO contacts5;

CREATE TABLE users
(
    uid       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username  VARCHAR(15) NOT NULL,
    email     TEXT        NOT NULL,
    firstname TEXT,
    lastname  TEXT,
    contacts  jsonb
);

INSERT INTO users (username, email, contacts)
VALUES ('denis', 'denis.rinfret@example.com',
        '{"emergency": "help@example.com", "friend": "minh@example.com"}'),
       ('minh', 'minh@example.com',
        '{"family": "contact@example.com", "colleague": "ha@example.com"}');

SELECT uid, contacts
FROM users;

uid	contacts
1	{"friend": "minh@example.com", "emergency": "help@example.com"}
2	{"family": "contact@example.com", "colleague": "ha@example.com"}

Trouver les utilisateurs avec au moins un contact d’urgence¶

SELECT uid, username, email
FROM users
WHERE 'emergency' IN (SELECT jsonb_object_keys(contacts));

uid	username	email
1	denis	denis.rinfret@example.com

SELECT uid, username, email
FROM users
WHERE contacts ? 'emergency';

uid	username	email
1	denis	denis.rinfret@example.com

SELECT uid, username, email, contacts -> 'emergency' AS emergency
FROM users
WHERE contacts ? 'emergency';

uid	username	email	emergency
1	denis	denis.rinfret@example.com	help@example.com

Trouver les utilisateurs sans contacts d’urgence¶

SELECT uid, username, email
FROM users
WHERE NOT (contacts ? 'emergency');

uid	username	email
2	minh	minh@example.com

Schéma relationnel avec les types de contact¶

`contacts6` Relational Schema¶

DROP SCHEMA IF EXISTS contacts6 CASCADE;
CREATE SCHEMA contacts6;
SET search_path TO contacts6;

CREATE TABLE users
(
    uid       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username  VARCHAR(15) NOT NULL,
    email     TEXT        NOT NULL,
    firstname TEXT,
    lastname  TEXT
);

CREATE TYPE contact_type AS ENUM ('emergency', 'friend',
    'family', 'colleague');

CREATE TABLE contacts
(
    cid   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT         NOT NULL,
    type  contact_type NOT NULL,
    uid   INTEGER REFERENCES users (uid)
);

INSERT INTO users (username, email)
VALUES ('denis', 'denis.rinfret@example.com'),
       ('minh', 'minh@example.com');

INSERT INTO contacts (email, type, uid)
VALUES ('help@example.com', 'emergency', 1),
       ('minh@example.com', 'friend', 1),
       ('contact@example.com', 'colleague', 2),
       ('ha@example.com', 'family', 2);

SELECT *
FROM users;

uid	username	email	firstname	lastname
1	denis	denis.rinfret@example.com	None	None
2	minh	minh@example.com	None	None

SELECT *
FROM contacts;

cid	email	type	uid
1	help@example.com	emergency	1
2	minh@example.com	friend	1
3	contact@example.com	colleague	2
4	ha@example.com	family	2

SELECT *
FROM users u
         INNER JOIN contacts c ON u.uid = c.uid;

uid	username	email	firstname	lastname	cid	email_1	type	uid_1
1	denis	denis.rinfret@example.com	None	None	1	help@example.com	emergency	1
1	denis	denis.rinfret@example.com	None	None	2	minh@example.com	friend	1
2	minh	minh@example.com	None	None	3	contact@example.com	colleague	2
2	minh	minh@example.com	None	None	4	ha@example.com	family	2

Trouver les utilisateurs sans contacts d’urgence¶

SELECT uid, username, email
FROM users
WHERE uid NOT IN (SELECT uid
                  FROM contacts
                  WHERE type = 'emergency');

uid	username	email
2	minh	minh@example.com

SELECT users.uid, username, email
FROM users
         LEFT JOIN (SELECT uid
                    FROM contacts
                    WHERE type = 'emergency') AS temp
                   ON users.uid = temp.uid
WHERE temp.uid IS NULL;

uid	username	email
2	minh	minh@example.com

Comment traitons-nous les relations plusieurs à plusieurs ?¶

Dans une base de données relationnelle, nous avons besoin d’une table supplémentaire entre les 2 tables.
- Par exemple, nous avons besoin d’une table entre “Users” et “Contacts”, contenant des clés étrangères pour les ID d’utilisateur et les ID de contact.
Comment récupérons-nous toutes les données ?
- Avec 2 jointures.
Qu’en est-il des autres modèles de données ?
- Avec des tableaux ?
- Avec JSONB ?

Il n’y a pas de solutions miracles pour les relations plusieurs à plusieurs

Modèle objet-relationel¶

Contacts normalisés¶

contacts1 Schéma relationnel¶

Que devons-nous faire pour permettre à un utilisateur d’avoir plus d’un contact ?¶

contacts2 Schéma relationnel¶

Jointure nécessaire pour obtenir toutes les données¶

Contacts dénormalisés¶

contacts3 Schéma objet-relationnel¶

Trouver les utilisateurs avec un courriel spécifique comme premier contact d’urgence¶

Trouver un utilisateur avec un courriel spécifique comme contact d’urgence (n’importe quelle position)¶

Trouver les utilisateurs qui sont listés comme contact d’urgence d’autres utilisateurs¶

Sans unnest ni sous-requêtes¶

Types définis par les utilisateurs¶

contacts4 Schéma objet-relationnel¶

Trouver tous les contacts d’urgence d’un utilisateur¶

Trouver les utilisateurs sans contacts d’urgence¶

JSONB à la place de tableaux¶

contacts5 Schéma objet-relationnel¶

Trouver les utilisateurs avec au moins un contact d’urgence¶

Trouver les utilisateurs sans contacts d’urgence¶

Schéma relationnel avec les types de contact¶

contacts6 Relational Schema¶

Trouver les utilisateurs sans contacts d’urgence¶

Comment traitons-nous les relations plusieurs à plusieurs ?¶

`contacts1` Schéma relationnel¶

`contacts2` Schéma relationnel¶

`contacts3` Schéma objet-relationnel¶

Sans `unnest` ni sous-requêtes¶

`contacts4` Schéma objet-relationnel¶

`contacts5` Schéma objet-relationnel¶

`contacts6` Relational Schema¶