MySQL table question. [MULTI PART QUESTION]

Question

I was wondering if I allowed my users to pick which categories their post will be displayed in and they can pick multiple categories from one to many.

• How would I store the categories id value or values in the database?
• Should I group each value together for example, 45,12,45,78 or should I store one value at a time?
• And how would my table structure look like?
• Or should I add it to an existing table?

Answer 1

The structure you are describing is really a many-to-many relationship (used in all tag-structures and alike).

Entries Table
+----+-------+------+
| id | title | text |
+----+-------+------+

Entries-to-Tags Table
+----------+--------+
| entry_id | tag_id |
+----------+--------+

Tags Table
+----+-----+
| id | tag |
+----+-----+

---

A typical SQL call to check on ALL entries from a tag would be

SELECT *
FROM tags_table as tg
LEFT JOIN entries_to_tags AS entg ON tg.id = entg.tag_id
LEFT JOIN entries AS en ON entg.entry_id = en.id
WHERE tg.tag = 'my tag'

A typical SQL call to check on tags from an entry would be

SELECT *
FROM entries AS en
LEFT JOIN entries_to_tags AS entg ON en.id = entg.entry_id 
LEFT JOIN tags_table AS tg ON entg.tag_id = tg.id
WHERE en.title = 'my article title'

Answer 2

The best way to design database tables is to separate them in smallest pieces possible. In this case, I will design it with 3 tables: Post, Category, and PostCategory. Post will be the table for each post created by the user. Category should probably be a lookup table. PostCategory is the table to store each post's categories.

• Post is one to many PostCategory
• Category is one to many PostCategory
• PostCategory has PostId and CategoryId primary ids