What is the best way to store a multi-valued item

Question

When issuing a NF-e it is possible to insert up to 990 products/items per document . Each product has several attributes. What would be the best way to store and link each product, and its respective attributes, to an NF-e?

I currently have the following structure:

Table nfe:

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_nfe` int(11) DEFAULT NULL,
  `id_emitente` varchar(255) DEFAULT NULL,
...

Table tributos_prod:

`id` int(11) NOT NULL AUTO_INCREMENT,
`id_nfe` int(11) NOT NULL,
`codigo` varchar(255) DEFAULT NULL,
...,
KEY `fk_nfe` (`id_nfe`),
CONSTRAINT `fk_nfe` FOREIGN KEY (`id_nfe`) REFERENCES `nfe` (`id`)

Problem: as each product/item will have a unique ID, there would be "infinite" table growth tributos_prod, making a maintainable control impossible, because the Authorize issuance of an NF-e I can remove all products linked to this, or even by simply removing a single item.

Answer 1

in the item table, make a composite key, being:

nfe (pk=id) (unique= numero, serie, modelo, emitente)
id | numero | serie | modelo | emitente | data_emissao | chave | destinatario | ...



nfe_itens (pk=item,nfe_id) (fk=nf_id)
item | nf_id | produto | valor | quantidade | ...

where the item of nfe_items is sequential but not AUTO_INCREMENT so each nfe starts the sequence again, item 1, item 2, item 3... so on and on

There are situations of using the same table to register a Care, or Guide to a health plan for example, in these cases, would recommend a key composed of 3 columns, because the items may vary between products / services / medicines / among others, so this tipo would be the 3rd column in the primary key composed of the table.