Linking one article to several categories.

I have a mysql database and i am searching for a way to create the database so that i can link one article to two categories.

For example let's say i have the articles and the category tables :

article
--------
ArticleID
Title
Content
CategoryID


category
----------
CategoryID
CateogryName


...and an article should have the option to be linked to several categories.Normally i would have the CategoryID column from the Articles table and INT and have the corresponding CategoryID from the Category table, but with an article corresponding to two categories (or several) there should be two integers.
How do i create the database to accomplish that?

 

In this case the best way would probably be 3 tables. One for the articles, one for the categories, and one for the relationship.

Basically:


article
--------
ArticleID
Title
Content

category
----------
CategoryID
CateogryName

articleCat
----------
ID
ArticleID
CategoryID

This way, you can associate an article with as many categories as you want. I would use foreign keys to ensure data integrity. articleCat.ArticleID to article.ArticleID and articleCat.CategoryID to category.CategoryID.

 

Thanks for the reply..

I was looking over the Wordpress structure and it was similar but a bit more confusing for me...now i get it...
I'll be creating the new tables with a appropriate structures...and see how to create the new insert and update pages for the CMS

 

Thanks

You've helped me a lot.

Fortumania

 

you don't need ID for artCat

and those ID's would NOT be AUTO_INCREMENT

thats the right idea tho

 

If you didn't have a ID for atrCat you either could not have a primary key for the table, or if you did have a primary key, you could only insert a single record.

The problem with creating tables without a key, is that it gets difficult when trying to edit them since there is no way to reference a single row. Other normal operations don't always work as expected.

 

No, you don't...

something like this will work, with no ID in artCat
(replace your "articles" table name with my "story" table name)

Our cat_id is AUTO_INCREMENT

mysql> select * from category;
+--------+-------------------------+
| cat_id  | cat_name                  |
+--------+-------------------------+
|      1    | developement            |
|     18   | uncategorized            |
+--------+-------------------------+

Code (markup):

NO, auto increment here, this is ONLY a relationship table,
so to follow it, we have cat_id =1, 18. if we look back at the "category" table will see these ID's 1 = developement, 18 = uncategorized.

mysql> select * from category_relationship order by cat_id;
+---------+--------+
| post_id  | cat_id |
+---------+--------+
|      66    |      1   |
|     146   |     18  |
|     147   |     18  |
|     148   |     18  |
+---------+--------+

Code (markup):

This can be expanded to select, titles, bodies, author_id's, or what ever

mysql> select id from story;
+-----+
| id    |
+-----+
| 161 |
| 163 |
| 164 |
| 165 |
| 166 |
+-----+

Code (markup):

Our query to relate articles with categories

mysql>  select s.id, c.cat_name 
    ->  from story as s, category as c, category_relationship as cr 
    ->  where c.cat_id = 1
    ->  and s.id = cr.post_id;
+-----+---------------+
| id     | cat_name     |
+-----+---------------+
| 163 | developement |
| 164 | developement |
| 165 | developement |
+-----+--------------+

Code (markup):

as we can see stories with ID 163, 164, 165 are associated with category ID 1

Obviously there's like a million and one ways to do this, you may have more or less fields in each table, but from my understanding a "artCat ID set to AUTO_INCREMENT" seems irrelevant, this is just my example, giving advice to the original poster...criticism? please explain with sample mysql/php code