Re: 1 post in 2 categories

On 10/14/2016 10:20 PM, bit-naughty_at_hotmail.com wrote:
> If I have say, a "blog" site with posts on it, I want a table for the posts, it'll have a primary key,
> post text, date posted etc. Now, *one* particular post can be in 2
categories - say,
> "gardening" and "vegetables" - obviously I can't have a "Category" field
> in the DB if 1 post can be in 2 categories.
> How do I do this? Having a whole separate line in the DB with 2
identical pieces of info,
> but different Categories, seems absolutely an AWFUL thing to do, and won't work cause they'll
> be different primary keys *anyway* - so do I just make a whole damn separate table just for "post id"
> (primary key from the above table), and a "category" field.....? Just so 1 post can be in 2 categories
> (or more).....? What's the most elegant solution for this?

You will usually have a linking table, say you have your post table with a post_id as the primary key.
Then you have your categories in one table, with columns like category_id, category_name, where the category_id is the primary key. Then you have the link table lets call it post_category and it has the following two columns: post_id and category_id, together they are the primary key.

This way a post can have multiple categories and it's easy to select all posts which belongs to a specific category. All you need to do is to join the tables when you need to know which categories a post belongs to or when you want to know which posts belongs a specific category

select * from post p
inner join post_category pc on pc.post_id = p.post_id inner join category c on c.category_id = pc.category_id where ...

all you have to modify is the columns you need in your selection and the where-statement "p.post_id = 1" / "c.category_id = 1"

-- 

 //Aho