StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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?) <em>does</em> 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).</p> <p>If, on the other hand, you want to check followers <em>at the time of viewing</em> (rather than at the time of posting), then you <em>could</em> 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 <em>that</em> 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").</p> <p><strong>Edit</strong>: the OP has clarified they mean the approach I mention in the second paragraph.</p> <p>Then, assume <code>userid</code> is the primary key of the <code>Users</code> table, the <code>Tweets</code> table has a primary key <code>tweetid</code> and a foreign key <code>author</code> for the userid of each tweet's author, the <code>Followers</code> table is a typical many-to-many relationship table with the two columns (both foreign keys into <code>Users</code>) <code>follower</code> and <code>followee</code>, and the <code>Canread</code> table a not-so-typical many-to-many relationship table, still with two column -- foreign key into <code>Users</code> is column <code>reader</code>, foreign key into <code>Tweets</code> is column <code>tweet</code> (phew;-). Two special users <code>@everybody</code> and <code>@allfollowers</code> are defined with the above meanings (so that posting to everybody, all followers, or "just myself", all add only one row to <code>Canread</code> -- only selective posting to a specific list of N people adds N rows).</p> <p>So the SQL for the set of tweet IDs a user <code>@me</code> can read is, I think, something like:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload