Implementation for tag based mail system by JOINS using MySQL Database

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, Auto Increment                    |
| 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  |
+---+-------------------------------+------------------+

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

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 MySQL 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.x. 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.