Gallery database design question regarding permissions/visibility

Question

I'm creating a gallery. I want users to be able to choose who can view their photos by group or individual. I figure I should have a table can_see with two columns (user_id, photo_id). This should work for individuals, but it won't work if I toss group_ids in there too. I'm thinking the best approach might be just to add each individual in the selected group to that table when a group is chosen, rather than its group_id. This however creates more entries in the table to sift through later. Is this still the best approach?

---

Here's what I have in mind:

users: id, username, ...
photos: id, album_id, ...
albums: id, user_id (owns all photos in album), ....
can_see: user_id, photo_id
groups: id, user_id(owner), name
member_of: user_id, group_id

Answer 1

If you would like to eliminate the amount of entries to search, I would go for the following approach.

User Table - UserID, UserName
Group Table - GroupID, GroupName
User Group Table - GroupID, UserID
Photo Table - PhotoID, UserID

If you would like to access photos for a specific user, you would get the User ID from user table and do a lookup by that ID on the photo table.

If you would like to access photos for a group, you would get the UserIDs from User Group table and do a lookup of those IDs on the photo table.

***Detailed comment

ok let me spell it out for you...There are couple of actions here to be performed by a photo owner or atleast that is my understanding. 1. Photo owner may grant viwership access to a single user. This will create a new record in the user table for a new user. For existing users it won't. This will also create a new record in the photo table with the existing or newly created user ID. 2. Photo owner may grant viewership access to a group of users. This action will create a new record in the group table for a new group and for existing group it won't. Any new users in the group will require new record additions to the user table. Eventually, photo table will be updated with as many records as there are users in the group.

Now to access the photos...There are multiple cases

1. A single user who does not belong to any group can request access to the photo.
2. A single user who is part of some group can request access to the photo.

In eiher of these cases, I am assuming the input from the application will have just the user name & the photo to access (This should actually be the album. Since you amended your question I am not sure if this is still the case). In which case, you will do a lookup in the user table to get the user ID for the user name and will do another lookup in the photo table to see if this user ID is granted access to the requested photo or not (by checking if the record exists or not).

So the next question is...why do we need group & user-group tables? This is required for the photo owner to easily grant and revoke access privileges from the UI. With these table it is easy to build an UI to show the photo owner what groups were created and the members of each group.

There are certain items missing from the question...For instance, how does one request for viewing access? What information will provided as input to the application? All the comments and answer updates from me are based on lot of assumptions. So now we know why requirements should be clearly spelled out;-)

Answer 2

Another solution I thought of, we can ammend the can_see table to include a group_id column, then to check if a user can see a photo:

SELECT 1 FROM can_see WHERE user_id=USERID AND photo_id=PHOTOID

or

SELECT 1 FROM can_see JOIN member_of ON can_see.group_id = member_of.group_id WHERE member_of.user_id=USERID AND can_see.photo_id=PHOTOID

It's extra column which might often be null, and it means we have to do 2 queries instead of 1 to check permissions, but it keeps the can_see table a bit smaller, and it means you can modify the group after permissions are set.