Managing Categories?!?

[Onloac]

Hello, Let me start off by saying that I'm not the most advanced user when it comes to PHP. I'm trying to teach myself everything and so far have been able to get what I want done. My host recently shut down and I lost some files so I'm looking to rebuild my CMS from the ground up. I've started to work on my news section and have come across a problem. Basically i'm wondering if I should continue to use the same methods I have in the past. I thought before going forward I would ask for everyone's advice here and see what some of the pros/cons are for each method of thinking of using. The two methods are below:

 

First Method:

Previously I would just add a column to the end of my article row in my database. So for example my database row would look like this:

 

ID

TITLE

BODY

AUTHOR

TIMESTAMP

CATEGORY 1

CATEGORY 2

CATEGORY 3

 

Now when an article would be posted to a category it would simple have a 1 within that categories column. It was a very simple way to do what I wanted. It's worked fine for me this far, but I've learned of a second method and want to know whats better about it.

 

Second Method:

Now rather then having the category info stored within the same row, some people suggest I create an entirely new table that would store the category ID/Article ID. Then just refer to that table when wanting to know what articles relate to what categories. I'm not to keen on this idea cause its creates alot more work for me. What I want to know is whats the good/bad about each of these methods?

[Alex]

It depends on how complicated things are going to get, and how much flexibility you need. Regardless, your first method is horrible.

 

Why don't you just create a single column named "category", then within that colunm you just enter the name (or id) of that category. Then go from there.

[Onloac]

Well, I was thinking of doing that but I'm dealing with articles posted to multiple categories. I was think of just storing them within one column kinda like "1,2,3,4" and just pull them into an array after. I'm not really sure what I should do. :S

[Alex]

Oh, well in that case you should use two tables. You could store it as 1,2,3, but that's a horrible database structure.

 

1 Table should be Categories, it'll just have an ID field (auto_increment primary key), and the name of it. Then Articles should have a auto_increment primary key (as well), a category_id, and any other information you need.

 

Then you'll just link the two by the category id in the articles table that'll correspond to a row from the Category table.

[TeNDoLLA]

I would do something like this, but I would probably use also foreign keys in the tables. This way you are able to add as many categories you want and you are able to link as many articles to as many categories you ever want.

 

/* Create tables */
CREATE TABLE `articles` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `data` TEXT NOT NULL,
  PRIMARY KEY (`article_id`)
) ENGINE=MyISAM;

CREATE TABLE `categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=MyISAM;

CREATE TABLE `articles_join_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` varchar(255) NOT NULL,
  `article_id` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

/* Some test data */
INSERT INTO articles (title, `data`) VALUES('test article 1', 'some text');
INSERT INTO articles (title, `data`) VALUES('test article 2', 'some text');
INSERT INTO articles (title, `data`) VALUES('test article 3', 'some text');
INSERT INTO articles (title, `data`) VALUES('test article 4', 'some text');
INSERT INTO articles (title, `data`) VALUES('test article 5', 'some text');

INSERT INTO categories (category_name) VALUES('category 1');
INSERT INTO categories (category_name) VALUES('category 2');
INSERT INTO categories (category_name) VALUES('category 3');

/* join some articles to categories */
INSERT INTO articles_join_categories (category_id, article_id) VALUES(1, 1);
INSERT INTO articles_join_categories (category_id, article_id) VALUES(1, 2);
INSERT INTO articles_join_categories (category_id, article_id) VALUES(1, 3);
INSERT INTO articles_join_categories (category_id, article_id) VALUES(2, 2);
INSERT INTO articles_join_categories (category_id, article_id) VALUES(2, 3);
INSERT INTO articles_join_categories (category_id, article_id) VALUES(3, 4);
INSERT INTO articles_join_categories (category_id, article_id) VALUES(3, 5);

/* For example get all articles in category 1 */
SELECT a.title, a.data, c.category_name
  FROM articles a  
  JOIN articles_join_categories link ON link.article_id = a.article_id  
  JOIN categories c ON c.category_id = link.category_id  
  WHERE c.category_id = 1;