Database design to enable Multiple tags like Stackoverflow?

Question

I have the following tables.

Articles table
a_id INT primary unique
name VARCHAR
Description VARCHAR
c_id INT

Category table
id INT
cat_name VARCHAR

For now I simply use

SELECT a_id,name,Description,cat_name FROM Articles LEFT JOIN Category ON Articles.a_id=Category.id WHERE c_id={$id}

This gives me all articles which belong to a certain category along with category name.
Each article is having only one category.

AND I use a sub category in a similar way(I have another table named sub_cat).
But every article doesn't necessary have a sub category.It may belong to multiple categories instead.

I now think of tagging an article with more than one category just like the questions at stackoverflow are tagged(eg: with multiple tags like PHP,MYSQL,SQL etc).

AND later I have to display(filter) all article with certain tags(eg: tagged with php,php +MySQL) and I also have to display the tags along with the article name,Description.
Can anyone help me redesign the database?(I am using php + MySQL at back-end)

Answer 1

Create a new table:

CREATE TABLE ArticleCategories(
    A_ID INT,
    C_ID INT,
    Constraint PK_ArticleCategories Primary Key (Article_ID, Category_ID)
)

(this is the SQL server syntax, may be slightly different for MySQL)

This is called a "Junction Table" or a "Mapping Table" and it is how you express Many-to-Many relationships in SQL. So, whenever you want to add a Category to an Article, just INSERT a row into this table with the IDs of the Article and the Category.

For instance, you can initialize it like this:

INSERT Into ArticleCategories(A_ID,C_ID)
    SELECT A_ID,C_ID From Articles

Now you can remove c_id from your Articles table.

To get back all of the Categories for a single Article, you would do use a query like this:

SELECT a_id,name,Description,cat_name 
FROM Articles 
LEFT JOIN  ArticleCategories ON Articles.a_id=ArticleCategories.a_id 
INNER JOIN Category ON ArticleCategories.c_id=Category.id 
WHERE Articles.a_id={$a_id}

---

Alternatively, to return all articles that have a category LIKE a certain string:

SELECT a_id,name,Description
FROM Articles 
WHERE EXISTS(   Select * 
                From ArticleCategories 
                INNER JOIN Category ON ArticleCategories.c_id=Category.id 
                WHERE Articles.a_id=ArticleCategories.a_id 
                  AND Category.cat_name LIKE '%'+{$match}+'%'
             )

(You may have to adjust the last line, as I am not sure how string parameters are passed MySQL+PHP.)