[eluser]lenwood[/eluser]
Hi All,
I'm slowly working my way forward in building a full featured blog with CI, and I'd like for my entries to have both categories and meta tags. What's the best (or common) way to implement these? Here's how I see these being used.
Categories: predefined, set number, will only be associated with blog posts, every blog post will have them. Clicking will show all blog entries within that same category.
Meta Tags: author adds them as posts are generated, unlimited number, will be associated with blog posts and static pages. Clicking will show all content on the site with that tag.
This is my first attempt at creating a dynamic site from scratch, and I'm having trouble wrapping my head around this. Would it be best for these to each be their own table within the database? Or is there a way to have these as fields within a table?
Thanks in Advance,
Chris
[eluser]n0xie[/eluser]
This is my 'simple' take on it.
I assume you have a table called blogs where your blog posts are. For example you have 4 blog entries:
Code: blog_id, title, content
=======================
1, my programming blog, a blogpost about programming
2, my ruby article, an article about ruby
3, my second ruby article, another article about ruby
4, my php article, an article about php
Let's say your categories have a table categories:
Code: cat_id, parent_id, name
=======================
1, 0, programming
2, 1, ruby
3, 1, php
Which will give you the categories:
/programming
/programming/ruby
/programming/php
Now all you need is a field in your blog table:
Code: blog_id, cat_id, title, content
===============================
1, 1, my programming blog, a blogpost about programming
2, 2, my ruby article, an article about ruby
3, 2, my second ruby article, another article about ruby
4, 3, my php article, an article about php
Which put your first blog under the category programming, your second and 3rd blog under the category /programming/ruby and your fourth blog under the category /programming/php
Now for tags you need a different structure. We start with a table called tags:
Code: tag_id, name
============
1, php
2, ruby
3, programming
Now we need a way to bind the tags to your post. We use different relations table for that named blog_tags:
Code: blog_tag_id, blog_id, tag_id
============================
1, 1, 3 --> bind the first blog to the 'programming' tag
2, 2, 3 --> bind the second blog to the 'programming' tag
3, 2, 2 --> bind the second blog to the 'ruby' tag
4, 3, 3 --> bind the third blog to the 'programming' tag
5, 3, 2 --> bind the third blog to the 'ruby' tag
6, 4, 3 --> bind the fourth blog to the 'programming' tag
7, 4, 1 --> bind the fourth blog to the 'php' tag
Hope this gets you on your way.
[eluser]Benito[/eluser]
What are looking for exactly? The ideal Blog DB design for example?
[eluser]Benito[/eluser]
you beat me to it!  very nice skeleton, but dont forget to include fileds like author, post_date, nice_name and so on fields for the blog table.
[eluser]dmorin[/eluser]
n0xie's response is pretty good, but limits you to one category per post. Most blog engines allow multiple categories per post so if you're looking for that, your posts-to-categories relationship will be many-to-many instead of one-to-many so you'll need an intermediary joining table like he created for tags. His version of categories also allows for hierarchal categories which may or may not be more complex than you're looking for right now. I also don't like how in his example, he uses 0 as the parent id to represent that a category has no parent. This should instead be NULL so that parent_id could have a foreign key relationship with the cat_id column.
Do you understand table relationships and how to do joins or do you need further explanation on that?
[eluser]lenwood[/eluser]
This is great, VERY helpful. Thanks to all of you for informative responses!
[quote author="n0xie" date="1271702216"]We use different relations table for that named blog_tags.[/quote]
You lost me here, I don't know what a relations table is.
[quote author="dmorin" date="1271708295"]n0xie's response is pretty good, but limits you to one category per post. Most blog engines allow multiple categories per post so if you're looking for that, your posts-to-categories relationship will be many-to-many instead of one-to-many so you'll need an intermediary joining table like he created for tags... Do you understand table relationships and how to do joins or do you need further explanation on that?[/quote]
No, not at all. I've never set up a database for an application before. The extent of my experience is creating a database for WordPress. I will want the ability to have multiple categories per post.
I need to do some more reading...
[eluser]dmorin[/eluser]
Alright, well databases can be a little daunting, but once you understand the concepts, it's pretty easy. The one that you need to worry about here is the R part of a RDBMS - Relational. You have data in multiple tables and you need to have it be related to one another.
There are three main types of relationships in a database: one-to-one, one-to-many, and many-to-many.
One-to-one:
Example: Authors-to-UserProfiles. In your blog, you might have authors and user profiles in separate tables. In that case, each author can have one and only one profile and each profile can have one and only one author.
One-to-many:
Example: Posts-to-Comments or one post can have many comments. But, one comment cannot belong to many posts.
Many-to-many:
Example: Categories-to-Posts. Each post can have many categories. Each category can also be assigned to many posts.
So for each relationship you have, it's important to know which type it is. Both one-to-one and one-to-many are constructed in the same way by adding what's called a foreign key column to the right side of the relationship (the "many" side in a one-to-many). For example, if your Posts table has a primary key column called post_id, your comments table would also include a post_id column and contain the id of the post that it is related to.
Many-to-many relationships are more difficult because they require a third "joining" table. So if your Posts table has a primary key column called post_id and your Categories table has a primary key column called cat_id, you would create a third table called something like Categories_Posts with an an id column, a post_id column, and a cat_id column.
Here is that schema:
Code: mysql> select * from posts;
+----+------------+-----------------+-----------+---------------------+
| id | title | text | author_id | publish_date |
+----+------------+-----------------+-----------+---------------------+
| 1 | Post One | Post One Text | 1 | 2010-04-19 00:00:00 |
| 2 | Post Two | Post Two Text | 1 | 2010-04-19 00:00:00 |
| 3 | Post Three | Post Three Text | 1 | 2010-04-19 00:00:00 |
+----+------------+-----------------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from comments;
+----+---------+-----------+------+----------------------------+
| id | post_id | name | url | comment |
+----+---------+-----------+------+----------------------------+
| 1 | 1 | John Doe | | Worst Post Ever |
| 2 | 2 | Dan M | | FIRST! |
| 3 | 2 | John Does | NULL | People who say First Suck! |
| 4 | 3 | Dan M | NULL | These posts are lame... |
+----+---------+-----------+------+----------------------------+
4 rows in set (0.00 sec)
mysql> select * from categories;
+----+-----------------+
| id | category |
+----+-----------------+
| 1 | Fun with CI |
| 2 | Fun with DBs |
| 3 | Not so much fun |
+----+-----------------+
3 rows in set (0.00 sec)
mysql> select * from categories_posts;
+----+-------------+---------+
| id | category_id | post_id |
+----+-------------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 1 |
| 4 | 2 | 2 |
| 5 | 1 | 3 |
+----+-------------+---------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM posts LEFT JOIN comments ON posts.id = comments.post_id;
+----+------------+-----------------+-----------+---------------------+------+---------+-----------+------+----------------------------+
| id | title | text | author_id | publish_date | id | post_id | name | url | comment |
+----+------------+-----------------+-----------+---------------------+------+---------+-----------+------+----------------------------+
| 1 | Post One | Post One Text | 1 | 2010-04-19 00:00:00 | 1 | 1 | John Doe | | Worst Post Ever |
| 2 | Post Two | Post Two Text | 1 | 2010-04-19 00:00:00 | 2 | 2 | Dan M | | FIRST! |
| 2 | Post Two | Post Two Text | 1 | 2010-04-19 00:00:00 | 3 | 2 | John Does | NULL | People who say First Suck! |
| 3 | Post Three | Post Three Text | 1 | 2010-04-19 00:00:00 | 4 | 3 | Dan M | NULL | These posts are lame... |
+----+------------+-----------------+-----------+---------------------+------+---------+-----------+------+----------------------------+
4 rows in set (0.00 sec)
mysql> SELECT *
-> FROM posts p
-> LEFT JOIN categories_posts cp ON p.id = cp.post_id
-> LEFT JOIN categories c ON cp.category_id = c.id;
+----+------------+-----------------+-----------+---------------------+------+-------------+---------+------+-----------------+
| id | title | text | author_id | publish_date | id | category_id | post_id | id | category |
+----+------------+-----------------+-----------+---------------------+------+-------------+---------+------+-----------------+
| 1 | Post One | Post One Text | 1 | 2010-04-19 00:00:00 | 1 | 1 | 1 | 1 | Fun with CI |
| 1 | Post One | Post One Text | 1 | 2010-04-19 00:00:00 | 2 | 2 | 1 | 2 | Fun with DBs |
| 1 | Post One | Post One Text | 1 | 2010-04-19 00:00:00 | 3 | 3 | 1 | 3 | Not so much fun |
| 2 | Post Two | Post Two Text | 1 | 2010-04-19 00:00:00 | 4 | 2 | 2 | 2 | Fun with DBs |
| 3 | Post Three | Post Three Text | 1 | 2010-04-19 00:00:00 | 5 | 1 | 3 | 1 | Fun with CI |
+----+------------+-----------------+-----------+---------------------+------+-------------+---------+------+-----------------+
5 rows in set (0.01 sec)
[eluser]dmorin[/eluser]
Ran out of room...
So you should be able to work through my naming convention and determine how the relationships work. I also included some joins so you can infer the relationship by reading them. Also note that I included an author_id in the posts table, but didn't show an author's table, which would still have to be completed.
Once you get the structure setup, you should also define the relationships in the database by creating Foreign Key indexes. They are useful to ensure data consistency so you don't add a comment with a post_id that doesn't exist for example. In some engines, you can also define actions for ondelete and onupdate. That way, if you deleted a post and set the ondelete action to cascade, it would also delete the comments. It only cascades down though so if you had categories defined, it would delete the record in categories_posts, but not the category record itself.
I realize this is a lot to digest, but read through it and do some more research on your own and then let us know if you have specific questions.
EDIT:
I just realized I was talking through it calling the primary keys post_id, but then in my queries, I showed them as just id. Sorry if that's confusing. I usually always make the primary key of each table just "id" unless I have a good reason to change it.
[eluser]lenwood[/eluser]
This is awesome dmorin, thank you very much. It'll take me a couple of days to have time to get back to my system for more development, but this will definitely help. I really appreciate you taking the time to write this.
[eluser]lenwood[/eluser]
I've been reading about many-to-many relationships for the past couple of weeks and finally began coding this up over the weekend. I think I realize now that I was doing this differently than you propose.
My intent was to call each blog entry, and then call the categories with an entry_id that matches, then read the associated cat_id and call the cat_name that matches. I couldn't get it to run the way I wanted it to and ran out of time before completing the task.
I've now slept on it and was making some notes on how to proceed. I just read this again. It sounds like you're suggesting calling everything (entries, comments & categories), then making the associations in CI/PHP and putting together a query string with joins to call all of the data at once.
This is a conceptual shift for me. Being new to building a dynamically generated website, I was just doing this the way that made the most sense to me. Can anyone comment on why I'd want to use one vs the other? Is calling everything more efficient or better practice?
Thanks in advance for comments. This is more challenging than I anticipated, but I'm enjoying learning along the way.
|
