Advice needed on creating 'sub-catagory's' in my mySQL

Databases
1

Hey Folks,

 I have written a website for publishing articles online. It is a very basic website, with around 5 different main catagories for articles (one catagory per php page). My database skills are somewhat basic compared to my programming skills so I thought I would query the following with you.

I would like to be able to add sub-catagorys (at will) to articles published under each of the 5 main catagories so I can list articles by sub-catagory on each of the main 5 pages are required. Also I am likely to write a search function later down the line, so it would be handy for my users to search by catagory and sub-catagory.

Here is my current table structure, as you can see its currently one table with a column for the main catagory:-


CREATE TABLE IF NOT EXISTS `articles` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `isApproved` tinyint(4) NOT NULL DEFAULT '0',
  `dateTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `title` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `opening` varchar(165) COLLATE utf8_unicode_ci NOT NULL,
  `Left_P1` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `Left_P2` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `Left_P3` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `Left_P4` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `img_path` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `Right_P5` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `Right_P6` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `Right_P7` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `Right_P8` varchar(650) COLLATE utf8_unicode_ci NOT NULL,
  `catagory` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `userID_FK` bigint(20) NOT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;

If someone could recommend how to acheive my requirements with the table structure, and perhaps a sample query on pulling articles out by catagory and / or sub catagory I would be most appreciative :slight_smile:

Many thanks

2

perhaps this may help… Categories and Subcategories

3

Thanks for the reply. I have to say that left my head spinning a little…

4

Another option is the Nested Set Model for database hierarchies.

5

Thats even more confusing to me… my fault entirley of course :smiley:

I think im going to scrap the idea all-together, I clearly dont have the skills :slight_smile:

6

Whichever model you go with, you’re really going to need to have at least a basic understanding of sql and the concept behind the model otherwise you’re most likely to mess up your database very quickly.

7

I have a basic understanding of SQL, im just not experienced in it. I appreciated the help, ill go away and think about my next move.

8

Have a read through the 2 links and do some more googling on the one you are initially most comfortable with. Personally I prefer the nested set model because I find it relatively easy but many prefer the adjacency list model. There is plenty of info and sample queries on google for both models.

9

Thankyou Kind Sir