* Bonjour, *

J'ai un problème de conception sur une base de donnée mais je veux aussi savoir s'il existe une solution universelle en SQL.

Les tables ont été définies comme ceci (je ne peux rien y changer) :

  • dossier
    pk : d_id
    Nombre d'enregistrements : 10 000 + 1000/an
  • utilisateur
    pk : u_id,
    fk sur groupe_utilisateur: g_id not null
    Nombre d'enregistrements : 5000
  • groupe_utilisateur
    pk: g_id
    boolean: can_see_b
    aucune idée du nombre d'enregistrement
  • alpha
    pk: a_id
    fk sur groupe_utilisateur:g_id not null
    fk sur dossier: d_id not null
    Nombre d'enregistrements : 800 000 + 100 000 / an
  • version
    pk : v_id
    fk sur alpha : a_id not null
    Nombre d'enregistrements : 5 / alpha en moyenne
  • beta
    pk : (b1_id, b2_id, b3_id, b4_id, b5_id)
    fk sur groupe_utilisateur: g_id not null
    fk sur dossier: d_id
    Nombre d'enregistrements : 2 000 000 + 200 000 / an
  • comment
    pk: c_id
    fk sur beta: (b1_id, b2_id, b3_id, b4_id, b5_id)
    fk sur groupe_utilisateur: g_id not null
    c_type_id [a|b|other] not null
    Nombre d'enregistrements : 3 000 000 + 300 000/an
  • associative_v_c
    pk : (v_id,c_id)
    fk sur version : v_id not null
    fk sur comment : c_id not null
    Nombre d'enregistrement : 3 / version en moyenne
  • acces_groupe
    pk: ag_id
    fk sur groupe_utilisateur: g_id not null
    fk sur dossier: d_id
    fk sur beta: (b1_id, b2_id, b3_id, b4_id, b5_id)
    fk sur alpha: a_id
    ak sur alpha+beta: (b1_id, b2_id, b3_id, b4_id, b5_id, a_id))
    Nombre d'enregistrements : dépendra de la réponse!
Voici la liste des règles suivies :
* un groupe utilisateur peut avoir 0 ou plusieurs utilisateurs (0, n)
* un utilisateur appartient toujours à un unique groupe_utilisateur(1,1)

* groupe_utilisateur peut avoir plusieurs alpha (0,n)
* un alpha appartient toujours à un unique groupe_utilisateur (1,1)
* un alpha a au moins une version (1,n)
* version appartient toujours à un unique alpha (1,1)
* un dossier peut contenir plusieurs alpha (0,n)
* alpha appartient toujours à un unique dossier (n,1)

* beta appartient toujours à un unique groupe_utilisateur (n,1)
* beta peut appartenir à un dossier (0,1)
* un dossier peut contenir plusieurs beta (1,n)

* alpha et beta peuvent appartenir au même dossier

* un beta peut avoir plusieurs comment (0,n)
* comment peut appartenir à un beta (0,1)
* une version peut avoir plusieurs comments et un comment peut être sur plusieurs versions (n,n)
* comment peut appartenir à plusieurs versions de plusieurs alpha du même groupe_utilisateur
* comment ne peut pas appartenir à la fois à un beta et à la fois à un alpha
* comment appartient au même groupe_utilisateur que alpha ou beta (il y a donc redondance dans les colonnes)
* On identifie plus facilement le type de comment grâce à c_type_id.
(a : alpha, b: beta, other : autre... car il y en a d'autres...)

* acces_groupe est défini soit pour alpha, soit pour beta (i.e. a_id is null ou (b1_id, b2_id, b3_id, b4_id, b5_id) is null) et appartient forcément au même dossier que alpha ou beta (il y a donc redondance dans les colonnes)
* Si on nomme le groupe_utilisateur définit sur un alpha ou un beta 'propriétaire', on peut dire que acces_groupe contient les groupe_utilisateurs qui ont accès à un alpha ou un beta dont ils ne sont pas propriétaires.
Le but est d'afficher les listes de alpha, dossier, beta ou comment en testant les droits de visualisation suivants pour un utilisateur d'un groupe_utilisateur :

groupe_utilisateur peut voir :
  • alpha si alpha appartient à groupe_utilisateur ou il existe un acces_groupe entre alpha et groupe_utilisateur
  • beta si (beta appartient à groupe_utilisateur et groupe_utilisateur.can_see_b est vrai) OU il existe un acces_groupe entre beta et groupe_utilisateur
  • dossier s'il existe un alpha ou un beta du dossier que groupe_utilisateur peut voir
  • comment si comment appartient à un alpha ou un beta que groupe_utilisateur peut voir
La question est :

Vaut-il mieux faire des vues pour les listes ou introduire de la redondance de donnée dans acces_groupe? ou bien existe-t-il une solution à laquelle on n'a pas pensé?
  • créer des vues
    on crée une vue par type de table vue_alpha(a_id, g_id), vue_beta(b_id,g_id), vue_dossier(d_id, g_id), vue_comment(c_id,g_id)).
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
     
Exemple utilisant des union (pas forcément optimisé) :
CREATE OR REPLACE VIEW VUE_ALPHA (a_id, d_id, g_id)
AS (
-- Liste des alpha de groupe_utilisateur
(SELECT a_id, d_id, g_id FROM alpha)
UNION
-- Liste des accès de groupe_utilisateurs sur alpha
(SELECT a_id, d_id, g_id FROM acces_groupe WHERE a_id is not null)
);
 
CREATE OR REPLACE VIEW VUE_BETA (b1_id, b2_id, b3_id, b4_id, b5_id, d_id, g_id)
AS (
-- Liste de beta visible par son groupe_utilisateur
(SELECT b1_id, b2_id, b3_id, b4_id, b5_id, beta.d_id, beta.g_id
FROM beta, groupe_utilisateur
WHERE beta.g_id = groupe_utilisateur.g_id
AND groupe_utilisateur.can_see_b = true)
-- Liste des accès de groupe_utilisateur sur beta
UNION
(SELECT b1_id, b2_id, b3_id, b4_id, b5_id, d_id, g_id
FROM acces_groupe
WHERE a_id is null)
);
 
CREATE OR REPLACE VIEW VUE_DOSSIER (d_id, g_id)
AS (
-- Liste des dossiers pour lesquels un groupe_utilisateur peut voir un alpha
(SELECT d_id, g_id FROM VUE_ALPHA)
UNION
-- Liste des dossiers pour lesquels un groupe_utilisateur peut voir un beta
(SELECT d_id, g_id FROM VUE_BETA)
);
 
CREATE OR REPLACE VIEW VUE_COMMENT (c_id, g_id)
AS (
-- Liste des comments sur un alpha d'un groupe_utilisateur
(SELECT c_id, g_id
FROM comment
WHERE c_type_id = 'a')
-- Liste des comments sur un beta d'un groupe_utilisateur que celui-ci peut voir
UNION
(SELECT c_id, comment.g_id
FROM comment, groupe_utilisateur
WHERE c_type_id = 'b'
AND comment.g_id = groupe_utilisateur.g_id
AND groupe_utilisateur.can_see_b = true)
UNION
-- Liste des commentaires d'un alpha pour lequel un acces_groupe a été défini
(SELECT associative_v_c.c_id, acces_groupe.g_id
FROM acces_groupe, version, associative_v_c
WHERE acces_groupe.a_id = version.a_id
AND version.v_id = associative_v_c.v_id)
UNION
-- Liste des commentaires d'un beta pour lequel un acces_groupe a été défini
(SELECT comment.c_id, acces_groupe.g_id
FROM comment, acces_groupe
WHERE comment.b1_id = acces_groupe.b1_id
AND comment.b2_id = acces_groupe.b2_id
AND comment.b3_id = acces_groupe.b3_id
AND comment.b4_id = acces_groupe.b4_id
AND comment.b5_id = acces_groupe.b5_id)
);
  • introduire de la redondance de donnée
    on remplit l'associative acces_groupe avec les données suivantes :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
insert into acces_groupe
(ag_id, g_id, d_id, a_id)
-- Liste des alpha qui sont visibles par leur propriétaire (donc tous)
(select seq.nextval, g_id, d_id, a_id
    from alpha)
insert into acces_groupe
(ag_id, g_id, d_id, b1_id, b2_id, b3_id, b4_id, b5_id)
-- Liste des beta qui sont visibles par leur propriétaire
(select seq.nextval, beta.g_id, d_id, b1_id, b2_id, b3_id, b4_id, b5_id
from beta, groupe_utilisateur
where beta.g_id = groupe_utilisateur.g_id and groupe_utilisateur.can_see_b = true)
    ça n'empêchera pas le besoin d'une vue pour Comment car ça a été mal conçu
  • autre solution?

Note : rencontré dans une base de donnée Oracle

* Merci *