Blog Database Structure - Categories & Meta Tags

[eluser]lenwood[/eluser]
First, hearty thanks to all, I really appreciate the help.

[quote author="jedd" date="1272978124"]...it may be useful to know here how much data you anticipate pulling out in total with each pass. It's possible that it'd be more elegant to simply pull everything you need in a single query.[/quote]

This will be for a blog, I would anticipate that over time it will end up with hundreds of entries and thousands of comments. I think it would be rare that any single entry would receive hundreds of comments, though that's possible. This is all for a site that is just launching, so it'll likely be months before it gains the popularity to deal with that kind of volume though.

[quote author="jedd" date="1272978124"]An updated and annotated schema would be handy at this point.[/quote]

Here's what I have right now.

Code:

entries table
+----+-----------+------------+-----------+------+
| id | date_time | post_title | post_slug | body |
+----+-----------+------------+-----------+------+

comments table
+----+-----------+----------+------+--------+
| id | date_time | entry_id | body | author |
+----+-----------+----------+------+--------+

categories table
+----+------+
| id | name |
+----+------+

post_cat table
+----------+--------+
| entry_id | cat_id |
+----------+--------+

Over time I intend to add a few more things to these tables, but that's what I have right now.

Here's what I plan to add:
entries
-author
-published

comments
-URL
-approved

[eluser]lenwood[/eluser]
[quote author="dmorin" date="1273007459"]

Code:

$this->db->like('entry_id', $row->id);

Why are you doing "like" here? when comparing ids, you should use a straight "column = value".[/quote]

That's the only way that I could get the query to run. I don't recall the specific error that I was getting, but when I originally wrote this I was using $this->db->where. I haven't tried it again since updating to the join statements.

I'll try that, as well as your updated query tomorrow and let you know how it goes.

[eluser]psp83[/eluser]
[quote author="dmorin" date="1273015270"]

Or, use group_concat to display multiple categories

As long as you don't want/need to turn each category into a link back to the category page, that's a great solution.[/quote]

You can turn them into links with the following.

Code:

GROUP_CONCAT(CONCAT('< a href="/blog/category/', LOWER(table_name.name), '">', table_name.name, '</ a>') SEPARATOR ", ") as categories

[eluser]mrwilson1[/eluser]
I have a question on this. Could you not put the tags and categories all in the main table(blog entry) and use explode when it comes time to display them? In place of having a relational database and not having to perform joins? Of course for comments that would be a different issue and require a separate table

[eluser]dmorin[/eluser]
Sure you could do it that way, but everything is a tradeoff. Sure it's a little more simple at first, but lets say you want to create category pages. How do you list all of the categories that you've used in your blog? You'd probably have to pull back each entry, explode the categories into a single array and then filter out the duplicates. That isn't very efficient. Or you could just have one table that lists each category you've used, but if you're doing that, you're not really saving a lot of effort by storing them all in one field.

Same probably when someone browses to the category page. How do you pull back all of the posts for a given category? Use a LIKE "% catname%" is the first that comes to mind, but that doesn't scale very well to large numbers of posts (though it would probably perform fine for a single blog). The biggest issue with using like is that you'll find some category names inside others. For example, the category "Cat" would also return posts with the category "Category", etc. unless you used a good delimiter.

So sure, it's an option. But do the costs of doing it the "easy" way really make up for the crap you're going to have to put up with?

[eluser]mrwilson1[/eluser]
You are right, i didnt think far enough ahead. But it would have been for a simple one author blog. You make excellent points, thanks