Building a relation between tables

Question

I need a searchable database with messages, which I can attach tags to, and see whether they have been used, when and where.

For example, I got the following message "Hi! I live in Stockholm and are looking for a handyman. I saw in your profile that you own a toolbox and since I don't own any tools myself, except for a screwdriver, I was hoping that hiring you would be the best since yo can bring your own tools! Please contact me ASAP!"

To this message, I want to attach the tags "Stockholm, handyman, toolbox, screwdriver and tools".

When searching the database, I wish to be able to find all messages containing the tags "Stockholm" and "Toolbox".

If I then decide to use this message above, and use it, I want to be able to set that it was used 2018-02-11 11.52 using the name "John Doe" at the site "findahandyman.site".

Now, this is all fictional, I will use completely different messages with other tags, places etc. But the scenario is real. However, I am not sure what way to do this best would be.

I am thinking like this:

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

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

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

And then build a view where I can search the messages, registered with the tags #1 #2 #3 etc.

Am I thinking right? If I am, how can I relate them all and how to build the view. If I am not, how should I think? And also, how to relate them all and build the view according to your suggestion(s)?

Answer 1

In my opinion you would need to do this:

1.) make the parent tables like this:

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

create the tbl_message table using an id as a primary key (tagName is here primary because this way tag names will not duplicate) like this:

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;

For the tbl_used I would make it a mapping table with three columns. One column would be the message_ID (a foreign key from the table tbl_messages) and the other the date and time it was used, I would also add an id as primary here to avoid getting an error if multiple users try to use the same message at the same time.

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.) create another mapping table to relate the messages and tags tables to each other:

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;

You will notice that you will be unable to populate the foreign key columns in the mapping tables with arbitrary content. You can only insert valid values from their respective parent tables. That means your mapping table data is consistent.

To fill the tables, you first need to fill the parent tables (tbl_messages, tbl_tags), then you can populate the mapping tables (tbl_messages_x_tbl_tags, tbl_used).

On insertion of a new message you would simply check for new tags and insert new tags into the table tbl_tags if they are not already there. Then add the message into tbl_messages and populate the mapping table tbl_messages_x_tbl_tags with (message_ID, tagName) rows.

After that, on each use of the message you can simply write to the database:

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

Answer 2

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

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

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

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

Creating tables (if you want, you can add constraints):

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 some test data:

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);

After this you can make query like this:

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;

Result will be:

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

Also, you need to add message identifier field into the tbl-used to get message, linked with every row.

---

Another variant (not preferable):

You need tbl-tags only if you want to use similar tags in many messages (after receving message, you can normalize case, split it and append only new tags into the tbl-tags), but if you don't need this type of optimization, you can use "array field" in the message table (i.e., in mysql you can do this in the similar way: How can I simulate an array variable in MySQL?).