Require some assistance on this DB design I'm trying to implement.
It's a web project, mySQL.
So, this is my problem:
* A site has a certain amount of Users;
* Users can create Resources (by uploading files, images, writing articles, etc);
* Those Resources are considered private;
* Users must give permissions to other Users for access (in other words, you just uploaded 20 pics of your vacations in Spain, for me to have access, you have to give me permission);
* Things get simpler (and more complicated) because Users can put other Users in Groups (in other words, you have already put me in your Friends Group, and you've already given your Friends Group view permissions for those photos, so I should be able to access it);
* Now, how do I structure this, so that it is simple and fast, or at least not so simple, but fast?
Recap:
* Resources cannot be accessed without permission;
* Access Permission can be granted to User(s) or Group(s);
* Groups can contain multiple Users.
Crystallizing my problem:
* My real problem with all of this seems to be a path-finding problem of sorts;
* You and Mark work together. You just hot-linked me to a PDF Mark created this morning, in hopes I can understand the problem and fix it. I open it in the browser. This is the moment the Server has to decide if I have access to said PDF.
* You have access because you're on Mark's "WorkGroup" & "FriendGroup". I on the other hand am only on his "FriendGroup", so the Server has to try and path-find a route between User "Me" and Resource "PDF".
* So, the server should come to the conclusion that there is no path solution to this:
Me ---> Mark's Friend Group -//-> WorkGroup ---> PDF
... and issue some browser info: "User You does not have rights to access that resource. Please contact Resource owner for privileges."
* I would like to make the problem linear. If I start going down what already is starting to look like an exponential road, searches will explode the DB. I hope mySQL offers some sort of functionality that eludes my noob eyes, so that this can be made simpler (sub-queries perhaps?).
Thanks in advance for any pointers...
A DB Conundrum...
Normally these systems aren't that complicated. They don't take into account how you came to know about the resource or who directed you to it or where you found the link. They just check your permissions for that resource.
Are you the owner? You can view the resource.
Are you in the list of explicitly allowed users? You can view the resource.
Are you in any of the explicitly allowed groups? You can view the resource.
Otherwise, you can't.
Each of the above can be done with one SQL query. (You don't need subqueries - in fact, you hardly ever NEED subqueries. The natural way to compare more than one table is via joins.)
Also note that lots of prebuilt web systems have this stuff already built in and ready to go. For example, I sometimes use TurboGears, a Python web development framework, and it autogenerates user and group tables from the start.
Are you the owner? You can view the resource.
Are you in the list of explicitly allowed users? You can view the resource.
Are you in any of the explicitly allowed groups? You can view the resource.
Otherwise, you can't.
Each of the above can be done with one SQL query. (You don't need subqueries - in fact, you hardly ever NEED subqueries. The natural way to compare more than one table is via joins.)
Also note that lots of prebuilt web systems have this stuff already built in and ready to go. For example, I sometimes use TurboGears, a Python web development framework, and it autogenerates user and group tables from the start.
I am no expert but this is a simplified version of what I would do if doing something similar.
Users
--------
ID (auto increasing unique key)
Name
...
Resource Table
--------------
ID (auto increasing unique key)
OwnerID (linked to Users.ID )
...
Groups
------
ID (auto increasing unique key)
UserID (linked to Users.ID )
Name
GroupMembers
-------------
GroupID (linked to Groups.ID )
MemberID (linked to Users.ID )
Permissions
------------
ResourceID
Type ( 1 for Group, 2 for User )
AccessID ( linked to either Users.ID, or Groups.ID )
CanView ( 1 for allowed, 0 for not viewable )
CanComment( 1 for allowed, 0 for not )
...
then when you want to get a list of items you just have to do something like this ( not tested )
SELECT *
FROM Permissions
LEFT JOIN Groups ON Permissions.AccessID=Groups.ID
LEFT JOIN GroupMembers ON Groups.ID=GroupMembers.GroupID
WHERE
Permissions.ResourceID=<RESOURCE_ID_HERE>
AND (
( Permissions.Type=2 AND Permissions.AccessID =<USERID_HERE> )
OR
( Permissions.Type=1 AND GroupMembers.MemberID=<USERID_HERE> )
)
Users
--------
ID (auto increasing unique key)
Name
...
Resource Table
--------------
ID (auto increasing unique key)
OwnerID (linked to Users.ID )
...
Groups
------
ID (auto increasing unique key)
UserID (linked to Users.ID )
Name
GroupMembers
-------------
GroupID (linked to Groups.ID )
MemberID (linked to Users.ID )
Permissions
------------
ResourceID
Type ( 1 for Group, 2 for User )
AccessID ( linked to either Users.ID, or Groups.ID )
CanView ( 1 for allowed, 0 for not viewable )
CanComment( 1 for allowed, 0 for not )
...
then when you want to get a list of items you just have to do something like this ( not tested )
SELECT *
FROM Permissions
LEFT JOIN Groups ON Permissions.AccessID=Groups.ID
LEFT JOIN GroupMembers ON Groups.ID=GroupMembers.GroupID
WHERE
Permissions.ResourceID=<RESOURCE_ID_HERE>
AND (
( Permissions.Type=2 AND Permissions.AccessID =<USERID_HERE> )
OR
( Permissions.Type=1 AND GroupMembers.MemberID=<USERID_HERE> )
)
taliesinnz's suggestion seems fine. I've implemented similar systems at work and that's more or less what I did.
Except, have separate tables for group/user permissions.
Except, have separate tables for group/user permissions.
Author
Thanks for the replies guys, I'll have to take a good look at Left Joins, it isn't something I know that well.
R++ all'round
R++ all'round
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
