There is a product that is recorded in a table and consists of other products that are in this same table, while these products consist of third products that are in the same table, and they, for example, consist of materials that must also be in this table .

  • Not all DBMS support hierarchical queries. It is better to clarify which DBMS is of interest. - 4per
  • 3
    It may be worth creating an additional table with two numeric fields: child - the product id, parent - the parent id (component, spare parts). For one product several entries with one child and different parent of which the product consists. - Visman
  • Thanks for the quick response. I will make two ID fields. The first will be the unique key of the node, the second - the key of this node to the key of its assembly. Long did not reach how it works. - Valentine
  • There are several ways to store hierarchical structures in a database. For example, four ways are described in this article. Choose the most suitable. - Alexander Petrov

2 answers 2

In order not to limit ourselves to anything, let's make two tables. One table of products / materials products. With her everything is clear and true.
Another table of components. This table has two links to the product table. product_id indicates a product in which or for the production of which another product / material is used, indicated by the component_id field.
Thus it is possible to assemble products from several components. And at the same time, the same product / material can be used to produce several other products.

 create table products ( id int primary key, ... ); create table components ( id int primary key, product_id int foreign key references products(id), component_id int foregin key references products(id), ... );

    Store as ordinary wood. You already have the ID, just add to the ParentId table.

    Now fill the ParentId with the children.

    If ParentId is null, then this is a top-level element, i.e. product name. All the others are its component parts.