Best way to build blog tables

[eluser]pixelcoder[/eluser]
It's been a while since doing any real code. Only bits and bobs, even CI is giving a big helping hand.

For the past year i've been doing nothing but WP but that time is over. Anyway...

I've made a couple of tables to handle a basic blog, they are outlined below.

Code:

------------
POSTS
------------
id
user_id
title
body
excerpt
slug
tags
status
created

------------
CATEGORIES
------------
id
user_id
name
slug
description
status

I'm wondering what the best way is to handle assigning the blog posts to the blog categories.

Would you build a table to bridge the gap? Something like...

Code:

------------
CATEGORY_POSTS
------------
id
blog_id
category_id

If that's the case, any tips on how to query that based on a url passed.

e.g.

Code:

http://site.com/blog/category/slug/

Thanks in advance

[eluser]Twisted1919[/eluser]
You really need to learn about the relationships between the tables aka relation types .
Just do a google search on the subject, you will find the answers, having that said, once you know how to handle relationships between tables, then MYSQL WORKBENCH is a great tool to built you database structure .

[eluser]pixelcoder[/eluser]
Hi thanks for the tips,

I do understand how a database works, it's been a little while though since college days and looking for a friendly nudge.

That's all will go and check out Workbench.

Cheers

[eluser]Bas Vermeulen[/eluser]
I'd add category_id to your posts table

[eluser]techgnome[/eluser]
The answer, like all things, is "it depends". Can posts belong to one category and one category only? If so then add a category_id to the posts table. If they can belong to more than one, then, yes, you need a table in the middle that will hold the link between posts & categories.

To retrieve the data back, you would need to
select from the posts table, inner joined to the post categories table on the post id, inner joined to the categories table on the category id, where the category slug matches the parameter passed in.


-tg

[eluser]Twisted1919[/eluser]
[quote author="techgnome" date="1287424138"]The answer, like all things, is "it depends". Can posts belong to one category and one category only? If so then add a category_id to the posts table. If they can belong to more than one, then, yes, you need a table in the middle that will hold the link between posts & categories.

To retrieve the data back, you would need to
select from the posts table, inner joined to the post categories table on the post id, inner joined to the categories table on the category id, where the category slug matches the parameter passed in.


-tg[/quote]
That's why i gave him the best advice he could get here.
He needs to understand and learn so that he can decide by himslef what's the best for each situation

[eluser]Bas Vermeulen[/eluser]
Ahh yeah you are right tg, I kinda assumed posts belong to one category because that's how I did it lol. It's never good to assume something ghehe

[eluser]pixelcoder[/eluser]
Hi guys,

Thanks for the replies placing a category_id field i had thought about but would limit posts to only one category. Unless did some weird comma insert and explode on output. Hence the table in the middle.

This is how WP does it, and this is how we normalised back in college. The inner join was the bit I needed, thanks for that. Gonna be a trip.

[eluser]techgnome[/eluser]
Oddly... last night, I just got done implementing multi-cats for something I'm working on... and this is the exact structure I ended up using: Articles, Categories, & Article_Categories. ... it really messed up my nice simple CRUD model... but oh well, going for function over form here.

-tg

[eluser]pixelcoder[/eluser]
Hey techgnome,

Surely that's the best way to go about things? If it messed up your simple CRUD model by making it more complicated. Why not make sure the next level of complication is as simple and tight as it can be?

I've been building this little project and rapidly these things have also happened. For once though, this project will be a spare time project and will be sure to make all the code as best as it possibly can be. To my standards at least.