观看权限通常在关系数据库中实现？ [英] how are viewing permissions usually implemented in a relational database?

问题描述

什么是用于设置项目权限的标准关系数据库成语？

What's the standard relational database idiom for setting permissions for items?

答案应该是一般的;然而，他们应该能够应用于下面的例子。任何事情都会发生：添加列，添加另一个表，无论它是否正常工作。

Answers should be general; however, they should be able to be applied to example below. Anything flies: adding columns, adding another table—whatever as long as it works well.

假设Twitter数据库非常简单：我们有一个用户表，其中包含一个登录名和用户标识;我们有一个 Tweet 表，其中包含一个tweet id，tweet文本和创建者id;我们有一个跟随者表，其中包含被跟随的人的id和跟随者。

Assume the Twitter database is extremely simple: we have one User table, which contains a login and user id; we have a Tweet table, which contains a tweet id, tweet text, and creator id; and we have a Follower table, which contains the id of the person being followed and the follower.

现在，假设Twitter要启用高级隐私设置（查看权限），以便用户可以准确地选择哪些关注者可以查看推文。设置可以是：

Now, assume Twitter wants to enable advanced privacy settings (viewing permissions), so that users can pick exactly which followers can view tweets. The settings can be:

• Twitter上的每个人


• 只有当前的关注者被用户批准，这并不重要）编辑：目前，我得到一个新的追随者，他看到它;


• 具体关注者（例如，用户标识5,10,234和1）


• 只有所有者

• Everyone on Twitter
• Only current followers (which would of course have to be approved by the user, this doesn't really matter though) Current as in, I get a new follower, he sees it; I remove a follower, he stops seeing it.
• Specific followers (e.g., user id 5, 10, 234, and 1)
• Only the owner

在这种情况下，代表观看权限的最佳方法是什么？优先级顺序是查找速度（您希望能够快速找出向用户显示哪些推文），创建速度想要永久地发布推文），并且有效利用空间（每次向我的关注者列表中的每个人发布推文时，我不必为每个人添加一行，每个追随者都必须有一些表格。）

Under these circumstances, what's the best way to represent viewing permissions? The priorities, in order, are speed of lookup (you want to be able to figure out what tweets to display to a user quickly), speed of creation (you don't want to take forever to post a tweet), and efficient use of space (every time I post a tweet to everyone on my followers' list, I shouldn't have to add a row for each and every follower I have to some table.)

推荐答案

看起来像一个典型的多对多关系 - 我不看到任何限制，你将会允许空间节省的典型的关系数据库成语为那些，即一个表有两列（两个外键，一个用户和一个tweet）...由于当前的追随者可以和确实改变所有的时间，发布一个推特到所有当前在发布的时刻的追随者（我认为这是你的意思？）是意味着添加许多（极短）行到这种关系表（保持时间戳的追随者集的历史的替代方案，以便您可以在任何给定的tweet发布时间重建谁是追随者，显然在时间上更加糟糕，而且在空间上并不会更好）。

Looks like a typical many-to-many relationship -- I don't see any restrictions on what you desire that would allow space savings wrt the typical relational DB idiom for those, i.e., a table with two columns (both foreign keys, one into users and one into tweets)... since the current followers can and do change all the time, posting a tweet to all the followers that are current at the instant of posting (I assume that's what you mean?) does mean adding that many (extremely short) rows to that relationship table (the alternative of keeping a timestamped history of follower sets so you can reconstruct who was a follower at any given tweet-posting time appears definitely worse in time and not substantially better in space).

另一方面，如果您想在查看时检查关注者（而不是发布时），那么您可以一个特殊的用户名人为地意思是当前用户的所有追随者（就像你会有一个意思Twitter上的所有用户）;所需的SQL可以快速查找，在这种情况下，看起来很漂亮，但可行（一个UNION或OR，所有的tweets，我是作者的追随者，并且推文可被[所有关注者的人造用户代码]读取）。我不会深入到这个的迷宫之中，除非你确认这是你所想到的这个奇怪的意思（而不是简单的，对我来说似乎更自然，但不是允许在关系表上任意空间节省向所有追随者发帖的操作。

If, on the other hand, you want to check followers at the time of viewing (rather than at the time of posting), then you could make a special userid artificially meaning "all followers of the current user" (just like you'll have one meaning "all users on Twitter"); the needed SQL to make the lookup fast, in that case, looks hairy but feasible (a UNION or OR with "all tweets for which I'm a follower of the author and the tweet is readable by [the artificial userid representing] all followers"). I'm not getting deep into that maze of SQL until and unless you confirm that it is this peculiar meaning that you have in mind (rather than the simple one which seems more natural to me but doesn't allow any space savings on the relationship table for the action of "post tweet to all followers").

修改：OP已经澄清了他们意思是我在第二段提到的方法。

Edit: the OP has clarified they mean the approach I mention in the second paragraph.

然后，假设 userid 是用户表， Tweets 表有一个主键 tweetid 和一个$ code>作者为每个tweet的作者的用户标识，关注者表是一个典型的多对多关系表与两列（两个外键进入用户）追随者和 followee 和 Canread 表是一个非常典型的多对多关系表，仍然有两列 - 外键到用户是列 reader ，外键 Tweets 是列 tweet （phew ;-)。两个特殊用户 @everybody 和 @allfollowers 被定义为上述含义（以便发布给所有人，所有追随者，或只是我自己，只添加一行到 Canread - 只有选择性地发布到N个人的特定列表添加N行）。

Then, assume userid is the primary key of the Users table, the Tweets table has a primary key tweetid and a foreign key author for the userid of each tweet's author, the Followers table is a typical many-to-many relationship table with the two columns (both foreign keys into Users) follower and followee, and the Canread table a not-so-typical many-to-many relationship table, still with two column -- foreign key into Users is column reader, foreign key into Tweets is column tweet (phew;-). Two special users @everybody and @allfollowers are defined with the above meanings (so that posting to everybody, all followers, or "just myself", all add only one row to Canread -- only selective posting to a specific list of N people adds N rows).

因此，用户 @me 可以读取的一组tweet ID的SQL可以读取，我认为是这样的：

So the SQL for the set of tweet IDs a user @me can read is, I think, something like:

SELECT Tweets.tweetid 
  FROM Tweets
  JOIN Canread ON(Tweets.tweetid=Canread.tweet)
 WHERE Canread.reader IN (@me, @everybody)

UNION

SELECT Tweets.tweetid 
  FROM Tweets
  JOIN Canread ON(Tweets.tweetid=Canread.tweet)
  JOIN Followers ON(Tweets.author=Followers.followee)
 WHERE Canread.reader=@allfollowers
   AND Followers.follower=@me

这篇关于观看权限通常在关系数据库中实现？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！