Hello, I ran into the problem of designing a database.

The problem is that I have an entity, such as a product. This item has nested attributes, i.e. attributes are dependent. Outwardly, it looks like this:

Product

  • Attribute # 1
    • By attribute number 1
    • Under the attribute number 2
      • Under the attribute number 1
  • Attribute number 2
  • Attribute number 3

How do I store this tree dependency in MySQL? The user can request Under the Attribute number 1 and I must issue all entities that have Under the Attribute number 1 at the same time show the entire tree of parents. How can I do that? With reference books? I read about the Closure Table, do not quite understand whether it will help me or not.

Nesting levels can be 3 and 5 and 7, but not more than 10. Thanks for the answers.

  • Read Nested Sets , Materialized Path - vp_arth

1 answer 1

SQL pseudocode for clarity

There are products with ID and other descriptions

 create table Wares( id_ware INT PRIMARY KEY, .... )

Here attributes with ID and other descriptions

 create table Attributes( id_attribute INT PRIMARY KEY, .... )

There is a binding of goods to the first-level attributes

 create table WareToAttributeRelations( id_ware INT FOREIGN KEY TO (Wares:id_ware), id_attribute INT FOREIGN KEY TO (Attributes:id_attribute), )

Everything is the attribute tree

 create table AttributToAttributeRelations( id_attribute_parent INT FOREIGN KEY TO (Attributes:id_attribute), id_attribute_child INT FOREIGN KEY TO (Attributes:id_attribute), )
  • it is more than a tree. Here you can portray any directed graph. - vp_arth