User & Group DB design.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
penguinboy
Forum Contributor
Posts: 171
Joined: Thu Nov 07, 2002 11:25 am

User & Group DB design.

Post by penguinboy »

I'm trying to design a user & group database.
I need to have groups that can delegate rights to other groups.

Like an insurence company [ic] is one group,
and an insurence agent's office [ia] is another group.
There are also users with each group.

Where an [ic] grants (user rights) an for the [ic]'s records.
Where an [ic] grants (group rights) an [ia] for the [ic]'s records.
Where an [ia] grants (user rights) an for the [ia]'s records.

Here's what I have so far.

-------------------format---------------------------------
====
table
====
fields
// pk = primary key
// fk = foreign key (primary key from another table)
------------------------------------------------------------

====
users
====
id_user // pk
name
------------------------

====
group
====
id_group // pk
id_owner // fk -> id_user
name
------------------------

=====
rights
=====
id_rights // pk
view
create
edit
delete
------------------------

==========
group_group
==========
id_parent // fk -> id_group
id_child // fk -> id_group
news_access // fk -> id_rights
client_access // fk -> id_rights
user_access // fk -> id_rights
------------------------

========
group_user
========
id_group // fk -> id_group
id_user // fk -> id_user
news_access // fk -> id_rights
client_access // fk -> id_rights
user_access // fk -> id_rights
------------------------

/* This is what user rights would look like
0 = no access
1 = your files
2 = group files
delete,edit,create,view
[0][0][0][0] // no rights
[0][0][0][2]
[0][0][1][0]
[0][0][1][1] // create & view: user rights
[0][0][1][2]
[0][1][1][1] // full user -delete
[0][1][1][2]
[1][1][1][1] // full user rights
[1][1][1][2]
[1][2][1][2]
[2][2][1][2] // full group rights
*/
Post Reply