Matching from multiple relationships

Question

I have a "posts" table. Each post record can have one or more tags. If I store the tags into a single column, as a comma delimited string, I can easily find the posts that have tag "Abc" and tag "Def", or even do partial matching.

I'm using sqlite and a "FTS" table with this method and it works fine, but someone told me that it's a bad practice and I should store the tags in a different table and use JOINs. If I store the tags in a different table and create a relationship between these 2 tables, how do I find posts with both tag "Abc" and tag "Def" in a single query? Is it possible?

Answer 1

That is indeed bad design practice.

You will get slow performance since you need to do string operations on the column value to seperate the tags. So indexes can't be used. And it is against the normalization rules of database design.

A better design would be

tags table
----------
id
name


posts table
-----------
id
title
body
...


post_tags table
---------------
post_id
tag_id

To get all posts have both tags do

select p.id, p.title, p.body
from posts p
join post_tags pt on pt.post_id = p.id
join tags t on pt.tag_id = t.id
where t.name in ('abc','def')
group by p.id, p.title, p.body
having count(distinct t.id) = 2