CODEWITHSUNDEEP
phpmysqldatabase-design
user533178
user533178

Reputation: 77

one article on multiple category

I need to add article to multiple category for example I have article name 'test article' need to add it to category 1 and category2 need to know how to make relationship between to tables for this

note : in the past I make row in article database table name 'category' and add category id like 1,2,3 but this make problem in search and list please help me about that

Upvotes: 1

Views: 2197

Answers (3)

Johan
Johan

Reputation: 76537

The link between article and a category is a n-to-n relationship.
A article can belong to multiple categories and a category can be attached to multiple articles.

Because SQL does not support n-to-n relationships directly, you need a link table.

Table article_cats
------------------
cat_id integer,
article_id integer,
primary key (cat_id, article_id)

You then link the articles like so:

SELECT a.name, GROUP_CONCAT(c.name) as cats
FROM articles a
LEFT JOIN article_cats ac ON (a.id = ac.article_id)
LEFT JOIN cats c ON (c.id = ac.cat_id)
GROUP BY a.id

Upvotes: 2

Konerak
Konerak

Reputation: 39763

You need three tables:

  • One table for articles
  • One table for categories
  • One table linking articles to categories, we might call it categories_per_article. This table often is called a Junction table or a Association table.

Example data:

ARTICLES
ID | Name | Description
1  | Fuzz | A fuzzy three
2  | Bizz | A five that means bizznezz

CATEGORIES
ID  |  Name 
1   |  Prime numbers
2   |  Multiples of five
3   |  Smaller than four

CATEGORIES_PER_ARTICLE
ID | ARTICLE_ID | CATEGORY_ID
1  | 1          | 1
2  | 1          | 2
3  | 1          | 3
4  | 2          | 1

You will see that article 1 (Fuzz) has three categories, and article 2 (Bizz) only has category 1. We call this a many-to-many relationship (or n-to-n, for example in ER or UML), because article 1 has multiple categories, and category 1 is used by multiple articles.

You can do all possible queries using this schema - feel free to ask in comments for specific examples (like How do I get all articles which have categories 1 and 3 but not 2).

Upvotes: 7

Kilian Foth
Kilian Foth

Reputation: 14336

You have a many-to-many relationship here: articles can have multiple categories, and categories obviously can have multiple articles in them. This cannot be represented properly without at least one more table that explicitly stores the associations between your entities.

Upvotes: 0

Related Questions