Implementation for tag based mail system by JOINS using MySQL Database

@praveenkumar-66Ze92 • Oct 8, 2024

Oct 8, 2024

1.2K

This is the same thing we studied in our Database Management Systems. This is a practical use of JOINS. Consider this problem description:

Problem:
I want to create a database table in which, I want to have an attribute called label. It should be able to have multiple values and if I want records, where label = value, it should come for all. I hope you understand my question.

Database / Table: Mails

Description
id – Primary Key, Integer, Autoincrement.
subject – Varchar, 50
label – should be an array of labels. Each may have different lengths. (i.e. a mail may contain 2 labels, another may have 5, another may don’t have any!)

Records
1, My first mail, {inbox, personal}
2, Welcome to our company, {official}
3, Mr. T has added you on this!, {friends, inbox}

Now if I give a command,

select * from Mails where label = inbox

The result should be
1, My first mail, inbox, personal
3, Mr. T has added you on this!, friends, inbox

Like 0 Replies 6

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

CrazyEngineers powered by Jatra Community Platform

PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Mar 2, 2011

Solution
If you see the solution, having the labels field as an array is not possible. Its because, it is very tedious both for the programmer as well as the server. Instead, we can do one thing. We can use JOINS and join two tables, and proceed.

What two tables?
Have one table for the mails and another separate table for the labels. Join them by referring the Mail ID for which the label is assigned, in the labels table with the ID of mail in the mails table. To do this, follow the steps.

Step 1: Create a Database named mailserver.

CREATE DATABASE `mailserver`;

Step 2: Create a Table named mails, with the following options:

CREATE TABLE `mailserver`.`mails` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Subject` VARCHAR( 50 ) NOT NULL ,
`Content` VARCHAR( 500 ) NOT NULL
) ENGINE = InnoDB;

Step 3: Create a Table named maillabel, with the following options:

CREATE TABLE `mailserver`.`maillabel` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`LabelName` VARCHAR( 50 ) NOT NULL ,
`MailID` INT NOT NULL
) ENGINE = InnoDB;

Step 4: Insert a few sample mails under the table mails.

INSERT INTO `mailserver`.`mails` (
`ID` ,
`Subject` ,
`Content`
)
VALUES (
NULL , 'Welcome Home', 'Hey man, Welcome to your new house.'
), (
NULL , 'Hi', 'Hey there, wanna see what you doing at home!'
), (
NULL , 'Your promotion', 'This is to say about your promotion!'
), (
NULL , 'What the hell?', 'College is really bad!'
), (
NULL , 'My Project Work!', 'I have hereby attached my project work!'
);

Step 5: Assign the mails with the mail labels as shown here.

INSERT INTO `mailserver`.`maillabel` (
`ID` ,
`LabelName` ,
`MailID`
)
VALUES (
NULL , 'inbox', '1'
), (
NULL , 'inbox', '3'
), (
NULL , 'inbox', '5'
), (
NULL , 'personal', '1'
), (
NULL , 'friends', '2'
), (
NULL , 'office', '3'
), (
NULL , 'personal', '4'
), (
NULL , 'college', '5'
);

Now lets see the contents of the tables.

mails

mysql> select * from mailserver.mails;

+----+------------------+----------------------------------------------+
| ID | Subject          | Content                                      |
+----+------------------+----------------------------------------------+
|  1 | Welcome Home     | Hey man, Welcome to your new house.          |
|  2 | Hi               | Hey there, wanna see what you doing at home! |
|  3 | Your promotion   | This is to say about your promotion!         |
|  4 | What the hell?   | College is really bad!                       |
|  5 | My Project Work! | I have hereby attached my project work!      |
+----+------------------+----------------------------------------------+
5 rows in set (0.00 sec)

maillabel

mysql> select * from mailserver.maillabel;

+----+-----------+--------+
| ID | LabelName | MailID |
+----+-----------+--------+
|  1 | inbox     |      1 |
|  2 | inbox     |      3 |
|  3 | inbox     |      5 |
|  4 | personal  |      1 |
|  5 | friends   |      2 |
|  6 | office    |      3 |
|  7 | personal  |      4 |
|  8 | college   |      5 |
+----+-----------+--------+
8 rows in set (0.02 sec)

Step 6: Using JOIN, join both the tables, mails and maillabel, with the following command and see the output.

mysql> select * from mailserver.mails join mailserver.maillabel
       on mailserver.mails.id=mailserver.maillabel.mailid;

+----+------------------+----------------------------------------------+----+-----------+--------+
| ID | Subject          | Content                                      | ID | LabelName | MailID |
+----+------------------+----------------------------------------------+----+-----------+--------+
|  1 | Welcome Home     | Hey man, Welcome to your new house.          |  1 | inbox     |      1 |
|  1 | Welcome Home     | Hey man, Welcome to your new house.          |  4 | personal  |      1 |
|  2 | Hi               | Hey there, wanna see what you doing at home! |  5 | friends   |      2 |
|  3 | Your promotion   | This is to say about your promotion!         |  2 | inbox     |      3 |
|  3 | Your promotion   | This is to say about your promotion!         |  6 | office    |      3 |
|  4 | What the hell?   | College is really bad!                       |  7 | personal  |      4 |
|  5 | My Project Work! | I have hereby attached my project work!      |  3 | inbox     |      5 |
|  5 | My Project Work! | I have hereby attached my project work!      |  8 | college   |      5 |
+----+------------------+----------------------------------------------+----+-----------+--------+
8 rows in set (0.00 sec)

Step 7: Using a condition, we can get the contents of the mails from the particular label, inbox.

mysql> select * from mailserver.mails join mailserver.maillabel
       on mailserver.mails.id=mailserver.maillabel.mailid
       where mailserver.maillabel.labelname="inbox";

+----+------------------+-----------------------------------------+----+-----------+--------+
| ID | Subject          | Content                                 | ID | LabelName | MailID |
+----+------------------+-----------------------------------------+----+-----------+--------+
|  1 | Welcome Home     | Hey man, Welcome to your new house.     |  1 | inbox     |      1 |
|  3 | Your promotion   | This is to say about your promotion!    |  2 | inbox     |      3 |
|  5 | My Project Work! | I have hereby attached my project work! |  3 | inbox     |      5 |
+----+------------------+-----------------------------------------+----+-----------+--------+
3 rows in set (0.00 sec)

Step 8: The same way, we can get the mails labeled personal using this command.

mysql> select * from mailserver.mails join mailserver.maillabel
       on mailserver.mails.id=mailserver.maillabel.mailid
       where mailserver.maillabel.labelname="personal";

+----+----------------+-------------------------------------+----+-----------+--------+
| ID | Subject        | Content                             | ID | LabelName | MailID |
+----+----------------+-------------------------------------+----+-----------+--------+
|  1 | Welcome Home   | Hey man, Welcome to your new house. |  4 | personal  |      1 |
|  4 | What the hell? | College is really bad!              |  7 | personal  |      4 |
+----+----------------+-------------------------------------+----+-----------+--------+
2 rows in set (0.00 sec)

Step 9: If we need to get, what all the labels assigned to a particular mail, we can use this command, giving the ID of the mail.

mysql> select * from mailserver.maillabel join mailserver.mails
       on mailserver.mails.id=mailserver.maillabel.mailid
       where mailserver.mails.id=3;

+----+-----------+--------+----+----------------+--------------------------------------+
| ID | LabelName | MailID | ID | Subject        | Content                              |
+----+-----------+--------+----+----------------+--------------------------------------+
|  2 | inbox     |      3 |  3 | Your promotion | This is to say about your promotion! |
|  6 | office    |      3 |  3 | Your promotion | This is to say about your promotion! |
+----+-----------+--------+----+----------------+--------------------------------------+
2 rows in set (0.00 sec)

Step 10: This is for scripting, getting only the labels. The command is:

mysql> select labelname from mailserver.maillabel
       join mailserver.mails
       on mailserver.mails.id=mailserver.maillabel.mailid
       where mailserver.mails.id=3;

+-----------+
| labelname |
+-----------+
| inbox     |
| office    |
+-----------+
2 rows in set (0.00 sec)

This implementation is given using MySQL Server 5.1. This concept can be applied to Oracle, Microsoft SQL Server, IBM DB2, and so on. Even we can apply this to MS Access. Just the syntax of the queries differ in each database.

Source: #-Link-Snipped-#

PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Mar 27, 2011
Fetching multiple tags...

Guys, today something struck my mind. This method can tag multiple, but can retrieve only mails from a single label. I was wondering, how to retrieve if I want to get mails from more than one label. Today morning, sorry its too late now, I figured out the way... 😀
```
SELECT *
FROM `mails`
WHERE `mails`.`id`
IN (
    SELECT `mailid`
    FROM `maillabel`
    WHERE `LabelName`
    IN (
        'inbox', 'office'
    )
    GROUP BY mailid
    HAVING count( * ) =2
);
```
This query does the magic. If you break it, you can see we are selecting all the mails, where we are filtering with the mails with ID corresponding to some select statement, namely,
```
SELECT `mailid`
FROM `maillabel`
WHERE `LabelName`
IN (
    'inbox', 'office'
)
GROUP BY mailid
HAVING count( * ) =2;
```
If we explore this statement, it returns all the rows with the tags specified. Here, we have specified two tags. The result set is grouped by the mail IDs and once it is done, it returns only unique values of mail IDs and we also have a constraint of the count of the returned mail ID rows should be equal to 2, which is the sum of the number of tags specified. Try it out and do say your results... 😀
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Feb 7, 2012

Guys, did anyone try this???
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Mar 14, 2012

#-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-#, #-Link-Snipped-# Did you guys check this out? Try it out and do say... 😀
ISHAN TOPRE

@ishan-nohePN • Mar 14, 2012

Uum...What exactly am I supposed to try? I never studied MySQL in my college. If you can say clearly, I will give my review. 👍
PraveenKumar Purushothaman

@praveenkumar-66Ze92 • Mar 14, 2012

Issue
Uum...What exactly am I supposed to try? I never studied MySQL in my college. If you can say clearly, I will give my review. 👍
Sure buddy... If you wanna run a MySQL Server in your own machine, without any hassle, download #-Link-Snipped-# and you can try all those. If you are very new to Databases and Tables, and if you are really interested to learn, I would recommend you to head over to <a href="https://www.w3schools.com/php/php_mysql_intro.asp" target="_blank" rel="nofollow noopener noreferrer">PHP: MySQL Database</a> Introduction. I just demonstrated how our GMail's tag system works... 😀