Item composition of the same SQL table

Navigation
0

Well, I started studying MySQL recently and I found a question I could not solve: An ITEM table has the columns (PK) cd_item, nm_item, ds_item, cd_type_item, the problem in the case is that an item can be formed by the composition of 2 or 3 items or simply be unique (not composed of anything), the purpose of it is to select from the bank the item and its recipe (composition made), but I do not know how to solve this, I appreciate the help.

    
asked by anonymous 13.12.2017 / 04:31

1 answer

0

I had to do something similar, I did this: I have a produto table, where all product information is stored, these are just fake information. 

CREATE TABLE IF NOT EXISTS 'produto' (
  'produto_id' INT NOT NULL AUTO_INCREMENT,
  'descricao' VARCHAR(45) NULL,
  'quantidade' INT NULL,
  PRIMARY KEY ('produto_id'))
ENGINE = InnoDB;

Here we have a table of composicao where we will refer to the table produto 2 times where produto_id will be the id of the main product and materia_id will receive the id of the product that forms its composition. 

CREATE TABLE IF NOT EXISTS 'composicao' (
  'composicao_id' INT NOT NULL AUTO_INCREMENT,
  'produto_id' INT NOT NULL,
  'materia_id' INT NOT NULL,
  PRIMARY KEY ('composicao_id'),
  INDEX 'fk_composicao_produto_idx' ('produto_id' ASC),
  INDEX 'fk_composicao_produto1_idx' ('materia_id' ASC),
  CONSTRAINT 'fk_composicao_produto'
    FOREIGN KEY ('produto_id')
    REFERENCES 'forum'.'produto' ('produto_id')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_composicao_produto1'
    FOREIGN KEY ('materia_id')
    REFERENCES 'produto' ('produto_id')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

In this INSERT I will give an example of 4 products, 3 that have no composition (Wood, Screw and Nail) and one that will have composition that is the Wooden Cart. 

INSERT INTO produto(descricao, quantidade) VALUES('Madeira', 1),('Parafuso', 1),('Prego',1),('Carrinho de Madeira', 1);

Here we will insert the composition of the wooden cart (Wood and Prego) 

INSERT INTO composicao(produto_id, materia_id) VALUES(4,1),(4,3);

And finally the% w / w of the products. 

SELECT
    p.*,
    GROUP_CONCAT(pc.descricao SEPARATOR ',') AS composicao
FROM produto p
LEFT JOIN composicao c
ON p.produto_id = c.produto_id
LEFT JOIN produto pc
ON pc.produto_id = c.materia_id
GROUP BY p.produto_id;

With the result that:

    
13.12.2017 / 15:37
Add 2 arrays with equal indexes JPA references an unknown entity