Hierarchical filtering in a relational database

Question

I have a bunch of items in my program that belong to a certain category. I would like to return only those items that belong to this category. The problem is that categories can have parent categories. For example, let's say there is a category “Material” with a child category “Food” with a child category “Fruits”. I have items, an apple, a pear, chocolate and a computer.

If I want to display all the fruits, it is easy to query the database with the WHERE clause item.category = FRUIT_ID. However, if I want all the products to be included, I need a way to get the benefits there too.

I know that some databases, such as Oracle, have the concept of recursive queries, and this may be the right solution, but I don't have much experience with hierarchical data, and I'm looking for general suggestions. Suppose I have unlimited control over the database schema, the category tree works, maybe only 5 categories, and I need it to be as ridiculously fast as possible.

Answer 1

Look at the adjacency list model - it is not perfect (it updates very slowly), but in some situations (hierarchical queries), this is a great idea, especially for problems like yours.

Answer 2

, SQL. .

Answer 3

, , , , .

, , IN

Answer 4

- . , , . , - , - . , where category='food'.

, , - where category='food' or category='fruit'.

Answer 5

I think your database schema is pretty good, but the implementation of this search really depends on your specific DBMS. Many of them have ways to do this recursion. One example I can think of is SQL Server support Common table expressions , which are a lightning fast alternative to these nasty cursors.

If you specify which RDBMS you are using, you can get more specific answers.