Mysql – View for a MANY_MANY_MANY relationship

foreign keyMySQLrelational-theoryview

Let's say I have 3 main tables:
category, tag, url

category and tag have a MANY_MANY relationship (category_tag).

tag and url have a MANY_MANY relationship (url_tag).

I would like for a given category to retrieve all the linked urls.

What is the easiest, most efficient way to do this?

Could I maybe build a "VIEW"? What would be the query?

I am using MySQL.

Best Answer

Encapsulating the queries into Views or not, is your choice. For every one of the below queries, you can create a view:

CREATE VIEW category_url AS
  ( Query
  ) ;

If you want only the IDs of the categories and URLs, the simplest (joining only 2 tables) is this - no need for joining the tag table, as long as the foreign key constraints are defined:

    SELECT DISTINCT 
        ct.category_id,
        ut.url_id 
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id

or (the equivalent) which can be easily extended to include the number of tags relating a category with a url:

    SELECT 
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags         --- optional
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 
    GROUP BY 
        ct.category_id,
        ut.url_id

If you also want columns from the category and url tables as well (which is very probable), you need to join those tables, too:

    SELECT 
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags,        --- optional

        c.*,                                       --- the columns from `category`
        u.*                                        --- and `url` that you need
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 

      JOIN 
        category AS c 
          ON c.id = ct.category_id
      JOIN 
        url AS u
          ON u.id = ut.url_id

    GROUP BY 
        ct.category_id,
        ut.url_id

Related Solutions

Mysql – How should I save apache logs into a thesql table

Because this is really logging data that you are capturing, I would first store it in its raw form, then ETL/normalize it as needed.

For searching, you can index the front part of the URLs first few characters. Let's say the options are http:// and https:// - then limit the index size to three characters passed the protocol part.

CREATE TABLE log ( 
datetime_created DATETIME, 
url VARCHAR(1024), 
domainname VARCHAR(255),
someotherdata VARCHAR(255), 
...
INDEX `idx_domain` (domainname(3))) 
ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

InnoDB will allow you to search the table w/o locking it.
Compression will help with disk space.
INDEX idx_domain (domainname(3)) will index the first 3 characters of the domain name column and speed up searches. However, the strategy for REGEXP will be to try to match on the front of the domain name.

Scale will eventually be an issue if the site becomes popular, so buyer beware.

Mysql – Efficient way to query set of rows from MySQL

First, try putting together the ids in a query (a tableless query)

SELECT 1 id UNION
SELECT 2    UNION
SELECT 3    UNION
SELECT 4    UNION
SELECT 5    UNION
SELECT 6    UNION
SELECT 7;

Watch it work...

mysql> SELECT 1 id UNION
    -> SELECT 2    UNION
    -> SELECT 3    UNION
    -> SELECT 4    UNION
    -> SELECT 5    UNION
    -> SELECT 6    UNION
    -> SELECT 7;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
+----+
7 rows in set (0.00 sec)

mysql>

Now, JOIN that tableless query to the data table

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B ON A.id = B.id
;

SELECT
    B.mydata
FROM
    (SELECT 1 id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) A
    INNER JOIN mytable B USING (id)
;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – How should I save apache logs into a thesql table

Mysql – Efficient way to query set of rows from MySQL

Related Question