tags:

views:

42

answers:

2

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?
A: 

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'
Frankie
The `id` field in the `Entries-to-Tags` table is unnecessary. Make `entry_id` and `tag_id` the (compound) primary key for that table.
@Jeff Mattfield, true! I've set it up on my own scheme for future revision control. Changed for simplicity. Thks!
Frankie
A: 

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
dannielum