Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
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. –  Peter Apr 21 '13 at 18:29
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
    
+1 for identifying the problem as many-to-many. The original title indicated many-to-one. –  Walter Mitty Apr 21 '13 at 14:32
add comment

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.

share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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