Hi!
I need to prepare a DB design with users, documents and permissions.

The average values will be:

Number of USERS: 10 000

Number of DOCUMENTS:
Type 1: 300 000
Type 2: 750 000
Type 3: 800 000

All document types have common 'header' data

I prefer to follow MS Windows permissions schema as follows:
1. Organize USERS in GLOBAL GROUPS
2. Put GLOBAL GROUPS in LOCAL GROUPS
3. Give document PERMISSIONS to both USERS and LOCAL GROUPS

In this case:
The average values will be:
Number of LOCAL GROUPS: 200
Number of GLOBAL GROUPS: 50
Number of PERMISSIONS: 50

I prepared the neccessery index files - all tables are indexed.

I prepared some views:
1. users, global groups and local groups
2. users, documents and permissions
UNION ALL
users, global groups, local groups, documents and permissions


Questions:
1. Is this the prefered schema for users, documents and permissions?
2. I prepared the schema and it works VERY SLOW ;( The problem appeared
when I want to list a documents that a certain user have permission to
see. It taked a LOT of time. How could I optimize this schema or how to
change the schema ?

NOTES:
Here are the TABLES:
================================================
USERS
~~~~~

Table "t_users"
col 1: user_id (sequence)
col 2..n: user_data


GROUPS
~~~~~~
Table "t_local_groups"
col 1: local_groups_id (sequence)
col 2..n: local_groups_data

Table "t_global_groups"
col 1: global_groups_id (sequence)
col 2..n: global_groups_data

PERMISSION_TYPES
~~~~~~~~~~~~~~~~
Table "t_permission_types"
col 1: permission_types_id (sequence)
col 2..n: permission_types_data

DOCUMENTS
~~~~~~~~~

Table "t_documents"
col 1: document_id (sequence)
col 2..n: document_data

Table "t_doc_type1"
col 1: document_id
col 2..n: doc_type1_data

Table "t_doc_type2"
col 1: document_id
col 2..n: doc_type2_data

Table "t_doc_type3"
col 1: document_id
col 2..n: doc_type3_data


USER & GROUP LINKS
~~~~~~~~~~~~~~~~~~

Table "t_users_global_groups"
col 1: user_id
col 2: global_groups_id


Table "t_global_local_groups"
col 1: local_groups_id
col 2: global_groups_id


PERMISSION LINKS
~~~~~~~~~~~~~~~~

Table "t_documents_persimmsions_local_groups"
col 1: local_groups_id
col 2: document_id
col 3: permission_types_id

Table "t_documents_persimmsions_users"
col 1: user_id
col 2: document_id
col 3: permission_types_id
================================================

Additional data:

Simple Example:

user_id = 1
document_id = 1

user_id/global_groupd_id
~~~~~~~~~~~~~~~~~~~~~~~~
1/1
1/2
1/3
1/4

global_groupd_id/local_group_id
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1/1
1/2
1/3
1/4
2/1
2/2
2/3
2/4
2/5
3/1
3/5
3/6
4/7
4/8
4/9
4/10

documents/persimmsions/local_groups
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1/1/1
1/5/2
1/3/3
1/2/4
1/1/5
1/3/6
1/4/7
1/5/8
1/6/9
1/1/10

documents/persimmsions/users
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1/4/1



permissions-read/write/change/create/perm_read/perm_write
~~~~~~~~~~~
= 0 - missing
= 1 - allowed
= 2 - denied

1 - 1/1/1/1/1/0
2 - 1/0/0/0/0/0
3 - 2/1/0/0/2/0
4 - 1/2/0/1/2/0
5 - 1/2/1/0/1/0
6 - 0/0/0/0/1/0

NOTES: when I want to see effective permission I use
MAX(permissions-columX). If it's 0 or 2 then DENIED. If = 1 then ALLOWED