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, 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

Replies

  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    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 Purushothaman
    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 Purushothaman
    Guys, did anyone try this???
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    #-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 TOPRE
    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 Purushothaman
    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 PHP: MySQL Database Introduction. I just demonstrated how our GMail's tag system works... 😀

You are reading an archived discussion.

Related Posts

I recently install windows server 2008 R2 as my home server. It has two NIC card, one connected to internet, and other connected to my private network. Now I enable...
Guys have you ever tried ecommerce site and buy or sell something through it? How was your experience ? Is it reliable?
About 30-40% of power outages around the world are caused due to the lightening. The transmission lines are subjected to both direct lightning strokes and induced over voltages. Of these,...
This is not exactly a puzzle.I have invented this myself. Have you ever wondered why we have a circle of 360 degrees only? When we can have it of 100...
The world is moving the past-PC era and soon we'll see touch screen devices everywhere. There's lot of innovation happening on the display technology and it seems that Quantum Dot...