Database Design Question

[dprichard]

I have database tables for folders and a database with documents in it. 

 

The folders table is setup like this

 

folderid

foldername

folderdescription

 

The document table is setup like this

 

documentid

documentname

documentdescription

folderid

 

I have a user table and want to set it so I can say certain users have permission on certain folder but not others.

 

I was thinking about adding a group table and putting the users in groups, but then certain users may need access to more than one document folder.  I am thinking I will need to add some sort of table that handles just the permissions, but I am not sure.

 

I am just trying to sort out how to make it so I can put groups in and assign access to folders based on the groups. 

 

Any input would be greatly appreciated.

[bcoffin]

I've never set it up, so I don't know the ins/outs, but

hear that GACL was designed for such a need:

http://phpgacl.sourceforge.net/

[radalin]

Well something like this may be useful:

 

You have a user table like:

userid, username,...

A group table like:

groupid, groupname,...

and a usergroups table which will hold which user will belong to which group and user may join to more then one groups so my user_groups table is like:

ugid, userid, groupid

Now that I have the groups and users and relation between groups and users I can create my permissions table:

permissionid, userid, groupid, folderid, documentid

 

Here is the thing. You may set the user and folderid or group and folderid or user and documentid or group and documentid in this table. You may seperate them, but you may not too as it wont change a lot. So from this permissions table you can get the permission list which user can reach which document or folder or other things. I hope it was helpful for you.