画廊数据库设计问题有关权限/可见性 [英] Gallery database design question regarding permissions/visibility

问题描述

我正在创建一个画廊。我希望用户能够选择谁可以按组或个人查看他们的照片。我想我应该有两个列（user_id，photo_id）的表 can_see 。这应该适用于个人，但如果我也在此折腾 group_id ，则无效。我认为最好的方法可能只是在选择组时将所选组中的每个人添加到该表，而不是其 group_id 。然而，这会在表中创建更多的条目，以便稍后筛选。这是否仍然是最好的方法？

---

这是我想到的：

 用户：id，username，... 
照片：id，album_id，... 
相册：id，user_id（拥有所有照片相册），.... 
 can_see：user_id，photo_id 
 groups：id，user_id（owner），name 
 member_of：user_id，group_id 
  

解决方案

如果您想删除要搜索的条目数量，我将采取以下方法。 / p>

 用户表 -  UserID，UserName 
组表 -  GroupID，GroupName 
用户组表 -  GroupID，UserID 
照片表 -  PhotoID，UserID 
  

如果您想要访问特定用户的照片，您将从用户表中获取用户ID，并在照片表上查找该ID。

如果要访问组中的照片，您将从用户组表中获取UserID，并在照片表上查找这些ID。

***详细评论

好的，让我拼出来给你...有
是这里的几个动作是由照片所有者执行的
或至少
是我的理解
1.照片所有者可以授予对单个用户的访问权限。
这将为新用户在用户表
中创建一个新记录。对于现有的用户来说，
不会。这也将在具有
现有或新创建的用户标识的照片表中创建一个新的
记录。
2.照片所有者可以向一组用户授予访问权限。
此操作将在新组中的
组表中为
现有组创建一个新记录。组中的任何新用户
将需要对用户表添加新记录
。
最终，照片表将被更新为
，其中有
是组中的用户。

现在访问照片...有
多种情况

1. 不属于任何组的单个用户可以请求访问
   照片


2. 作为某个群组一部分的单一用户可以请求访问照片。

在这些情况下，我假设
从应用程序的输入将
只有用户名&照片到
访问（这应该实际上是
专辑。由于你修改了你的问题
我不知道这是否仍然是
的案例）。在这种情况下，您将在用户表中执行
查找以获取用户名的
用户ID，并在
的照片表中执行
另一个查找，查看是否该用户ID被授予对请求的照片
（由
检查是否存在该记录）。

所以下一个问题是...为什么我们
需要组&用户组表？该
是照片所有者需要
轻松地从UI授予和撤销访问
权限。使用这些
表，很容易构建一个UI到
显示照片所有者创建了哪些组
和每个组的成员。

中有某些项目缺少问题...例如，
如何查看访问权限？什么
信息将作为输入提供给
应用程序？所有的评论和
回答我的更新是基于很多
的假设。所以现在我们知道为什么
的要求应该明确地写成
出来; - ）

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

解决方案

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;-)

这篇关于画廊数据库设计问题有关权限/可见性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！