How to view permissions typically implemented in a relational database?

Question

What is the standard idiom of a relational database for setting permissions for items?

Answers should be general; however, they should be applicable to the example below. Something flies: adding columns, adding another table - no matter how it works.

Application / Example

Suppose the Twitter database is extremely simple: we have one User table that contains the login ID and user; we have a Tweet table containing the tweet id, tweet code and creator id; and we have a Follower table that contains the identifier of the person being followed and the follower.

Now suppose Twitter wants to enable advanced privacy settings (viewing permissions) so that users can choose exactly which followers can view tweets. Settings can be:

• Everything on Twitter
• Only current subscribers (which, of course, must be approved by the user, it really does not matter) EDIT: Current, as in, I get a new follower, he sees it; I delete the follower, he stops seeing him.
• Specific followers (e.g. user ID 5, 10, 234, and 1).
• Owner only

Under these conditions, what is the best way to present viewing permissions? The priorities, in order, are search speed (you want to know which tweets will be displayed to the user quickly), creation speed (you do not want to leave a message on Twitter forever) and efficient use of space (every time I post a tweet for everyone in my list subscribers, I don’t need to add a row for every follower that I have in some kind of table.)

Answer 1

It looks like a typical many-to-many relationship - I don’t see any restrictions on what you want, which would save space in relation to a typical relational DB idiom for those, that is, to a table with two columns (as with foreign keys, one for users and one for tweets) ... since current subscribers can and are constantly changing, posting a tweet to all followers who are current at the time of publication (I assume that you mean?) does mean many (extremely short) lines rel syatsya to the relationship table (alternative to the preservation of the history of the time sets of followers, so you can reconstruct who was a follower at any given time tweets, definitely worse over time, and not much better in the space).

If, on the other hand, you want to check subscribers during viewing (and not during publication), then you can create a special user ID that artificially means "all followers of the current user" (the same as you have the same value "all users on Twitter "); the necessary SQL for a quick search, in this case it looks hairy, but doable (UNION or OR with all the tweets for which I am a follower of the author, and the tweet is read by [an artificial user identifier representing] all followers "). I do not delve into this maze SQL until you confirm that it is a special meaning that you have in mind (and not simple, which seems more natural to me, but does not save space on the table relationships for the action "post tweet for all followers").

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

Then suppose userid is the primary key of the Users table, the Tweets table has the primary key tweetid and the foreign key author for the user ID of each author of the tweet, the Followers table is a typical many-to-many relationship table with two columns (both foreign keys in Users ) follower and followee , and the Canread table is not a typical relationship table for many, many, still with two columns - the foreign key in Users is the reader column, the foreign key in Tweets is the tweet column (phew ;-). The two special users @everybody and @allfollowers defined with the above values ​​(so publishing for everyone, all followers, or "just me") adds only one line to Canread - only selective publishing to a specific list of N people adds N lines).

So the SQL for the set of ids of the @me user @me can read, 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