database - 如何在我的数据库中实现Twitter转推操作

我正在实现类似于Twitter的Web应用程序。我需要实施'retweet'操作，并且可以由一个人多次转发一次twit_rstrong。

我有一个基本的“tweets”表，其中包含用于以下内容的列:



(其中tweet_id是tweets的主键，tweet_text包含tweet文本，tweet_date_created是创建tweet时的DateTime，tweet_user_id是users表的外键，用于标识创建该tweet的用户)

现在，我想知道如何在数据库中实施转发操作。

选项1

我应该创建新的联接表，如下所示:



(其中tweet_id是tweets表的外键，user_id是users表的外键，用于标识转发了该推文的用户，retweet_date_retweeted是一个DateTime，用于指定转发时间。)

优点:将没有空列，当用户处理reetet时，将在retweets表中创建新行。

缺点:查询过程将更加困难，它将需要连接两个表，并以某种方式按两个日期对这些推文进行排序(当未对推文进行推文时，请按tweet_date_created进行排序，当对推文进行推文时，请通过retweet_date_retweeted进行排序)。

选项2

还是应该在tweets表中将其实现为parent_id，它将如下所示:



(在所有列均保持不变且parent_id是同一tweets表的外键的情况下。创建tweet时，parent_id保持为空。当转发tweet时，parent_id包含原始tweet id，tweet_user_id包含处理了转发操作的用户，tweet_date_created包含进行转推时的DateTime，并且tweet_text保持为空-因为，我们将不允许用户在转推时更改原始推文。)

优点:查询过程更加优雅，因为我不必连接两个表。

缺点:每次转推时都将有空单元格。因此，如果我的数据库中有1 000条推文，而每条推文都转发了5次，则tweets表中将有5 000行。

哪种方法最有效？有空单元格还是使查询过程更干净更好？

最佳答案

IMO选项1会更好。联接tweet和retweet表的查询根本不会很复杂，可以通过左联接或内部联接来完成，具体取决于您是要显示所有tweet还是仅显示已转推的tweet。由于表很窄，联接的查询应该是高效的，联接的列是整数，由于FK约束，它们每个都有索引。

另一个建议是不要用tweet或reweet标记所有列，可以从存储数据的表中推断出这些列，例如:

tweet
    id
    user_id
    text
    created_at

retweet
    tweet_id
    user_id
    created_at

和示例联接:

# Return all tweets which have been retweeted
SELECT
    count(*),
    t.id
FROM
    tweet AS t
INNER JOIN retweet AS rt ON rt.tweet_id = t.id
GROUP BY
    t.id

# Return tweet and possible retweet data for a specific tweet
SELECT
    t.id
FROM
    tweet AS t
LEFT OUTER JOIN retweet AS rt ON rt.tweet_id = t.id
WHERE
    t.id = :tweetId

-每个请求的更新-

下面仅是说明性的，代表为什么我选择选项＃1，没有外键，也没有索引，您必须自己添加这些。但是结果应该表明联接不会太痛苦。

CREATE TABLE `tweet` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` int(10) unsigned NOT NULL,
    `value` varchar(255) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

CREATE TABLE `retweet` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `tweet_id` int(10) unsigned NOT NULL,
    `user_id` int(10) unsigned NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

# Sample Rows

mysql> select * from tweet;
+----+---------+----------------+---------------------+
| id | user_id | value          | created_at          |
+----+---------+----------------+---------------------+
|  1 |       1 | User1 | Tweet1 | 2012-07-27 00:04:30 |
|  2 |       1 | User1 | Tweet2 | 2012-07-27 00:04:35 |
|  3 |       2 | User2 | Tweet1 | 2012-07-27 00:04:47 |
|  4 |       3 | User3 | Tweet1 | 2012-07-27 00:04:58 |
|  5 |       1 | User1 | Tweet3 | 2012-07-27 00:06:47 |
|  6 |       1 | User1 | Tweet4 | 2012-07-27 00:06:50 |
|  7 |       1 | User1 | Tweet5 | 2012-07-27 00:06:54 |
+----+---------+----------------+---------------------+

mysql> select * from retweet;
+----+----------+---------+---------------------+
| id | tweet_id | user_id | created_at          |
+----+----------+---------+---------------------+
|  1 |        4 |       1 | 2012-07-27 00:06:37 |
|  2 |        3 |       1 | 2012-07-27 00:07:11 |
+----+----------+---------+---------------------+

# Query to pull all tweets for user_id = 1, including retweets and order from newest to oldest

select * from (
    select t.* from tweet as t where user_id = 1
    union
    select t.* from tweet as t where t.id in (select tweet_id from retweet where user_id = 1))
a order by created_at desc;

mysql> select * from (select t.* from tweet as t where user_id = 1 union select t.* from tweet as t where t.id in (select tweet_id from retweet where user_id = 1)) a order by created_at desc;
+----+---------+----------------+---------------------+
| id | user_id | value          | created_at          |
+----+---------+----------------+---------------------+
|  7 |       1 | User1 | Tweet5 | 2012-07-27 00:06:54 |
|  6 |       1 | User1 | Tweet4 | 2012-07-27 00:06:50 |
|  5 |       1 | User1 | Tweet3 | 2012-07-27 00:06:47 |
|  4 |       3 | User3 | Tweet1 | 2012-07-27 00:04:58 |
|  3 |       2 | User2 | Tweet1 | 2012-07-27 00:04:47 |
|  2 |       1 | User1 | Tweet2 | 2012-07-27 00:04:35 |
|  1 |       1 | User1 | Tweet1 | 2012-07-27 00:04:30 |
+----+---------+----------------+---------------------+

请注意，在最后一组结果中，我们还能够包含转发，并在＃3的转发之前显示＃4的转发。

-更新-

您可以通过稍微更改查询来满足您的要求:

select * from (
    select t.id, t.value, t.created_at from tweet as t where user_id = 1
    union
    select t.id, t.value, rt.created_at from tweet as t inner join retweet as rt on rt.tweet_id = t.id where rt.user_id = 1)
a order by created_at desc;

mysql> select * from (select t.id, t.value, t.created_at from tweet as t where user_id = 1 union select t.id, t.value, rt.created_at from tweet as t inner join retweet as rt on rt.tweet_id = t.id where rt.user_id = 1) a order by created_at desc;
+----+----------------+---------------------+
| id | value          | created_at          |
+----+----------------+---------------------+
|  3 | User2 | Tweet1 | 2012-07-27 00:07:11 |
|  7 | User1 | Tweet5 | 2012-07-27 00:06:54 |
|  6 | User1 | Tweet4 | 2012-07-27 00:06:50 |
|  5 | User1 | Tweet3 | 2012-07-27 00:06:47 |
|  4 | User3 | Tweet1 | 2012-07-27 00:06:37 |
|  2 | User1 | Tweet2 | 2012-07-27 00:04:35 |
|  1 | User1 | Tweet1 | 2012-07-27 00:04:30 |
+----+----------------+---------------------+