MySQL storing and searching string

Question

Hi all,

I’m currently in the process of developing my own blogging system. Currently when you create a new post, you get the option to archive it categories of your own choise.

Currently I’m storing the categories as a VARCHAR value in a mysql database. As an example the field will contain 2,4,8 if the user has chosen the categories with ID: 2, 4 and 8.

To retrieve the blog posts for the category with ID 4 I then use:

SELECT col FROM table WHERE LOCATE(',4,', CONCAT(',',col,','))

I’ve been told that values seperated with a decimal comma is a no-go (very bad) when it comes to good database structure!

Can anyone provide me with a good way/technique to make this the most effective way?

Thanks in advance

Answer 1

A flexible & robust setup, as posted so many times in SO:

POSTS
id
name
text

CATEGORIES
id 
name

POST_CATEGORIES
post_id
category_id

Where the current query would be:

SELECT p.id, p.name, p.text
FROM posts p
JOIN post_categories pc
ON pc.post_id = p.id
AND pc.category_id = 4;

Answer 2

Look into relational database normalization. For your specific case consider creating 2 additional tables, Categories and BlogCategories in addition to your Blog content table. Categories contain the definition of all tags/categories and nothing else. The BlogCategories table is a many-to-many cross reference table that probably in your case just contains the foreign key reference to the Blog table and the foreign key reference to the Categories table. This allows 1 Blog entry to be associated with multiple categories and 1 Category to be associated with multiple Blog entries.

Getting the data out won't be any more difficult than a 3 table join at worst and you'll be out of the substring business to figure our your business logic.