3

I have posts stored in a posts table and I want each post to have multiple categories, what is the most efficient way to store the categories and their relationships ?

I thought of it this way first:

Categories Table:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(32)   | NO   |     | NULL    |                |
| parent     | int(11)       | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Relationships Table:

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| post_id    | int(11)       | NO   |     | NULL    |                |
| cat_id     | int(11)       | NO   |     | NULL    |                |
| order      | int(11)       | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

Using this structure I will have lots of rows inside the relationships table, for example if a post has 4 categories I will have 4 rows only for this post, of course I won't have that much categories related to the post but I want to implement the same structure for tags too.

Am I doing it right ? is there a better structure to use ?

  • What is the order column for? Is it to define "order" (of significance?) to posts of the same category? Or to categories of the same post? – ypercubeᵀᴹ Apr 21 '13 at 18:04
  • @ypercube it's to define the categories order for the post. – Pierre Apr 21 '13 at 18:29
5

The way that you have described it is the way to do it. The Primary key for this table is Post_Id, Cat_ID.

  • +1 for identifying the problem as many-to-many. The original title indicated many-to-one. – Walter Mitty Apr 21 '13 at 14:32
1

One-to-many are generally stored with a key in the detail table

Something like:

Master-table
============
Id    Name
--    -----
1     Test1
2     Test2

Detail-table
===============
Id MasterId Text
-- -------- -----
1  1        Text1
2  1        Text2
3  2        Text4
4  2        Text5

If you have a many-to-many relationship you will need an extra table in between.

  • 1
    this is a many-to-many relationship. each post has many categories and each category has many posts. he did the relationship table ok (entity B). – haki Apr 21 '13 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.