如果我想创建一个类似于Facebook "reactions"数据库，这种架构有意义吗？

Question

我试图了解以下设计是否对Facebook反应有意义。我的想法是，每个反应最终都会是一个user_ids列表。 例： reaction_like =[19,24,36,75] [19,24,36,75] 是人们的用户 ID

CREATE TABLE users (
user_id BIGINT(20) unsigned NOT NULL auto_increment,
user_name varchar(255) NOT NULL UNIQUE,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
phone_number varchar(255) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(user_id)
) ENGINE = InnoDB;
CREATE TABLE posts (
user_id BIGINT(20) unsigned NOT NULL auto_increment,
post_id BIGINT(20) unsigned NOT NULL auto_increment,
post_text varchar(255) NOT NULL,
reaction_happy BIGINT(20) unsigned NOT NULL auto_increment
reaction_sad BIGINT(20) unsigned NOT NULL auto_increment
reaction_surprised BIGINT(20) unsigned NOT NULL auto_increment
reaction_like BIGINT(20) unsigned NOT NULL auto_increment
PRIMARY KEY (post_id)
)

Answer 1

你需要第三个表。从posts表中删除reaction_happy、reaction_sad等。然后做这样的东西：

CREATE TABLE reactions (
reaction_id BIGINT(20) unsigned NOT NULL auto_increment,
user_id BIGINT(20) unsigned NOT NULL,
post_id BIGINT(20) unsigned NOT NULL,
type ENUM('happy','sad','surprised','like') NOT NULL,
PRIMARY KEY (reaction_id)
)

此外，为了提高性能，您可能希望在所有 ID 列上添加索引。当您尝试执行诸如以Facebook呈现方式呈现主提要之类的事情时，您需要LEFT JOINusers并reactionsposts表。