SQL Database Design, recursive parent-child relationship?

Question

I want to represent a recursive parent-child relationship in a MySQL database. I want to create a category-subcategory relationship. A category can have N subcategories and each one of those can have N subcategories and so on. I was thinking of having a single category table with a foreign key pointing in it's self. Here is what i mean:

CREATE TABLE `category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `parent_category` int NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`parent_category`) REFERENCES `category` (`id`)
)

parent_category can be null if a category is a top-level category.

Is this the correct way of representing a relationship like this? Also are there other things i should take into consideration in my design (performance, queries...)?

Answer 1

It depends on how you want to use your data and wether you want to favor queries over update and insert. Your solution is called the adjacent list model and makes you data very easy to insert or update. Queries can be complicated if you have an unlimited depth of siblings, but again, it depends on how you plan to use this data structure. If the only thing you want is to show all the siblings of one node, this is ok. on the other hand, if you want to show the entire tree developed and do this in one query, you're in for a head ache.

Another solution would be to use a concatenated string representing your hierarchy. For example :

1. Europe 1.1 France 1.1.1 Paris 1.1.2 Marseille
2. America 2.1 United States of America 2.1.1 Washington

The DDL would be something like this :

   CREATE TABLE `category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `link` varchar(30) NOT NULL DEFAULT '.',
  PRIMARY KEY (`id`),
)

This data structure makes your queries easier, but updates are slower

Another solution is the nested set model where you register the id of the node on the right and left of your current node. It's the most efficient structure for queries, but makes insert and update harder.

http://en.wikipedia.org/wiki/Nested_set_model

I recommend you Joe Celko's book on trees and hierarchies