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.

[PappaJohn]

If you're going to use the group table for other reasons as well, could you simply allow a user to be a member of more than one group? This could solve the problem of permissions to multiple folders.

If not, a permissions table might be the easier way to go. You already have a users table, so something like this (I'm assuming)

userid
username
....

A permissions table would be something along these lines:

id
userid
folderid
permissionlevel

With this structure you could also define different permission-levels, such as read-only, read-write, etc. If you don't need that, of course the permissionlevel could simply be "yes/no"