All geek questions in one place

SQL Database Design, a recursive parent-child relationship?

I want to introduce 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 of them can have N subcategories, and so on. I was thinking of having one category table with a foreign key pointing to it. 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 the category is a top-level category.

Is this the right way to represent such a relationship? Also, are there other things that I should consider in my design (performance, queries ...)?

+5
source share
1 answer

It depends on how you want to use your data and whether you want to support requests for updates and insertions. Your solution is called an adjacent list model and allows you to insert or update data very easily. Queries can be tricky if you have unlimited siblings depth, but again, it depends on how you plan to use this data structure. If you only want to show all the siblings of one node, that's fine. on the other hand, if you want to show the whole tree designed and do it in one query, you have a headache.

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

  • Europe 1.1 France 1.1.1 Paris 1.1.2 Marseille
  • America 2.1 United States 2.1.1 Washington

DDL will 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 simplifies your queries, but updates are slower

Another solution is a nested set model in which you register the node identifier to the right and left of the current node. This is the most efficient query structure, but makes it more difficult to insert and update.

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

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

+4
source

Source: https://habr.com/ru/post/1201477/

More articles:


All Articles