最优化的方式来组织MySQL和url mod_rewrite中的博客类别？

Question

I've been creating a blog and I'm unable to decide the best way to categorize the articles.

The current way I thought was best would be to include the category in the articles table which includes the following rows.

  articles structure
+----------------------+
| article_id           |
| article_title        |
| article_content      |
| article_category     |
+----------------------+

In each article where I write I'd include the article category. Example.

+------------+---------------+------------------+------------------+
| article_id | article_title | article_content  | article_category |
+------------------------------------------------------------------+
|     1      | How do I....  | Want to know...  |      How-to      | 
|     2      | Learn faceb.. | Facebooks new... |  Social Network  | 
+------------+---------------+------------------+------------------+

and then use if(isset($_GET['article_id']) && isset($_GET['article_category'])){ } to retrieve both together, or would it be better to create a new table for the specific categories? an example below.

  categories structure
+-----------------+
| category_id     |
| category_title  |
| article_id      |
+-----------------+

and whenever I need to retrieve the articles based on categories I'd just use it from the categories table (The categories structure above is just an example)?

Also if someone could spare another moment to answer a simple question I'd appreciate it. I'll be using the articles table above as an example with the information.

instead of using the following to retrieve the articles,

www.example.com/article.php?article_id=1&article_category=How-to

I would like to display the article url as followed, could this be accomplished by using mod_rewrite or would I need to create folders in my server for it to be possible?

www.example.com/How-to/How-do-i

Does stackoverflow use mod_rewrite to do the following?

www.stackoverflow.com/questions/id/title?

Note: I know how to display the title in the url, all I've been wondering is about the rewriting.

Thank you for your time.

Answer 1

What you want is a regular 1:n relationship. One article can have one category, one category can have multiple articles. This can be resolved with two different models where one holds an identifier of an element of the other model. Speaking in terms of SQL: You would have two tables where in articles you'd save the category_id (remember: one article one category, so this is a correct implementation).

  articles
+----------------------+
| article_id           |
| article_title        |
| article_content      |
| category_id          |
+----------------------+

  categories
+-----------------+
| category_id     |
| category_title  |
+-----------------+

This would usually be done using a foreign key constraint. Once done, SQL checks if an entry is valid (so an article cannot have a number for category_id that doesn't exist in table categories). Also this will allow you to specify, e.g. that once a category is deleted, all referencing articles shall be deleted as well, or not.

If you want one article to be able to have multiple categories, you are lookin for a n:m relationship. In SQL you create a third table

articles_categories
+-----------------+
| article_id      |
| category_id     |
+-----------------+

Both attributes build the primary key, each is a foreign key as well.

---

To your second question: Yes this can easily be done via mod_rewrite. There are lots of tutorials out there and it should be quite simple. SO probably uses this as well, but I can't tell you for sure.