[eluser]Twisted1919[/eluser]
Hi , i have the following table schema .
Code:
CREATE TABLE IF NOT EXISTS `mdb`.`categories` (
`category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`parent_id` INT UNSIGNED NOT NULL DEFAULT 0 ,
`name` VARCHAR(50) NULL ,
`description` TEXT NULL ,
`nicename` VARCHAR(50) NULL ,
`url` VARCHAR(100) NULL ,
`sort_order` INT(5) UNSIGNED NOT NULL DEFAULT 0 ,
PRIMARY KEY (`category_id`) ,
INDEX `parent_id` (`parent_id` ASC) )
ENGINE = InnoDB;
Right now a new created category will have parent_id set to 0 , meaning that has no parents and if i create another sub-category, this will get the parent_id of the parent category .
My problem is that i want to represent in this table a relationship of one-to-many , i mean a category can have multiple sub-categories and a sub-category belongs only to a category.
Making this reference , deleting a category having subcategories will delete the subcategories too.
The issue is that when i create the reference between the two , i must always supply a valid parent_id, not 0 or null , and i don't want this , i just want to be able to let the parent id to be optional (0 or null) and on delete , if a category has childs , delete the childs too , if not delete only the category .
How can i achieve this ?
