Hi!

I would like to design a database with folders and sub-folders and all access rights given to admin and only certain folders are accessible to certain users.

I have a folder table with a id and parentId and name of folder. ParentId point to the ID of the parent subfolder.

I also have a user table with admin and normal users with IDs.

I have a user_to_folder table to allow multiple to multiple relationship.

Question is how should I allow admin for all folders but restrict certain users to certain folders. Do I keep a list of userIDs who are allowed to certain folders or should I keep a list who are not allowed.

My SQL would be:

SELECT * FROM folderTable
LEFT JOIN user_to_folder ON user.id = user_to_folder.fkuerId
WHERE parentID = 1 OR user.right = 'admin' and (isnull(user_to_folder.fkUserId)) or user.id = 45

But I am getting folders which are not supposed to be accessible to user 45.

Is my design correct?

Your comments please. Thanks