[eluser]flaky[/eluser]
why create 2 tables for categories, you could have the categories table like this
Code:
category_id smallint
category_name varchar(50)
category_parent_id smallint
//the sql code
CREATE TABLE `category` (
`category_id` smallint(6) NOT NULL AUTO_INCREMENT,
`category_name` varchar(50) COLLATE utf8_bin NOT NULL,
`category_parent_id` smallint(6) DEFAULT NULL,
PRIMARY KEY (`category_id`),
KEY `FK_category_parent_id` (`category_parent_id`),
CONSTRAINT `FK_category_parent_id` FOREIGN KEY (`category_parent_id`) REFERENCES `category` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
whenever category_parent_id is null that is a category, else it's a sub-category
