MySQL Creating Categories?

[random1]

Hey All,

 

I've created a table:

 

CREATE TABLE `product_category` (
`productcategory_id` BIGINT(50) UNSIGNED NOT NULL COMMENT 'The ID of the Product Category',
`productcategory_parent_id` BIGINT(50) UNSIGNED NOT NULL COMMENT 'The ID of the Product Category\'s Parent',
`productcategry_status_id` BIGINT(50) UNSIGNED NOT NULL COMMENT 'The Status ID of the Product Category',
`productcategory_name` VARCHAR(100) NOT NULL COMMENT 'The Name of the Product Category',
PRIMARY KEY (`productcategory_id`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

 

This table holds the categories that I want to link to my 'product' table.

 

I've looked at: http://www.sqllessons.com/categories.html

 

as a starting point.

 

Is there a standardized structure of handling categories for products, services, items etc?

 

The structure needs to allow for categories having parents and for subcategories.

 

E.g:          Food > Fish > Fresh Water Fish ......

 

Any ideas?

[fenway]

The sticky has some great resources on various hierarchical table design considerations.