How to design unlimited category tables?

I would like to have unlimited categories with unlimited sub levels. How to design such tables in my database?

 

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) unsigned DEFAULT NULL,
  `title` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Code (markup):

Where if root category is created parent_id will be null, etc. I hope you just need data structure.

 

Thanks. So, if I have this two categories with id 1 and 2:

color
size

the red, blue, green with ids e.g. 3,4 and 5 would all have parent_id equals to 1 and

s,xl,xxl with ids e.g. 6,7,8 would have parent_id equals to two?

Is this the correct approach?

 

Other than starting at 0 and using NULL or -1 as 'this is a category', yes.

 

NULL or 0 is fine, -1 no, as if you look closely the type it is 'unsigned' integer, and arbitrary precision wont allow value < 0. Just to correct.

regards

 

you have to create table & then coding for this.

 

You set up two tables, one for the unlimited levels and another for the unlimited sublevels. First table would have the level name and a unique key. Second table would have the unique keys and the sublevel names. Then you would link them together in a 1 to many relationship.

Then you could add as many levels and sublevels that you need now and at any time in the future.