[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)
