在表之间建立关系

8ftvxx2r  于 2021-06-25  发布在  Mysql
关注(0)|答案(2)|浏览(350)

我需要一个搜索数据库与信息,我可以附加标签,看看他们是否已被使用,何时何地。
例如,我收到以下消息“嗨!我住在斯德哥尔摩,正在找一个勤杂工。我在你的个人资料里看到你有工具箱,因为我自己没有工具,除了螺丝刀,我希望雇用你会是最好的,因为你可以带自己的工具!请尽快与我联系!”
在这封信上,我想附上标签“斯德哥尔摩,勤杂工,工具箱,螺丝刀和工具”。
在搜索数据库时,我希望能够找到包含标签“stockholm”和“toolbox”的所有消息。
如果我决定使用上面的消息,并使用它,我想能够设置它是使用2018-02-11 11.52使用的名称“john doe”在网站“findahandyman.site”。
现在,这都是虚构的,我将使用完全不同的消息与其他标签,地方等,但场景是真实的。然而,我不知道怎样才能做到最好。
我是这样想的:

tbl-tags
 ----------
|id  | tag |
 ----------

  tbl-messages
 --------------
| id | message |
 --------------

          tbl-used
 -------------------------
| id | date | name | site |
 -------------------------

然后建立一个视图,在那里我可以搜索消息,注册了标签1、2、3等等。
我想得对吗?如果我是,我如何将它们联系起来,以及如何构建视图。如果我不是,我该怎么想?另外,如何将它们联系起来并根据您的建议构建视图?

gstyhher

gstyhher1#

tbl-tags
 ----------
|id  | tag |
 ----------

tbl-message-tags
 ----------------------
| id | tag_id | msg_id |
 ----------------------

tbl-messages
 --------------
| id | message |
 --------------

          tbl-used
 -------------------------
| id | date | name | site |
 -------------------------

创建表(如果需要,可以添加约束):

create table tbl_tags(id mediumint not null auto_increment, tag varchar(255) not null, primary key(id));
create table tbl_messages(id mediumint not null auto_increment, message text not null, primary key(id));
create table tmt(tag_id mediumint not null, msg_id mediumint not null, primary key(tag_id, msg_id));

插入一些测试数据:

insert into tbl_tags(tag) values ('tag0'), ('tag1');
insert into tbl_messages(message) values ('msg1'), ('msg2'), ('msg3'), ('msg4'), ('msg5');
insert into tbl_message_tags(tag_id, msg_id) values (1, 1), (0, 1), (1, 2), (0, 3);

在此之后,您可以这样进行查询:

select tag from tbl_tags join (select tag_id from tbl_messages join tbl_message_tags on id = msg_id where msg_id = 1) as t on id = t.tag_id;

结果将是:

----------
| id | tag |
|----|-----|
| 1  | tag0|
|----|-----|
| 2  | tag1|
 ---- -----

此外,还需要将消息标识符字段添加到 tbl-used 要获取消息,请链接到每一行。
另一种变体(不可取):
你需要 tbl-tags 仅当您希望在许多消息中使用类似的标记时(在接收消息后,您可以规范化大小写、拆分大小写并仅将新标记附加到 tbl-tags ),但是如果您不需要这种类型的优化,您可以在消息表中使用“array field”(也就是说,在mysql中,您可以用类似的方式来实现:如何在mysql中模拟数组变量?)。

oaxa6hgo

oaxa6hgo2#

我认为你需要这样做:
1.)使父表如下:

create table tbl_tags
(
   tagName VARCHAR(50) NOT NULL,
   dateAdded datetime NULL,
   primary key(tagName) 
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

创建 tbl_message 使用 id 作为主键( tagName 这里是主要的,因为这样标记名就不会重复)如下所示:

create table tbl_messages
(
   message_ID INT(11) NOT NULL AUTO_INCREMENT,
   message text NOT NULL,
   dateAdded NULL,
   primary key(message_ID) 
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

对于 tbl_used 我会让它成为一个有三列的Map表。一列是 message_ID (表中的外键) tbl_messages )另一个是 date and time 如果用的话,我还会加一个 id 如果多个用户试图同时使用同一条消息,则此处设置为primary以避免出错。

create table tbl_used
(
   used_ID INT(11) NOT NULL AUTO_INCREMENT,
   message_ID INT(11) NOT NULL,
   timeOfUse dateTime NOT NULL,
   PRIMARY KEY (`used_ID`),
   FOREIGN KEY (`message_ID`) REFERENCES `tbl_messages` (`message_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

2.)创建另一个Map表来关联 messages 以及 tags 表格之间:

create table tbl_messages_x_tbl_tags
(
    message_ID INT(11) NOT NULL,
    tagName VARCHAR(50) NOT NULL,
    PRIMARY KEY (`message_ID`, `tagName`),
    FOREIGN KEY (`message_ID`) REFERENCES `tbl_messages` (`message_ID`) ON UPDATE CASCADE,
    FOREIGN KEY (`tagName`) REFERENCES `tbl_tags` (`tagName`) ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

您将注意到,您将无法用任意内容填充Map表中的外键列。只能从各自的父表中插入有效值。这意味着您的Map表数据是一致的。
要填充表格,首先需要填充 parent tables ( tbl_messages , tbl_tags ),则可以填充 mapping tables ( tbl_messages_x_tbl_tags , tbl_used ).
在插入新消息时,只需检查新标记并将新标记插入表中 tbl_tags 如果他们还没到。然后将消息添加到 tbl_messages 并填充Map表 tbl_messages_x_tbl_tags(message_ID, tagName) 排。
之后,每次使用消息时,只需向数据库写入:

mysqli_query($connection, "INSERT INTO tbl_used (message_ID,timeOfUse) VALUES($msgID, NOW())");

相关问题