Exemple : ORDBMS

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

Last modified: 26 avril 2024

Exemple : ORDBMS

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